-- Additional tables for new features
USE risk_assessment_db;

-- Color Themes Table
CREATE TABLE IF NOT EXISTS color_themes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    theme_name VARCHAR(100) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    primary_color VARCHAR(7) NOT NULL COMMENT 'Hex color',
    secondary_color VARCHAR(7) NOT NULL,
    accent_color VARCHAR(7) NOT NULL,
    success_color VARCHAR(7) DEFAULT '#10b981',
    warning_color VARCHAR(7) DEFAULT '#f59e0b',
    danger_color VARCHAR(7) DEFAULT '#ef4444',
    background_gradient_start VARCHAR(7) NOT NULL,
    background_gradient_end VARCHAR(7) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_active (is_active),
    INDEX idx_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PDF Templates Table
CREATE TABLE IF NOT EXISTS pdf_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    header_logo_url VARCHAR(255) NULL,
    header_text TEXT NULL,
    footer_text TEXT NULL,
    show_risk_matrix TINYINT(1) DEFAULT 1,
    show_photos TINYINT(1) DEFAULT 1,
    show_action_items TINYINT(1) DEFAULT 1,
    show_signatures TINYINT(1) DEFAULT 0,
    page_orientation VARCHAR(20) DEFAULT 'portrait',
    font_family VARCHAR(50) DEFAULT 'Arial',
    font_size INT DEFAULT 12,
    include_fields JSON NULL COMMENT 'Array of field names to include',
    custom_css TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_active (is_active),
    INDEX idx_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User Theme Preferences
CREATE TABLE IF NOT EXISTS user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    theme_id INT NULL,
    default_pdf_template_id INT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (theme_id) REFERENCES color_themes(id) ON DELETE SET NULL,
    FOREIGN KEY (default_pdf_template_id) REFERENCES pdf_templates(id) ON DELETE SET NULL,
    UNIQUE KEY unique_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert Default Theme
INSERT INTO color_themes (theme_name, is_default, primary_color, secondary_color, accent_color, 
                          background_gradient_start, background_gradient_end) VALUES
('Modern Indigo', 1, '#6366f1', '#8b5cf6', '#ec4899', '#667eea', '#764ba2'),
('Ocean Blue', 0, '#0ea5e9', '#06b6d4', '#22d3ee', '#0284c7', '#0891b2'),
('Forest Green', 0, '#10b981', '#059669', '#34d399', '#047857', '#065f46'),
('Sunset Orange', 0, '#f97316', '#ea580c', '#fb923c', '#c2410c', '#9a3412'),
('Royal Purple', 0, '#9333ea', '#7c3aed', '#a855f7', '#6b21a8', '#581c87'),
('Corporate Gray', 0, '#64748b', '#475569', '#94a3b8', '#334155', '#1e293b');

-- Insert Default PDF Template
INSERT INTO pdf_templates (template_name, is_default, header_text, footer_text) VALUES
('Standard Report', 1, 
 'HGV Delivery Risk Assessment Report', 
 'This document is confidential and for internal use only. Generated on {{date}}');

SELECT 'Schema additions created successfully!' as Status;
