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

/*!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 caurlaidesapp
CREATE DATABASE IF NOT EXISTS `caurlaidesapp` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `caurlaidesapp`;

-- Dumping structure for table caurlaidesapp.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=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table caurlaidesapp.companies: ~2 rows (approximately)
INSERT INTO `companies` (`id`, `name`, `address`, `created_at`, `updated_at`) VALUES
	(6, 'QQQ', 'gdfgfdgh', '2024-12-02', '2024-12-09'),
	(9, 'DDD', 'v ananas', '2024-12-13', '2024-12-13');

-- Dumping structure for function caurlaidesapp.delete_funkc
DELIMITER //
CREATE FUNCTION `delete_funkc`(`id` INT,
	`pass_id` INT
) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
    DECLARE result_message VARCHAR(255);

    -- Attempt to delete the pass with the given ID
    DELETE FROM passes
    WHERE id = pass_id;

    -- Check if the row was actually deleted
    IF ROW_COUNT() > 0 THEN
        SET result_message = CONCAT('Pass with ID ', pass_id, ' has been successfully deleted.');
    ELSE
        SET result_message = CONCAT('Pass with ID ', pass_id, ' does not exist.');
    END IF;

    RETURN result_message;
END//
DELIMITER ;

-- Dumping structure for table caurlaidesapp.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=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table caurlaidesapp.passes: ~0 rows (approximately)
INSERT INTO `passes` (`id`, `name`, `date`, `PIN`, `remaining`, `phone`, `company_id`, `QR`, `created_by`, `checkin_at`, `created_at`, `updated_at`) VALUES
	(22, 'ghdcjhnsdfg', '2024-12-26 11:12:00', 829161, 5, '223445667', 6, '4136422482', 0, 0, 0, 0),
	(23, 'trgfrgtg', '2024-12-25 01:12:00', 835572, 4, '23456789', 6, '8313860762', 0, 0, 0, 0);

-- Dumping structure for function caurlaidesapp.remaining_funkc
DELIMITER //
CREATE FUNCTION `remaining_funkc`(`id` INT,
	`pass_id` INT
) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
    DETERMINISTIC
BEGIN
    DECLARE remaining_visits INT;
    DECLARE result_message VARCHAR(255);

    -- Ensure the SELECT query only returns one row
    SELECT remaining INTO remaining_visits
    FROM passes
    WHERE id = pass_id
    LIMIT 1;

    -- Handle scenarios based on the remaining visits
    IF remaining_visits IS NULL THEN
        SET result_message = CONCAT('Pass with ID ', pass_id, ' not found.');
    ELSEIF remaining_visits > 0 THEN
        UPDATE passes
        SET remaining = remaining - 1
        WHERE id = pass_id;
        SET result_message = CONCAT('Pass with ID ', pass_id, ' decremented. Remaining visits: ', remaining_visits - 1);
    ELSE
        SET result_message = CONCAT('Pass with ID ', pass_id, ' has no remaining visits.');
    END IF;

    RETURN result_message;
END//
DELIMITER ;

-- Dumping structure for table caurlaidesapp.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `email_verified_at` date 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=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table caurlaidesapp.users: ~15 rows (approximately)
INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `phone`, `company_id`) VALUES
	(3, 'frrt', 'rtretgrtgr@gmail.com', NULL, '$2y$10$XSxhlIZUBqz6zV7NcBI3WeAScYNIq8BK2F.bqhpkPpY2d7QUDbBUG', NULL, '2024-12-06', NULL, '2345678', 1),
	(4, 'frrt', 'rtretgrtgr@gmail.com', NULL, '$2y$10$orhojod2JKiRQ.Rg9IqiL.d/adBFvvflEWNOHcgk/jT1A5V7T0K4.', NULL, '2024-12-06', NULL, '2345678', 1),
	(5, 'frrt', 'rtretgrtgr@gmail.com', NULL, '$2y$10$JDgUsp8HZ8WgeDcsnDuRNOg2NXuBd58vgX5OthY54W6cdQuHFFnsi', NULL, '2024-12-06', NULL, '2345678', 1),
	(6, 'frrt', 'rtretgrtgr@gmail.com', NULL, '$2y$10$o6G7QVBcDqKVCkkDlLtQqu4kajj77ju/R7s.CfshnV7ZnwURATsH.', NULL, '2024-12-06', NULL, '2345678', 1),
	(8, 'frrt', 'slavalavadurka@gmail.com', NULL, '$2y$10$Xsxu3h6lAPvF9.uMu3vwvOK6xXxP80vj7/ejvy1AywG6lVbRRa8.W', NULL, '2024-12-06', NULL, 'r12345678', 2),
	(9, 'k,jm,', 'dghdfgs@gmail.com', NULL, '$2y$10$I5UbSmH/3/WJDrog79EvnO.UPbVUi74JsmTb3DGZFXK4eQuU4Phue', NULL, '2024-12-09', NULL, '12345678', 6),
	(10, 'jk,jhgykujk', 'slavalavadurka@gmail.com', NULL, '$2y$10$lsW/ynLoria1EKkzTqQy3uo.0KwSfNG0a4UV.Ziz4J6NDlRRD3pia', NULL, '2024-12-09', NULL, '12345678', 1),
	(11, 'hygh', 'laura.kokina123@gmail.com', NULL, '$2y$10$QI4tEjPaXQ5YYdPoVz74leNr9Saen4PJMg8vnfpBvDhyVIUL6ZBe6', NULL, '2024-12-09', NULL, '12345678', 1),
	(12, 'WWW', 'ernestsbalodis05@gmail.com', NULL, '$2y$10$4RwTNeSVjqQWIN5HP6gfbOISUBzNgmqmkMhHcPJu85mw2svA6bZ2u', NULL, '2024-12-09', NULL, '12345678', 1),
	(13, 'ujhj', 'rtretgrtgr@gmail.com', NULL, '$2y$10$X8EuutROBIDHlQ3R8eYXa.2WqyzwyAkHKDCWeHjMg0ELGrUui0KW2', NULL, '2024-12-09', NULL, '98765432', 1),
	(14, 'fvsdgv', 'nikinsh2008@gmail.com', NULL, '$2y$10$6pk1Uj38Tsu6JW.rvb7n7eFKfLkMSuNdMjFDvBbpPihjqu3Git5Oe', NULL, '2024-12-09', NULL, '23456789', 1),
	(15, 'ftdygdfgy', 'laura.kokina123@gmail.com', NULL, '$2y$10$IGugHD0crGin9PBvJx5xP.glGdKDuoZiryBOCIP69PrRMJXCfe7XG', NULL, '2024-12-09', NULL, 'yrtfydr5yrdt', 1),
	(16, 'hjjnyhj', 'rtretgrtgr@gmail.com', NULL, '$2y$10$2YsilELTg.DsKJVdXZy8sOb770or6S6h3JajAd1hHqjOOg/kh6UoK', NULL, '2024-12-09', NULL, '23456789', 2),
	(17, 'sawa', 'sawa123@gmail.com', NULL, '$2y$10$.3KVKLZO855GfovFYuyFgelsSJDcHqay.5y9YOJHFnzUIxnJL.H3.', NULL, '2024-12-09', NULL, '23456789', 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) */;