-- FlashCore Production-Ready Database Script
-- This file contains the complete schema and initial data (including admin and demo users)

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

-- ==========================================
-- 1. SCHEMA DEFINITION
-- ==========================================

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

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

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

-- Create users table
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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,
    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
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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,
    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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS 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)
);

-- ==========================================
-- 2. INITIAL DATA & SEEDING
-- ==========================================

-- Seed subscription tiers
INSERT IGNORE INTO subscription_tiers (id, name) VALUES (1, 'basic'), (2, 'pro'), (3, 'enterprise');

-- Seed license status
INSERT IGNORE INTO license_status (id, name) VALUES (1, 'active'), (2, 'expired'), (3, 'suspended'), (4, 'pending');

-- Seed user roles
INSERT IGNORE INTO user_roles (id, name) VALUES (1, 'admin'), (2, 'user');

-- Create Admin User (Password: Admin123!)
SET @admin_id = '88888888-4444-4444-4444-121212121212';
INSERT IGNORE INTO users (id, email, password_hash, first_name, last_name, email_verified, created_at) 
VALUES (@admin_id, 'admin@flashcore.com', '$2y$10$BQqvqMS.nrML.BmIrjAW.OrguJZapK9fLbMA9vE0.H.4P5gOIojzC', 'System', 'Administrator', TRUE, NOW());

-- Create Admin Profile
INSERT IGNORE INTO profiles (id, user_id, first_name, last_name, role_id) 
VALUES (UUID(), @admin_id, 'System', 'Administrator', 1);

-- Create Admin User Profile
INSERT IGNORE INTO user_profiles (id, user_id, username, first_name, last_name) 
VALUES (UUID(), @admin_id, 'admin', 'System', 'Administrator');

-- Create Admin User Progress
INSERT IGNORE INTO user_progress (id, user_id, current_rank, mails_sent, progress_percentage, referral_count, referral_earnings, total_earnings) 
VALUES (UUID(), @admin_id, 'Diamond', 1000, 100, 50, 250.00, 1000.00);

-- Create Demo User (Password: Admin123!)
SET @user_id = '99999999-4444-4444-4444-121212121212';
INSERT IGNORE INTO users (id, email, password_hash, first_name, last_name, email_verified, created_at) 
VALUES (@user_id, 'user@flashcore.com', '$2y$10$BQqvqMS.nrML.BmIrjAW.OrguJZapK9fLbMA9vE0.H.4P5gOIojzC', 'Demo', 'User', TRUE, NOW());

-- Create User Profile
INSERT IGNORE INTO profiles (id, user_id, first_name, last_name, role_id) 
VALUES (UUID(), @user_id, 'Demo', 'User', 2);

-- Create User Extended Profile
INSERT IGNORE INTO user_profiles (id, user_id, username, first_name, last_name) 
VALUES (UUID(), @user_id, 'demouser', 'Demo', 'User');

-- Create User Progress
INSERT IGNORE INTO user_progress (id, user_id, current_rank, mails_sent, progress_percentage, referral_count, referral_earnings, total_earnings) 
VALUES (UUID(), @user_id, 'Silver', 150, 25, 5, 25.00, 100.00);

-- Seed SMTP Settings
INSERT IGNORE INTO smtp_settings (id, provider, host, port, username, password, from_email, from_name, is_active) 
VALUES (UUID(), 'gmail', 'smtp.gmail.com', 587, 'noreply@flashcore.com', 'password123', 'noreply@flashcore.com', 'FlashCore', TRUE);

-- Seed Email Templates
INSERT IGNORE INTO email_templates (id, name, subject, content, template_type, is_active, created_by) 
VALUES 
(UUID(), 'Welcome Email', 'Welcome to FlashCore!', '<h1>Welcome {{username}}!</h1><p>Thank you for joining FlashCore.</p>', 'welcome', TRUE, @admin_id),
(UUID(), 'License Activation', 'Your License is Active!', '<h1>Hello {{username}}!</h1><p>Your license key {{license_key}} has been activated.</p>', 'license_activation', TRUE, @admin_id);

-- ==========================================
-- 3. STORED PROCEDURES
-- ==========================================

DELIMITER //
DROP PROCEDURE IF EXISTS generate_license_key //
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 //

DROP PROCEDURE IF EXISTS activate_license_key //
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);
    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
        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 LIMIT 1;
        IF v_license_id IS NULL THEN SET v_success = FALSE; SET v_message = 'Invalid license';
        ELSE
            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;
            SELECT name INTO v_tier_name FROM subscription_tiers WHERE id = v_subscription_tier_id;
            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 = 'Success';
        END IF;
    END IF;
    SET result = JSON_OBJECT('success', v_success, 'message', v_message);
END //
DELIMITER ;
