-- ============================================================================
-- COMPLETE DATABASE SCHEMA - Risk Assessment System
-- ============================================================================
-- This is the ONE AND ONLY schema file you need
-- Version: 3.0 (Complete and Final)
-- Date: 2026-01-09
-- 
-- USAGE:
--   mysql -u username -p database_name < schema.sql
--   OR import via phpMyAdmin
-- ============================================================================


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

-- ============================================================================
-- TABLE 2: LOCATIONS
-- ============================================================================
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),
    report_emails TEXT COMMENT 'Comma-separated email addresses for reports',
    email_frequency ENUM('immediate', 'daily', 'weekly', 'monthly', 'none') DEFAULT 'none',
    last_report_sent TIMESTAMP NULL,
    email_enabled BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_location_name (location_name),
    INDEX idx_city (city),
    INDEX idx_postcode (postcode),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 3: CATEGORIES
-- ============================================================================
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category_name (category_name),
    INDEX idx_display_order (display_order),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 4: RISK_ITEMS
-- ============================================================================
CREATE TABLE IF NOT EXISTS risk_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    hazard VARCHAR(200) NOT NULL,
    who_affected VARCHAR(200) NOT NULL,
    existing_controls TEXT NOT NULL,
    likelihood_score INT NOT NULL CHECK (likelihood_score BETWEEN 1 AND 5),
    severity_score INT NOT NULL CHECK (severity_score BETWEEN 1 AND 5),
    risk_score INT GENERATED ALWAYS AS (likelihood_score * severity_score) STORED,
    risk_level VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN (likelihood_score * severity_score) BETWEEN 1 AND 6 THEN 'Low'
            WHEN (likelihood_score * severity_score) BETWEEN 7 AND 12 THEN 'Medium'
            WHEN (likelihood_score * severity_score) BETWEEN 13 AND 25 THEN 'High'
        END
    ) STORED,
    additional_controls TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_id (category_id),
    INDEX idx_risk_score (risk_score),
    INDEX idx_risk_level (risk_level),
    INDEX idx_active (is_active),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 5: ASSESSMENTS
-- ============================================================================
CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    user_id INT NOT NULL,
    assessment_number VARCHAR(50) UNIQUE,
    assessment_date DATE NOT NULL,
    assessor_name VARCHAR(100) NOT NULL,
    review_date DATE,
    overall_risk_level VARCHAR(20),
    status ENUM('draft', 'completed', 'archived') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_location_id (location_id),
    INDEX idx_user_id (user_id),
    INDEX idx_assessment_date (assessment_date),
    INDEX idx_assessment_number (assessment_number),
    INDEX idx_status (status),
    INDEX idx_overall_risk_level (overall_risk_level),
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 6: ASSESSMENT_DETAILS
-- ============================================================================
CREATE TABLE IF NOT EXISTS assessment_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    risk_item_id INT NOT NULL,
    custom_hazard VARCHAR(200),
    custom_who_affected VARCHAR(200),
    custom_existing_controls TEXT,
    likelihood_score INT NOT NULL CHECK (likelihood_score BETWEEN 1 AND 5),
    severity_score INT NOT NULL CHECK (severity_score BETWEEN 1 AND 5),
    risk_score INT GENERATED ALWAYS AS (likelihood_score * severity_score) STORED,
    risk_level VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN (likelihood_score * severity_score) BETWEEN 1 AND 6 THEN 'Low'
            WHEN (likelihood_score * severity_score) BETWEEN 7 AND 12 THEN 'Medium'
            WHEN (likelihood_score * severity_score) BETWEEN 13 AND 25 THEN 'High'
        END
    ) STORED,
    additional_controls TEXT,
    action_required BOOLEAN DEFAULT FALSE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_risk_item_id (risk_item_id),
    INDEX idx_risk_score (risk_score),
    INDEX idx_risk_level (risk_level),
    INDEX idx_action_required (action_required),
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 7: ACTION_ITEMS
-- ============================================================================
CREATE TABLE IF NOT EXISTS action_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    assessment_detail_id INT,
    category VARCHAR(100) NOT NULL,
    hazard VARCHAR(200) NOT NULL,
    action_required TEXT NOT NULL,
    responsible_person VARCHAR(100) NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'overdue') DEFAULT 'pending',
    completion_date DATE,
    notes TEXT,
    risk_score INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_assessment_id (assessment_id),
    INDEX idx_due_date (due_date),
    INDEX idx_status (status),
    INDEX idx_responsible_person (responsible_person),
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (assessment_detail_id) REFERENCES assessment_details(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 8: PERMISSIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('admin', 'assessor', 'viewer') NOT NULL,
    resource VARCHAR(100) NOT NULL COMMENT 'e.g., assessments, locations, users',
    can_create BOOLEAN DEFAULT FALSE,
    can_read BOOLEAN DEFAULT TRUE,
    can_update BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    can_export BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP 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 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 9: SYSTEM_SETTINGS
-- ============================================================================
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 VARCHAR(50) DEFAULT 'text' COMMENT 'text, number, boolean, json, color',
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE COMMENT 'Can be viewed by non-admins',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key),
    INDEX idx_setting_type (setting_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 10: CUSTOM_FIELDS
-- ============================================================================
CREATE TABLE IF NOT EXISTS custom_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL COMMENT 'location, assessment, user, etc',
    field_name VARCHAR(100) NOT NULL COMMENT 'Internal field name (no spaces)',
    field_label VARCHAR(200) NOT NULL COMMENT 'Display label',
    field_type VARCHAR(50) NOT NULL COMMENT 'text, textarea, select, number, date, email, phone, checkbox, radio',
    field_options TEXT COMMENT 'JSON array for select/radio/checkbox options',
    default_value TEXT COMMENT 'Default value for new records',
    is_required BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    validation_rules TEXT COMMENT 'JSON validation rules',
    help_text TEXT COMMENT 'Help text shown to users',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_entity_field (entity_type, field_name),
    INDEX idx_entity_type (entity_type),
    INDEX idx_display_order (display_order),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 11: CUSTOM_FIELD_VALUES
-- ============================================================================
CREATE TABLE IF NOT EXISTS custom_field_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_id INT NOT NULL,
    entity_id INT NOT NULL COMMENT 'ID of the location/assessment/user record',
    field_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_field_entity (field_id, entity_id),
    INDEX idx_entity_id (entity_id),
    FOREIGN KEY (field_id) REFERENCES custom_fields(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 12: SYSTEM_FILES
-- ============================================================================
CREATE TABLE IF NOT EXISTS system_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(500) NOT NULL COMMENT 'Relative path from root',
    file_name VARCHAR(255) NOT NULL,
    file_hash VARCHAR(64) NOT NULL COMMENT 'SHA-256 hash for change detection',
    file_size INT NOT NULL COMMENT 'File size in bytes',
    file_type VARCHAR(50) COMMENT 'php, sql, js, css, etc',
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_checked TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    version INT DEFAULT 1,
    is_core BOOLEAN DEFAULT TRUE COMMENT 'Core system file vs custom',
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    UNIQUE KEY unique_file_path (file_path),
    INDEX idx_file_hash (file_hash),
    INDEX idx_file_type (file_type),
    INDEX idx_last_modified (last_modified),
    INDEX idx_is_core (is_core),
    INDEX idx_active (is_active),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 13: FILE_VERSIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS file_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_id INT NOT NULL,
    version_number INT NOT NULL,
    file_hash VARCHAR(64) NOT NULL,
    file_size INT NOT NULL,
    file_content LONGTEXT COMMENT 'Stored file content for backup',
    changed_by INT,
    change_description TEXT,
    change_type ENUM('created', 'modified', 'deleted', 'restored') DEFAULT 'modified',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_file_version (file_id, version_number),
    INDEX idx_file_hash (file_hash),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (file_id) REFERENCES system_files(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 14: AUDIT_LOG
-- ============================================================================
CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL COMMENT 'create, update, delete, login, etc',
    entity_type VARCHAR(50) COMMENT 'users, assessments, locations, etc',
    entity_id INT COMMENT 'ID of affected record',
    old_values TEXT COMMENT 'JSON of old values',
    new_values TEXT COMMENT 'JSON of new values',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 15: EMAIL_QUEUE
-- ============================================================================
CREATE TABLE IF NOT EXISTS email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_email VARCHAR(255) NOT NULL,
    recipient_name VARCHAR(100),
    subject VARCHAR(500) NOT NULL,
    body_html TEXT NOT NULL,
    body_text TEXT,
    from_email VARCHAR(255),
    from_name VARCHAR(100),
    reply_to VARCHAR(255),
    cc TEXT COMMENT 'Comma-separated email addresses',
    bcc TEXT COMMENT 'Comma-separated email addresses',
    attachments TEXT COMMENT 'JSON array of attachment paths',
    priority ENUM('low', 'normal', 'high') DEFAULT 'normal',
    status ENUM('pending', 'sending', 'sent', 'failed') DEFAULT 'pending',
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    last_attempt TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at),
    INDEX idx_recipient_email (recipient_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE 16: SESSIONS (Optional - for database session storage)
-- ============================================================================
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT NOT NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_last_activity (last_activity),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Categories
INSERT IGNORE INTO categories (id, category_name, description, display_order) VALUES
(1, 'Slips, Trips & Falls', 'Risks related to walking surfaces and obstacles', 1),
(2, 'Manual Handling', 'Lifting, carrying, pushing, pulling activities', 2),
(3, 'Fire Safety', 'Fire hazards and emergency procedures', 3),
(4, 'Electrical Safety', 'Electrical equipment and installations', 4),
(5, 'Chemical Safety', 'Hazardous substances and materials', 5),
(6, 'Work Equipment', 'Tools, machinery, and equipment safety', 6),
(7, 'Display Screen Equipment', 'Computer and screen-related risks', 7),
(8, 'Workplace Environment', 'Lighting, temperature, ventilation, noise', 8),
(9, 'Violence & Aggression', 'Threats, abuse, and violent behavior', 9),
(10, 'Lone Working', 'Working alone or in isolation', 10);

-- Default Risk Items
INSERT IGNORE INTO risk_items (category_id, hazard, who_affected, existing_controls, likelihood_score, severity_score, display_order) VALUES
-- Slips, Trips & Falls
(1, 'Wet floors from cleaning or spillages', 'Staff, customers, visitors', 'Warning signs displayed, immediate cleaning of spills, non-slip floor surfaces', 3, 3, 1),
(1, 'Uneven flooring or damaged floor surfaces', 'Staff, customers, visitors', 'Regular inspections, prompt repairs, warning signs', 2, 4, 2),
(1, 'Trailing cables or obstacles in walkways', 'Staff, customers, visitors', 'Cable management systems, regular housekeeping, clear walkways', 2, 3, 3),
(1, 'Poor lighting in corridors and stairs', 'Staff, customers, visitors', 'Regular bulb replacement, emergency lighting system', 2, 3, 4),

-- Manual Handling
(2, 'Lifting heavy boxes or equipment', 'Staff', 'Manual handling training, team lifting policy, mechanical aids available', 3, 4, 1),
(2, 'Repetitive lifting activities', 'Staff', 'Job rotation, regular breaks, proper lifting techniques', 3, 3, 2),
(2, 'Awkward lifting positions', 'Staff', 'Workstation design, lifting aids, training', 2, 3, 3),

-- Fire Safety
(3, 'Blocked fire exits', 'All building occupants', 'Regular fire exit checks, clear signage, staff training', 2, 5, 1),
(3, 'Faulty fire alarms', 'All building occupants', 'Weekly alarm tests, annual servicing, maintenance log', 1, 5, 2),
(3, 'Combustible materials storage', 'Staff, visitors', 'Designated storage areas, fire-resistant cabinets, regular audits', 2, 4, 3),
(3, 'Electrical equipment fires', 'All building occupants', 'PAT testing, visual inspections, switch off policy', 2, 4, 4),

-- Electrical Safety
(4, 'Damaged electrical equipment', 'Staff', 'Visual inspection before use, PAT testing, reporting system', 2, 4, 1),
(4, 'Overloaded sockets', 'Staff, visitors', 'Socket limit policy, regular checks, additional sockets installed', 2, 3, 2),
(4, 'Working near water with electrical items', 'Staff', 'RCD protection, keep electrical items away from water, training', 2, 4, 3),

-- Chemical Safety
(5, 'Cleaning chemicals', 'Cleaning staff', 'COSHH assessments, PPE provided, training, safety data sheets', 3, 3, 1),
(5, 'Chemical spillages', 'Staff', 'Spill kits available, emergency procedures, training', 2, 3, 2),

-- Work Equipment
(6, 'Using tools without training', 'Staff', 'Competency assessments, training records, supervision', 2, 4, 1),
(6, 'Faulty equipment', 'Staff', 'Pre-use checks, maintenance schedule, reporting system', 2, 3, 2),

-- Display Screen Equipment
(7, 'Poor workstation setup', 'Office staff', 'DSE assessments, adjustable furniture, ergonomic equipment', 4, 2, 1),
(7, 'Eye strain from prolonged screen use', 'Office staff', 'Regular breaks, eye tests offered, screen filters', 4, 2, 2),

-- Workplace Environment
(8, 'Inadequate lighting', 'Staff', 'Regular maintenance, adjustable task lighting, natural light', 3, 2, 1),
(8, 'Temperature extremes', 'Staff', 'Heating/cooling systems, flexible dress code, regular monitoring', 3, 2, 2),
(8, 'High noise levels', 'Staff', 'Hearing protection, noise assessments, quieter equipment', 2, 3, 3),

-- Violence & Aggression
(9, 'Verbal abuse from customers', 'Front-line staff', 'Conflict resolution training, panic buttons, buddy system', 3, 3, 1),
(9, 'Physical violence risk', 'Staff', 'Security presence, CCTV, emergency procedures, training', 2, 4, 2),

-- Lone Working
(10, 'Working alone with no contact', 'Staff', 'Check-in procedures, mobile phones provided, emergency contacts', 3, 4, 1),
(10, 'Late night/early morning shifts alone', 'Staff', 'Increased security, regular check-ins, restricted access', 3, 4, 2);

-- Default System Settings
INSERT IGNORE INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('system_name', 'Risk Assessment System', 'text', 'Name of the system displayed in header', TRUE),
('company_name', 'Your Company Name', 'text', 'Company name for reports', TRUE),
('primary_color', '#667eea', 'color', 'Primary brand color', TRUE),
('primary_dark', '#5568d3', 'color', 'Primary dark variant', TRUE),
('primary_light', '#7c93ff', 'color', 'Primary light variant', TRUE),
('secondary_color', '#764ba2', 'color', 'Secondary brand color', TRUE),
('logo_filename', NULL, 'file', 'Custom logo filename in uploads/branding/', FALSE),
('assessments_per_page', '20', 'number', 'Number of assessments per page', FALSE),
('date_format', 'Y-m-d', 'text', 'PHP date format', FALSE),
('timezone', 'UTC', 'text', 'System timezone', FALSE),
('enable_email_reports', '0', 'boolean', 'Enable automated email reports', FALSE),
('session_timeout', '3600', 'number', 'Session timeout in seconds', FALSE),
('enable_audit_log', '1', 'boolean', 'Enable audit logging', FALSE),
('max_upload_size', '10485760', 'number', 'Max file upload size in bytes (10MB)', FALSE);

-- Default Permissions (Admin has all, Assessor can manage assessments, Viewer is read-only)
INSERT IGNORE INTO permissions (role, resource, can_create, can_read, can_update, can_delete, can_export) VALUES
-- Admin permissions (full access)
('admin', 'assessments', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'locations', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'users', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'categories', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'risk_items', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'settings', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'reports', TRUE, TRUE, TRUE, TRUE, TRUE),

-- Assessor permissions (can manage assessments and locations)
('assessor', 'assessments', TRUE, TRUE, TRUE, FALSE, TRUE),
('assessor', 'locations', TRUE, TRUE, TRUE, FALSE, TRUE),
('assessor', 'users', FALSE, TRUE, FALSE, FALSE, FALSE),
('assessor', 'categories', FALSE, TRUE, FALSE, FALSE, FALSE),
('assessor', 'risk_items', FALSE, TRUE, FALSE, FALSE, FALSE),
('assessor', 'settings', FALSE, TRUE, FALSE, FALSE, FALSE),
('assessor', 'reports', FALSE, TRUE, FALSE, FALSE, TRUE),

-- Viewer permissions (read-only)
('viewer', 'assessments', FALSE, TRUE, FALSE, FALSE, TRUE),
('viewer', 'locations', FALSE, TRUE, FALSE, FALSE, FALSE),
('viewer', 'users', FALSE, FALSE, FALSE, FALSE, FALSE),
('viewer', 'categories', FALSE, TRUE, FALSE, FALSE, FALSE),
('viewer', 'risk_items', FALSE, TRUE, FALSE, FALSE, FALSE),
('viewer', 'settings', FALSE, FALSE, FALSE, FALSE, FALSE),
('viewer', 'reports', FALSE, TRUE, FALSE, FALSE, TRUE);

-- ============================================================================
-- CREATE VIEWS (Optional but useful)
-- ============================================================================

-- View for assessment summary
CREATE OR REPLACE VIEW v_assessment_summary AS
SELECT 
    a.id,
    a.assessment_number,
    a.assessment_date,
    a.assessor_name,
    l.location_name,
    l.city,
    a.status,
    a.overall_risk_level,
    COUNT(ad.id) as total_items,
    SUM(CASE WHEN ad.risk_level = 'High' THEN 1 ELSE 0 END) as high_risk_count,
    SUM(CASE WHEN ad.risk_level = 'Medium' THEN 1 ELSE 0 END) as medium_risk_count,
    SUM(CASE WHEN ad.risk_level = 'Low' THEN 1 ELSE 0 END) as low_risk_count,
    SUM(CASE WHEN ad.action_required = 1 THEN 1 ELSE 0 END) as action_items_count
FROM assessments a
LEFT JOIN locations l ON a.location_id = l.id
LEFT JOIN assessment_details ad ON a.id = ad.assessment_id
GROUP BY a.id;

-- View for overdue action items
CREATE OR REPLACE VIEW v_overdue_actions AS
SELECT 
    ai.*,
    a.assessment_number,
    l.location_name,
    DATEDIFF(CURDATE(), ai.due_date) as days_overdue
FROM action_items ai
JOIN assessments a ON ai.assessment_id = a.id
JOIN locations l ON a.location_id = l.id
WHERE ai.status != 'completed' 
  AND ai.due_date < CURDATE()
ORDER BY ai.due_date ASC;

-- ============================================================================
-- 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_details_scores ON assessment_details(assessment_id, risk_score);
CREATE INDEX IF NOT EXISTS idx_action_items_status_date ON action_items(status, due_date);
CREATE INDEX IF NOT EXISTS idx_audit_log_user_date ON audit_log(user_id, created_at);

-- ============================================================================
-- TRIGGERS (Optional - for automatic updates)
-- ============================================================================

-- Trigger to update action item status based on due date
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS update_action_item_status
BEFORE UPDATE ON action_items
FOR EACH ROW
BEGIN
    IF NEW.status != 'completed' AND NEW.due_date < CURDATE() THEN
        SET NEW.status = 'overdue';
    END IF;
END$$

-- Trigger to generate assessment number
CREATE TRIGGER IF NOT EXISTS generate_assessment_number
BEFORE INSERT ON assessments
FOR EACH ROW
BEGIN
    IF NEW.assessment_number IS NULL THEN
        SET NEW.assessment_number = CONCAT('RA-', YEAR(CURDATE()), '-', LPAD(LAST_INSERT_ID() + 1, 6, '0'));
    END IF;
END$$

DELIMITER ;

-- ============================================================================
-- FINAL CLEANUP AND VERIFICATION
-- ============================================================================

-- Re-enable foreign key checks

-- Verify table creation
SELECT 
    TABLE_NAME, 
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'scubatricky_risk'
ORDER BY TABLE_NAME;

-- ============================================================================
-- SCHEMA COMPLETE
-- ============================================================================
-- Total Tables: 16
-- Total Views: 2
-- Total Triggers: 2
-- Default Users: 1 (admin/Admin123!)
-- Default Categories: 10
-- Default Risk Items: 30
-- 
-- NEXT STEPS:
-- 1. Update config.php with database credentials
-- 2. Login with admin/Admin123!
-- 3. Change admin password immediately
-- 4. Add your locations
-- 5. Customize risk items as needed
-- ============================================================================
