-- ============================================================
-- quoERP — Gestión de Calidad ISO 9001:2015
-- Migración: 037_quality_iso.sql
-- No conformidades · Acciones correctivas · Auditorías
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- No Conformidades (ISO 9001:2015 cláusula 10.2)
CREATE TABLE IF NOT EXISTS quality_nonconformities (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    number              VARCHAR(20)     NOT NULL,
    source              ENUM('audit','customer_complaint','internal','supplier','process','product','other')
                        NOT NULL DEFAULT 'internal',
    nc_type             ENUM('mayor','menor','observacion') NOT NULL DEFAULT 'menor',
    detected_date       DATE            NOT NULL,
    detected_by         INT UNSIGNED    DEFAULT NULL,
    process_area        VARCHAR(150)    DEFAULT NULL,
    iso_clause          VARCHAR(50)     DEFAULT NULL
        COMMENT 'Cláusula ISO 9001:2015 (ej: 8.5.1, 10.2)',
    description         TEXT            NOT NULL,
    immediate_action    TEXT            DEFAULT NULL
        COMMENT 'Acción inmediata de contención',
    root_cause          TEXT            DEFAULT NULL,
    root_cause_method   ENUM('5why','fishbone','fmea','other') DEFAULT NULL,
    status              ENUM('abierta','en_analisis','plan_accion','verificacion','cerrada','cancelada')
                        NOT NULL DEFAULT 'abierta',
    due_date            DATE            DEFAULT NULL,
    closed_date         DATE            DEFAULT NULL,
    closed_by           INT UNSIGNED    DEFAULT NULL,
    verified_by         INT UNSIGNED    DEFAULT NULL,
    effectiveness_ok    TINYINT(1)      DEFAULT NULL
        COMMENT '1=eficaz, 0=no eficaz',
    notes               TEXT            DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_nc_number (company_id, number),
    INDEX idx_nc_company (company_id),
    INDEX idx_nc_status  (status),
    INDEX idx_nc_type    (nc_type),
    INDEX idx_nc_date    (detected_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='No Conformidades ISO 9001:2015 cláusula 10.2';

-- Acciones Correctivas / Preventivas
CREATE TABLE IF NOT EXISTS quality_corrective_actions (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    nc_id               INT UNSIGNED    NOT NULL,
    action_type         ENUM('correctiva','preventiva','mejora') NOT NULL DEFAULT 'correctiva',
    description         TEXT            NOT NULL,
    responsible_id      INT UNSIGNED    DEFAULT NULL,
    responsible_name    VARCHAR(150)    DEFAULT NULL,
    planned_date        DATE            DEFAULT NULL,
    completion_date     DATE            DEFAULT NULL,
    status              ENUM('pendiente','en_proceso','completada','verificada','cancelada')
                        NOT NULL DEFAULT 'pendiente',
    result              TEXT            DEFAULT NULL
        COMMENT 'Resultado / evidencia de implementación',
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_ca_nc      (nc_id),
    INDEX idx_ca_status  (status),
    INDEX idx_ca_resp    (responsible_id),
    INDEX idx_ca_date    (planned_date),
    CONSTRAINT fk_ca_nc FOREIGN KEY (nc_id)
        REFERENCES quality_nonconformities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auditorías (ISO 9001:2015 cláusula 9.2)
CREATE TABLE IF NOT EXISTS quality_audits (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    number              VARCHAR(20)     NOT NULL,
    audit_type          ENUM('interna','externa','proveedor','certificacion','seguimiento')
                        NOT NULL DEFAULT 'interna',
    standard            VARCHAR(100)    NOT NULL DEFAULT 'ISO 9001:2015',
    scope               TEXT            DEFAULT NULL,
    auditee_areas       VARCHAR(300)    DEFAULT NULL,
    lead_auditor        VARCHAR(150)    DEFAULT NULL,
    auditor_team        VARCHAR(300)    DEFAULT NULL,
    planned_start       DATE            NOT NULL,
    planned_end         DATE            NOT NULL,
    actual_start        DATE            DEFAULT NULL,
    actual_end          DATE            DEFAULT NULL,
    status              ENUM('planificada','en_curso','completada','cancelada')
                        NOT NULL DEFAULT 'planificada',
    objective           TEXT            DEFAULT NULL,
    criteria            VARCHAR(300)    DEFAULT NULL
        COMMENT 'Criterios de auditoría (normas, procedimientos)',
    summary             TEXT            DEFAULT NULL,
    conclusion          ENUM('conforme','no_conforme','condicionado') DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_audit_number (company_id, number),
    INDEX idx_aud_company (company_id),
    INDEX idx_aud_status  (status),
    INDEX idx_aud_dates   (planned_start, planned_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Auditorías ISO 9001:2015 cláusula 9.2';

-- Hallazgos de Auditoría
CREATE TABLE IF NOT EXISTS quality_audit_findings (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    audit_id            INT UNSIGNED    NOT NULL,
    finding_type        ENUM('conformidad','no_conformidad','observacion','oportunidad_mejora')
                        NOT NULL DEFAULT 'observacion',
    iso_clause          VARCHAR(50)     DEFAULT NULL,
    process_area        VARCHAR(150)    DEFAULT NULL,
    description         TEXT            NOT NULL,
    nc_id               INT UNSIGNED    DEFAULT NULL
        COMMENT 'NC generada a partir de este hallazgo',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_find_audit (audit_id),
    INDEX idx_find_type  (finding_type),
    CONSTRAINT fk_finding_audit FOREIGN KEY (audit_id)
        REFERENCES quality_audits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Actividad / bitácora de NC
CREATE TABLE IF NOT EXISTS quality_nc_activities (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    nc_id               INT UNSIGNED    NOT NULL,
    user_id             INT UNSIGNED    DEFAULT NULL,
    user_name           VARCHAR(100)    DEFAULT NULL,
    activity_type       ENUM('comment','status_change','action_added','action_updated','closed','verified')
                        NOT NULL DEFAULT 'comment',
    description         TEXT            NOT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_act_nc   (nc_id),
    CONSTRAINT fk_act_nc FOREIGN KEY (nc_id)
        REFERENCES quality_nonconformities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
