-- FashCognitive Quality OS - Advanced CAPA Management Schema
-- Run AFTER migration_qc_inspection.sql
-- Implements closed-loop CAPA with RCA, actions, verification, and knowledge base

SET FOREIGN_KEY_CHECKS = 0;

-- ========================
-- EXTEND inspection_capa with advanced fields
-- ========================
ALTER TABLE `inspection_capa`
  ADD COLUMN `department` VARCHAR(100) DEFAULT NULL COMMENT 'Department responsible' AFTER `title`,
  ADD COLUMN `severity` ENUM('critical','major','minor') NOT NULL DEFAULT 'major' AFTER `department`,
  ADD COLUMN `source` VARCHAR(100) DEFAULT NULL COMMENT 'CAPA trigger source e.g. inspection, complaint, audit' AFTER `severity`,
  ADD COLUMN `containment_action` TEXT DEFAULT NULL AFTER `preventive_action`,
  ADD COLUMN `containment_responsible` VARCHAR(255) DEFAULT NULL AFTER `containment_action`,
  ADD COLUMN `containment_deadline` DATE DEFAULT NULL AFTER `containment_responsible`,
  ADD COLUMN `containment_completed_at` TIMESTAMP NULL DEFAULT NULL AFTER `containment_deadline`,
  ADD COLUMN `effectiveness_rating` ENUM('effective','partial','ineffective') DEFAULT NULL AFTER `re_inspection_session_id`,
  ADD COLUMN `effectiveness_review_date` DATE DEFAULT NULL AFTER `effectiveness_rating`,
  ADD COLUMN `closure_notes` TEXT DEFAULT NULL AFTER `effectiveness_review_date`,
  ADD COLUMN `lessons_learned` TEXT DEFAULT NULL AFTER `closure_notes`,
  ADD COLUMN `approved_by` INT UNSIGNED DEFAULT NULL AFTER `lessons_learned`,
  ADD COLUMN `approved_at` TIMESTAMP NULL DEFAULT NULL AFTER `approved_by`;

-- ========================
-- CAPA ROOT CAUSES (supports multiple RCA methods per CAPA)
-- ========================
DROP TABLE IF EXISTS `capa_root_causes`;
CREATE TABLE `capa_root_causes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `capa_id` INT UNSIGNED NOT NULL,
  `method` VARCHAR(50) NOT NULL COMMENT 'e.g. 5whys, fishbone, fmea',
  `root_cause` TEXT NOT NULL,
  `category` VARCHAR(100) DEFAULT NULL COMMENT 'man, machine, material, method, measurement, environment',
  `why_1` TEXT DEFAULT NULL,
  `why_2` TEXT DEFAULT NULL,
  `why_3` TEXT DEFAULT NULL,
  `why_4` TEXT DEFAULT NULL,
  `why_5` TEXT DEFAULT NULL,
  `ai_confidence` DECIMAL(5,2) DEFAULT NULL COMMENT 'AI suggestion confidence score',
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_rca_capa` (`capa_id`),
  FOREIGN KEY (`capa_id`) REFERENCES `inspection_capa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA ACTIONS (individual corrective/preventive action items)
-- ========================
DROP TABLE IF EXISTS `capa_actions`;
CREATE TABLE `capa_actions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `capa_id` INT UNSIGNED NOT NULL,
  `action_type` ENUM('corrective','preventive') NOT NULL,
  `description` TEXT NOT NULL,
  `department` VARCHAR(100) DEFAULT NULL,
  `owner` VARCHAR(255) DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `deadline` DATE DEFAULT NULL,
  `priority` ENUM('high','medium','low') DEFAULT 'medium',
  `status` ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `completion_date` DATE DEFAULT NULL,
  `notes` TEXT 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_action_capa` (`capa_id`),
  INDEX `idx_action_status` (`status`),
  FOREIGN KEY (`capa_id`) REFERENCES `inspection_capa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA VERIFICATION RECORDS
-- ========================
DROP TABLE IF EXISTS `capa_verifications`;
CREATE TABLE `capa_verifications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `capa_id` INT UNSIGNED NOT NULL,
  `verification_type` ENUM('physical','process','statistical') NOT NULL DEFAULT 'physical',
  `verified_by` VARCHAR(255) DEFAULT NULL,
  `verification_date` DATE DEFAULT NULL,
  `method` VARCHAR(255) DEFAULT NULL,
  `criteria` TEXT DEFAULT NULL,
  `result` TEXT DEFAULT NULL,
  `effectiveness_score` INT DEFAULT NULL COMMENT '0-100',
  `defects_eliminated` TINYINT(1) DEFAULT 0,
  `rework_completed` TINYINT(1) DEFAULT 0,
  `sop_implemented` TINYINT(1) DEFAULT 0,
  `training_completed` TINYINT(1) DEFAULT 0,
  `before_value` VARCHAR(100) DEFAULT NULL COMMENT 'Statistical before value',
  `after_value` VARCHAR(100) DEFAULT NULL COMMENT 'Statistical after value',
  `improvement_pct` DECIMAL(5,1) DEFAULT NULL,
  `remarks` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ver_capa` (`capa_id`),
  FOREIGN KEY (`capa_id`) REFERENCES `inspection_capa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA ATTACHMENTS
-- ========================
DROP TABLE IF EXISTS `capa_attachments`;
CREATE TABLE `capa_attachments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `capa_id` INT UNSIGNED NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` ENUM('photo','document','video','other') NOT NULL DEFAULT 'document',
  `file_path` VARCHAR(500) DEFAULT NULL,
  `file_url` VARCHAR(500) DEFAULT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL COMMENT 'Size in bytes',
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_att_capa` (`capa_id`),
  FOREIGN KEY (`capa_id`) REFERENCES `inspection_capa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA TASKS
-- ========================
DROP TABLE IF EXISTS `capa_tasks`;
CREATE TABLE `capa_tasks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `capa_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `department` VARCHAR(100) DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `deadline` DATE DEFAULT NULL,
  `priority` ENUM('high','medium','low') DEFAULT 'medium',
  `status` ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `completion_date` DATE DEFAULT NULL,
  `notes` TEXT 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_task_capa` (`capa_id`),
  INDEX `idx_task_assigned` (`assigned_to`),
  INDEX `idx_task_status` (`status`),
  FOREIGN KEY (`capa_id`) REFERENCES `inspection_capa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
