-- COSHH (Control of Substances Hazardous to Health) Register
-- Track all hazardous substances with risk assessments and MSDS sheets

-- COSHH Products
CREATE TABLE IF NOT EXISTS coshh_products (
    id_product INT AUTO_INCREMENT PRIMARY KEY,
    coshh_id VARCHAR(20) UNIQUE NOT NULL COMMENT 'Product ID number',
    product_name VARCHAR(255) NOT NULL,
    hazard_classification VARCHAR(255) COMMENT 'Type of hazard',
    control_measures LONGTEXT COMMENT 'Control measures in place',
    storage_location VARCHAR(100) NOT NULL COMMENT 'Storage location/number',
    supplier_name VARCHAR(255),
    status ENUM('Active', 'Removed', 'Archived') DEFAULT 'Active',
    notes LONGTEXT,
    last_reviewed DATE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (coshh_id),
    INDEX (product_name),
    INDEX (status),
    INDEX (storage_location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- COSHH Documents (Risk Assessments and MSDS sheets)
CREATE TABLE IF NOT EXISTS coshh_documents (
    id_document INT AUTO_INCREMENT PRIMARY KEY,
    id_product INT NOT NULL,
    document_type ENUM('Risk Assessment', 'MSDS', 'Safety Data Sheet', 'Other') NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    version VARCHAR(50),
    review_date DATE,
    expiry_date DATE,
    description VARCHAR(500),
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_product) REFERENCES coshh_products(id_product) ON DELETE CASCADE,
    INDEX (id_product),
    INDEX (document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- COSHH Review Log
CREATE TABLE IF NOT EXISTS coshh_reviews (
    id_review INT AUTO_INCREMENT PRIMARY KEY,
    id_product INT NOT NULL,
    review_date DATE NOT NULL,
    reviewed_by VARCHAR(100),
    findings LONGTEXT,
    action_required LONGTEXT,
    status ENUM('Compliant', 'Non-Compliant', 'Action Required') DEFAULT 'Compliant',
    next_review_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_product) REFERENCES coshh_products(id_product) ON DELETE CASCADE,
    INDEX (id_product),
    INDEX (review_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- COSHH Statistics
CREATE TABLE IF NOT EXISTS coshh_statistics (
    id_stat INT AUTO_INCREMENT PRIMARY KEY,
    total_products INT DEFAULT 0,
    active_products INT DEFAULT 0,
    products_with_risk_assessment INT DEFAULT 0,
    products_with_msds INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;