-- ============================================================ -- 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;