-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for praksesprojekts
CREATE DATABASE IF NOT EXISTS `praksesprojekts` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `praksesprojekts`;

-- Dumping structure for table praksesprojekts.companies
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `address` text NOT NULL,
  `created_at` date DEFAULT NULL,
  `updated_at` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table praksesprojekts.companies: ~2 rows (approximately)
INSERT INTO `companies` (`id`, `name`, `address`, `created_at`, `updated_at`) VALUES
	(2, 'ZED', 'Bauska', NULL, NULL),
	(3, 'BCA', 'Jelgava, Svētes iela 8', NULL, NULL);

-- Dumping structure for procedure praksesprojekts.create_pass
DELIMITER //
CREATE PROCEDURE `create_pass`(
	IN `p_name` TEXT,
	IN `p_company_id` INT,
	IN `p_phone` TEXT,
	IN `p_date` DATETIME,
	IN `p_remaining` INT,
	IN `p_PIN` INT,
	IN `p_QR` INT,
	IN `p_pass_id` INT
)
BEGIN
    -- Generē PIN un QR
    SET p_PIN = FLOOR(RAND() * 1000000);
    SET p_QR = FLOOR(RAND() * 10000000000);
    
    -- Pievieno caurlaidi
    INSERT INTO passes (name, PIN, QR, company_id, phone, date, remaining)
    VALUES (p_name, p_PIN, p_QR, p_company_id, p_phone, p_date, p_remaining);
    
    SET p_pass_id = LAST_INSERT_ID();
END//
DELIMITER ;

-- Dumping structure for function praksesprojekts.decrease_remaining
DELIMITER //
CREATE FUNCTION `decrease_remaining`(`id` INT
) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
BEGIN
    DECLARE result_message VARCHAR(255);
    
    -- Pārbauda, vai caurlaide ar norādīto ID eksistē
    IF EXISTS (SELECT 1 FROM passes WHERE id = id) THEN
        -- Samazina atlikušās apmeklēšanas reizes par 1
        UPDATE passes SET remaining = remaining - 1 WHERE id = id AND remaining > 0;
        
        SET result_message = CONCAT('Caurlaide ar ID ', id, ' veiksmīgi atjaunināta.');
    ELSE
        SET result_message = CONCAT('Caurlaide ar ID ', id, ' netika atrasta.');
    END IF;

    RETURN result_message;
END//
DELIMITER ;

-- Dumping structure for function praksesprojekts.delete_pass
DELIMITER //
CREATE FUNCTION `delete_pass`(`id` INT
) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
BEGIN
    DECLARE result_message VARCHAR(255);
    
    -- Pārbauda, vai ieraksts ar norādīto ID eksistē
    IF EXISTS (SELECT 1 FROM passes WHERE id = id) THEN
        DELETE FROM passes WHERE id = id LIMIT 1;
        SET result_message = CONCAT('Caurlaide ar ID ', id, ' veiksmīgi dzēsta.');
    ELSE
        SET result_message = CONCAT('Caurlaide ar ID ', id, ' netika atrasta.');
    END IF;

    RETURN result_message;
END//
DELIMITER ;

-- Dumping structure for table praksesprojekts.passes
CREATE TABLE IF NOT EXISTS `passes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `date` datetime NOT NULL,
  `PIN` int(11) NOT NULL DEFAULT 0,
  `remaining` int(11) NOT NULL DEFAULT 1,
  `phone` text NOT NULL,
  `company_id` int(11) NOT NULL DEFAULT 1,
  `QR` text NOT NULL,
  `created_by` int(11) DEFAULT 0,
  `checkin_at` int(11) DEFAULT 0,
  `created_at` int(11) DEFAULT 0,
  `updated_at` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table praksesprojekts.passes: ~4 rows (approximately)
INSERT INTO `passes` (`id`, `name`, `date`, `PIN`, `remaining`, `phone`, `company_id`, `QR`, `created_by`, `checkin_at`, `created_at`, `updated_at`) VALUES
	(34, 'pls', '2024-12-29 20:50:00', 652476, 6, '23456789', 2, '9589514052', 0, 0, 0, 0),
	(35, 'fdgfnhgj', '2024-12-29 20:57:00', 413948, 67, '+37126294502', 2, '2147483647', 0, 0, 0, 0),
	(36, 'fdgfnhgj', '2024-12-29 20:57:00', 24978, 67, '+37126294502', 2, '147227663', 0, 0, 0, 0),
	(37, 'isit', '2025-01-05 21:02:00', 229951, 7, '13242353', 2, '2147483647', 0, 0, 0, 0);

-- Dumping structure for table praksesprojekts.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `account_activation_hash` text DEFAULT NULL,
  `password` text NOT NULL,
  `remember_token` int(11) DEFAULT NULL,
  `created_at` date DEFAULT NULL,
  `updated_at` date DEFAULT NULL,
  `phone` text DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table praksesprojekts.users: ~0 rows (approximately)
INSERT INTO `users` (`id`, `name`, `email`, `account_activation_hash`, `password`, `remember_token`, `created_at`, `updated_at`, `phone`, `company_id`) VALUES
	(1, 'slava', 'slava@gmail.com', NULL, 'dasfadsfgsdg', NULL, NULL, NULL, '4236346', 1),
	(2, 'Slava', 'slava.hopper@gmail.com', NULL, '$2y$10$HBgMHy.HTWIG.ir0Lx9EbuJw7stSyqZaltwMWXvajpm4RXKf1786q', NULL, '2024-12-13', NULL, '+37126294502', NULL),
	(3, 'Slava', 'slava.hopper@gmail.com', 'cd0dd7b9a26f053168d7c01b07caa28d', '$2y$10$3wsoxZYWw8jkKrXGLb7O5O/uDheIuh8PdFvpSaeKo1gviT7r2bjxS', NULL, '2024-12-13', NULL, '+37126294502', NULL),
	(4, 'slava06060', 'young.royalc@gmail.com', NULL, '$2y$10$fWJk7tXTdnpCu2hziSn9c.1RNrniaJTun62afVGTpPoZaYx5o1A0i', NULL, '2024-12-13', NULL, '+37126294502', 2);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;