-- Payment System Migration
-- Run this to add payment tables to existing database

-- Create payment_plans table
CREATE TABLE IF NOT EXISTS payment_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    price_cents INT NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    `interval` VARCHAR(20) NOT NULL,
    stripe_price_id VARCHAR(255),
    features JSON,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    trial_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_slug (slug),
    INDEX idx_active (is_active),
    INDEX idx_sort (sort_order)
);

-- Create subscriptions table
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    stripe_subscription_id VARCHAR(255),
    stripe_customer_id VARCHAR(255),
    status ENUM('active', 'canceled', 'past_due', 'incomplete', 'trialing') DEFAULT 'active',
    current_period_start TIMESTAMP NULL,
    current_period_end TIMESTAMP NULL,
    trial_start TIMESTAMP NULL,
    trial_end TIMESTAMP NULL,
    cancel_at TIMESTAMP NULL,
    canceled_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 (plan_id) REFERENCES payment_plans(id),
    INDEX idx_user (user_id),
    INDEX idx_plan (plan_id),
    INDEX idx_status (status),
    INDEX idx_stripe_sub (stripe_subscription_id),
    INDEX idx_stripe_customer (stripe_customer_id)
);

-- Create payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    subscription_id INT NULL,
    stripe_payment_intent_id VARCHAR(255),
    stripe_charge_id VARCHAR(255),
    amount_cents INT NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'succeeded', 'failed', 'canceled', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    description VARCHAR(255),
    metadata JSON,
    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_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_subscription (subscription_id),
    INDEX idx_status (status),
    INDEX idx_stripe_payment (stripe_payment_intent_id),
    INDEX idx_stripe_charge (stripe_charge_id)
);

-- Create user_access_levels table
CREATE TABLE IF NOT EXISTS user_access_levels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    access_level VARCHAR(50) NOT NULL,
    granted_by INT NULL,
    granted_reason VARCHAR(255),
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_level (access_level),
    INDEX idx_active (is_active),
    INDEX idx_expires (expires_at)
);

-- Create email_verifications table
CREATE TABLE IF NOT EXISTS email_verifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    verification_token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (verification_token),
    INDEX idx_user (user_id),
    INDEX idx_expires (expires_at)
);

-- Insert default payment plans
INSERT IGNORE INTO payment_plans (name, slug, description, price_cents, currency, `interval`, features, is_active, sort_order, trial_days) VALUES
('Free Access', 'free', 'Basic access to AI chat with limited features', 0, 'USD', 'forever', '["Basic AI chat", "Limited conversations", "Survey access"]', TRUE, 1, 0),
('Monthly Pro', 'monthly-pro', 'Full access to AI advisor with premium features', 1999, 'USD', 'monthly', '["Unlimited AI chat", "Priority responses", "Advanced analytics", "Premium features"]', TRUE, 2, 7),
('Lifetime Pro', 'lifetime-pro', 'Lifetime access to all premium features', 14900, 'USD', 'lifetime', '["Everything in Monthly Pro", "Lifetime access", "VIP support", "Early feature access"]', TRUE, 3, 0);
