-- Risk Assessment Database Schema
CREATE DATABASE IF NOT EXISTS scubatricky_risk CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE scubatricky_risk;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'assessor', 'viewer') DEFAULT 'assessor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Delivery locations table
CREATE TABLE IF NOT EXISTS locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_name VARCHAR(200) NOT NULL,
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    postcode VARCHAR(10) NOT NULL,
    region VARCHAR(100),
    contact_name VARCHAR(100),
    contact_phone VARCHAR(20),
    contact_email VARCHAR(100),
    notes TEXT,
    -- Opening Hours (stored as JSON for flexibility)
    opening_hours JSON,
    -- Delivery Windows (stored as JSON array of time slots)
    delivery_windows JSON,
    -- GPS Coordinates
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    -- What3Words address
    what3words VARCHAR(100),
    -- Google Maps data
    google_maps_place_id VARCHAR(255),
    google_maps_url TEXT,
    google_street_view_url TEXT,
    -- Custom Fields (stored as JSON for dynamic fields)
    custom_fields JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_postcode (postcode),
    INDEX idx_city (city),
    INDEX idx_coordinates (latitude, longitude),
    INDEX idx_what3words (what3words)
) ENGINE=InnoDB;

-- Custom Field Definitions table
CREATE TABLE IF NOT EXISTS custom_field_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL,
    field_label VARCHAR(200) NOT NULL,
    field_type ENUM('text', 'textarea', 'number', 'date', 'time', 'select', 'checkbox', 'url', 'email', 'tel') NOT NULL,
    field_options TEXT, -- JSON array for select options
    is_required BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    help_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_field_name (field_name),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB;

-- Insert some default custom fields
INSERT INTO custom_field_definitions (field_name, field_label, field_type, help_text, display_order) VALUES
('loading_bay_number', 'Loading Bay Number', 'text', 'Specific bay number or identifier', 1),
('height_restriction', 'Height Restriction (meters)', 'number', 'Maximum vehicle height allowed', 2),
('weight_restriction', 'Weight Restriction (tonnes)', 'number', 'Maximum vehicle weight allowed', 3),
('access_code', 'Access/Security Code', 'text', 'Gate or door entry code', 4),
('parking_spaces', 'Available Parking Spaces', 'number', 'Number of parking/waiting spaces', 5),
('site_manager_contact', 'Site Manager Contact', 'tel', 'Direct phone number for site manager', 6),
('alternative_access', 'Alternative Access Route', 'textarea', 'Backup route if primary is blocked', 7),
('unloading_equipment', 'Unloading Equipment Available', 'text', 'e.g., Forklift, Pallet jack, Tail lift required', 8);

-- Risk assessments table
CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_number VARCHAR(50) UNIQUE NOT NULL,
    location_id INT NOT NULL,
    assessor_id INT NOT NULL,
    assessment_date DATE NOT NULL,
    assessment_time TIME NOT NULL,
    weather_conditions VARCHAR(100),
    vehicle_type VARCHAR(50),
    delivery_volume VARCHAR(50),
    overall_risk_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    status ENUM('draft', 'completed', 'reviewed', 'archived') DEFAULT 'draft',
    notes TEXT,
    recommendations TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    FOREIGN KEY (assessor_id) REFERENCES users(id),
    INDEX idx_assessment_date (assessment_date),
    INDEX idx_status (status),
    INDEX idx_risk_level (overall_risk_level)
) ENGINE=InnoDB;

-- Risk categories table
CREATE TABLE IF NOT EXISTS risk_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- Vehicle types reference
CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- Delivery volumes reference
CREATE TABLE IF NOT EXISTS delivery_volumes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    volume_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- Risk items table
CREATE TABLE IF NOT EXISTS risk_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    item_name VARCHAR(200) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (category_id) REFERENCES risk_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Assessment details table (individual risk item ratings)
CREATE TABLE IF NOT EXISTS assessment_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    risk_item_id INT NOT NULL,
    likelihood INT NOT NULL CHECK (likelihood BETWEEN 1 AND 5),
    severity INT NOT NULL CHECK (severity BETWEEN 1 AND 5),
    risk_score INT GENERATED ALWAYS AS (likelihood * severity) STORED,
    current_controls TEXT,
    additional_actions TEXT,
    action_owner VARCHAR(100),
    target_date DATE,
    photos TEXT, -- JSON array of photo filenames
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id),
    INDEX idx_risk_score (risk_score)
) ENGINE=InnoDB;

-- Photos table
CREATE TABLE IF NOT EXISTS photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    detail_id INT,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255),
    file_size INT,
    mime_type VARCHAR(50),
    caption TEXT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (detail_id) REFERENCES assessment_details(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Audit log table
CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values TEXT,
    new_values TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

-- Offline sync queue table
CREATE TABLE IF NOT EXISTS sync_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    operation ENUM('insert', 'update', 'delete') NOT NULL,
    data TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL,
    sync_status ENUM('pending', 'synced', 'failed') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sync_status (sync_status)
) ENGINE=InnoDB;

-- Insert default risk categories
INSERT INTO risk_categories (category_name, description, display_order) VALUES
('Vehicle Access', 'Risks related to vehicle access and maneuverability', 1),
('Loading Bay', 'Risks in the loading/unloading area', 2),
('Pedestrian Safety', 'Risks to pedestrians and delivery personnel', 3),
('Environmental Hazards', 'Weather, lighting, and environmental conditions', 4),
('Manual Handling', 'Risks during manual handling of goods', 5),
('Security', 'Security and access control risks', 6);

-- Insert default risk items
INSERT INTO risk_items (category_id, item_name, description, display_order) VALUES
(1, 'Tight turning circles', 'Limited space for vehicle maneuvers', 1),
(1, 'Overhead clearance', 'Low bridges, overhangs, or height restrictions', 2),
(1, 'Parking area surface', 'Condition of parking surface (potholes, uneven)', 3),
(1, 'Reversing space', 'Adequate space for reversing maneuvers', 4),
(2, 'Loading dock height', 'Compatibility with vehicle tailgate height', 1),
(2, 'Bay surface condition', 'Trip hazards, spillages, or damage', 2),
(2, 'Weather protection', 'Cover from rain, wind, or snow', 3),
(2, 'Equipment availability', 'Availability of trolleys, pallet jacks', 4),
(3, 'Pedestrian segregation', 'Separation between vehicles and pedestrians', 1),
(3, 'Visibility', 'Blind spots or poor sightlines', 2),
(3, 'Signage', 'Warning signs and safety instructions', 3),
(3, 'Traffic management', 'Control of vehicle and pedestrian movement', 4),
(4, 'Lighting levels', 'Adequate lighting for safe operations', 1),
(4, 'Surface conditions', 'Ice, water, leaves, or debris', 2),
(4, 'Wind exposure', 'Risk of wind affecting stability', 3),
(4, 'Temperature extremes', 'Very hot or cold conditions', 4),
(5, 'Load weight', 'Heavy items requiring team lift', 1),
(5, 'Repetitive movements', 'High volume deliveries', 2),
(5, 'Awkward postures', 'Bending, reaching, or twisting required', 3),
(5, 'Distance to carry', 'Long distances from vehicle to store', 4),
(6, 'Site access control', 'Barriers, gates, or security presence', 1),
(6, 'Lone working', 'Working alone without support', 2),
(6, 'Public access', 'Unrestricted access by public', 3),
(6, 'Valuable goods', 'Risk of theft of high-value items', 4);

-- Insert default admin user (password: Admin123!)
-- Note: This is a bcrypt hash. If login fails, use reset-admin.php to reset the password
INSERT INTO users (username, email, password_hash, full_name, role) VALUES
('admin', 'admin@example.com', '$2y$10$rQZ9kN.6YXxL0V8BKXKj4eRZKJxJ0gxKvX8cHJGqK8qzN6JqP6/J2', 'System Administrator', 'admin');

-- Insert default vehicle types
INSERT INTO vehicle_types (type_name, description, display_order) VALUES
('Small Van', 'Small delivery van (e.g., Transit Connect, Caddy)', 1),
('Large Van', 'Large panel van (e.g., Sprinter, Transit)', 2),
('7.5 Tonne', '7.5 tonne rigid vehicle', 3),
('Rigid Truck', 'Rigid truck (18-26 tonne)', 4),
('Articulated Lorry', 'Articulated lorry/HGV', 5),
('Box Truck', 'Box truck with tail lift', 6),
('Flatbed', 'Flatbed truck', 7);

-- Insert default delivery volumes
INSERT INTO delivery_volumes (volume_name, description, display_order) VALUES
('Small (1-10 items)', 'Small delivery, few items', 1),
('Medium (11-50 items)', 'Medium delivery', 2),
('Large (51-100 items)', 'Large delivery', 3),
('Very Large (100+ items)', 'Very large or full load', 4),
('Full Pallet', 'Full pallet delivery', 5),
('Multiple Pallets', 'Multiple pallets', 6);

-- PDF template system
CREATE TABLE IF NOT EXISTS pdf_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    template_type ENUM('assessment', 'location', 'summary', 'custom') DEFAULT 'assessment',
    page_size ENUM('A4', 'Letter', 'Legal', 'A3') DEFAULT 'A4',
    orientation ENUM('portrait', 'landscape') DEFAULT 'portrait',
    template_config JSON,
    is_default BOOLEAN DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pdf_field_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    field_name VARCHAR(100) NOT NULL,
    field_label VARCHAR(200) NOT NULL,
    field_type ENUM('text', 'number', 'date', 'datetime', 'boolean', 'json') DEFAULT 'text',
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    display_order INT DEFAULT 0,
    UNIQUE KEY unique_table_field (table_name, field_name)
) ENGINE=InnoDB;

-- Populate PDF field mappings for template designer
INSERT INTO pdf_field_mapping (table_name, field_name, field_label, field_type, display_order) VALUES
-- Assessments table
('assessments', 'assessment_number', 'Assessment Number', 'text', 1),
('assessments', 'assessment_date', 'Assessment Date', 'date', 2),
('assessments', 'assessment_time', 'Assessment Time', 'text', 3),
('assessments', 'weather_conditions', 'Weather Conditions', 'text', 4),
('assessments', 'vehicle_type', 'Vehicle Type', 'text', 5),
('assessments', 'delivery_volume', 'Delivery Volume', 'text', 6),
('assessments', 'overall_risk_level', 'Overall Risk Level', 'text', 7),
('assessments', 'status', 'Status', 'text', 8),
('assessments', 'notes', 'Notes', 'text', 9),
('assessments', 'recommendations', 'Recommendations', 'text', 10),
-- Locations table
('locations', 'location_name', 'Location Name', 'text', 11),
('locations', 'address', 'Address', 'text', 12),
('locations', 'city', 'City', 'text', 13),
('locations', 'county', 'County', 'text', 14),
('locations', 'postcode', 'Postcode', 'text', 15),
('locations', 'contact_name', 'Contact Name', 'text', 19),
('locations', 'contact_phone', 'Contact Phone', 'text', 20),
('locations', 'contact_email', 'Contact Email', 'text', 21),
-- Users table
('users', 'full_name', 'Assessor Name', 'text', 22),
('users', 'email', 'Assessor Email', 'text', 23),
-- Assessment Details
('assessment_details', 'likelihood', 'Likelihood Rating', 'number', 25),
('assessment_details', 'severity', 'Severity Rating', 'number', 26),
('assessment_details', 'risk_score', 'Risk Score', 'number', 27),
('assessment_details', 'current_controls', 'Current Controls', 'text', 28),
-- Risk Items
('risk_items', 'item_name', 'Risk Item Name', 'text', 32),
-- Risk Categories
('risk_categories', 'category_name', 'Risk Category', 'text', 34);
