-- ========================================
-- RISK ASSESSMENT SYSTEM v3.4.0
-- Complete Database Schema
-- ========================================
-- This file contains all tables, indexes, and sample data
-- Run this single file to set up the entire database
-- ========================================

-- Drop and create database
DROP DATABASE IF EXISTS risk_assessment_db;
CREATE DATABASE risk_assessment_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE risk_assessment_db;

-- Locations Table (Enhanced with opening hours and delivery windows)
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    chain VARCHAR(200) NULL,
    address TEXT NULL,
    city VARCHAR(100) NULL,
    postcode VARCHAR(20) NULL,
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    what3words VARCHAR(100) NULL,
    opening_hours JSON NULL COMMENT 'Opening hours by day of week',
    delivery_windows JSON NULL COMMENT 'Delivery time windows by day',
    special_instructions TEXT NULL COMMENT 'Special delivery or access instructions',
    contact_name VARCHAR(200) NULL,
    contact_phone VARCHAR(50) NULL,
    contact_email VARCHAR(200) NULL,
    parking_info TEXT NULL COMMENT 'Parking and access information',
    delivery_restrictions TEXT NULL COMMENT 'Size, weight, vehicle restrictions',
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_chain (chain),
    INDEX idx_active (active),
    INDEX idx_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Location Hours Table (Detailed scheduling)
CREATE TABLE location_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    day_of_week TINYINT NOT NULL COMMENT '0=Sunday, 1=Monday, ..., 6=Saturday',
    open_time TIME NULL,
    close_time TIME NULL,
    delivery_start TIME NULL,
    delivery_end TIME NULL,
    is_closed TINYINT(1) DEFAULT 0,
    notes TEXT NULL,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    INDEX idx_location_day (location_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Delivery Slots Table
CREATE TABLE delivery_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    day_of_week TINYINT NOT NULL,
    slot_start TIME NOT NULL,
    slot_end TIME NOT NULL,
    slot_name VARCHAR(100) NULL COMMENT 'e.g., Morning, Afternoon, Evening',
    max_deliveries INT DEFAULT 1 COMMENT 'Max concurrent deliveries in this slot',
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    INDEX idx_location_day (location_id, day_of_week),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Risk Categories Table
CREATE TABLE risk_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    display_order INT DEFAULT 0,
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_active (active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Risk Items Table (Enhanced for Hazard Library)
CREATE TABLE risk_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    default_likelihood INT DEFAULT 3 COMMENT '1-5 scale',
    default_severity INT DEFAULT 3 COMMENT '1-5 scale',
    default_risk_score INT AS (default_likelihood * default_severity) STORED,
    photos JSON NULL COMMENT 'Array of photo filenames',
    typical_controls TEXT NULL COMMENT 'Standard control measures',
    residual_risk VARCHAR(50) NULL COMMENT 'After-controls risk level',
    affected_persons TEXT NULL COMMENT 'Who is at risk',
    is_template TINYINT(1) DEFAULT 0 COMMENT 'Library template flag',
    usage_count INT DEFAULT 0 COMMENT 'Times used in assessments',
    last_used DATETIME NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES risk_categories(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_is_template (is_template),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Control Measures Table (Hierarchy of controls)
CREATE TABLE control_measures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    risk_item_id INT NOT NULL,
    control_type ENUM('elimination', 'substitution', 'engineering', 'administrative', 'ppe') NOT NULL,
    control_description TEXT NOT NULL,
    implementation_notes TEXT NULL,
    cost_estimate VARCHAR(100) NULL,
    effectiveness ENUM('high', 'medium', 'low') DEFAULT 'medium',
    display_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE CASCADE,
    INDEX idx_risk_item (risk_item_id),
    INDEX idx_type (control_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Hazard Library Photos Table
CREATE TABLE hazard_library_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    risk_item_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    caption TEXT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    display_order INT DEFAULT 0,
    uploaded_by INT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_risk_item (risk_item_id),
    INDEX idx_is_primary (is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- SECTION 2: ASSESSMENT TABLES
-- ========================================

-- Assessments Table
CREATE TABLE assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    location_id INT NOT NULL,
    visit_date DATE NOT NULL,
    assessor_id INT NULL,
    status ENUM('draft', 'in_progress', 'completed', 'reviewed', 'archived') DEFAULT 'draft',
    overall_risk_level VARCHAR(50) NULL,
    vehicle_type VARCHAR(100) NULL,
    delivery_type VARCHAR(100) NULL,
    weather_conditions VARCHAR(100) NULL,
    additional_notes TEXT NULL,
    created_by INT NULL,
    updated_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (assessor_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_assessment_number (assessment_number),
    INDEX idx_location (location_id),
    INDEX idx_assessor (assessor_id),
    INDEX idx_status (status),
    INDEX idx_visit_date (visit_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessment Risks Table
CREATE TABLE assessment_risks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    template_id INT NULL COMMENT 'Links to original library template',
    risk_item_id INT NULL,
    hazard_description TEXT NOT NULL,
    persons_at_risk TEXT NULL,
    existing_controls TEXT NULL,
    likelihood INT NOT NULL COMMENT '1-5 scale',
    severity INT NOT NULL COMMENT '1-5 scale',
    risk_score INT AS (likelihood * severity) STORED,
    risk_level VARCHAR(50) AS (
        CASE 
            WHEN (likelihood * severity) >= 20 THEN 'critical'
            WHEN (likelihood * severity) >= 15 THEN 'high'
            WHEN (likelihood * severity) >= 10 THEN 'medium'
            ELSE 'low'
        END
    ) STORED,
    additional_controls TEXT NULL,
    residual_likelihood INT NULL,
    residual_severity INT NULL,
    residual_score INT AS (residual_likelihood * residual_severity) STORED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES risk_items(id) ON DELETE SET NULL,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE SET NULL,
    INDEX idx_assessment (assessment_id),
    INDEX idx_template (template_id),
    INDEX idx_risk_level (risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Action Items Table
CREATE TABLE action_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    description TEXT NOT NULL,
    responsible_person VARCHAR(200) NULL,
    due_date DATE NULL,
    status ENUM('pending', 'in_progress', 'completed', 'overdue') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    completed_date DATE NULL,
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    INDEX idx_assessment (assessment_id),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessment Versions Table
CREATE TABLE assessment_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_number INT NOT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    version_type VARCHAR(50) DEFAULT 'auto' COMMENT 'auto, manual, scheduled',
    version_notes TEXT NULL COMMENT 'User provided notes',
    snapshot_data JSON NOT NULL COMMENT 'Complete assessment state',
    status VARCHAR(50) NULL,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_version (assessment_id, version_number),
    INDEX idx_assessment (assessment_id),
    INDEX idx_created_by (created_by),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User Activity Table
CREATE TABLE user_activity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type VARCHAR(50) NOT NULL COMMENT 'page_view, search, export, download, etc',
    activity_description TEXT NULL,
    page_url VARCHAR(500) NULL,
    duration_seconds INT NULL COMMENT 'Time spent on page/activity',
    metadata JSON NULL COMMENT 'Additional activity data',
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (activity_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Change Requests Table (for approval workflows)
CREATE TABLE change_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    requested_by INT NOT NULL,
    requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_by INT NULL,
    approved_at DATETIME NULL,
    rejected_by INT NULL,
    rejected_at DATETIME NULL,
    status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, approved, rejected',
    change_type VARCHAR(50) NOT NULL COMMENT 'update, delete',
    old_data JSON NULL,
    new_data JSON NULL,
    reason TEXT NULL,
    approval_notes TEXT NULL,
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (rejected_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_requested_by (requested_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Retention Policies Table
CREATE TABLE retention_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    retention_days INT NOT NULL DEFAULT 365,
    archive_after_days INT NULL,
    auto_delete TINYINT(1) DEFAULT 0,
    policy_description TEXT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_entity (entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- SECTION 4: PERMISSION & CUSTOMIZATION TABLES
-- ========================================

-- Permissions Table
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('admin', 'assessor', 'viewer') NOT NULL,
    resource VARCHAR(50) NOT NULL,
    can_read TINYINT(1) DEFAULT 0,
    can_create TINYINT(1) DEFAULT 0,
    can_update TINYINT(1) DEFAULT 0,
    can_delete TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_role_resource (role, resource),
    INDEX idx_role (role),
    INDEX idx_resource (resource)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Custom Fields Table
CREATE TABLE custom_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL COMMENT 'assessment, location, etc',
    field_name VARCHAR(100) NOT NULL,
    field_label VARCHAR(200) NOT NULL,
    field_type VARCHAR(50) NOT NULL COMMENT 'text, number, date, select, textarea',
    field_options JSON NULL COMMENT 'Options for select fields',
    is_required TINYINT(1) DEFAULT 0,
    display_order INT DEFAULT 0,
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity_type (entity_type),
    INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Custom Field Values Table
CREATE TABLE custom_field_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    custom_field_id INT NOT NULL,
    entity_id INT NOT NULL,
    field_value TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (custom_field_id) REFERENCES custom_fields(id) ON DELETE CASCADE,
    INDEX idx_field (custom_field_id),
    INDEX idx_entity (entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- System Settings Table
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    setting_type VARCHAR(50) DEFAULT 'text',
    description TEXT NULL,
    is_public TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key),
    INDEX idx_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Email Queue Table
CREATE TABLE email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    to_email VARCHAR(200) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    body TEXT NOT NULL,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    last_attempt DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sessions Table
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    payload TEXT NOT NULL,
    last_activity INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sync Queue Table (for offline PWA)
CREATE TABLE sync_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    data JSON NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    synced_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================
-- SECTION 5: TRIGGERS
-- ========================================

DELIMITER //

CREATE TRIGGER after_assessment_update
AFTER UPDATE ON assessments
FOR EACH ROW
BEGIN
    -- Only create version if status changed or significant fields changed
    IF OLD.status != NEW.status OR 
       OLD.title != NEW.title OR
       OLD.location_id != NEW.location_id THEN
        
        INSERT INTO assessment_versions (
            assessment_id, 
            version_number, 
            created_by,
            version_type,
            version_notes,
            snapshot_data,
            status
        )
        SELECT 
            NEW.id,
            COALESCE(MAX(version_number), 0) + 1,
            NEW.updated_by,
            'auto',
            CONCAT('Auto-saved: Status changed from ', OLD.status, ' to ', NEW.status),
            JSON_OBJECT(
                'id', OLD.id,
                'title', OLD.title,
                'assessment_number', OLD.assessment_number,
                'location_id', OLD.location_id,
                'visit_date', OLD.visit_date,
                'status', OLD.status,
                'overall_risk_level', OLD.overall_risk_level,
                'updated_at', OLD.updated_at
            ),
            NEW.status
        FROM assessment_versions
        WHERE assessment_id = NEW.id;
    END IF;
END//

DELIMITER ;

-- Default Risk Categories
INSERT INTO risk_categories (name, description, display_order, active) VALUES
('Vehicle Operations', 'Risks related to vehicle movement and operations', 1, 1),
('Loading/Unloading', 'Risks during loading and unloading activities', 2, 1),
('Access & Egress', 'Risks related to entering and exiting vehicles/sites', 3, 1),
('Environmental', 'Weather and environmental hazards', 4, 1),
('People & Pedestrians', 'Risks involving interaction with people', 5, 1),
('Equipment & Tools', 'Equipment and tool-related hazards', 6, 1),
('Manual Handling', 'Lifting and manual handling risks', 7, 1),
('Site Specific', 'Location-specific hazards', 8, 1),
('Traffic Management', 'Traffic and parking hazards', 9, 1),
('Other Hazards', 'Miscellaneous risks', 10, 1);

-- Pre-loaded Hazard Templates
INSERT INTO risk_items (category_id, name, description, default_likelihood, default_severity, typical_controls, affected_persons, residual_risk, is_template, created_by) VALUES
-- Vehicle Operations (category 1)
(1, 'Reversing Collisions', 'Risk of collision when reversing HGV vehicles, particularly in confined spaces with limited visibility. Pedestrians, cyclists, and other vehicles in the vicinity are at risk of being struck.', 4, 4, 'Install reversing cameras and audible warning systems; Implement banksman procedures; Create designated reversing zones with signage; Regular driver training on reversing procedures; High-visibility clothing for all personnel', 'Drivers, pedestrians, cyclists, site workers, delivery personnel', 'Medium', 1, 1),
(1, 'Vehicle Rollover', 'Risk of vehicle tipping or rolling over due to unstable ground, uneven surfaces, excessive speed on corners, or improper loading causing weight distribution issues.', 2, 5, 'Ground condition surveys before parking; Designated parking areas with stable surfaces; Speed limits enforced; Load securing procedures; Driver training on stability', 'Drivers, nearby workers, public', 'Low', 1, 1),
(1, 'Blind Spot Incidents', 'Large blind spots on HGVs create risk of collision with pedestrians, cyclists, or other vehicles that are not visible to the driver despite use of mirrors.', 4, 4, 'Blind spot awareness training; Additional mirrors and camera systems; Exclusion zones around vehicles; Pedestrian barriers and walkways; Visual warning signs', 'Pedestrians, cyclists, motorcyclists, other drivers', 'Medium', 1, 1),

-- Loading/Unloading (category 2)
(2, 'Manual Handling Injuries', 'Risk of musculoskeletal injuries when manually handling heavy or awkward items during delivery. Back injuries, strains, and sprains are common.', 5, 3, 'Manual handling training; Mechanical handling aids provided; Two-person lift procedures for heavy items; Regular breaks; Assessment of all loads', 'Delivery drivers, warehouse staff, goods receivers', 'Low', 1, 1),
(2, 'Falls from Height', 'Risk of falling from vehicle bed, tail lift, or loading bay during loading/unloading operations. Can result in serious injury.', 3, 5, 'Edge protection on loading bays; Tail lift training and certification; Three points of contact rule; Non-slip surfaces; Safety harnesses where appropriate', 'Drivers, warehouse staff, loading personnel', 'Low', 1, 1),
(2, 'Crushing Injuries', 'Risk of being crushed between vehicle and loading bay, or by moving loads. Particularly during reversing or when loads shift unexpectedly.', 3, 5, 'Designated safe zones; Barriers between vehicle and pedestrians; Load securing procedures; Communication protocols; Emergency stop systems', 'All personnel in loading area', 'Low', 1, 1),
(2, 'Falling Objects', 'Risk of items falling from vehicle during loading/unloading, or from stacked pallets. Can cause head injuries and crush injuries.', 4, 4, 'Proper load securing; Stack height limits; Safety helmets in loading areas; Load restraint equipment; Regular equipment inspections', 'Drivers, warehouse staff, nearby workers', 'Low', 1, 1),

-- Access & Egress (category 3)
(3, 'Slips and Trips', 'Risk of slipping on wet/icy steps, tripping on uneven surfaces, or falling when entering/exiting vehicle cab or cargo area.', 5, 2, 'Regular cleaning and gritting of steps; Anti-slip surfaces on steps and platforms; Good lighting; Three points of contact rule; Handrails maintained', 'All drivers and personnel accessing vehicles', 'Low', 1, 1),
(3, 'Unsafe Access Points', 'Poorly maintained or inadequate access points to vehicles or loading areas. Missing handrails, damaged steps, or improper access routes.', 3, 4, 'Regular vehicle inspections; Maintenance schedule for access equipment; Clear access routes; Proper lighting; Signage for safe access points', 'Drivers, maintenance staff, loading personnel', 'Low', 1, 1),
(3, 'Door Strike Injuries', 'Risk of injury from vehicle doors (cab doors, trailer doors, tail lift gates) swinging open unexpectedly or being blown by wind.', 4, 3, 'Door restraint systems; Wind awareness training; Designated door opening areas; Regular maintenance of door mechanisms; Warning signs', 'Drivers, nearby workers, public', 'Low', 1, 1),

-- Environmental (category 4)
(4, 'Poor Visibility', 'Reduced visibility due to fog, heavy rain, darkness, or bright sunlight affecting driving safety and site operations.', 4, 3, 'Headlights and fog lights used appropriately; Reduce speed in poor conditions; Enhanced lighting at delivery locations; High-visibility clothing; Delay deliveries in extreme conditions', 'Drivers, pedestrians, cyclists, site workers', 'Low', 1, 1),
(4, 'Ice and Snow Hazards', 'Slippery surfaces due to ice and snow affecting vehicle control, walking surfaces, and loading/unloading operations.', 4, 4, 'Winter tyres or chains; Gritting and snow clearing procedures; Appropriate footwear; Risk assessment before travel; Alternative access routes identified', 'All personnel on site', 'Medium', 1, 1),
(4, 'Extreme Weather', 'High winds, storms, or extreme temperatures affecting safe operations and creating hazards from flying debris or heat/cold stress.', 3, 4, 'Weather monitoring systems; Suspension of operations in severe weather; Secure all loose items; Shelter provided; Appropriate PPE for conditions', 'All personnel, especially outdoor workers', 'Low', 1, 1),

-- People & Pedestrians (category 5)
(5, 'Vehicle-Pedestrian Collision', 'Risk of striking pedestrians in delivery areas, particularly in busy locations with high foot traffic or where pedestrians and vehicles share space.', 4, 5, 'Segregated pedestrian walkways; Barriers and bollards; Speed limits enforced; Banksman used in congested areas; Audible reversing alarms; High-visibility clothing mandatory', 'Pedestrians, site workers, public, vulnerable road users', 'Medium', 1, 1),
(5, 'Contractor Coordination', 'Risk of incidents when multiple contractors or delivery vehicles are operating in same area without proper coordination.', 4, 3, 'Site coordination meetings; Permit to work systems; Clear communication protocols; Designated parking and unloading areas; Traffic management plan', 'All workers on site, delivery drivers', 'Low', 1, 1),
(5, 'Lone Working', 'Drivers working alone during deliveries, particularly at remote locations or outside normal hours, unable to summon help if injured.', 3, 4, 'Lone working procedures; Regular check-in systems; Mobile phones provided; Panic alarms; Journey planning; Risk assessment for each location', 'Drivers working alone', 'Low', 1, 1);

-- Default Permissions
INSERT INTO permissions (role, resource, can_read, can_create, can_update, can_delete) VALUES
-- Admin - Full access to everything
('admin', 'assessments', 1, 1, 1, 1),
('admin', 'locations', 1, 1, 1, 1),
('admin', 'users', 1, 1, 1, 1),
('admin', 'hazards', 1, 1, 1, 1),
('admin', 'audit', 1, 1, 1, 1),
('admin', 'settings', 1, 1, 1, 1),
('admin', 'permissions', 1, 1, 1, 1),
('admin', 'reports', 1, 1, 1, 1),

-- Assessor - Can create and edit assessments and locations
('assessor', 'assessments', 1, 1, 1, 0),
('assessor', 'locations', 1, 1, 1, 0),
('assessor', 'users', 1, 0, 0, 0),
('assessor', 'hazards', 1, 1, 1, 0),
('assessor', 'audit', 0, 0, 0, 0),
('assessor', 'settings', 1, 0, 0, 0),
('assessor', 'permissions', 0, 0, 0, 0),
('assessor', 'reports', 1, 0, 0, 0),

-- Viewer - Read-only access
('viewer', 'assessments', 1, 0, 0, 0),
('viewer', 'locations', 1, 0, 0, 0),
('viewer', 'users', 1, 0, 0, 0),
('viewer', 'hazards', 1, 0, 0, 0),
('viewer', 'audit', 0, 0, 0, 0),
('viewer', 'settings', 1, 0, 0, 0),
('viewer', 'permissions', 0, 0, 0, 0),
('viewer', 'reports', 1, 0, 0, 0);

-- Default Retention Policies
INSERT INTO retention_policies (entity_type, retention_days, archive_after_days, policy_description) VALUES
('audit_log', 730, 365, 'Audit logs retained for 2 years, archived after 1 year'),
('assessment_versions', 1095, 730, 'Assessment versions retained for 3 years'),
('user_activity', 365, 180, 'User activity logs retained for 1 year'),
('assessments', 1825, 1095, 'Assessments retained for 5 years (regulatory requirement)');

-- Default System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('site_name', 'Risk Assessment System', 'text', 'Name of the application', 1),
('items_per_page', '20', 'number', 'Number of items to display per page', 0),
('enable_email_notifications', 'true', 'boolean', 'Enable email notifications', 0),
('assessment_number_prefix', 'RA', 'text', 'Prefix for assessment numbers', 0),
('date_format', 'd/m/Y', 'text', 'Date display format', 0),
('timezone', 'UTC', 'text', 'System timezone', 0);

-- ========================================
-- SECTION 7: INDEXES FOR PERFORMANCE
-- ========================================

-- Additional composite indexes for common queries
CREATE INDEX idx_assessment_location_date ON assessments(location_id, visit_date);
CREATE INDEX idx_assessment_status_date ON assessments(status, created_at);
CREATE INDEX idx_action_items_status_date ON action_items(status, due_date);

-- ========================================
-- COMPLETION MESSAGE
-- ========================================

SELECT 'Database schema created successfully!' as Status,
       'Total Tables: 23' as Tables,
       'Version: 3.4.0' as Version,
       'Ready for use!' as Message;

-- Show all tables
SHOW TABLES;
