-- ============================================================
-- Elegant Work — Checklist System Migration
-- Run once to enhance existing checklist tables
-- ============================================================

-- Enhance checklist_template_items
ALTER TABLE `checklist_template_items`
  ADD COLUMN `answer_type` ENUM('yes_no','text','number') NOT NULL DEFAULT 'yes_no' AFTER `item_text`,
  ADD COLUMN `requires_image` ENUM('never','always','on_yes','on_no') NOT NULL DEFAULT 'never' AFTER `answer_type`,
  ADD COLUMN `is_required` TINYINT(1) NOT NULL DEFAULT 1 AFTER `requires_image`,
  MODIFY COLUMN `item_text` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- Enhance checklist_templates
ALTER TABLE `checklist_templates`
  ADD COLUMN `linked_type` ENUM('general','fleet','job_card') NOT NULL DEFAULT 'general' AFTER `is_active`,
  ADD COLUMN `fleet_vehicle_id` INT(11) DEFAULT NULL AFTER `linked_type`,
  ADD COLUMN `job_type_id` INT(11) DEFAULT NULL AFTER `fleet_vehicle_id`,
  ADD COLUMN `assigned_to` INT(11) DEFAULT NULL AFTER `job_type_id`,
  MODIFY COLUMN `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  MODIFY COLUMN `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- Enhance checklist_instances
ALTER TABLE `checklist_instances`
  ADD COLUMN `vehicle_id` INT(11) DEFAULT NULL AFTER `user_id`,
  ADD COLUMN `completed_by` INT(11) DEFAULT NULL AFTER `vehicle_id`,
  ADD COLUMN `instance_notes` TEXT DEFAULT NULL AFTER `completed_by`;

-- Enhance checklist_instance_items
ALTER TABLE `checklist_instance_items`
  ADD COLUMN `answer_text` TEXT DEFAULT NULL AFTER `is_checked`,
  ADD COLUMN `answer_bool` TINYINT(1) DEFAULT NULL AFTER `answer_text`,
  ADD COLUMN `image_filename` VARCHAR(255) DEFAULT NULL AFTER `photo_filename`;

-- Job Card Types table (replaces hardcoded enum)
CREATE TABLE IF NOT EXISTS `job_card_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `default_checklist_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed with the existing hardcoded types
INSERT IGNORE INTO `job_card_types` (`name`, `slug`, `sort_order`) VALUES
  ('Installation',  'installation',  0),
  ('Maintenance',   'maintenance',   1),
  ('Repair',        'repair',        2),
  ('Site Survey',   'site_survey',   3),
  ('Other',         'other',         4);

-- Uploads dir marker (the uploads happen to api/checklists/uploads/)
-- You need to create: api/checklists/uploads/ and chmod 755