-- Migration: Add AI Model Preference and Package Enhancements
-- Description: Adds user AI model preference, package model access grants, and model access tracking
-- Date: 2025-11-28

-- 1. Add preferred AI model column to users table
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS preferred_ai_model VARCHAR(100) DEFAULT 'llama-3.3-70b' COMMENT 'User\'s default AI model for all features',
ADD COLUMN IF NOT EXISTS model_selection_synced_at TIMESTAMP NULL COMMENT 'Last time model preference was synced';

-- 2. Add model grants to token packages
ALTER TABLE ai_token_packages 
ADD COLUMN IF NOT EXISTS grants_models JSON DEFAULT NULL COMMENT 'Array of model names this package unlocks, e.g. ["gpt-4", "claude-opus"]';

-- 3. Create user model access tracking table
CREATE TABLE IF NOT EXISTS user_model_access (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    model_name VARCHAR(100) NOT NULL,
    access_type ENUM('subscription', 'package', 'admin', 'free') DEFAULT 'package' COMMENT 'How user got access to this model',
    granted_by_package_id INT NULL COMMENT 'Reference to ai_token_packages if access_type is package',
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL COMMENT 'NULL means never expires',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_user_model (user_id, model_name),
    KEY idx_user_id (user_id),
    KEY idx_model_name (model_name),
    KEY idx_expires_at (expires_at),
    KEY idx_active (is_active),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by_package_id) REFERENCES ai_token_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tracks which users have access to which AI models via packages or subscriptions';

-- 4. Add index on ai_token_transactions for better analytics
ALTER TABLE ai_token_transactions
ADD INDEX IF NOT EXISTS idx_model_endpoint (ai_model, endpoint),
ADD INDEX IF NOT EXISTS idx_user_created (user_id, created_at);

-- 5. Mark some popular free models (optional - adjust based on your models)
-- This helps the access check system identify free models quickly
ALTER TABLE ai_models
ADD COLUMN IF NOT EXISTS is_free BOOLEAN DEFAULT FALSE COMMENT 'Whether this model is free for all users';

-- Mark free models (update with your actual free model names)
UPDATE ai_models 
SET is_free = TRUE 
WHERE name IN ('llama-3.3-70b', 'geminisearch', 'llama-3.1-70b', 'mixtral-8x7b')
AND STATUS IN ('active', 'working');

-- 6. Create a view for easy access checking
CREATE OR REPLACE VIEW user_accessible_models AS
SELECT 
    u.id as user_id,
    m.id as model_id,
    m.name as model_name,
    m.provider,
    m.description,
    m.cost_per_1k_input_tokens,
    m.cost_per_1k_output_tokens,
    m.is_free,
    CASE
        WHEN m.is_free = TRUE THEN 'free'
        WHEN uma.access_type IS NOT NULL AND (uma.expires_at IS NULL OR uma.expires_at > NOW()) THEN uma.access_type
        ELSE NULL
    END as access_type,
    CASE
        WHEN m.is_free = TRUE THEN TRUE
        WHEN uma.id IS NOT NULL AND uma.is_active = TRUE AND (uma.expires_at IS NULL OR uma.expires_at > NOW()) THEN TRUE
        ELSE FALSE
    END as has_access,
    uma.expires_at
FROM users u
CROSS JOIN ai_models m
LEFT JOIN user_model_access uma ON u.id = uma.user_id AND m.name = uma.model_name AND uma.is_active = TRUE
WHERE m.status IN ('active', 'working');

-- Sample data: Create some example token packages with model access
-- Uncomment and adjust as needed
/*
INSERT INTO ai_token_packages (
    package_name, description, token_amount, bonus_tokens, 
    price_npr, price_usd, grants_models, validity_days, 
    is_popular, display_order
) VALUES 
(
    'Starter Pack',
    'Perfect for beginners - 1000 AI tokens to get started',
    1000, 100,
    500, 3.99,
    NULL,  -- No model access, just tokens
    NULL,  -- Never expires
    FALSE, 1
),
(
    'GPT-4 Monthly',
    'Access GPT-4 for 30 days plus 500 bonus tokens',
    500, 0,
    1500, 11.99,
    '["gpt-4", "gpt-4-turbo"]',  -- Grants GPT-4 access
    30,  -- 30 days validity
    TRUE, 2
),
(
    'Power User Bundle',
    'Ultimate package - 5000 tokens + access to all premium models',
    5000, 1000,
    3000, 24.99,
    '["gpt-4", "gpt-4-turbo", "claude-3-opus", "claude-3-sonnet", "gemini-ultra"]',
    NULL,  -- Never expires
    TRUE, 3
);
*/
