-- SQLite Database Schema for FlashCore

-- Enable foreign keys
PRAGMA foreign_keys = ON;

-- Create enum-like tables for subscription tiers
CREATE TABLE IF NOT EXISTS subscription_tiers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT OR IGNORE INTO subscription_tiers (name) VALUES ('basic'), ('pro'), ('enterprise');

-- Create enum-like tables for license status
CREATE TABLE IF NOT EXISTS license_status (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT OR IGNORE INTO license_status (name) VALUES ('active'), ('expired'), ('suspended'), ('pending');

-- Create enum-like tables for user roles
CREATE TABLE IF NOT EXISTS user_roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT OR IGNORE INTO user_roles (name) VALUES ('admin'), ('user');

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    is_approved INTEGER DEFAULT 0,
    email_verified INTEGER DEFAULT 0,
    verification_token TEXT,
    role TEXT DEFAULT 'user', -- simplified role
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
);

-- Create profiles table
CREATE TABLE IF NOT EXISTS profiles (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    role_id INTEGER DEFAULT 2, -- Default to 'user'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES user_roles(id)
);

-- Create subscribers table
CREATE TABLE IF NOT EXISTS subscribers (
    id TEXT PRIMARY KEY,
    user_id TEXT,
    email TEXT NOT NULL UNIQUE,
    stripe_customer_id TEXT,
    subscribed INTEGER NOT NULL DEFAULT 0,
    subscription_tier_id INTEGER,
    subscription_end DATETIME,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id)
);

-- Create license_keys table
CREATE TABLE IF NOT EXISTS license_keys (
    id TEXT PRIMARY KEY,
    key_value TEXT NOT NULL UNIQUE,
    user_id TEXT,
    subscription_tier_id INTEGER NOT NULL,
    status_id INTEGER DEFAULT 4, -- Default to 'pending'
    expires_at DATETIME,
    activated_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id),
    FOREIGN KEY (status_id) REFERENCES license_status(id)
);

-- Create orders table
CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    user_id TEXT,
    stripe_session_id TEXT UNIQUE,
    amount INTEGER NOT NULL,
    currency TEXT DEFAULT 'usd',
    subscription_tier_id INTEGER NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id)
);

-- Create admin_logs table
CREATE TABLE IF NOT EXISTS admin_logs (
    id TEXT PRIMARY KEY,
    admin_id TEXT,
    action TEXT NOT NULL,
    target_table TEXT,
    target_id TEXT,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create dashboard_logs table
CREATE TABLE IF NOT EXISTS dashboard_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    action TEXT NOT NULL,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create mail_activity table
CREATE TABLE IF NOT EXISTS mail_activity (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    email_type TEXT NOT NULL,
    recipient_email TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'sent',
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create user_progress table
CREATE TABLE IF NOT EXISTS user_progress (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL UNIQUE,
    current_rank TEXT NOT NULL DEFAULT 'Bronze',
    mails_sent INTEGER NOT NULL DEFAULT 0,
    progress_percentage INTEGER NOT NULL DEFAULT 0,
    referral_count INTEGER NOT NULL DEFAULT 0,
    referral_earnings REAL NOT NULL DEFAULT 0.00,
    total_earnings REAL NOT NULL DEFAULT 0.00,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create smtp_settings table
CREATE TABLE IF NOT EXISTS smtp_settings (
    id TEXT PRIMARY KEY,
    provider TEXT NOT NULL,
    host TEXT,
    port INTEGER,
    username TEXT,
    password TEXT,
    from_email TEXT NOT NULL,
    from_name TEXT,
    is_active INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create user_profiles table
CREATE TABLE IF NOT EXISTS user_profiles (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL UNIQUE,
    username TEXT,
    first_name TEXT,
    last_name TEXT,
    phone TEXT,
    avatar_url TEXT,
    bio TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create email_templates table
CREATE TABLE IF NOT EXISTS email_templates (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    subject TEXT NOT NULL,
    content TEXT NOT NULL,
    template_type TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_by TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Create sessions table
CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    session_token TEXT NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create user_assets table (missing in original schema but required by logic)
CREATE TABLE IF NOT EXISTS user_assets (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    balance REAL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Update admin-dashboard query dependencies
CREATE TABLE IF NOT EXISTS licenses (
    -- License table seems to be `license_keys` in the schema but `licenses` in queries.
    -- I will create an alias view or just rename `license_keys` to `licenses`?
    -- Actually, the schema calls it `license_keys`. The PHP calls it `licenses`.
    -- `admin-dashboard.php` query: `SELECT COUNT(*) as total FROM licenses WHERE status = 'active'`
    -- This implies there is a `licenses` table.
    -- Or `license_keys` table uses `status_id`.
    -- I will create a View or Table for compatibility.
    -- For now I will create a `licenses` table that mimics what is expected.
    id TEXT PRIMARY KEY,
    key_value TEXT NOT NULL UNIQUE,
    user_id TEXT,
    subscription_tier TEXT, -- 'basic', 'pro', etc.
    status TEXT DEFAULT 'pending', -- 'active'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    type TEXT, -- 'info', 'success', 'warning', 'error'
    message TEXT NOT NULL,
    is_read INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Pricing Plans Table
CREATE TABLE IF NOT EXISTS pricing_plans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL,
    price VARCHAR(20) NOT NULL,
    description TEXT,
    features_json TEXT, -- JSON array of strings
    not_included_json TEXT, -- JSON array of strings
    is_popular BOOLEAN DEFAULT 0,
    color_gradient VARCHAR(100),
    button_text VARCHAR(50),
    order_index INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed Default Pricing Plans
INSERT INTO pricing_plans (name, price, description, features_json, not_included_json, is_popular, color_gradient, button_text, order_index) 
VALUES 
('Basic', '$9.99', 'Perfect for small businesses', 
 '["1,000 emails per month", "Basic email templates", "Email analytics", "24/7 support", "Standard delivery", "Basic reporting"]', 
 '["Advanced templates", "Custom branding", "API access", "Priority support"]', 
 0, 'from-flashcore-purple to-flashcore-green', 'Get Basic', 1),
('Pro', '$29.99', 'For growing businesses', 
 '["10,000 emails per month", "Advanced email templates", "Advanced analytics", "Priority support", "Custom branding", "Advanced reporting", "A/B testing", "Automation workflows"]', 
 '["Unlimited emails", "Dedicated support", "Custom integrations"]', 
 1, 'from-flashcore-green to-flashcore-purple', 'Upgrade to Pro', 2),
('Enterprise', '$99.99', 'For large organizations', 
 '["Unlimited emails", "Custom email templates", "Advanced reporting", "Dedicated support", "API access", "Custom integrations", "White-label options", "Advanced security"]', 
 '[]', 
 0, 'from-flashcore-orange to-flashcore-purple', 'Contact Sales', 3);

