-- Replaces the MOD (Manager on Duty) report's hardcoded checklist fields
-- with an admin-configurable structure: sections group related fields,
-- fields define what gets asked (and how), and report_values holds what
-- was actually submitted for a given report -- proper structured storage
-- instead of the single JSON blob the old process_mod.php used
-- (maintenance_defects_log).
--
-- report_date / manager_name / shift_timeline stay as fixed columns on
-- mod_reports (they're header/metadata, not checklist rows). Everything
-- that WAS a checklist item -- personnel roll call, vacant rooms, generator
-- runtimes/fuel, tank levels, the front-office verification toggles -- gets
-- seeded as default rows here, so existing functionality carries over
-- exactly, and superadmin can add more from the same place going forward.
--
-- Safe to run more than once.

CREATE TABLE IF NOT EXISTS `mod_field_sections` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `icon` VARCHAR(10) DEFAULT NULL,
    `display_order` INT(11) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mod_fields` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `section_id` INT(11) NOT NULL,
    `field_key` VARCHAR(100) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `field_type` ENUM('text','number','textarea','yesno') NOT NULL DEFAULT 'text',
    `placeholder` VARCHAR(255) DEFAULT NULL,
    `is_required` TINYINT(1) NOT NULL DEFAULT 0,
    `display_order` INT(11) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_section_field` (`section_id`, `field_key`),
    CONSTRAINT `fk_mod_field_section` FOREIGN KEY (`section_id`) REFERENCES `mod_field_sections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mod_report_values` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `report_id` INT(11) NOT NULL,
    `field_id` INT(11) NOT NULL,
    `value` TEXT DEFAULT NULL,
    `note` TEXT DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_report_field` (`report_id`, `field_id`),
    CONSTRAINT `fk_mrv_report` FOREIGN KEY (`report_id`) REFERENCES `mod_reports` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mrv_field` FOREIGN KEY (`field_id`) REFERENCES `mod_fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the two existing sections + their fields, matching what the old
-- hardcoded form asked for. Guarded so re-running this migration never
-- creates duplicates.
INSERT INTO `mod_field_sections` (`name`, `icon`, `display_order`)
SELECT 'Front Office & Reception', '🛎️', 1
WHERE NOT EXISTS (SELECT 1 FROM `mod_field_sections` WHERE `name` = 'Front Office & Reception');

INSERT INTO `mod_field_sections` (`name`, `icon`, `display_order`)
SELECT 'Infrastructure & Utilities', '🔧', 2
WHERE NOT EXISTS (SELECT 1 FROM `mod_field_sections` WHERE `name` = 'Infrastructure & Utilities');

SET @fo_section := (SELECT id FROM `mod_field_sections` WHERE `name` = 'Front Office & Reception' LIMIT 1);
SET @infra_section := (SELECT id FROM `mod_field_sections` WHERE `name` = 'Infrastructure & Utilities' LIMIT 1);

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @fo_section, 'roll_call', 'Personnel Roll Call', 'text', 'e.g. 4 Persons', 1) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @fo_section AND `field_key` = 'roll_call');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `display_order`)
SELECT * FROM (SELECT @fo_section, 'arrival_verified', 'Expected Arrival / VIP Departure / Room Position verified?', 'yesno', 2) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @fo_section AND `field_key` = 'arrival_verified');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `display_order`)
SELECT * FROM (SELECT @fo_section, 'long_stay_verified', 'Number of Long Stay Guests mapped accurately?', 'yesno', 3) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @fo_section AND `field_key` = 'long_stay_verified');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @fo_section, 'vacant_rooms', 'Vacant Rooms List', 'textarea', 'List room numbers', 4) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @fo_section AND `field_key` = 'vacant_rooms');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'gen1_runtime', 'Generator 1 Runtime', 'text', 'e.g. 6PM - 6AM', 1) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'gen1_runtime');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'gen1_fuel', 'Generator 1 Fuel Level', 'text', 'e.g. 3/4 Tank', 2) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'gen1_fuel');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'gen2_runtime', 'Generator 2 Runtime', 'text', 'e.g. 6PM - 6AM', 3) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'gen2_runtime');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'gen2_fuel', 'Generator 2 Fuel Level', 'text', 'e.g. 3/4 Tank', 4) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'gen2_fuel');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'surface_tank', 'Surface Tank Level', 'text', 'e.g. 80%', 5) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'surface_tank');

INSERT INTO `mod_fields` (`section_id`, `field_key`, `label`, `field_type`, `placeholder`, `display_order`)
SELECT * FROM (SELECT @infra_section, 'underground_tank', 'Underground Tank Level', 'text', 'e.g. 60%', 6) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `mod_fields` WHERE `section_id` = @infra_section AND `field_key` = 'underground_tank');