-- ============================================================================
-- COMPLETE DATABASE SCHEMA - Risk Assessment System
-- ============================================================================
-- This file contains ALL tables, data, and features in one place
-- Use this for NEW installations
-- Database: scubatricky_risk
-- Version: 2.0 (Complete)
-- Date: 2026-01-07
-- ============================================================================

-- Drop existing database if recreating from scratch (CAUTION!)
-- DROP DATABASE IF EXISTS scubatricky_risk;

-- Create database
CREATE DATABASE IF NOT EXISTS scubatricky_risk CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE scubatricky_risk;

-- 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 JSON,
    delivery_windows JSON,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    what3words VARCHAR(100),
    google_maps_place_id VARCHAR(255),
    google_maps_url TEXT,
    google_street_view_url TEXT,
    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_location_name (location_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicle Types Table
CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_display_order (display_order),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Delivery Volumes Table
CREATE TABLE IF NOT EXISTS delivery_volumes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    volume_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_display_order (display_order),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Risk Categories Table
CREATE TABLE IF NOT EXISTS risk_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(20),
    color VARCHAR(7),
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_display_order (display_order),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES risk_categories(id) ON DELETE CASCADE,
    INDEX idx_category_id (category_id),
    INDEX idx_display_order (display_order),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessments Table
CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_number VARCHAR(50) NOT NULL UNIQUE,
    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(100),
    delivery_volume VARCHAR(100),
    overall_risk_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    status ENUM('draft', 'completed', 'archived') DEFAULT 'draft',
    notes TEXT,
    recommendations TEXT,
    current_version_id INT NULL,
    version_count INT DEFAULT 1,
    last_revised_at TIMESTAMP NULL,
    last_revised_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP 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,
    FOREIGN KEY (last_revised_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_assessment_number (assessment_number),
    INDEX idx_location_id (location_id),
    INDEX idx_assessor_id (assessor_id),
    INDEX idx_assessment_date (assessment_date),
    INDEX idx_status (status),
    INDEX idx_overall_risk_level (overall_risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessment Details Table
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,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE RESTRICT,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_risk_item_id (risk_item_id),
    INDEX idx_risk_score (risk_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Photos Table
CREATE TABLE IF NOT EXISTS photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT,
    mime_type VARCHAR(50),
    caption TEXT,
    uploaded_by INT NOT NULL,
    display_order INT DEFAULT 0,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CUSTOM FIELDS SYSTEM
-- ============================================================================

-- Custom Fields Definition Table
CREATE TABLE IF NOT EXISTS custom_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL UNIQUE,
    field_label VARCHAR(200) NOT NULL,
    field_type ENUM('text', 'number', 'date', 'select', 'textarea', 'checkbox') NOT NULL,
    field_options JSON,
    is_required BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    help_text TEXT,
    default_value VARCHAR(255),
    validation_rules JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_field_name (field_name),
    INDEX idx_is_active (is_active),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custom Field Values Table
CREATE TABLE IF NOT EXISTS custom_field_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    field_id INT NOT NULL,
    field_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES custom_fields(id) ON DELETE CASCADE,
    UNIQUE KEY unique_location_field (location_id, field_id),
    INDEX idx_location_id (location_id),
    INDEX idx_field_id (field_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- VERSION CONTROL SYSTEM
-- ============================================================================

-- Assessment Versions Table
CREATE TABLE IF NOT EXISTS assessment_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_number INT NOT NULL,
    assessment_number VARCHAR(50) 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(100),
    delivery_volume VARCHAR(100),
    overall_risk_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    status ENUM('draft', 'completed', 'archived') DEFAULT 'draft',
    notes TEXT,
    recommendations TEXT,
    version_type ENUM('original', 'revision', 'major_revision') DEFAULT 'revision',
    revision_reason TEXT,
    changes_summary TEXT,
    is_current BOOLEAN DEFAULT FALSE,
    supersedes_version INT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE RESTRICT,
    FOREIGN KEY (assessor_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_assessment_version (assessment_id, version_number),
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_version_number (version_number),
    INDEX idx_is_current (is_current),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessment Version Details Table
CREATE TABLE IF NOT EXISTS assessment_version_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version_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,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE RESTRICT,
    INDEX idx_version_id (version_id),
    INDEX idx_risk_item_id (risk_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessment Version Photos Table
CREATE TABLE IF NOT EXISTS assessment_version_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
    INDEX idx_version_id (version_id),
    INDEX idx_photo_id (photo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessment Change Log Table
CREATE TABLE IF NOT EXISTS assessment_change_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    from_version INT NULL,
    to_version INT NULL,
    action_type ENUM('created', 'revised', 'archived', 'restored', 'superseded') NOT NULL,
    change_description TEXT,
    changed_by INT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_changed_at (changed_at),
    INDEX idx_action_type (action_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assessment Archives Table
CREATE TABLE IF NOT EXISTS assessment_archives (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_id INT NOT NULL,
    archive_reason TEXT,
    archived_by INT NOT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    can_restore BOOLEAN DEFAULT TRUE,
    restore_notes TEXT,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (version_id) REFERENCES assessment_versions(id) ON DELETE CASCADE,
    FOREIGN KEY (archived_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_version_id (version_id),
    INDEX idx_archived_at (archived_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PDF TEMPLATE SYSTEM
-- ============================================================================

-- PDF Templates Table
CREATE TABLE IF NOT EXISTS pdf_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    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 FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    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 RESTRICT,
    INDEX idx_template_type (template_type),
    INDEX idx_is_default (is_default),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PDF Field Mapping Table
CREATE TABLE IF NOT EXISTS pdf_field_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    field_label VARCHAR(100) NOT NULL,
    field_type ENUM('text', 'number', 'date', 'datetime', 'image') DEFAULT 'text',
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_table_field (table_name, field_name),
    INDEX idx_table_name (table_name),
    INDEX idx_is_active (is_active),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DEFAULT DATA - VEHICLE TYPES
-- ============================================================================

INSERT INTO vehicle_types (type_name, description, display_order) VALUES
('Small Van (3.5t)', 'Standard small delivery van', 1),
('Large Van (7.5t)', 'Larger delivery vehicle', 2),
('Rigid Truck (18t)', '18 tonne rigid truck', 3),
('Articulated Lorry (44t)', 'Full size articulated lorry', 4),
('Other', 'Other vehicle type', 99)
ON DUPLICATE KEY UPDATE type_name=VALUES(type_name);

-- ============================================================================
-- DEFAULT DATA - DELIVERY VOLUMES
-- ============================================================================

INSERT INTO delivery_volumes (volume_name, description, display_order) VALUES
('Small (< 10 cages)', 'Less than 10 roll cages', 1),
('Medium (10-20 cages)', '10 to 20 roll cages', 2),
('Large (20-30 cages)', '20 to 30 roll cages', 3),
('Extra Large (30+ cages)', 'More than 30 roll cages', 4),
('Other', 'Custom volume', 99)
ON DUPLICATE KEY UPDATE volume_name=VALUES(volume_name);

-- ============================================================================
-- DEFAULT DATA - RISK CATEGORIES
-- ============================================================================

INSERT INTO risk_categories (category_name, description, icon, color, display_order) VALUES
('Access & Egress', 'Risks related to entering and exiting the delivery location', '🚪', '#3B82F6', 1),
('Manual Handling', 'Risks from lifting, carrying, pushing and pulling', '📦', '#F59E0B', 2),
('Vehicle Movements', 'Risks from reversing, parking and maneuvering', '🚛', '#DC2626', 3),
('Environmental', 'Weather, lighting, surface conditions', '🌦️', '#059669', 4),
('Security', 'Personal safety and security concerns', '🔒', '#8B5CF6', 5),
('Equipment', 'Risks from use of equipment and tools', '🔧', '#EC4899', 6)
ON DUPLICATE KEY UPDATE category_name=VALUES(category_name);

-- ============================================================================
-- DEFAULT DATA - RISK ITEMS
-- ============================================================================

INSERT INTO risk_items (category_id, item_name, description, display_order) VALUES
-- Access & Egress (Category 1)
((SELECT id FROM risk_categories WHERE category_name = 'Access & Egress'), 'Narrow entrance/exit', 'Limited space for safe access', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Access & Egress'), 'Steps or stairs', 'Uneven surfaces, trip hazards', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Access & Egress'), 'Poor lighting', 'Inadequate visibility at access points', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Access & Egress'), 'Obstructions', 'Blocked or restricted access routes', 4),

-- Manual Handling (Category 2)
((SELECT id FROM risk_categories WHERE category_name = 'Manual Handling'), 'Heavy items', 'Items requiring team lift or equipment', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Manual Handling'), 'Awkward shapes', 'Difficult to grip or carry items', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Manual Handling'), 'Repetitive lifting', 'Multiple items over extended period', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Manual Handling'), 'Long carry distance', 'Extended distance from vehicle to delivery point', 4),

-- Vehicle Movements (Category 3)
((SELECT id FROM risk_categories WHERE category_name = 'Vehicle Movements'), 'Limited visibility', 'Blind spots, restricted view', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Vehicle Movements'), 'Pedestrian traffic', 'Public or staff in delivery area', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Vehicle Movements'), 'Tight maneuvering space', 'Limited room for vehicle movement', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Vehicle Movements'), 'Reversing required', 'Need to reverse vehicle', 4),

-- Environmental (Category 4)
((SELECT id FROM risk_categories WHERE category_name = 'Environmental'), 'Wet/icy surfaces', 'Slip hazards from weather', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Environmental'), 'Uneven ground', 'Potholes, slopes, rough surface', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Environmental'), 'Extreme weather', 'High winds, heavy rain, snow', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Environmental'), 'Poor visibility conditions', 'Fog, darkness, glare', 4),

-- Security (Category 5)
((SELECT id FROM risk_categories WHERE category_name = 'Security'), 'Isolated location', 'Remote or poorly monitored area', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Security'), 'Aggressive animals', 'Dogs or other animals present', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Security'), 'Previous incidents', 'History of theft or aggression', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Security'), 'Inadequate lighting', 'Poor visibility for security', 4),

-- Equipment (Category 6)
((SELECT id FROM risk_categories WHERE category_name = 'Equipment'), 'Equipment failure', 'Faulty trolleys, cages, straps', 1),
((SELECT id FROM risk_categories WHERE category_name = 'Equipment'), 'Inappropriate equipment', 'Wrong equipment for task', 2),
((SELECT id FROM risk_categories WHERE category_name = 'Equipment'), 'Equipment damage', 'Damaged or worn equipment', 3),
((SELECT id FROM risk_categories WHERE category_name = 'Equipment'), 'Missing equipment', 'Required equipment not available', 4)
ON DUPLICATE KEY UPDATE item_name=VALUES(item_name);

-- ============================================================================
-- DEFAULT DATA - PDF FIELD MAPPINGS
-- ============================================================================

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_line1', 'Address Line 1', 'text', 12),
('locations', 'address_line2', 'Address Line 2', 'text', 13),
('locations', 'city', 'City', 'text', 14),
('locations', 'postcode', 'Postcode', 'text', 15),
('locations', 'region', 'Region', 'text', 16),
('locations', 'contact_name', 'Contact Name', 'text', 17),
('locations', 'contact_phone', 'Contact Phone', 'text', 18),
('locations', 'contact_email', 'Contact Email', 'text', 19),
-- Users table
('users', 'full_name', 'Assessor Name', 'text', 20),
('users', 'email', 'Assessor Email', 'text', 21),
('users', 'username', 'Assessor Username', 'text', 22),
-- Assessment Details
('assessment_details', 'likelihood', 'Likelihood Rating', 'number', 23),
('assessment_details', 'severity', 'Severity Rating', 'number', 24),
('assessment_details', 'risk_score', 'Risk Score', 'number', 25),
('assessment_details', 'current_controls', 'Current Controls', 'text', 26),
('assessment_details', 'additional_actions', 'Additional Actions', 'text', 27),
('assessment_details', 'action_owner', 'Action Owner', 'text', 28),
('assessment_details', 'target_date', 'Target Date', 'date', 29),
-- Risk Items
('risk_items', 'item_name', 'Risk Item Name', 'text', 30),
('risk_items', 'description', 'Risk Item Description', 'text', 31),
-- Risk Categories
('risk_categories', 'category_name', 'Risk Category', 'text', 32),
('risk_categories', 'description', 'Category Description', 'text', 33),
-- Photos
('photos', 'caption', 'Photo Caption', 'text', 34),
('photos', 'uploaded_at', 'Photo Upload Date', 'datetime', 35)
ON DUPLICATE KEY UPDATE field_label=VALUES(field_label);

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Additional composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_assessment_location_date ON assessments(location_id, assessment_date);
CREATE INDEX IF NOT EXISTS idx_assessment_assessor_date ON assessments(assessor_id, assessment_date);
CREATE INDEX IF NOT EXISTS idx_details_assessment_item ON assessment_details(assessment_id, risk_item_id);
CREATE INDEX IF NOT EXISTS idx_photos_assessment_order ON photos(assessment_id, display_order);

-- High Risk Items View
CREATE OR REPLACE VIEW view_high_risk_items AS
SELECT 
    a.assessment_number,
    l.location_name,
    rc.category_name,
    ri.item_name,
    ad.likelihood,
    ad.severity,
    ad.risk_score,
    ad.current_controls,
    a.assessment_date
FROM assessment_details ad
JOIN assessments a ON ad.assessment_id = a.id
JOIN risk_items ri ON ad.risk_item_id = ri.id
JOIN risk_categories rc ON ri.category_id = rc.id
JOIN locations l ON a.location_id = l.id
WHERE ad.risk_score >= 15
ORDER BY ad.risk_score DESC, a.assessment_date DESC;

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

SELECT 'Database schema created successfully!' as Message;
SELECT 'Default admin user: admin / admin123 (CHANGE PASSWORD!)' as Important;
SELECT 'Database name: scubatricky_risk' as Database_Name;

-- ============================================================================
-- SYSTEM CUSTOMIZATION
-- ============================================================================

-- System Settings Table
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('string', 'json', 'boolean', 'integer') DEFAULT 'string',
    description TEXT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_setting_key (setting_key),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('system_name', 'Risk Assessment', 'string', 'System name displayed in header'),
('color_scheme', NULL, 'json', 'Custom color scheme configuration'),
('logo_filename', NULL, 'string', 'Custom logo filename'),
('timezone', 'Europe/London', 'string', 'System timezone'),
('date_format', 'd/m/Y', 'string', 'Date display format'),
('time_format', 'H:i', 'string', 'Time display format')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

