-- ============================================================================
-- QUICK FIX: Missing Tables Patch
-- ============================================================================
-- Run this if you get errors about missing tables
-- Usage: mysql -u username -p database_name < quick-fix-missing-tables.sql
-- ============================================================================

USE scubatricky_risk;

-- Fix 1: Custom Fields Tables
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;

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;

-- Fix 2: System Files Tables
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;

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;

-- Fix 3: 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;

-- Fix 4: 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;

-- Fix 5: Add missing columns to locations table
ALTER TABLE locations 
ADD COLUMN IF NOT EXISTS report_emails TEXT COMMENT 'Comma-separated email addresses',
ADD COLUMN IF NOT EXISTS email_frequency ENUM('immediate', 'daily', 'weekly', 'monthly', 'none') DEFAULT 'none',
ADD COLUMN IF NOT EXISTS last_report_sent TIMESTAMP NULL,
ADD COLUMN IF NOT EXISTS email_enabled BOOLEAN DEFAULT FALSE;

-- Insert default system settings if not exists
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);

-- Insert default permissions if not exists
INSERT IGNORE INTO permissions (role, resource, can_create, can_read, can_update, can_delete, can_export) VALUES
-- Admin permissions
('admin', 'assessments', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'locations', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'users', TRUE, TRUE, TRUE, TRUE, TRUE),
('admin', 'settings', TRUE, TRUE, TRUE, TRUE, TRUE),

-- Assessor permissions
('assessor', 'assessments', TRUE, TRUE, TRUE, FALSE, TRUE),
('assessor', 'locations', TRUE, TRUE, TRUE, FALSE, TRUE),
('assessor', 'users', FALSE, TRUE, FALSE, FALSE, FALSE),

-- Viewer permissions
('viewer', 'assessments', FALSE, TRUE, FALSE, FALSE, TRUE),
('viewer', 'locations', FALSE, TRUE, FALSE, FALSE, FALSE);

-- Success message
SELECT 'Quick fix patch applied successfully!' AS Status,
       'All missing tables have been created.' AS Message,
       'You can now use all system features.' AS NextStep;
