-- FashCognitive Quality OS - Inspection Management Schema
-- Run AFTER migration_qc.sql
-- Implements metadata-driven inspection engine


-- ========================
-- INSPECTION TEMPLATES
-- ========================
CREATE TABLE `inspection_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `template_type` ENUM('FRI','DUPRO','fabric','loading') NOT NULL DEFAULT 'FRI',
  `name` VARCHAR(255) NOT NULL,
  `version` INT NOT NULL DEFAULT 1,
  `status` ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
  `config` JSON DEFAULT NULL COMMENT 'Template-level configuration (auto_save_interval, gps_required, etc)',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_template_buyer` (`buyer_id`),
  INDEX `idx_template_type` (`template_type`),
  INDEX `idx_template_company` (`company_id`),
  INDEX `idx_template_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION PAGES
-- ========================
CREATE TABLE `inspection_pages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `page_key` VARCHAR(100) NOT NULL COMMENT 'Unique key within template (e.g. overview, defects, measurements)',
  `label` VARCHAR(255) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_conditional` TINYINT(1) NOT NULL DEFAULT 0,
  `condition_rule` JSON DEFAULT NULL COMMENT 'JSON rule that determines when this page is shown',
  `category` ENUM('universal','fri','dupro','fabric','loading') NOT NULL DEFAULT 'universal',
  `config` JSON DEFAULT NULL COMMENT 'Page-level config (columns, layout, etc)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  INDEX `idx_page_template` (`template_id`),
  INDEX `idx_page_category` (`category`),
  UNIQUE KEY `uq_page_template_key` (`template_id`, `page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION FIELDS
-- ========================
CREATE TABLE `inspection_fields` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `page_id` INT UNSIGNED NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `field_type` ENUM('text','number','select','multi_select','date','photo','signature','defect_grid','measurement_grid','aql_calculator','textarea','checkbox','radio','info_text','divider') NOT NULL DEFAULT 'text',
  `label` VARCHAR(255) NOT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `validation_rules` JSON DEFAULT NULL COMMENT 'JSON validation rules (min, max, pattern, required_if, etc)',
  `options` JSON DEFAULT NULL COMMENT 'For select/multi_select/radio: array of {label, value}',
  `config` JSON DEFAULT NULL COMMENT 'Field-level config (placeholder, help_text, units, default_value, etc)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`page_id`) REFERENCES `inspection_pages`(`id`) ON DELETE CASCADE,
  INDEX `idx_field_page` (`page_id`),
  UNIQUE KEY `uq_field_page_key` (`page_id`, `field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION RULES (JSON-based rules engine)
-- ========================
CREATE TABLE `inspection_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `priority` INT NOT NULL DEFAULT 0,
  `condition_expression` VARCHAR(500) NOT NULL COMMENT 'Evaluable expression like "critical_defects > 0"',
  `actions` JSON NOT NULL COMMENT 'Array of actions: ["set_status:FAIL", "require_manager_approval", "trigger_CAPA"]',
  `label` VARCHAR(255) DEFAULT NULL COMMENT 'Human-readable description',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  INDEX `idx_rule_template` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION SESSIONS (core - stores inspection state & data)
-- ========================
CREATE TABLE `inspection_sessions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `assignment_id` INT UNSIGNED DEFAULT NULL,
  `template_id` INT UNSIGNED NOT NULL,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `factory_id` INT UNSIGNED DEFAULT NULL,
  `po_id` INT UNSIGNED DEFAULT NULL,
  `style_id` INT UNSIGNED DEFAULT NULL,
  `inspector_id` INT UNSIGNED DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('assigned','started','draft','in_progress','submitted','under_approval','approved','rejected','cancelled') NOT NULL DEFAULT 'assigned',
  `data` JSON DEFAULT NULL COMMENT 'All field values stored as JSON {page_key: {field_key: value}}',
  `aql_data` JSON DEFAULT NULL COMMENT 'AQL calculation results',
  `decision` ENUM('PASS','CONDITIONAL_PASS','FAIL','RE_INSPECTION','HOLD') DEFAULT NULL,
  `decision_reason` TEXT DEFAULT NULL,
  `gps_lat` DECIMAL(10,7) DEFAULT NULL,
  `gps_lng` DECIMAL(10,7) DEFAULT NULL,
  `gps_accuracy` DECIMAL(10,2) DEFAULT NULL,
  `gps_timestamp` TIMESTAMP NULL DEFAULT NULL,
  `started_at` TIMESTAMP NULL DEFAULT NULL,
  `submitted_at` TIMESTAMP NULL DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_session_assignment` (`assignment_id`),
  INDEX `idx_session_template` (`template_id`),
  INDEX `idx_session_buyer` (`buyer_id`),
  INDEX `idx_session_factory` (`factory_id`),
  INDEX `idx_session_po` (`po_id`),
  INDEX `idx_session_inspector` (`inspector_id`),
  INDEX `idx_session_status` (`status`),
  INDEX `idx_session_company` (`company_id`),
  INDEX `idx_session_decision` (`decision`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION ASSIGNMENTS (scheduling)
-- ========================
CREATE TABLE `inspection_assignments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `factory_id` INT UNSIGNED DEFAULT NULL,
  `po_id` INT UNSIGNED DEFAULT NULL,
  `style_id` INT UNSIGNED DEFAULT NULL,
  `inspector_id` INT UNSIGNED DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `scheduled_date` DATE DEFAULT NULL,
  `scheduled_time_slot` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 09:00-13:00',
  `status` ENUM('planned','assigned','in_progress','completed','cancelled','rebooked') NOT NULL DEFAULT 'planned',
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading') NOT NULL DEFAULT 'FRI',
  `priority` ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `notes` TEXT DEFAULT NULL,
  `booking_ref` VARCHAR(50) DEFAULT NULL COMMENT 'Unique booking reference code',
  `capacity_hours` DECIMAL(5,2) DEFAULT NULL COMMENT 'Estimated inspection hours',
  `rebooked_from_id` INT UNSIGNED DEFAULT NULL COMMENT 'If rebooked, points to original assignment',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Soft delete for recycle/restore',
  INDEX `idx_assign_template` (`template_id`),
  INDEX `idx_assign_buyer` (`buyer_id`),
  INDEX `idx_assign_factory` (`factory_id`),
  INDEX `idx_assign_po` (`po_id`),
  INDEX `idx_assign_inspector` (`inspector_id`),
  INDEX `idx_assign_date` (`scheduled_date`),
  INDEX `idx_assign_status` (`status`),
  INDEX `idx_assign_company` (`company_id`),
  INDEX `idx_assign_deleted` (`deleted_at`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`),
  FOREIGN KEY (`rebooked_from_id`) REFERENCES `inspection_assignments`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION DRAFTS (auto-save recovery)
-- ========================
CREATE TABLE `inspection_drafts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `data` JSON NOT NULL COMMENT 'Snapshot of session data at save time',
  `version` INT NOT NULL DEFAULT 1,
  `field_key` VARCHAR(100) DEFAULT NULL COMMENT 'Last edited field',
  `page_key` VARCHAR(100) DEFAULT NULL COMMENT 'Current page',
  `progress_pct` DECIMAL(5,2) DEFAULT 0 COMMENT 'Completion percentage',
  `device_info` VARCHAR(255) DEFAULT NULL COMMENT 'Browser/device identifier',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_draft_session` (`session_id`),
  INDEX `idx_draft_version` (`session_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION SIGNATURES
-- ========================
CREATE TABLE `inspection_signatures` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `role` ENUM('inspector','factory_qa','buyer_qa','regional_manager') NOT NULL,
  `signatory_name` VARCHAR(255) NOT NULL,
  `signatory_title` VARCHAR(255) DEFAULT NULL,
  `signature_data` TEXT NOT NULL COMMENT 'Base64 encoded signature image data',
  `signed_at` TIMESTAMP NULL DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_sig_session` (`session_id`),
  INDEX `idx_sig_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION REPORT VERSIONS (immutable)
-- ========================
CREATE TABLE `inspection_report_versions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `version_number` INT NOT NULL,
  `data_snapshot` JSON NOT NULL COMMENT 'Complete frozen copy of session data at version creation',
  `pdf_path` VARCHAR(500) DEFAULT NULL COMMENT 'Server path to generated PDF',
  `pdf_url` VARCHAR(500) DEFAULT NULL COMMENT 'Public URL for PDF',
  `checksum` VARCHAR(64) NOT NULL COMMENT 'SHA-256 checksum of data_snapshot for tamper verification',
  `qr_code` VARCHAR(500) DEFAULT NULL COMMENT 'QR code data/URL for report verification',
  `version_label` VARCHAR(255) DEFAULT NULL COMMENT 'e.g. "Submitted", "Buyer Comments", "CAPA Updates"',
  `change_summary` TEXT DEFAULT NULL COMMENT 'Summary of changes from previous version',
  `previous_version_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`previous_version_id`) REFERENCES `inspection_report_versions`(`id`),
  INDEX `idx_ver_session` (`session_id`),
  INDEX `idx_ver_version` (`session_id`, `version_number`),
  UNIQUE KEY `uq_version` (`session_id`, `version_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PLANS (buyer-specific)
-- ========================
CREATE TABLE `quality_plans` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `template_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `version` INT NOT NULL DEFAULT 1,
  `aql_level` VARCHAR(10) DEFAULT 'II' COMMENT 'AQL inspection level (I, II, III, S1-S4)',
  `aql_critical` DECIMAL(5,2) DEFAULT 0 COMMENT 'Critical defects AQL',
  `aql_major` DECIMAL(5,2) DEFAULT 2.5 COMMENT 'Major defects AQL',
  `aql_minor` DECIMAL(5,2) DEFAULT 4.0 COMMENT 'Minor defects AQL',
  `critical_defects_limit` INT DEFAULT 0 COMMENT 'Zero tolerance typically',
  `major_defects_limit` INT DEFAULT NULL,
  `minor_defects_limit` INT DEFAULT NULL,
  `rules` JSON DEFAULT NULL COMMENT 'Buyer-specific rules overriding inspection_rules',
  `config` JSON DEFAULT NULL COMMENT 'Plan-level configuration',
  `status` ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_qp_buyer` (`buyer_id`),
  INDEX `idx_qp_template` (`template_id`),
  INDEX `idx_qp_company` (`company_id`),
  INDEX `idx_qp_status` (`status`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PLAN SECTIONS
-- ========================
CREATE TABLE `quality_plan_sections` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `quality_plan_id` INT UNSIGNED NOT NULL,
  `section_key` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `config` JSON DEFAULT NULL COMMENT 'Section-level overrides',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`quality_plan_id`) REFERENCES `quality_plans`(`id`) ON DELETE CASCADE,
  INDEX `idx_qps_plan` (`quality_plan_id`),
  UNIQUE KEY `uq_qps_plan_key` (`quality_plan_id`, `section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- MASTER DEFECT LIBRARY
-- ========================
CREATE TABLE `master_defect_library` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category` ENUM('critical','major','minor') NOT NULL DEFAULT 'major',
  `code` VARCHAR(50) NOT NULL COMMENT 'Defect code (e.g. CRK-01, CLR-02)',
  `description` VARCHAR(500) NOT NULL,
  `detailed_description` TEXT DEFAULT NULL,
  `defect_type` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. sewing, color, sizing, fabric, finishing',
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading','all') NOT NULL DEFAULT 'all',
  `photo_required` TINYINT(1) NOT NULL DEFAULT 0,
  `auto_capa` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Auto-generate CAPA when this defect is recorded',
  `capa_template` TEXT DEFAULT NULL COMMENT 'Template text for CAPA',
  `reference_image_url` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mdl_category` (`category`),
  INDEX `idx_mdl_type` (`defect_type`),
  INDEX `idx_mdl_inspection_type` (`inspection_type`),
  INDEX `idx_mdl_company` (`company_id`),
  INDEX `idx_mdl_active` (`is_active`),
  UNIQUE KEY `uq_mdl_code` (`code`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- MEASUREMENT TEMPLATES
-- ========================
CREATE TABLE `measurement_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading','all') NOT NULL DEFAULT 'all',
  `point_name` VARCHAR(255) NOT NULL COMMENT 'Measurement point (e.g. Chest Width, Center Back Length)',
  `tolerance_min` DECIMAL(10,2) DEFAULT NULL COMMENT 'Negative tolerance',
  `tolerance_max` DECIMAL(10,2) DEFAULT NULL COMMENT 'Positive tolerance',
  `unit` VARCHAR(20) DEFAULT 'cm' COMMENT 'cm, inch, mm, g, etc',
  `sort_order` INT NOT NULL DEFAULT 0,
  `category` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. body, sleeve, collar',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mt_type` (`inspection_type`),
  INDEX `idx_mt_category` (`category`),
  INDEX `idx_mt_company` (`company_id`),
  INDEX `idx_mt_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- PHOTO REQUIREMENTS
-- ========================
CREATE TABLE `photo_requirements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED DEFAULT NULL,
  `quality_plan_id` INT UNSIGNED DEFAULT NULL,
  `page_key` VARCHAR(100) NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `min_count` INT NOT NULL DEFAULT 0,
  `max_count` INT DEFAULT NULL,
  `require_annotation` TINYINT(1) NOT NULL DEFAULT 0,
  `require_gps` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_pr_template` (`template_id`),
  INDEX `idx_pr_plan` (`quality_plan_id`),
  INDEX `idx_pr_page` (`page_key`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`quality_plan_id`) REFERENCES `quality_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION PHOTOS (actual captured photos)
-- ========================
CREATE TABLE `inspection_photos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `page_key` VARCHAR(100) NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `photo_path` VARCHAR(500) NOT NULL,
  `photo_url` VARCHAR(500) DEFAULT NULL,
  `annotation_data` JSON DEFAULT NULL COMMENT 'JSON with annotation coordinates and text',
  `gps_lat` DECIMAL(10,7) DEFAULT NULL,
  `gps_lng` DECIMAL(10,7) DEFAULT NULL,
  `captured_at` TIMESTAMP NULL DEFAULT NULL,
  `file_size` INT DEFAULT NULL COMMENT 'File size in bytes',
  `mime_type` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_photo_session` (`session_id`),
  INDEX `idx_photo_page` (`page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION APPROVALS (multi-level approval trail)
-- ========================
CREATE TABLE `inspection_approvals` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `approval_level` ENUM('inspector','factory_qa','buyer_qa','regional_manager') NOT NULL,
  `approver_id` INT UNSIGNED DEFAULT NULL,
  `approver_name` VARCHAR(255) DEFAULT NULL,
  `decision` ENUM('approved','rejected','pending','conditional') NOT NULL DEFAULT 'pending',
  `comments` TEXT DEFAULT NULL,
  `signature_id` INT UNSIGNED DEFAULT NULL,
  `decided_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`signature_id`) REFERENCES `inspection_signatures`(`id`),
  INDEX `idx_app_session` (`session_id`),
  INDEX `idx_app_level` (`approval_level`),
  INDEX `idx_app_decision` (`decision`),
  UNIQUE KEY `uq_app_level` (`session_id`, `approval_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA RECORDS (quality-specific, links to inspection failures)
-- ========================
CREATE TABLE `inspection_capa` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED DEFAULT NULL,
  `defect_id` INT UNSIGNED DEFAULT NULL COMMENT 'Link to master_defect_library if auto-generated',
  `capa_number` VARCHAR(50) NOT NULL COMMENT 'Auto-generated CAPA reference number',
  `title` VARCHAR(500) NOT NULL,
  `root_cause` TEXT DEFAULT NULL,
  `root_cause_category` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. material, process, training, equipment',
  `corrective_action` TEXT DEFAULT NULL,
  `preventive_action` TEXT DEFAULT NULL,
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `deadline` DATE DEFAULT NULL,
  `status` ENUM('open','in_progress','verified','closed','cancelled') NOT NULL DEFAULT 'open',
  `verification_method` VARCHAR(255) DEFAULT NULL,
  `verification_result` TEXT DEFAULT NULL,
  `verified_by` INT UNSIGNED DEFAULT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `re_inspection_session_id` INT UNSIGNED DEFAULT NULL COMMENT 'Link to re-inspection session',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_capa_session` (`session_id`),
  INDEX `idx_capa_defect` (`defect_id`),
  INDEX `idx_capa_status` (`status`),
  INDEX `idx_capa_assigned` (`assigned_to`),
  INDEX `idx_capa_company` (`company_id`),
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`),
  FOREIGN KEY (`defect_id`) REFERENCES `master_defect_library`(`id`),
  FOREIGN KEY (`re_inspection_session_id`) REFERENCES `inspection_sessions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PERMISSIONS
-- ========================
INSERT IGNORE INTO permissions (code, label, group_name, description) VALUES
('quality.view', 'View Quality Module', 'Quality', 'View quality inspection pages and reports'),
('quality.inspect', 'Perform Inspections', 'Quality', 'Create and submit inspection sessions'),
('quality.approve', 'Approve Inspections', 'Quality', 'Approve or reject inspection reports'),
('quality.manage', 'Manage Quality Config', 'Quality', 'Manage templates, rules, defect libraries');

-- Assign quality permissions to admin role
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM app_roles WHERE name = 'admin'), id FROM permissions
WHERE code LIKE 'quality.%';

