-- ============================================================
-- Rebrandly AI - MySQL Database Schema
-- For Namecheap Hosting
-- Database: rebrandlyai_db (or your chosen database name)
-- ============================================================

-- Drop tables if they exist (for clean reinstall)
DROP TABLE IF EXISTS admin_logs;
DROP TABLE IF EXISTS billing_events;
DROP TABLE IF EXISTS exports;
DROP TABLE IF EXISTS analysis_runs;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS users;

-- ============================================================
-- USERS TABLE
-- Stores all user account information
-- ============================================================
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    
    -- Email verification
    email_verified BOOLEAN DEFAULT FALSE,
    email_verification_token VARCHAR(255) DEFAULT NULL,
    email_verification_expires_at DATETIME DEFAULT NULL,
    
    -- Password reset
    password_reset_token VARCHAR(255) DEFAULT NULL,
    password_reset_expires_at DATETIME DEFAULT NULL,
    
    -- Plan and billing
    plan ENUM('free', 'premium', 'extra_premium', 'platinum') DEFAULT 'free',
    is_admin BOOLEAN DEFAULT FALSE,
    stripe_customer_id VARCHAR(255) DEFAULT NULL,
    stripe_subscription_id VARCHAR(255) DEFAULT NULL,
    stripe_subscription_status VARCHAR(50) DEFAULT NULL,
    subscription_current_period_start DATETIME DEFAULT NULL,
    subscription_current_period_end DATETIME DEFAULT NULL,
    
    -- Usage tracking
    analyses_today INT UNSIGNED DEFAULT 0,
    products_today INT UNSIGNED DEFAULT 0,
    analyses_today_date DATE DEFAULT NULL,
    total_analyses INT UNSIGNED DEFAULT 0,
    total_products_processed INT UNSIGNED DEFAULT 0,
    total_exports INT UNSIGNED DEFAULT 0,
    last_active_at DATETIME DEFAULT NULL,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_email (email),
    INDEX idx_username (username),
    INDEX idx_email_verification_token (email_verification_token),
    INDEX idx_password_reset_token (password_reset_token),
    INDEX idx_stripe_customer_id (stripe_customer_id),
    INDEX idx_plan (plan),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SESSIONS TABLE
-- Stores user session tokens for authentication
-- ============================================================
CREATE TABLE sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign key
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    -- Indexes
    INDEX idx_session_token (session_token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ANALYSIS_RUNS TABLE
-- Stores each scraping and rebranding analysis run
-- ============================================================
CREATE TABLE analysis_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    
    -- Store information
    store_url VARCHAR(500) NOT NULL,
    store_domain VARCHAR(255) NOT NULL,
    
    -- Results
    products_found INT UNSIGNED DEFAULT 0,
    products_processed INT UNSIGNED DEFAULT 0,
    pages_crawled INT UNSIGNED DEFAULT 1,
    
    -- Settings used
    plan_used ENUM('free', 'premium', 'extra_premium', 'platinum') NOT NULL,
    old_vendor VARCHAR(255) DEFAULT NULL,
    new_vendor VARCHAR(255) DEFAULT NULL,
    price_factor DECIMAL(5,2) DEFAULT 1.00,
    paraphrase_titles BOOLEAN DEFAULT TRUE,
    paraphrase_descriptions BOOLEAN DEFAULT TRUE,
    keep_images BOOLEAN DEFAULT TRUE,
    
    -- Status and results
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    warning_message TEXT DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    
    -- Result data (JSON)
    result_data LONGTEXT DEFAULT NULL,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME DEFAULT NULL,
    
    -- Foreign key
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    -- Indexes
    INDEX idx_user_id (user_id),
    INDEX idx_store_domain (store_domain),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_plan_used (plan_used)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EXPORTS TABLE
-- Stores CSV export records
-- ============================================================
CREATE TABLE exports (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    analysis_run_id INT UNSIGNED DEFAULT NULL,
    
    -- File information
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) DEFAULT NULL,
    file_size INT UNSIGNED DEFAULT 0,
    
    -- Export details
    export_type ENUM('csv', 'json') DEFAULT 'csv',
    product_count INT UNSIGNED DEFAULT 0,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign keys
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (analysis_run_id) REFERENCES analysis_runs(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_user_id (user_id),
    INDEX idx_analysis_run_id (analysis_run_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BILLING_EVENTS TABLE
-- Stores Stripe webhook events for audit trail
-- ============================================================
CREATE TABLE billing_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    
    -- Stripe event details
    stripe_event_id VARCHAR(255) NOT NULL UNIQUE,
    event_type VARCHAR(100) NOT NULL,
    
    -- Payload
    payload_json LONGTEXT NOT NULL,
    
    -- Processing status
    processed BOOLEAN DEFAULT FALSE,
    processed_at DATETIME DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign key
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_stripe_event_id (stripe_event_id),
    INDEX idx_user_id (user_id),
    INDEX idx_event_type (event_type),
    INDEX idx_processed (processed),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ADMIN_LOGS TABLE
-- Stores admin actions for audit trail
-- ============================================================
CREATE TABLE admin_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NOT NULL,
    target_user_id INT UNSIGNED DEFAULT NULL,
    
    -- Action details
    action VARCHAR(100) NOT NULL,
    details_json TEXT DEFAULT NULL,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign keys
    FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_admin_user_id (admin_user_id),
    INDEX idx_target_user_id (target_user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- INSERT DEFAULT ADMIN USER (OPTIONAL)
-- Password: Admin@123 (CHANGE THIS IMMEDIATELY AFTER FIRST LOGIN)
-- This is a bcrypt hash of "Admin@123"
-- ============================================================
-- INSERT INTO users (
--     email, 
--     username, 
--     password_hash, 
--     email_verified, 
--     is_admin, 
--     plan
-- ) VALUES (
--     'admin@rebrandlyai.com',
--     'admin',
--     '$2b$10$rKJ5YvZ8vZ8vZ8vZ8vZ8vOXxYxYxYxYxYxYxYxYxYxYxYxYxYxYxY',
--     TRUE,
--     TRUE,
--     'platinum'
-- );

-- ============================================================
-- CLEANUP QUERIES (for maintenance)
-- ============================================================

-- Delete expired sessions
-- DELETE FROM sessions WHERE expires_at < NOW();

-- Delete expired verification tokens
-- UPDATE users SET email_verification_token = NULL, email_verification_expires_at = NULL 
-- WHERE email_verification_expires_at < NOW();

-- Delete expired password reset tokens
-- UPDATE users SET password_reset_token = NULL, password_reset_expires_at = NULL 
-- WHERE password_reset_expires_at < NOW();

-- ============================================================
-- USEFUL QUERIES
-- ============================================================

-- Get user with session
-- SELECT u.* FROM users u 
-- INNER JOIN sessions s ON u.id = s.user_id 
-- WHERE s.session_token = ? AND s.expires_at > NOW();

-- Get user analysis history
-- SELECT * FROM analysis_runs WHERE user_id = ? ORDER BY created_at DESC LIMIT 50;

-- Get user exports
-- SELECT e.*, a.store_domain FROM exports e 
-- LEFT JOIN analysis_runs a ON e.analysis_run_id = a.id 
-- WHERE e.user_id = ? ORDER BY e.created_at DESC;

-- Get admin stats
-- SELECT 
--     COUNT(*) as total_users,
--     SUM(CASE WHEN email_verified = TRUE THEN 1 ELSE 0 END) as verified_users,
--     SUM(CASE WHEN plan = 'free' THEN 1 ELSE 0 END) as free_users,
--     SUM(CASE WHEN plan = 'premium' THEN 1 ELSE 0 END) as premium_users,
--     SUM(CASE WHEN plan = 'extra_premium' THEN 1 ELSE 0 END) as extra_premium_users,
--     SUM(CASE WHEN plan = 'platinum' THEN 1 ELSE 0 END) as platinum_users
-- FROM users;

-- ============================================================
-- END OF SCHEMA
-- ============================================================