-- MySQL Database Schema for FlashCore
-- Based on Supabase migrations

-- Create database
CREATE DATABASE IF NOT EXISTS flashcore_dashboard CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE flashcore_dashboard;

-- Create enum-like tables for subscription tiers
CREATE TABLE subscription_tiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Create enum-like tables for license status
CREATE TABLE license_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Create enum-like tables for user roles
CREATE TABLE user_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Create users table (replaces auth.users)
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- Create profiles table
CREATE TABLE profiles (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role_id INT DEFAULT 2, -- Default to 'user'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES user_roles(id),
    INDEX idx_user_id (user_id)
);

-- Create subscribers table for subscription management
CREATE TABLE subscribers (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36),
    email VARCHAR(255) NOT NULL UNIQUE,
    stripe_customer_id VARCHAR(255),
    subscribed BOOLEAN NOT NULL DEFAULT FALSE,
    subscription_tier_id INT,
    subscription_end TIMESTAMP NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id),
    INDEX idx_user_id (user_id),
    INDEX idx_email (email)
);

-- Create license_keys table
CREATE TABLE license_keys (
    id CHAR(36) PRIMARY KEY,
    key_value VARCHAR(255) NOT NULL UNIQUE,
    user_id CHAR(36),
    subscription_tier_id INT NOT NULL,
    status_id INT DEFAULT 4, -- Default to 'pending'
    expires_at TIMESTAMP NULL,
    activated_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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),
    INDEX idx_user_id (user_id),
    INDEX idx_key_value (key_value)
);

-- Create orders table for payment tracking
CREATE TABLE orders (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36),
    stripe_session_id VARCHAR(255) UNIQUE,
    amount INT NOT NULL,
    currency VARCHAR(10) DEFAULT 'usd',
    subscription_tier_id INT NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_tier_id) REFERENCES subscription_tiers(id),
    INDEX idx_user_id (user_id),
    INDEX idx_stripe_session (stripe_session_id)
);

-- Create admin_logs table for audit trail
CREATE TABLE admin_logs (
    id CHAR(36) PRIMARY KEY,
    admin_id CHAR(36),
    action VARCHAR(255) NOT NULL,
    target_table VARCHAR(100),
    target_id CHAR(36),
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_admin_id (admin_id),
    INDEX idx_action (action)
);

-- Create dashboard_logs table for dashboard activity
CREATE TABLE dashboard_logs (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    action VARCHAR(255) NOT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action)
);

-- Create mail_activity table for tracking email activity
CREATE TABLE mail_activity (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    email_type VARCHAR(100) NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'sent',
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_email_type (email_type)
);

-- Create user_progress table for tracking user progress
CREATE TABLE user_progress (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL UNIQUE,
    current_rank VARCHAR(100) NOT NULL DEFAULT 'Bronze',
    mails_sent INT NOT NULL DEFAULT 0,
    progress_percentage INT NOT NULL DEFAULT 0,
    referral_count INT NOT NULL DEFAULT 0,
    referral_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

-- Create smtp_settings table for admin SMTP configuration
CREATE TABLE smtp_settings (
    id CHAR(36) PRIMARY KEY,
    provider VARCHAR(100) NOT NULL,
    host VARCHAR(255),
    port INT,
    username VARCHAR(255),
    password VARCHAR(255),
    from_email VARCHAR(255) NOT NULL,
    from_name VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create user_profiles table for extended user information
CREATE TABLE user_profiles (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL UNIQUE,
    username VARCHAR(100),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    avatar_url TEXT,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_username (username)
);

-- Create email_templates table
CREATE TABLE email_templates (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    template_type VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_by CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_template_type (template_type),
    INDEX idx_is_active (is_active)
);

-- Create sessions table for user sessions
CREATE TABLE sessions (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_session_token (session_token)
);

-- Create function to generate license key (simulated with stored procedure)
DELIMITER //
CREATE PROCEDURE generate_license_key(OUT license_key VARCHAR(255))
BEGIN
    DECLARE chars VARCHAR(36) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE result VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 20 DO
        SET result = CONCAT(result, SUBSTRING(chars, FLOOR(RAND() * 36) + 1, 1));
        IF i % 4 = 0 AND i < 20 THEN
            SET result = CONCAT(result, '-');
        END IF;
        SET i = i + 1;
    END WHILE;
    
    SET license_key = result;
END //
DELIMITER ;

-- Create function to activate license key
DELIMITER //
CREATE PROCEDURE activate_license_key(IN p_key_value VARCHAR(255), IN p_user_email VARCHAR(255), OUT result JSON)
BEGIN
    DECLARE v_user_id CHAR(36);
    DECLARE v_license_id CHAR(36);
    DECLARE v_subscription_tier_id INT;
    DECLARE v_success BOOLEAN DEFAULT FALSE;
    DECLARE v_message VARCHAR(255);
    DECLARE v_tier_name VARCHAR(50);
    
    -- Get user by email
    SELECT id INTO v_user_id FROM users WHERE email = p_user_email LIMIT 1;
    
    IF v_user_id IS NULL THEN
        SET v_success = FALSE;
        SET v_message = 'User not found';
    ELSE
        -- Get license key
        SELECT id, subscription_tier_id INTO v_license_id, v_subscription_tier_id 
        FROM license_keys 
        WHERE key_value = p_key_value AND status_id = 4 -- pending status
        LIMIT 1;
        
        IF v_license_id IS NULL THEN
            SET v_success = FALSE;
            SET v_message = 'Invalid or already activated license key';
        ELSE
            -- Update license key
            UPDATE license_keys 
            SET user_id = v_user_id, status_id = 1, activated_at = NOW(), expires_at = DATE_ADD(NOW(), INTERVAL 1 YEAR)
            WHERE id = v_license_id;
            
            -- Get subscription tier name
            SELECT name INTO v_tier_name FROM subscription_tiers WHERE id = v_subscription_tier_id;
            
            -- Update or insert subscriber record
            INSERT INTO subscribers (user_id, email, subscribed, subscription_tier_id, subscription_end)
            VALUES (v_user_id, p_user_email, TRUE, v_subscription_tier_id, DATE_ADD(NOW(), INTERVAL 1 YEAR))
            ON DUPLICATE KEY UPDATE
                subscribed = TRUE,
                subscription_tier_id = v_subscription_tier_id,
                subscription_end = DATE_ADD(NOW(), INTERVAL 1 YEAR),
                updated_at = NOW();
                
            SET v_success = TRUE;
            SET v_message = 'License activated successfully';
        END IF;
    END IF;
    
    SET result = JSON_OBJECT('success', v_success, 'message', v_message, 'tier', IFNULL(v_tier_name, ''));
END //
DELIMITER ;