-- Supplier CAPA Portal Migration
-- Adds tables for supplier-facing CAPA response workflow

-- Evidence files uploaded by suppliers in response to CAPAs
CREATE TABLE IF NOT EXISTS supplier_capa_evidence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    capa_id INT UNSIGNED NOT NULL,
    supplier_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT DEFAULT NULL,
    mime_type VARCHAR(100) DEFAULT NULL,
    evidence_type ENUM('root_cause', 'corrective_action', 'preventive_action', 'general') DEFAULT 'general',
    description TEXT DEFAULT NULL,
    uploaded_by VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_capa_id (capa_id),
    INDEX idx_supplier_id (supplier_id),
    FOREIGN KEY (capa_id) REFERENCES supplier_audit_capa(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Track portal CAPA acknowledgements and response timeline
CREATE TABLE IF NOT EXISTS supplier_capa_timeline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    capa_id INT UNSIGNED NOT NULL,
    action_type ENUM('viewed', 'acknowledged', 'responded', 'evidence_uploaded', 'status_change') NOT NULL,
    action_by VARCHAR(100) DEFAULT NULL,
    action_notes TEXT DEFAULT NULL,
    old_status VARCHAR(50) DEFAULT NULL,
    new_status VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_capa_id (capa_id),
    FOREIGN KEY (capa_id) REFERENCES supplier_audit_capa(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
