CREATE TABLE IF NOT EXISTS `inspection_checklist_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` INT UNSIGNED NOT NULL,
  `section_label` VARCHAR(255) DEFAULT NULL,
  `item_text` TEXT NOT NULL,
  `item_type` ENUM('yes_no','rating','text','observation') NOT NULL DEFAULT 'yes_no',
  `status` ENUM('pass','fail','na','pending') NOT NULL DEFAULT 'pending',
  `answer_value` VARCHAR(500) DEFAULT NULL,
  `answer_score` DECIMAL(5,2) DEFAULT NULL,
  `finding` TEXT DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `photo_url` VARCHAR(500) DEFAULT NULL,
  `item_order` INT NOT NULL DEFAULT 0,
  `is_mandatory` TINYINT(1) NOT NULL DEFAULT 0,
  `severity` ENUM('critical','major','minor','observation') DEFAULT NULL,
  `created_by` INT UNSIGNED 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`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `fk_checklist_session` FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
