import os
import pg8000.dbapi as pg
import pandas as pd
import sys

# Redirect stdout to a file
sys.stdout = open('task_analysis.txt', 'w', encoding='utf-8')

def load_env():
    env_vars = {}
    try:
        with open('.env', 'r', encoding='utf-8') as f:
            for line in f:
                line = line.strip()
                if not line or line.startswith('#'):
                    continue
                if '=' in line:
                    k, v = line.split('=', 1)
                    env_vars[k.strip()] = v.strip()
    except Exception:
        pass
    return env_vars

env = load_env()
HOST = env.get("LIC_PG_HOST", "172.24.240.1")
PORT = int(env.get("LIC_PG_PORT", "5432"))
USER = env.get("LIC_PG_USER", "postgres")
PASSWORD = env.get("LIC_PG_PASSWORD", "")
DATABASE = env.get("LIC_PG_DATABASE", "licensing_risks")

print(f"Connecting to DB...")
try:
    conn = pg.connect(host=HOST, port=PORT, user=USER, password=PASSWORD, database=DATABASE)
    cursor = conn.cursor()
    
    print("\n--- Regions ---")
    cursor.execute("SELECT id, name FROM regions")
    for r in cursor.fetchall():
        print(f"{r[1]}: {r[0]}")
        
    print("\n--- Current Counts ---")
    cursor.execute("SELECT COUNT(*) FROM themes")
    print(f"Themes: {cursor.fetchone()[0]}")
    
    try:
        cursor.execute("SELECT COUNT(*) FROM region_theme_permits")
        print(f"Region Theme Permits: {cursor.fetchone()[0]}")
    except: pass
    
    try:
        cursor.execute("SELECT COUNT(*) FROM permit_theme_rules")
        print(f"Permit Theme Rules: {cursor.fetchone()[0]}")
    except: pass

    conn.close()

except Exception as e:
    print(f"DB Error: {e}")

print("\n--- Excel Structure ---")
try:
    # Read header at row 1
    df = pd.read_excel('主题-事项绑定.xlsx', header=1)
    print("Columns:", df.columns.tolist())
    # Identify key columns
    print("\nSample Data:")
    print(df.iloc[:3].to_string())
except Exception as e:
    print(f"Excel Error: {e}")