-- Color Themes Table
CREATE TABLE color_themes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    theme_name VARCHAR(100) NOT NULL,
    primary_color VARCHAR(7) NOT NULL DEFAULT '#6366f1',
    secondary_color VARCHAR(7) NOT NULL DEFAULT '#8b5cf6',
    accent_color VARCHAR(7) NOT NULL DEFAULT '#ec4899',
    success_color VARCHAR(7) NOT NULL DEFAULT '#10b981',
    warning_color VARCHAR(7) NOT NULL DEFAULT '#f59e0b',
    danger_color VARCHAR(7) NOT NULL DEFAULT '#ef4444',
    background_gradient_start VARCHAR(7) NULL,
    background_gradient_end VARCHAR(7) NULL,
    is_default TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_is_default (is_default),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User Preferences Table
CREATE TABLE user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    theme_id INT NULL,
    items_per_page INT DEFAULT 20,
    default_view VARCHAR(50) DEFAULT 'grid',
    email_notifications TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (theme_id) REFERENCES color_themes(id) ON DELETE SET NULL,
    UNIQUE KEY unique_user (user_id),
    INDEX idx_theme (theme_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PDF Templates Table
CREATE TABLE pdf_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    template_type VARCHAR(50) NOT NULL COMMENT 'assessment, location, report',
    header_html TEXT NULL,
    footer_html TEXT NULL,
    logo_url VARCHAR(500) NULL,
    styles_css TEXT NULL,
    is_default TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_type (template_type),
    INDEX idx_is_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default theme
INSERT INTO color_themes (theme_name, primary_color, secondary_color, accent_color, is_default, is_active) VALUES
('Default Blue', '#6366f1', '#8b5cf6', '#ec4899', 1, 1),
('Ocean Breeze', '#0ea5e9', '#06b6d4', '#14b8a6', 0, 1),
('Forest Green', '#10b981', '#059669', '#84cc16', 0, 1),
('Sunset Orange', '#f59e0b', '#f97316', '#ef4444', 0, 1),
('Royal Purple', '#8b5cf6', '#a855f7', '#d946ef', 0, 1),
('Dark Mode', '#1e293b', '#334155', '#475569', 0, 1);
