-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------



-- 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);
    
    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);
        
        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);
        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 ;

