# -*- coding: utf-8 -*-
"""修复数据库序列：将序列值同步到表中的最大主键值。

根因：序列被重置到 1，但表中已有数据，导致 INSERT 时主键冲突。
"""
import sys
from pathlib import Path

_FEIQIU_ROOT = Path(__file__).resolve().parents[2]
if str(_FEIQIU_ROOT) not in sys.path:
    sys.path.insert(0, str(_FEIQIU_ROOT))

from config.settings import AppConfig
from database.connection import DatabaseConnection

config = AppConfig.load()
db = DatabaseConnection(
    dsn=config["db"]["dsn"],
    connect_timeout=config["db"].get("connect_timeout_sec"),
)

# 需要修复的序列列表：(序列名, 表名, 主键列名)
SEQUENCES_TO_FIX = [
    ("meta.etl_run_run_id_seq", "meta.etl_run", "run_id"),
    ("dws.dws_index_percentile_history_history_id_seq", "dws.dws_index_percentile_history", "history_id"),
]

for seq_name, table_name, pk_col in SEQUENCES_TO_FIX:
    try:
        # 获取当前最大值
        rows = db.query(f"SELECT COALESCE(max({pk_col}), 0) as max_val FROM {table_name}")
        max_val = rows[0]["max_val"]
        
        # 获取当前序列值
        rows2 = db.query(f"SELECT last_value, is_called FROM {seq_name}")
        cur_val = rows2[0]["last_value"]
        
        print(f"{seq_name}:")
        print(f"  表 {table_name} 最大 {pk_col} = {max_val}")
        print(f"  序列当前值 = {cur_val}")
        
        if max_val > cur_val:
            # 修复：将序列设置为 max_val + 1
            db.query(f"SELECT setval('{seq_name}', {max_val})")
            db.commit()
            
            # 验证
            rows3 = db.query(f"SELECT last_value, is_called FROM {seq_name}")
            print(f"  ✓ 已修复: 序列新值 = {rows3[0]['last_value']}")
        else:
            print(f"  ✓ 序列值正常，无需修复")
        print()
    except Exception as e:
        print(f"  ✗ 修复失败: {e}")
        db.rollback()

db.close()
print("完成。")
