-- ============================================================
-- Token 表合并脚本 - PostgreSQL
-- 将 augment_session_tokens, anyrouter_tokens, agentrouter_tokens 
-- 合并为 cli_tokens 表
-- 
-- 执行方式: psql -U postgres -d ctf -f merge_tokens_tables.sql
-- 
-- ⚠️  执行前请备份数据库！
-- ============================================================

BEGIN;

-- ============================================================
-- 1. 创建新表 cli_tokens
-- ============================================================

CREATE TABLE IF NOT EXISTS cli_tokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    provider_type VARCHAR(50) NOT NULL,  -- 'augment' | 'anyrouter' | 'agentrouter'
    token TEXT NOT NULL,
    name VARCHAR(100),
    is_global BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    last_used TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    -- 唯一约束：每个用户每种类型的Token只能有一个
    CONSTRAINT uq_user_provider_token 
        UNIQUE(user_id, is_global, provider_type)
);

-- ============================================================
-- 2. 迁移 augment_session_tokens 数据
-- ============================================================

INSERT INTO cli_tokens (
    user_id, provider_type, token, name, is_global, 
    is_active, last_used, created_at, updated_at
)
SELECT 
    user_id, 
    'augment' as provider_type,
    token,
    name,
    is_global,
    TRUE as is_active,  -- augment_session_tokens 没有 is_active 字段，默认 TRUE
    last_used,
    created_at,
    updated_at
FROM augment_session_tokens
ON CONFLICT (user_id, is_global, provider_type) DO UPDATE
SET token = EXCLUDED.token,
    name = EXCLUDED.name,
    is_active = EXCLUDED.is_active,
    last_used = EXCLUDED.last_used,
    updated_at = EXCLUDED.updated_at;

-- ============================================================
-- 3. 迁移 anyrouter_tokens 数据
-- ============================================================

INSERT INTO cli_tokens (
    user_id, provider_type, token, name, is_global, 
    is_active, last_used, created_at, updated_at
)
SELECT 
    user_id,
    'anyrouter' as provider_type,
    token,
    name,
    is_global,
    is_active,
    last_used,
    created_at,
    updated_at
FROM anyrouter_tokens
ON CONFLICT (user_id, is_global, provider_type) DO UPDATE
SET token = EXCLUDED.token,
    name = EXCLUDED.name,
    is_active = EXCLUDED.is_active,
    last_used = EXCLUDED.last_used,
    updated_at = EXCLUDED.updated_at;

-- ============================================================
-- 4. 迁移 agentrouter_tokens 数据
-- ============================================================

INSERT INTO cli_tokens (
    user_id, provider_type, token, name, is_global, 
    is_active, last_used, created_at, updated_at
)
SELECT 
    user_id,
    'agentrouter' as provider_type,
    token,
    name,
    is_global,
    is_active,
    last_used,
    created_at,
    updated_at
FROM agentrouter_tokens
ON CONFLICT (user_id, is_global, provider_type) DO UPDATE
SET token = EXCLUDED.token,
    name = EXCLUDED.name,
    is_active = EXCLUDED.is_active,
    last_used = EXCLUDED.last_used,
    updated_at = EXCLUDED.updated_at;

-- ============================================================
-- 5. 创建索引
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_cli_tokens_user_provider 
ON cli_tokens(user_id, provider_type);

CREATE INDEX IF NOT EXISTS idx_cli_tokens_provider_global 
ON cli_tokens(provider_type, is_global);

CREATE INDEX IF NOT EXISTS idx_cli_tokens_is_active 
ON cli_tokens(is_active);

-- ============================================================
-- 6. 验证数据迁移
-- ============================================================

-- 检查迁移数据量
SELECT 
    provider_type,
    COUNT(*) as total_count,
    COUNT(CASE WHEN is_global THEN 1 END) as global_count,
    COUNT(CASE WHEN NOT is_global THEN 1 END) as user_count
FROM cli_tokens
GROUP BY provider_type;

-- 检查原表数据量（用于对比）
SELECT 'augment_session_tokens' as table_name, COUNT(*) as count FROM augment_session_tokens
UNION ALL
SELECT 'anyrouter_tokens', COUNT(*) FROM anyrouter_tokens
UNION ALL
SELECT 'agentrouter_tokens', COUNT(*) FROM agentrouter_tokens;

COMMIT;

-- ============================================================
-- 7. 删除旧表（⚠️  确认数据迁移成功后再执行）
-- ============================================================

-- 取消注释以下行来删除旧表
-- BEGIN;
-- DROP TABLE IF EXISTS augment_session_tokens;
-- DROP TABLE IF EXISTS anyrouter_tokens;
-- DROP TABLE IF EXISTS agentrouter_tokens;
-- COMMIT;

-- ============================================================
-- 回滚脚本（如果需要回滚）
-- ============================================================

-- 如果需要回滚，取消注释以下内容并执行
-- 
-- BEGIN;
-- 
-- -- 从 cli_tokens 恢复 augment_session_tokens
-- CREATE TABLE augment_session_tokens AS
-- SELECT id, user_id, token, name, is_global, last_used, created_at, updated_at
-- FROM cli_tokens
-- WHERE provider_type = 'augment';
-- 
-- ALTER TABLE augment_session_tokens ADD PRIMARY KEY (id);
-- ALTER TABLE augment_session_tokens ADD CONSTRAINT uq_user_token UNIQUE(user_id, is_global);
-- 
-- -- 恢复 anyrouter_tokens
-- CREATE TABLE anyrouter_tokens AS
-- SELECT id, user_id, token, name, is_global, is_active, last_used, created_at, updated_at
-- FROM cli_tokens
-- WHERE provider_type = 'anyrouter';
-- 
-- ALTER TABLE anyrouter_tokens ADD PRIMARY KEY (id);
-- ALTER TABLE anyrouter_tokens ADD CONSTRAINT uq_anyrouter_user_token UNIQUE(user_id, is_global);
-- 
-- -- 恢复 agentrouter_tokens
-- CREATE TABLE agentrouter_tokens AS
-- SELECT id, user_id, token, name, is_global, is_active, last_used, created_at, updated_at
-- FROM cli_tokens
-- WHERE provider_type = 'agentrouter';
-- 
-- ALTER TABLE agentrouter_tokens ADD PRIMARY KEY (id);
-- ALTER TABLE agentrouter_tokens ADD CONSTRAINT uq_agentrouter_user_token UNIQUE(user_id, is_global);
-- 
-- -- 删除新表
-- DROP TABLE cli_tokens;
-- 
-- COMMIT;

