-- RIDDOR (Reporting of Injuries, Diseases and Dangerous Occurrences) Tracking Tables
-- Organization-wide HSE incident tracking with 3-year rolling history

-- Annual RIDDOR Statistics Summary
CREATE TABLE IF NOT EXISTS riddor_annual_summary (
    id_summary INT AUTO_INCREMENT PRIMARY KEY,
    summary_year YEAR NOT NULL,
    fatalities INT DEFAULT 0,
    riddor_incidents INT DEFAULT 0,
    lost_time_injuries INT DEFAULT 0,
    non_lost_time_injuries INT DEFAULT 0,
    dangerous_occurrences INT DEFAULT 0,
    near_misses INT DEFAULT 0,
    average_employees INT DEFAULT 0,
    notes LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_year (summary_year),
    INDEX (summary_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Detailed RIDDOR Incident Records
CREATE TABLE IF NOT EXISTS riddor_incidents (
    id_incident INT AUTO_INCREMENT PRIMARY KEY,
    incident_date DATE NOT NULL,
    incident_year YEAR NOT NULL,
    incident_type ENUM('Fatality', 'RIDDOR Incident', 'Lost Time Injury', 'Non-lost Time Injury', 'Dangerous Occurrence', 'Near Miss') NOT NULL,
    employee_name VARCHAR(255),
    department VARCHAR(255),
    description LONGTEXT NOT NULL COMMENT 'What happened',
    investigation_outcome LONGTEXT COMMENT 'Outcome of investigation',
    prevention_actions LONGTEXT COMMENT 'Actions taken to prevent reoccurrence',
    included_in_other_category BOOLEAN DEFAULT FALSE COMMENT 'Whether incident included in multiple categories',
    severity_level ENUM('Low', 'Medium', 'High', 'Critical') DEFAULT 'Medium',
    status ENUM('Open', 'Under Investigation', 'Closed') DEFAULT 'Open',
    investigation_date DATE,
    investigation_completed_date DATE,
    responsible_person VARCHAR(255),
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (incident_date),
    INDEX (incident_year),
    INDEX (incident_type),
    INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RIDDOR Incident Attachments (Report Documents)
CREATE TABLE IF NOT EXISTS riddor_incident_attachments (
    id_attachment INT AUTO_INCREMENT PRIMARY KEY,
    id_incident INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    description VARCHAR(500),
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_incident) REFERENCES riddor_incidents(id_incident) ON DELETE CASCADE,
    INDEX (id_incident)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RIDDOR Compliance Tracking (derived from incidents and summary)
CREATE TABLE IF NOT EXISTS riddor_compliance (
    id_compliance INT AUTO_INCREMENT PRIMARY KEY,
    compliance_year YEAR NOT NULL,
    total_incidents INT DEFAULT 0,
    incidents_reported INT DEFAULT 0,
    incidents_investigated INT DEFAULT 0,
    investigation_completion_rate DECIMAL(5,2),
    prevention_actions_implemented INT DEFAULT 0,
    trend_vs_previous_year INT DEFAULT 0,
    hse_performance_rating ENUM('Excellent', 'Good', 'Fair', 'Poor') DEFAULT 'Fair',
    notes LONGTEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_compliance_year (compliance_year),
    INDEX (compliance_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;