-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------



-- Dumping database structure for oa_comm_mag
CREATE DATABASE IF NOT EXISTS `oa_comm_mag` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `oa_comm_mag`;

-- Dumping structure for table oa_comm_mag.com_type
CREATE TABLE IF NOT EXISTS `com_type` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `ord_type` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'Pasūtījuma tips/veids',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā saglabāti pasūtījumu tipi/veidi';

-- Dumping data for table oa_comm_mag.com_type: ~0 rows (approximately)

-- Dumping structure for procedure oa_comm_mag.display_info
DELIMITER //
CREATE PROCEDURE `display_info`(
	IN `order_id` INT
)
    COMMENT 'Procedūra, kura izvadīs pilnu informācijas paneli par konkrētu darba ierakstu, balstoties uz iegūto darba ID'
BEGIN
	DECLARE order_exists INT DEFAULT 0;		-- Mainīgais, lai pārliecinātos ka iegūts derīgs darba ID
	DECLARE gift JSON;							-- Atgriežamais mainīgais, ko apstrādās mājaslapa
	
	SELECT COUNT(*) INTO order_exists FROM orders WHERE orders.ID = order_id;		-- Pārbaudes mainīgajā tiek ievadīta informācija, ja tā tiek atrasta
	
	IF (order_exists IS NULL)			-- Ja pārbaudes mainīgajā nebija iegūta vērtība, tiek atgriezta kļūda
	THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Invalid order item.';
	END IF;
	
	-- Citādi tiek veikta izvdāmās informācijas saglabāšana JSON mainīgajā ērtākai apstrādei mājaslapā
	SELECT JSON_ARRAYAGG(
		JSON_OBJECT(
			'type', zeus.ord_type,
			'customer', hera.customer,
			'status', athena.title,
			'characters', hera.characters,
			'payment', hera.payment,
			'payment status', ares.title,
			'images', (SELECT JSON_ARRAYAGG(file_name)	-- Atsevišķa masīva izveide, ja darbam pievienoti vairāki attēli no lietotāja puses
                   FROM order_img ody
                   WHERE ody.order_id = hera.ID),
         'special notes', hera.special_notes,
         'references', hera.`references`
		)
	) INTO gift
	FROM orders hera
	JOIN com_type zeus ON zeus.ID = hera.type_id		-- Tiek pievienotas saistītās tabulas balstoties uz ārējām atslēgām
	JOIN order_status athena ON athena.ID = hera.status_id
	JOIN pay_status ares ON ares.ID = hera.paystatus_id
	JOIN order_img ody ON hera.ID = ody.order_id
	WHERE hera.ID = order_id;
		
	SELECT COALESCE(gift, JSON_ARRAY()) AS return_items;	-- Datu paka, ko atgriezīs procedūra, tālākai apstrādei mājaslapā
END//
DELIMITER ;

-- Dumping structure for table oa_comm_mag.form_instance
CREATE TABLE IF NOT EXISTS `form_instance` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `form_token` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'Formas tokens (izmantots publiskajā saitē)',
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'Formas nosaukums',
  `type_id` int NOT NULL DEFAULT '0' COMMENT 'Pasūtījuma tips formas instancei',
  `payment` int NOT NULL DEFAULT '0' COMMENT 'Pasūtījuma cena',
  `created_on` date NOT NULL COMMENT 'Kad formas instance izveidota',
  `expire_on` date NOT NULL COMMENT 'Kad formas instance vairs nebūs derīga',
  `user_id` int NOT NULL COMMENT 'Lietotāja ID, kas izveidojis specifisko formas instanci',
  `active_status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ACTIVE' COMMENT 'Norāda vai forma ir aktīva vai nē',
  PRIMARY KEY (`ID`),
  KEY `type_id` (`type_id`),
  KEY `user_id_here` (`user_id`),
  CONSTRAINT `type_id` FOREIGN KEY (`type_id`) REFERENCES `com_type` (`ID`),
  CONSTRAINT `user_id_here` FOREIGN KEY (`user_id`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā tiek uzglabāta informācija par formu instancēm (ņemot vērā, ka visas formas satura ziņā izmantos vienotu struktūru)';

-- Dumping data for table oa_comm_mag.form_instance: ~0 rows (approximately)

-- Dumping structure for procedure oa_comm_mag.load_items
DELIMITER //
CREATE PROCEDURE `load_items`(
	IN `user_ID` INT,
	IN `data_type` INT
)
    COMMENT 'Procedūra, kas atgriež JSON masīvu ar datu sarakstiem, balstoties uz lietotāja ID, nepieciešamo datu tipu (norāda, no kuras tabulas ņemt datus).'
BEGIN
	DECLARE user_exists INT DEFAULT 0;		-- Mainīgais, lai pārliecinātos par lietotāja esamību sistēmā
	DECLARE gift JSON;								-- Atgriežamais JSON mainīgais (šajā gadījumā būs masīva formā)
	
	SELECT COUNT(*) INTO user_exists FROM users WHERE id = user_ID;
	
	-- Tiek pārbaudīts, vai ir iegūta informācija par to, ka lietotāja ID eksistē, balstoties uz atgriezto rindu skaita (0 (nav ID) vai 1 (ir ID))
	IF (user_exists = 0) 
	THEN 						-- Ja lietotāja ID neeksistē, tad tiek izmesta SQL kļūda par to
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'Invalid user ID';
	END IF;
	
	-- Sadaļa, kurā, balstoties uz iegūto datu parametru, tiek sagatavoti izvadāmo datu masīvi - 1 (pasūtījumu saraksts) vai 2 (formu saraksts)
	IF (data_type = 1)
	THEN
		SELECT JSON_ARRAYAGG(
			JSON_OBJECT(		-- Masīva objekts, tā sastāvs un dati, kas to aizpilda
				'id', sun.ID,
				'type', moon.ord_type,
				'customer', sun.customer,
				'payment', cloud.title,
				'status', sky.title,
				'edited', sun.last_edited
			)
		) INTO gift
		FROM orders AS sun
		-- Tiek pievienotas citas izmantotās tabulas, balstoties uz to saistību ar oriģinālo tabulu
		JOIN com_type moon ON moon.id = sun.type_id
		JOIN pay_status cloud ON cloud.id = sun.paystatus_id
		JOIN order_status sky ON sky.id = sun.status_id
		WHERE sun.user_id = user_ID;
	
		ELSEIF (data_type = 2)
		THEN
			SELECT JSON_ARRAYAGG(
				JSON_OBJECT(			-- Masīva objekts, tā sastāvs un dati, kas to aizpilda
					'id', zeus.ID,
					'type', hera.com_type,
					'price', zeus.payment,
					'status', zeus.active_status
				)
			) INTO gift
			FROM form_instance zeus
			-- Tiek pievienotas citas izmantotās tabulas, balstoties uz to saistību ar oriģinālo tabulu
			JOIN com_type hera ON hera.ID = zeus.type_id
			WHERE zeus.user_id = user_ID;
			
		ELSE					-- Ja datu tips nav 1 vai 2, tad tiek izmesta SQL kļūda
			SIGNAL SQLSTATE '46000'
			SET MESSAGE_TEXT = 'Invalid item list type';
		END IF;
		
	SELECT COALESCE(gift, JSON_ARRAY()) AS return_items;	-- Datu paka, ko atgriezīs procedūra, tālākai apstrādei mājaslapā
END//
DELIMITER ;

-- Dumping structure for table oa_comm_mag.orders
CREATE TABLE IF NOT EXISTS `orders` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `type_id` int NOT NULL DEFAULT '0' COMMENT 'Pasūtījuma tips/veids',
  `customer` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Pasūtījuma veicējs (trešās puses klients)',
  `characters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Pasūtījumā attēlojamais tēls vai tēli (saites formātā)',
  `payment` int NOT NULL COMMENT 'Pasūtījuma cena',
  `paystatus_id` int NOT NULL COMMENT 'Maksājuma status',
  `last_edited` date NOT NULL COMMENT 'Datums, kad pēdējo reizi veiktas izmaiņas. Pēc noklusējuma - kad ieraksts izveidots',
  `special_notes` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Papildus pieraksti, ko klients vēlas norādīt',
  `status_id` int NOT NULL COMMENT 'Pasūtījuma statusa identifikators',
  `user_id` int NOT NULL COMMENT 'Pasūtījuma izpildītājs (lietotājs)',
  `references` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Saites uz atsauces attēliem (pozas, apģērbs, utt.)',
  PRIMARY KEY (`ID`),
  KEY `ordtype_id` (`type_id`),
  KEY `paystatus_id` (`paystatus_id`),
  KEY `status_id` (`status_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `ordtype_id` FOREIGN KEY (`type_id`) REFERENCES `com_type` (`ID`),
  CONSTRAINT `paystatus_id` FOREIGN KEY (`paystatus_id`) REFERENCES `pay_status` (`ID`),
  CONSTRAINT `status_id` FOREIGN KEY (`status_id`) REFERENCES `order_status` (`ID`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā saglabāta pasūtījumu informācija';

-- Dumping data for table oa_comm_mag.orders: ~0 rows (approximately)

-- Dumping structure for table oa_comm_mag.order_img
CREATE TABLE IF NOT EXISTS `order_img` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `order_id` int DEFAULT NULL COMMENT 'Pasūījums, pie kā piesaistīts attēls',
  `file_name` int DEFAULT NULL COMMENT 'Attēla nosaukums',
  `mime` int DEFAULT NULL COMMENT 'Attēla tips (png vai jpeg)',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā tiks uzglabāta informācija par pasūtījumiem pievienotajiem attēliem (tikai attēli, ko lietotājs manuāli pievieno ierakstiem).';

-- Dumping data for table oa_comm_mag.order_img: ~0 rows (approximately)

-- Dumping structure for table oa_comm_mag.order_status
CREATE TABLE IF NOT EXISTS `order_status` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'Statuss',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā saglabāti pasūtījumu statusi';

-- Dumping data for table oa_comm_mag.order_status: ~0 rows (approximately)
INSERT INTO `order_status` (`ID`, `title`) VALUES
	(1, 'NOT STARTED'),
	(2, 'STARTED'),
	(3, 'WAITING APPROVAL'),
	(4, 'FINISHED');

-- Dumping structure for table oa_comm_mag.pay_status
CREATE TABLE IF NOT EXISTS `pay_status` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Statuss',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā saglabāti maksājumu statusi';

-- Dumping data for table oa_comm_mag.pay_status: ~0 rows (approximately)
INSERT INTO `pay_status` (`ID`, `title`) VALUES
	(1, 'PAID'),
	(2, 'PARTIALLY PAID'),
	(3, 'UNPAID');

-- Dumping structure for table oa_comm_mag.users
CREATE TABLE IF NOT EXISTS `users` (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls identifikators',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Lietotāja e-pasta adrese',
  `passw_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Šifrēta lietotāja parole',
  `date_created` date NOT NULL COMMENT 'Lietotāja profila izveides datums',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'Lietotājvārds, kas piesaistīts e-pastam',
  `updated_on` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Pēdējās lietotājvārda maiņas datums',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Datu tabula, kurā atrodas dati par reģistrētiem lietotājiem.';

-- Dumping data for table oa_comm_mag.users: ~0 rows (approximately)
INSERT INTO `users` (`ID`, `email`, `passw_hash`, `date_created`, `username`, `updated_on`) VALUES
	(1, 'test@inbox.lv', '$2y$10$CWzZQfliYe66jK2ATlhl0OQKJp3bPwLYXfcI4N126c4A2qcsFkBBi', '2026-01-29', 'mace-race', '0000-00-00'),
	(2, 'test1@gmail.com', '$2y$10$UKvHI6V1R.KRzOy.3AJ6GeCOsBntuy45um/3qMtWbiJFoSVk.r.xq', '2026-01-29', 'hello', '0000-00-00');

