-- =============================================================================
-- Manager on Duty (MOD) checklist module -- full rebuild.
--
-- The old mod_reports table had a fixed set of ~12 columns, so it could only
-- ever capture a tiny fraction of the real checklist (which has ~130 items
-- across 18 sections). This replaces that rigid design with a proper
-- template + submission model:
--
--   mod_checklist_sections / mod_checklist_items  -- the editable TEMPLATE.
--     An admin manages these directly (add, edit, reorder, deactivate,
--     delete) via checklist_settings.php -- this is what makes "each row in
--     the PDF something an admin can add/edit/remove".
--
--   mod_reports / mod_report_answers  -- actual SUBMITTED reports.
--     Each answer stores a SNAPSHOT of the section name, question text, and
--     response type at submission time (not just a foreign key to the
--     template item). That matters: if an admin later edits or deletes a
--     checklist item, every report submitted before that change stays
--     exactly as it was when the manager actually filled it in -- editing
--     the template can never silently rewrite history.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `mod_checklist_sections` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS `mod_checklist_items` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `section_id` INT(11) NOT NULL,
    `question` VARCHAR(500) NOT NULL,
    -- 'yes_no'    -> Yes/No toggle plus an optional comment field
    -- 'text'      -> comment/free-text field only, no Yes/No
    `response_type` ENUM('yes_no','text') NOT NULL DEFAULT 'yes_no',
    `placeholder` VARCHAR(255) DEFAULT NULL,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `section_id` (`section_id`),
    CONSTRAINT `mod_items_section_fk` FOREIGN KEY (`section_id`) REFERENCES `mod_checklist_sections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS `mod_reports` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `report_date` DATE NOT NULL,
    `manager_name` VARCHAR(150) NOT NULL,
    `shift_timeline` VARCHAR(100) DEFAULT NULL,
    `conclusion` TEXT DEFAULT NULL,
    `note` TEXT DEFAULT NULL,
    `created_by` INT(11) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `report_date` (`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS `mod_report_answers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `mod_report_id` INT(11) NOT NULL,
    `item_id` INT(11) DEFAULT NULL,
    `section_name_snapshot` VARCHAR(150) NOT NULL,
    `question_snapshot` VARCHAR(500) NOT NULL,
    `response_type_snapshot` ENUM('yes_no','text') NOT NULL,
    `answer_value` VARCHAR(10) DEFAULT NULL,
    `comment` TEXT DEFAULT NULL,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `mod_report_id` (`mod_report_id`),
    CONSTRAINT `mod_answers_report_fk` FOREIGN KEY (`mod_report_id`) REFERENCES `mod_reports` (`id`) ON DELETE CASCADE,
    CONSTRAINT `mod_answers_item_fk` FOREIGN KEY (`item_id`) REFERENCES `mod_checklist_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- =============================================================================
-- Seed data: every section and item transcribed from the actual paper
-- checklist (MOD_REPORT_6TH_SEPT_2026.pdf). Safe to run more than once --
-- only inserts if the sections table is currently empty.
-- =============================================================================

SET @seed_needed := (SELECT COUNT(*) FROM mod_checklist_sections);

-- Only proceed with seeding if this is a fresh install (no sections yet).
-- Using a stored procedure here since plain SQL can't conditionally skip a
-- whole block of INSERTs based on a runtime check.
DELIMITER $$
CREATE PROCEDURE seed_mod_checklist()
BEGIN
    DECLARE existing_count INT;
    SELECT COUNT(*) INTO existing_count FROM mod_checklist_sections;

    IF existing_count = 0 THEN

        INSERT INTO mod_checklist_sections (name, sort_order) VALUES
            ('Front Office/Reception', 1),
            ('F&B Outlet/Event Hall/Board Room/Public Area', 2),
            ('Restaurant/Lounge/Greetings', 3),
            ('Ambience', 4),
            ('Grooming', 5),
            ('Buffet Standard', 6),
            ('Rest Rooms (Lobby/Event/Pool/Training Room)', 7),
            ('Main Porch and Parking', 8),
            ('Lobby', 9),
            ('Fitness Centre/Swimming Pool', 10),
            ('Guest Floor/Corridor', 11),
            ('Kitchen', 12),
            ('Receiving Area', 13),
            ('Security Office', 14),
            ('Engineering/CCTV/Laundry/HK/GYM', 15),
            ('Staff Lockers', 16),
            ('Room Status', 17),
            ('Staff Canteen/Cafeteria', 18);

        -- ---------------------------------------------------------------
        -- Front Office/Reception
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Front Office/Reception');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Roll Call', 'yes_no', 'e.g. FRONT OFFICE - 3', 1),
            (@sec, 'Expected Arrival/VIP Departure/Room Position', 'text', 'e.g. ARRIVAL-22, DEPARTURES-5', 2),
            (@sec, 'Number of Long Stay Guest and their room numbers?', 'text', 'e.g. 3 - 208, 212, 310', 3),
            (@sec, 'Was Express check out Displayed/Tent Card?', 'yes_no', NULL, 4),
            (@sec, 'BWP Service promise tent card display?', 'yes_no', NULL, 5),
            (@sec, 'Early Express Breakfast?', 'yes_no', NULL, 6),
            (@sec, 'Were all clocks showing correct time', 'yes_no', NULL, 7),
            (@sec, 'Were all the staff aware of BW Rewards/Loyalty/Points', 'yes_no', NULL, 8),
            (@sec, 'Were all the POS working properly including international pay', 'yes_no', NULL, 9),
            (@sec, 'Was lost/found log maintained properly', 'yes_no', NULL, 10),
            (@sec, 'Were the staff aware about BWR Elite member', 'yes_no', NULL, 11),
            (@sec, 'Visa/Master/Amex card accepted here displayed?', 'yes_no', NULL, 12),
            (@sec, 'Dental Kit/Sewing Kit/Comb available on request?', 'yes_no', NULL, 13),
            (@sec, 'Was First Aid Box available with enough medicine/kit?', 'yes_no', NULL, 14),
            (@sec, 'Fax/Photocopy/Printing available?', 'yes_no', NULL, 15),
            (@sec, 'Any pending payment or unauthorised credit to customer', 'yes_no', NULL, 16);

        -- ---------------------------------------------------------------
        -- F&B Outlet/Event Hall/Board Room/Public Area
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'F&B Outlet/Event Hall/Board Room/Public Area');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Roll Call', 'yes_no', 'e.g. F&B - 6', 1),
            (@sec, 'Was the area clean?', 'yes_no', NULL, 2),
            (@sec, 'Were all lighting & P.A. in place?', 'yes_no', NULL, 3),
            (@sec, 'Were the chairs/furniture in position?', 'yes_no', NULL, 4),
            (@sec, 'Were toilets clean/checklist updated?', 'yes_no', NULL, 5),
            (@sec, 'AC on/off', 'yes_no', NULL, 6),
            (@sec, 'Were all door/window locked/open properly', 'yes_no', NULL, 7);

        -- ---------------------------------------------------------------
        -- Restaurant/Lounge/Greetings
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Restaurant/Lounge/Greetings');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the staff manning the gate/hostess desk?', 'yes_no', NULL, 1),
            (@sec, 'Was standard greeting in place?', 'yes_no', NULL, 2),
            (@sec, 'Was the staff courteous?', 'yes_no', NULL, 3),
            (@sec, 'Were the staff aware of VIP/reservation?', 'yes_no', NULL, 4),
            (@sec, 'Cocktail/Mocktail/Espresso/Cappuccino/Latte available?', 'yes_no', NULL, 5),
            (@sec, 'Were all the staff on duty aware of the menu', 'yes_no', NULL, 6),
            (@sec, 'Were restaurant/lounge set as per standard', 'yes_no', NULL, 7),
            (@sec, 'Was any unauthorised person/visitor in premise', 'yes_no', NULL, 8);

        -- ---------------------------------------------------------------
        -- Ambience
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Ambience');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the music soothing and volume level controlled?', 'yes_no', NULL, 1),
            (@sec, 'Were all the lights on as per standard?', 'yes_no', NULL, 2),
            (@sec, 'Was the floor free from crumbs?', 'yes_no', NULL, 3),
            (@sec, 'Was the area surrounding the tables free from clutter?', 'yes_no', NULL, 4),
            (@sec, 'Was the side stations/bain-marie free from clutter?', 'yes_no', NULL, 5),
            (@sec, 'Were the table layout neatly laid?', 'yes_no', NULL, 6),
            (@sec, 'Were all the furniture aligned properly?', 'yes_no', NULL, 7),
            (@sec, 'Was the bar clean from inside?', 'yes_no', NULL, 8),
            (@sec, 'Was the flower arrangement in place?', 'yes_no', NULL, 9),
            (@sec, 'Were the table tops clean?', 'yes_no', NULL, 10),
            (@sec, 'Were all the display bottles arranged neatly?', 'yes_no', NULL, 11),
            (@sec, 'Was the temperature correct?', 'yes_no', NULL, 12),
            (@sec, 'Were there any extra wires visible?', 'yes_no', NULL, 13),
            (@sec, 'Was the cutlery clean & polished?', 'yes_no', NULL, 14),
            (@sec, 'Was the napkin clean & creased?', 'yes_no', NULL, 15),
            (@sec, 'Was the LCD/TV channels working properly?', 'yes_no', NULL, 16);

        -- ---------------------------------------------------------------
        -- Grooming
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Grooming');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the uniform clean & ironed properly', 'yes_no', NULL, 1),
            (@sec, 'Were the shoes polished?', 'yes_no', NULL, 2),
            (@sec, 'Were the staff to welcome-look standards?', 'yes_no', NULL, 3),
            (@sec, 'Was the name badge appropriately pinned?', 'yes_no', NULL, 4),
            (@sec, 'Did any staff have strong body odor? If yes, name of staff', 'yes_no', 'Name of staff if applicable', 5);

        -- ---------------------------------------------------------------
        -- Buffet Standard
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Buffet Standard');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was it well maintained & properly laid?', 'yes_no', NULL, 1),
            (@sec, 'Were there any spillage/crumbs on the buffet?', 'yes_no', NULL, 2),
            (@sec, 'Was the buffet neatly tagged?', 'yes_no', NULL, 3),
            (@sec, 'Was the buffet continuously replenished?', 'yes_no', NULL, 4),
            (@sec, 'Was the buffet orientation conducted by staff?', 'yes_no', NULL, 5),
            (@sec, 'Were the ladles free from stains and food coating?', 'yes_no', NULL, 6),
            (@sec, 'Were the plates sufficiently stacked?', 'yes_no', NULL, 7),
            (@sec, 'Were the chafing dishes heated at correct temperature?', 'yes_no', NULL, 8),
            (@sec, 'Was the food hot and properly displayed?', 'yes_no', NULL, 9),
            (@sec, 'Guest feedback (breakfast meeting)', 'yes_no', NULL, 10);

        -- ---------------------------------------------------------------
        -- Rest Rooms (Lobby/Event/Pool/Training Room)
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Rest Rooms (Lobby/Event/Pool/Training Room)');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the area clean?', 'yes_no', NULL, 1),
            (@sec, 'Was the urinal, WC & wash basin clean?', 'yes_no', NULL, 2),
            (@sec, 'Were the amenities placed?', 'yes_no', NULL, 3),
            (@sec, 'Was it odorless?', 'yes_no', NULL, 4),
            (@sec, 'Level of lighting appropriate?', 'yes_no', 'Note any faults, e.g. which lights', 5),
            (@sec, 'Cleaning checklist updated?', 'yes_no', NULL, 6);

        -- ---------------------------------------------------------------
        -- Main Porch and Parking
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Main Porch and Parking');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was somebody manning the main porch', 'yes_no', NULL, 1),
            (@sec, 'Was parking area clean', 'yes_no', NULL, 2),
            (@sec, 'Were lights working properly', 'yes_no', NULL, 3),
            (@sec, 'Were all the ceiling lights working in order?', 'yes_no', NULL, 4),
            (@sec, 'Was the telephone in working condition', 'yes_no', NULL, 5),
            (@sec, 'Was the light at BWP Brand Logo switched on?', 'yes_no', NULL, 6),
            (@sec, 'Were security and Mopol at post', 'yes_no', NULL, 7),
            (@sec, 'All the cars parked properly', 'yes_no', NULL, 8),
            (@sec, 'Any unauthorised vehicle/person in parking/driver lounge', 'yes_no', NULL, 9);

        -- ---------------------------------------------------------------
        -- Lobby
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Lobby');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was somebody manning the main door?', 'yes_no', NULL, 1),
            (@sec, 'Was the standard greeting in place?', 'yes_no', NULL, 2),
            (@sec, 'Was the area clean?', 'yes_no', NULL, 3),
            (@sec, 'Were bell boys/porter on duty?', 'yes_no', NULL, 4),
            (@sec, 'Was the in-house music working?', 'yes_no', NULL, 5),
            (@sec, 'Was there enough lighting in the lobby?', 'yes_no', NULL, 6),
            (@sec, 'Were the elevators working in order?', 'yes_no', NULL, 7),
            (@sec, 'Any object found unattended in the back area?', 'yes_no', NULL, 8);

        -- ---------------------------------------------------------------
        -- Fitness Centre/Swimming Pool
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Fitness Centre/Swimming Pool');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the lights on?', 'yes_no', NULL, 1),
            (@sec, 'Was the area (all machines & mirror) clean?', 'yes_no', NULL, 2),
            (@sec, 'Was First Aid Box available as per standard?', 'yes_no', NULL, 3),
            (@sec, 'Were the equipments working in order?', 'yes_no', NULL, 4),
            (@sec, 'Were TV/drinking water/towel available?', 'yes_no', NULL, 5),
            (@sec, 'Life guard on duty?', 'yes_no', NULL, 6),
            (@sec, 'Was the overall area clean & set as per standard?', 'yes_no', NULL, 7),
            (@sec, 'Was changing rooms clean and set?', 'yes_no', NULL, 8),
            (@sec, 'Was the WC & urinal clean?', 'yes_no', NULL, 9),
            (@sec, 'Was the guest lockers properly locked & record maintained?', 'yes_no', NULL, 10),
            (@sec, 'Was the pool water clean?', 'yes_no', NULL, 11),
            (@sec, 'Were the life saving equipments in proper order?', 'yes_no', NULL, 12),
            (@sec, 'Was the lights switched on/off?', 'yes_no', NULL, 13),
            (@sec, 'Was cleaning checklist updated?', 'yes_no', NULL, 14);

        -- ---------------------------------------------------------------
        -- Guest Floor/Corridor
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Guest Floor/Corridor');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Were all corridors clean', 'yes_no', NULL, 1),
            (@sec, 'Were service doors closed', 'yes_no', NULL, 2),
            (@sec, 'Was lighting on/off as per standard', 'yes_no', NULL, 3),
            (@sec, 'Were AC''s on/off as per standard/temperature', 'yes_no', NULL, 4),
            (@sec, 'Were all the lifts operational/clean properly', 'yes_no', NULL, 5),
            (@sec, 'Was lift UPS/battery charging properly', 'yes_no', NULL, 6),
            (@sec, 'Were all service doors on each floor open/close as per standard', 'yes_no', NULL, 7),
            (@sec, 'Were all fire/emergency exits as per standard', 'yes_no', NULL, 8),
            (@sec, 'Any unauthorized visitor/movement noticed', 'yes_no', NULL, 9);

        -- ---------------------------------------------------------------
        -- Kitchen
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Kitchen');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Roll Call', 'yes_no', 'e.g. KITCHEN - 5', 1),
            (@sec, 'Was the staff in proper uniform/cap/apron?', 'yes_no', NULL, 2),
            (@sec, 'Were all fridge/AC/cold room working in order', 'yes_no', NULL, 3),
            (@sec, 'Was notice board updated/N.A items etc.', 'yes_no', NULL, 4),
            (@sec, 'Was the area clean?', 'yes_no', NULL, 5),
            (@sec, 'Was Chef on duty aware about occupancy/VIP', 'yes_no', NULL, 6),
            (@sec, 'Was par stock/inventory log maintained properly', 'yes_no', NULL, 7),
            (@sec, 'Were gas burners off after cooking', 'yes_no', NULL, 8),
            (@sec, 'Gas cylinder stock level maintained/any leakage or smell?', 'yes_no', 'e.g. 3 in use, 4 filled, 2 empty', 9);

        -- ---------------------------------------------------------------
        -- Receiving Area
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Receiving Area');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the area clean?', 'yes_no', NULL, 1),
            (@sec, 'Was the weighing scale working properly?', 'yes_no', NULL, 2),
            (@sec, 'Was any item left unattended?', 'yes_no', NULL, 3);

        -- ---------------------------------------------------------------
        -- Security Office
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Security Office');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Roll Call', 'text', 'e.g. 8 - Security, 2 - Mopol', 1),
            (@sec, 'Was the area clean?', 'yes_no', NULL, 2),
            (@sec, 'Was the area manned?', 'yes_no', NULL, 3),
            (@sec, 'Was employee punching machine working in order?', 'yes_no', NULL, 4),
            (@sec, 'Was security log book updated?', 'yes_no', NULL, 5),
            (@sec, 'Was any vehicle unattended/unauthorized', 'yes_no', NULL, 6);

        -- ---------------------------------------------------------------
        -- Engineering/CCTV/Laundry/HK/GYM
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Engineering/CCTV/Laundry/HK/GYM');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Number of staff on duty? (Electrician, Plumber, AC Technician)', 'text', 'e.g. HK-1, Maintenance-3, Laundry-1, CCTV-2', 1),
            (@sec, 'Shift incharge on duty?', 'yes_no', NULL, 2),
            (@sec, 'Was the complaint register updated', 'yes_no', NULL, 3),
            (@sec, 'Was the diesel log book updated', 'yes_no', NULL, 4),
            (@sec, 'Generator 1 On & Off Time', 'text', 'e.g. 08:00pm = 08:36pm, 10:57pm = 11:03pm', 5),
            (@sec, 'Generator 2 On & Off Time', 'text', 'e.g. 07:14am = 10:04am, 12:02pm = 12:21pm', 6),
            (@sec, 'NEPA Status (last 24 hours)', 'text', 'e.g. NEPA = 15hrs 35mins, Generators = 8hrs 24mins', 7),
            (@sec, 'Gen 1 diesel stock level checked', 'text', 'e.g. 400 litres', 8),
            (@sec, 'Gen 2 diesel stock level checked', 'text', 'e.g. 100 litres', 9),
            (@sec, 'Surface tank level checked', 'text', 'e.g. Empty / litres', 10),
            (@sec, 'Underground tank level checked', 'text', 'e.g. 13,900 litres', 11),
            (@sec, 'Water level checked', 'text', 'e.g. 2.4m', 12);

        -- ---------------------------------------------------------------
        -- Staff Lockers
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Staff Lockers');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the area clean?', 'yes_no', NULL, 1),
            (@sec, 'Any unwanted people in the staff room/canteen?', 'yes_no', NULL, 2);

        -- ---------------------------------------------------------------
        -- Room Status
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Room Status');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Number of vacant rooms checked', 'text', 'e.g. 19 rooms', 1),
            (@sec, 'Room cleanliness & maintenance?', 'yes_no', NULL, 2),
            (@sec, 'In room lighting/ambience/comfort?', 'yes_no', NULL, 3),
            (@sec, 'Room key card jacket clean', 'yes_no', NULL, 4),
            (@sec, '"Tap water not safe to drink" sticker in place', 'yes_no', NULL, 5),
            (@sec, 'TV channel clear/remote working', 'yes_no', NULL, 6),
            (@sec, 'Was AC due for service/filter clean signal', 'yes_no', NULL, 7),
            (@sec, 'Soap/shampoo/body lotion/gel/conditioner/hair net', 'yes_no', NULL, 8),
            (@sec, 'Towel/hand towel/foot mat neat & clean?', 'yes_no', NULL, 9),
            (@sec, 'All lights working?', 'yes_no', NULL, 10),
            (@sec, 'Carpet clean?', 'yes_no', NULL, 11),
            (@sec, 'Curtain wrinkle-free and clean?', 'yes_no', NULL, 12),
            (@sec, 'Window clean from inside/outside both?', 'yes_no', NULL, 13),
            (@sec, 'View from window clean/flower plant/pot in place', 'yes_no', NULL, 14),
            (@sec, 'Bottled water/hair drier/8 hangers complete', 'yes_no', NULL, 15),
            (@sec, 'Was escape layout behind the door', 'yes_no', NULL, 16),
            (@sec, 'Directory/menu/barcode in room', 'yes_no', NULL, 17),
            (@sec, 'Safe box/locker guideline available', 'yes_no', NULL, 18),
            (@sec, 'Any pending repair/maintenance/laundry', 'yes_no', NULL, 19),
            (@sec, 'Reviewed revenue details for the night and confirmed guest quiet time', 'yes_no', 'e.g. Quiet time observed 12:30am - 7:00am', 20),
            (@sec, 'Reviewed in-house list and requirements for the night', 'yes_no', 'e.g. In-house = 16, Occupancy: 25.80%', 21),
            (@sec, 'Any discrepancy in room occupancy report', 'yes_no', NULL, 22);

        -- ---------------------------------------------------------------
        -- Staff Canteen/Cafeteria
        -- ---------------------------------------------------------------
        SET @sec = (SELECT id FROM mod_checklist_sections WHERE name = 'Staff Canteen/Cafeteria');
        INSERT INTO mod_checklist_items (section_id, question, response_type, placeholder, sort_order) VALUES
            (@sec, 'Was the area clean and organised properly', 'yes_no', NULL, 1),
            (@sec, 'Enough food available for staff on duty?', 'yes_no', NULL, 2),
            (@sec, 'Was there enough food for night shift', 'yes_no', NULL, 3),
            (@sec, 'Notice on staff notice board?', 'yes_no', NULL, 4),
            (@sec, 'Was there any security breach', 'yes_no', NULL, 5),
            (@sec, 'Any guest complaint', 'yes_no', NULL, 6);

    END IF;
END$$
DELIMITER ;

CALL seed_mod_checklist();
DROP PROCEDURE seed_mod_checklist;