-- Digital App TZ dynamic forms/admin migration
-- Run once on the existing blmtech1_digital_app database.
-- Recommended: take a backup before import.

SET NAMES utf8mb4;
SET time_zone = '+03:00';

ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `role` varchar(20) NOT NULL DEFAULT 'user' AFTER `password`;

-- Existing account in the supplied database is the current administrator.
UPDATE `users` SET `role`='admin' WHERE `id`=1;

ALTER TABLE `codes`
  ADD UNIQUE KEY `uq_codes_code` (`code`);

CREATE TABLE IF NOT EXISTS `admin_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `token_hash` char(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admin_session_hash` (`token_hash`),
  KEY `idx_admin_session_user` (`user_id`),
  KEY `idx_admin_session_expiry` (`expires_at`),
  CONSTRAINT `fk_admin_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `forms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(180) NOT NULL,
  `slug` varchar(220) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `paper_size` varchar(20) NOT NULL DEFAULT 'A4',
  `orientation` enum('portrait','landscape') NOT NULL DEFAULT 'portrait',
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_forms_slug` (`slug`),
  KEY `idx_forms_status` (`status`),
  KEY `idx_forms_created_by` (`created_by`),
  CONSTRAINT `fk_forms_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `form_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `form_id` int NOT NULL,
  `page_index` int NOT NULL DEFAULT 0,
  `file_path` varchar(500) NOT NULL,
  `original_name` varchar(255) DEFAULT NULL,
  `width_px` int DEFAULT NULL,
  `height_px` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_template_form_page` (`form_id`,`page_index`),
  CONSTRAINT `fk_templates_form` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `form_fields` (
  `id` int NOT NULL AUTO_INCREMENT,
  `form_id` int NOT NULL,
  `field_key` varchar(120) NOT NULL,
  `label` varchar(180) NOT NULL,
  `field_type` enum('text','multiline','number','date','select','image','signature') NOT NULL DEFAULT 'text',
  `placeholder` varchar(255) DEFAULT NULL,
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `options_json` text DEFAULT NULL,
  `page_index` int NOT NULL DEFAULT 0,
  `pos_x` decimal(9,6) NOT NULL DEFAULT 0.100000,
  `pos_y` decimal(9,6) NOT NULL DEFAULT 0.100000,
  `width` decimal(9,6) NOT NULL DEFAULT 0.350000,
  `height` decimal(9,6) NOT NULL DEFAULT 0.060000,
  `font_size` decimal(6,2) NOT NULL DEFAULT 14.00,
  `font_weight` enum('normal','bold') NOT NULL DEFAULT 'normal',
  `text_align` enum('left','center','right') NOT NULL DEFAULT 'left',
  `text_color` char(7) NOT NULL DEFAULT '#000000',
  `uppercase` tinyint(1) NOT NULL DEFAULT 0,
  `remove_background` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_form_field_key` (`form_id`,`field_key`),
  KEY `idx_form_field_page_order` (`form_id`,`page_index`,`sort_order`),
  CONSTRAINT `fk_fields_form` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `generation_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `code_id` int NOT NULL,
  `form_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_generation_code` (`code_id`),
  KEY `idx_generation_form` (`form_id`),
  KEY `idx_generation_created` (`created_at`),
  CONSTRAINT `fk_generation_code` FOREIGN KEY (`code_id`) REFERENCES `codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_generation_form` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;