-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.10.0.7000
-- --------------------------------------------------------



-- Dumping database structure for caurlaides_410
CREATE DATABASE IF NOT EXISTS `caurlaides_410` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `caurlaides_410`;

-- Dumping structure for table caurlaides_410.companies
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga - unikāls uzņēmuma identifikators',
  `name` varchar(100) NOT NULL COMMENT 'Uzņēmuma nosaukums',
  `address` varchar(150) NOT NULL COMMENT 'Uzņēmuma adrese',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT 'Ieraksta izveidošanas datums un laiks',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Ieraksta pēdējās izmaiņas datums un laiks',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Uzņēmumu dati - glabā informāciju par reģistrētajiem uzņēmumiem sistēmā';

-- Dumping data for table caurlaides_410.companies: ~5 rows (approximately)
INSERT INTO `companies` (`id`, `name`, `address`, `created_at`, `updated_at`) VALUES
	(14, 'test', 'test333', '2026-01-27 09:00:14', '2026-01-27 18:57:17'),
	(15, 'test2', 'test5', '2026-01-27 18:30:12', '2026-02-18 13:34:35'),
	(16, 'test56', 'test3', '2026-01-27 18:35:19', '2026-01-28 10:18:49'),
	(17, 'test5', 'test5', '2026-01-27 18:57:06', '2026-01-27 18:57:06'),
	(18, 'gfvfgvb', 'fgbfccbcdfb', '2026-01-28 10:20:30', '2026-01-28 10:20:30');

-- Dumping structure for table caurlaides_410.passes
CREATE TABLE IF NOT EXISTS `passes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga - unikāls caurlaides identifikators',
  `name` varchar(50) NOT NULL COMMENT 'Viesa vārds un uzvārds',
  `email` varchar(50) NOT NULL COMMENT 'Viesa e-pasta adrese',
  `date` datetime NOT NULL COMMENT 'Plānotais vizītes datums un laiks',
  `PIN` int(6) unsigned NOT NULL COMMENT '6-ciparu PIN kods rezerves identificēšanai',
  `remaining` int(2) unsigned NOT NULL DEFAULT 1 COMMENT 'Atlikušo ieeju skaits',
  `phone` varchar(20) NOT NULL COMMENT 'Viesa telefona numurs',
  `company_id` int(10) unsigned NOT NULL COMMENT 'Ārējā atslēga uz companies.id - uzņēmums, kuru viesis apmeklē',
  `QR` varchar(20) NOT NULL COMMENT '10-ciparu QR kods caurlaides identificēšanai',
  `created_by` int(10) unsigned DEFAULT NULL COMMENT 'Ārējā atslēga uz users.id - lietotājs, kurš izveidoja caurlaidi',
  `checkin_at` timestamp NULL DEFAULT NULL COMMENT 'Check-in datums un laiks (kad viesis ienāca)',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT 'Caurlaides izveidošanas datums un laiks',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Caurlaides pēdējās izmaiņas datums un laiks',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_qr` (`QR`),
  UNIQUE KEY `uk_pin` (`PIN`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_company_id` (`company_id`),
  KEY `idx_date` (`date`),
  KEY `idx_email` (`email`),
  CONSTRAINT `fk_passes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_passes_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Caurlaižu dati - viesa caurlaides ar QR kodiem un PIN kodiem';

-- Dumping data for table caurlaides_410.passes: ~6 rows (approximately)
INSERT INTO `passes` (`id`, `name`, `email`, `date`, `PIN`, `remaining`, `phone`, `company_id`, `QR`, `created_by`, `checkin_at`, `created_at`, `updated_at`) VALUES
	(39, 'test', 'darja.jeremejeva@gmail.com', '2026-01-28 11:03:00', 881897, 2, '22222444', 14, '3312770710', 55, '2026-01-27 09:06:12', '2026-01-27 09:03:56', '2026-01-27 09:06:12'),
	(40, 'test2', 'oi2en@virgilian.com', '2026-01-30 20:32:00', 568983, 4, '23333333', 15, '2069398801', 60, '2026-01-27 18:33:41', '2026-01-27 18:32:41', '2026-01-27 19:51:58'),
	(41, 'test3', 'mcc0l@virgilian.com', '2026-01-31 20:37:00', 530397, 2, '21111111', 16, '6517477867', 61, NULL, '2026-01-27 18:37:40', '2026-01-27 18:37:40'),
	(42, 'rgtrtgrg', 'kogosa9711@gamepec.com', '2026-01-30 12:39:00', 251200, 13, '23456784', 16, '4749888878', 62, '2026-01-28 16:27:35', '2026-01-28 10:39:43', '2026-01-28 16:27:35'),
	(43, 'test', 'caurlaidestest@outlook.com', '2026-02-12 18:07:00', 535191, 2, '23456789', 15, '8816314776', 60, '2026-02-04 16:09:13', '2026-02-04 16:08:28', '2026-02-04 16:09:13'),
	(44, 'outlook', 'caurlaidestest@outlook.com', '2026-02-19 14:01:00', 902559, 2, '23456789', 16, '0662798233', 62, '2026-02-17 12:03:54', '2026-02-17 12:01:26', '2026-02-18 12:05:32');

-- Dumping structure for table caurlaides_410.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga - unikāls lietotāja identifikators',
  `name` varchar(50) NOT NULL COMMENT 'Lietotāja vārds un uzvārds',
  `email` varchar(50) NOT NULL COMMENT 'Lietotāja e-pasta adrese (unikāla)',
  `role` enum('admin','uznemums','sargs') NOT NULL COMMENT 'Lietotāja loma sistēmā: admin=administrators, uznemums=uzņēmuma darbinieks, sargs=drošības darbinieks',
  `account_activation_hash` varchar(64) DEFAULT NULL COMMENT 'Aktivizācijas hash konta apstiprināšanai caur e-pastu',
  `password` varchar(255) NOT NULL COMMENT 'Hashēta parole (bcrypt algoritms)',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT 'Konta izveidošanas datums un laiks',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Konta pēdējās izmaiņas datums un laiks',
  `phone` varchar(20) NOT NULL COMMENT 'Lietotāja telefona numurs',
  `company_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Uzņēmuma ID (0=administrators, kas strādā ar visiem uzņēmumiem)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_company_id` (`company_id`),
  KEY `idx_account_activation` (`account_activation_hash`),
  KEY `idx_role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Lietotāju dati - administrators, uzņēmuma darbinieki ';

-- Dumping data for table caurlaides_410.users: ~11 rows (approximately)
INSERT INTO `users` (`id`, `name`, `email`, `role`, `account_activation_hash`, `password`, `created_at`, `updated_at`, `phone`, `company_id`) VALUES
	(54, 'test2', 'geyesic359@ixunbo.com', 'admin', NULL, '$2y$10$3bY4wC23xIqeogbSsczyl.tG/Cq5BJWEbw8MySG5mfpna7hB.pm2C', '2026-01-27 08:58:29', '2026-01-27 09:01:48', '22222222', 0),
	(55, 'test567', 'test@gmail.com', 'uznemums', NULL, '$2y$10$l5/NPOZTnXjxsG4YxTvyk.cfza570yjo6B8V4fgaGoP6KBhCLDPMq', '2026-01-27 09:00:46', '2026-01-27 18:58:17', '23333333', 14),
	(56, 'ti', 'resefi4956@ixunbo.com', 'admin', NULL, '$2y$10$5MKHRxh0stSYBXwvpMkHy.MYoTRhC24mP9AdygMjCOW0kFNd9SmJG', '2026-01-27 09:10:22', '2026-01-27 09:12:01', '44444444', 0),
	(58, 'krutiska', 'mahohah993@juhxs.com', 'admin', NULL, '$2y$10$oCvf7crQ93Ix4eRYM7a85ujToFZFbM.bHSVKu7a5aKqXQOgA9de8q', '2026-01-27 09:17:45', '2026-01-27 09:21:17', '24656890', 0),
	(59, 'test2', '8zddi@virgilian.com', 'admin', NULL, '$2y$10$qySH.zaa7ef5ZDxfvMkhqe83oHXh86EloSerLUcY4uOXfKBSF4I4q', '2026-01-27 18:29:28', '2026-01-27 18:29:45', '23344567', 0),
	(60, 'test2', 'test2@gmail.com', 'uznemums', NULL, '$2y$10$x9pK.LYgxt29Lpxq5etpGeta3LwAoDsEcXsuYEQzcdxPzmGSLIYMe', '2026-01-27 18:31:30', '2026-01-27 18:31:30', '23333333', 15),
	(61, 'test4', 'test4@gmail.com', 'uznemums', NULL, '$2y$10$9I5LenB2o7yULmCq/bxdW.gNLNDvbzckK/vcp6EhG1ppujbW.QXWe', '2026-01-27 18:36:11', '2026-01-27 18:36:11', '26666666', 16),
	(62, 'example', 'example@gmail.com', 'uznemums', NULL, '$2y$10$3GiS8T9wIk1Xsg2lz7BItOdZ678xzZSbnOwZIbIWszOItzmnDzUBi', '2026-01-28 10:11:31', '2026-01-28 10:11:41', '27777776', 16),
	(63, 'example3', 'example2@gmail.com', 'uznemums', NULL, '$2y$10$w8mZzfGSjGn0XKlZoEEGaOMxAkms.dgpnI125AmUdiMRLNQpmMGZy', '2026-01-28 10:17:42', '2026-01-28 10:17:59', '23453412', 16),
	(64, 'vfdvgfdvcfdcgv', 'rfgvgfbgfb@gmail.com', 'uznemums', NULL, '$2y$10$DZr826OQ7.K1todQ5vURPuB4MTJ9PD3zNip40s0dZ1Xd/.67ZtmEa', '2026-01-28 10:20:14', '2026-01-28 10:20:14', '23456789', 16),
	(65, 'admin', 'datanaw373@fentaoba.com', 'admin', NULL, '$2y$10$ppmax9lmWeknU0xaTKFS1eq7NKH90y5iLA1kgTpfQr9bdmJx42ZqG', '2026-02-17 12:15:06', '2026-02-17 12:15:28', '23334444', 0);

