import os
import pg8000.dbapi as pg
from lawrisk.utils.env_loader import load_env

load_env()

def get_db_connection():
    conn_params = {
        "host": os.getenv("LIC_PG_HOST", "172.24.240.1"),
        "port": int(os.getenv("LIC_PG_PORT", "5432")),
        "user": os.getenv("LIC_PG_USER", "postgres"),
        "password": os.getenv("LIC_PG_PASSWORD", ""),
        "database": os.getenv("LIC_PG_DATABASE", "licensing_risks"),
    }
    return pg.connect(**conn_params)

def delete_test_items():
    blacklist = [
        "测试许可_SearchTest",
        "演出经纪机构变更",
        "演出经纪机构从事营业性演出经营活动审批"
    ]
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    for item in blacklist:
        print(f"正在从数据库彻底删除: {item}")
        # 使用子查询直接全量清理
        try:
            # 1. 解绑主题
            cursor.execute("DELETE FROM region_theme_permits WHERE permit_id IN (SELECT id FROM permits WHERE name = %s)", [item])
            # 2. 解绑风险
            cursor.execute("DELETE FROM region_permit_risks WHERE permit_id IN (SELECT id FROM permits WHERE name = %s)", [item])
            # 3. 解绑服务部门
            cursor.execute("DELETE FROM service_department_permits WHERE permit_id IN (SELECT id FROM permits WHERE name = %s)", [item])
            # 4. 删除主表
            cursor.execute("DELETE FROM permits WHERE name = %s", [item])
            print(f"  事项 '{item}' 删除成功。")
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(f"  事项 '{item}' 删除过程中出错: {e}")
            
    conn.close()
    print("数据库清理完成。")

if __name__ == "__main__":
    delete_test_items()