import pandas as pd
import os
import sys

# Add project root to path
sys.path.append(os.getcwd())

from lawrisk.services import licensing_repo as lic_repo
from lawrisk.utils.env_loader import load_env

def bind_departments():
    load_env()
    file_path = "审批服务部门_修正.xlsx"
    output_path = "审批部门绑定结果.xlsx"
    
    if not os.path.exists(file_path):
        print(f"File not found: {file_path}")
        return

    print("Loading Excel...")
    df = pd.read_excel(file_path)
    
    # Normalize headers
    col_map = {c: c.strip() for c in df.columns}
    df.rename(columns=col_map, inplace=True)
    
    # Identify key columns
    permit_col = None
    dept_col = None
    
    for c in df.columns:
        if "事项名称" in c:
            permit_col = c
        if "部门系统简称" in c:
            dept_col = c
            
    if not permit_col or not dept_col:
        print(f"Required columns not found. Found: {df.columns.tolist()}")
        return

    print(f"Using Permit Column: '{permit_col}'")
    print(f"Using Department Column: '{dept_col}'")

    results = []
    updated_count = 0
    
    with lic_repo._lic_pg_conn() as conn:
        cur = conn.cursor()
        
        for idx, row in df.iterrows():
            p_name = str(row[permit_col]).strip()
            d_name = str(row[dept_col]).strip()
            
            if not p_name or p_name == 'nan':
                continue
                
            # Check if permit exists
            cur.execute("SELECT id FROM permits WHERE name = %s", (p_name,))
            res = cur.fetchone()
            
            status = "未绑定"
            note = "事项不存在"
            
            if res:
                pid = res[0]
                # Update unit_name in region_permit_details
                # We update for ALL regions for this permit, or just '市级'? 
                # Usually 'unit_name' is specific to a sheet/region, but here it seems global for the permit.
                # We will update for all entries of this permit_id to be safe/consistent with "Service Department" concept.
                
                try:
                    cur.execute("""
                        UPDATE region_permit_details 
                        SET unit_name = %s 
                        WHERE permit_id = %s
                    """, (d_name, pid))
                    
                    if cur.rowcount > 0:
                        status = "已绑定"
                        note = f"更新了 {cur.rowcount} 条记录"
                        updated_count += 1
                    else:
                        status = "未更新"
                        note = "事项存在但无详情记录"
                except Exception as e:
                    status = "错误"
                    note = str(e)
            
            print(f"Processing: {p_name} -> {status} ({note})")
            
            results.append({
                "序号": row.get("序号", ""),
                "事项名称": p_name,
                "应绑部门": d_name,
                "绑定状态": status,
                "备注": note
            })
            
        conn.commit()

    print(f"Binding complete. Updated {updated_count} permits.")
    
    # Save report
    res_df = pd.DataFrame(results)
    res_df.to_excel(output_path, index=False)
    
    # Check for unbound
    unbound = res_df[res_df["绑定状态"] != "已绑定"]
    if not unbound.empty:
        print("\n=== 未成功绑定的事项 ===")
        print(unbound[["事项名称", "备注"]])
    else:
        print("\n所有事项均已成功绑定！")

if __name__ == "__main__":
    bind_departments()