-- Risk Assessment System Database Schema
-- Version: 3.0.0 - HGV Delivery Safety Focus

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 (UK Supermarkets)
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    address TEXT NOT NULL,
    postcode VARCHAR(10) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(50) DEFAULT 'United Kingdom',
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    what3words VARCHAR(100) NULL,
    google_maps_url TEXT NULL,
    supermarket_chain VARCHAR(100) NULL COMMENT 'Tesco, Sainsburys, Asda, Morrisons, etc.',
    store_type VARCHAR(50) NULL,
    delivery_access_notes TEXT NULL,
    contact_person VARCHAR(100) NULL,
    contact_phone VARCHAR(20) NULL,
    contact_email VARCHAR(100) NULL,
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_postcode (postcode),
    INDEX idx_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- Risk Items 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,
    default_severity INT DEFAULT 3,
    default_controls TEXT NULL,
    active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (category_id) REFERENCES risk_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessments
CREATE TABLE assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_number VARCHAR(50) UNIQUE NOT NULL,
    location_id INT NOT NULL,
    assessor_id INT NOT NULL,
    visit_date DATE NOT NULL,
    visit_time TIME NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    delivery_type VARCHAR(100) NULL,
    vehicle_type VARCHAR(100) NULL,
    weather_conditions VARCHAR(100) NULL,
    status ENUM('draft', 'in_progress', 'completed', 'approved', 'archived') DEFAULT 'draft',
    overall_risk_level ENUM('low', 'medium', 'high', 'critical') NULL,
    is_locked TINYINT(1) DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE RESTRICT,
    FOREIGN KEY (assessor_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_assessment_number (assessment_number),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessment Risks
CREATE TABLE assessment_risks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    risk_item_id INT NULL,
    hazard_name VARCHAR(200) NOT NULL,
    hazard_description TEXT NULL,
    location_area VARCHAR(200) NULL,
    likelihood INT NOT NULL,
    severity INT NOT NULL,
    risk_score INT GENERATED ALWAYS AS (likelihood * severity) STORED,
    risk_level VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN (likelihood * severity) <= 4 THEN 'low'
            WHEN (likelihood * severity) <= 9 THEN 'medium'
            WHEN (likelihood * severity) <= 16 THEN 'high'
            ELSE 'critical'
        END
    ) STORED,
    existing_controls TEXT NULL,
    additional_controls TEXT NULL,
    photos JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    INDEX idx_assessment (assessment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Action Items
CREATE TABLE action_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    risk_id INT NULL,
    action_description TEXT NOT NULL,
    assigned_to INT NULL,
    due_date DATE NULL,
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- Audit Log
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- System Settings
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    description TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sync Queue (PWA Offline)
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_data JSON NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Risk Categories
INSERT INTO risk_categories (name, description, display_order) VALUES
('Vehicle Operations', 'HGV operation and maneuvering risks', 1),
('Loading/Unloading', 'Loading and unloading activities', 2),
('Access & Egress', 'Entry and exit points', 3),
('Environmental', 'Weather and surface conditions', 4),
('People & Pedestrians', 'Interaction with public', 5);

-- Default Permissions
INSERT INTO permissions (role, resource, can_create, can_read, can_update, can_delete) VALUES
('admin', 'assessments', 1, 1, 1, 1),
('admin', 'locations', 1, 1, 1, 1),
('admin', 'users', 1, 1, 1, 1),
('assessor', 'assessments', 1, 1, 1, 0),
('assessor', 'locations', 1, 1, 1, 0),
('viewer', 'assessments', 0, 1, 0, 0),
('viewer', 'locations', 0, 1, 0, 0);

-- System Settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'HGV Delivery Risk Assessment System', 'Application name'),
('enable_email_notifications', '1', 'Enable email notifications'),
('google_maps_api_key', '', 'Google Maps API key'),
('what3words_api_key', '', 'what3words API key');

SELECT 'Database created successfully!' as Status;
