-- Migration: User AI Model Management System
-- Date: 2024-01-27
-- Description: Implements two-tier AI model management with provider configuration and user model isolation

-- ============================================================================
-- PART 1: Create ai_model_providers table
-- ============================================================================

-- AI Model Provider Configuration Table
-- Stores global provider settings managed by administrators
CREATE TABLE IF NOT EXISTS ai_model_providers (
    id TEXT PRIMARY KEY,                    -- Provider ID (deepseek, openai, etc.)
    name TEXT NOT NULL,                     -- Display name
    enabled BOOLEAN DEFAULT 1,              -- Whether provider is enabled
    allow_custom_url BOOLEAN DEFAULT 1,     -- Whether users can customize URL (deprecated - use permission system)
    default_api_url TEXT,                   -- Admin-configured default URL
    display_label TEXT,                     -- UI display label
    registration_link TEXT,                 -- Registration link
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Trigger for automatic updated_at timestamp
CREATE TRIGGER IF NOT EXISTS update_ai_model_providers_updated_at
    AFTER UPDATE ON ai_model_providers
    BEGIN
        UPDATE ai_model_providers SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

-- ============================================================================
-- PART 2: Migrate provider configurations from system_config to ai_model_providers
-- ============================================================================

-- Insert provider configurations from system_config
-- This migrates existing configurations to the new table structure
INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'deepseek' as id,
    'DeepSeek' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_deepseek_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_deepseek_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_deepseek_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_deepseek_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'deepseek');

INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'qwen' as id,
    'Qwen' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_qwen_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_qwen_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_qwen_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_qwen_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'qwen');

INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'openai' as id,
    'OpenAI' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_openai_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_openai_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_openai_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_openai_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'openai');

INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'claude' as id,
    'Claude' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_claude_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_claude_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_claude_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_claude_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'claude');

INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'gemini' as id,
    'Gemini' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_gemini_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_gemini_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_gemini_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_gemini_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'gemini');

INSERT OR REPLACE INTO ai_model_providers (id, name, enabled, default_api_url, display_label, registration_link)
SELECT 
    'grok' as id,
    'Grok' as name,
    CASE WHEN (SELECT value FROM system_config WHERE key = 'ai_model_grok_enabled') = 'true' THEN 1 ELSE 0 END as enabled,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_grok_default_api_url'), '') as default_api_url,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_grok_display_label'), '') as display_label,
    COALESCE((SELECT value FROM system_config WHERE key = 'ai_model_grok_registration_link'), '') as registration_link
WHERE NOT EXISTS (SELECT 1 FROM ai_model_providers WHERE id = 'grok');

-- ============================================================================
-- PART 3: Update ai_models table structure
-- ============================================================================

-- The ai_models table already has user_id column (added in createTables)
-- We need to ensure proper indexes exist for user isolation

-- Create indexes for efficient user-based queries
CREATE INDEX IF NOT EXISTS idx_ai_models_user_id ON ai_models(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_models_user_enabled ON ai_models(user_id, enabled);
CREATE INDEX IF NOT EXISTS idx_ai_models_provider ON ai_models(provider);

-- Add unique constraint to prevent duplicate model names per user per provider
CREATE UNIQUE INDEX IF NOT EXISTS idx_ai_models_user_provider_name 
ON ai_models(user_id, provider, name);

-- ============================================================================
-- PART 4: Data Migration - Convert default models to user models
-- ============================================================================

-- This migration will be handled by Go code in database.go
-- because it requires:
-- 1. Querying all users
-- 2. Generating UUIDs for new models
-- 3. Complex logic for copying model configurations
-- 
-- The Go migration function will:
-- - Find all models with user_id = 'default'
-- - For each existing user, create a copy of each default model
-- - Clear API keys (require users to reconfigure)
-- - Set models to disabled by default
-- - Delete the original default models

-- Verification query (for manual checking after migration)
-- SELECT user_id, COUNT(*) as model_count FROM ai_models GROUP BY user_id;

-- ============================================================================
-- PART 5: Add foreign key constraint
-- ============================================================================

-- Note: SQLite doesn't support adding foreign key constraints to existing tables
-- The foreign key will be enforced in application code and in new table creation
-- Future table recreations should include:
-- FOREIGN KEY (provider) REFERENCES ai_model_providers(id)

SELECT 'Migration 005 completed: User model management schema created' as status;
