import os
import sys
import uuid
import logging
import pg8000.native as pg

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

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

from lawrisk.utils.env_loader import load_env
load_env()

def _lic_pg_conn(autocommit: bool = False) -> pg.Connection:
    host = os.getenv("LIC_PG_HOST", "8.138.196.105")  # Updated default to match known external IP
    port = int(os.getenv("LIC_PG_PORT", os.getenv("PG_PORT", "5432")))
    user = os.getenv("LIC_PG_USER", os.getenv("PG_USER", "postgres"))
    password = os.getenv("LIC_PG_PASSWORD", "")
    database = os.getenv("LIC_PG_DATABASE", "licensing_risks")
    
    logger.info(f"Connecting to {host}:{port}/{database} as {user}")
    try:
        conn = pg.Connection(user=user, host=host, port=port, password=password, database=database)
        conn.autocommit = autocommit
        return conn
    except Exception as e:
        logger.error(f"Connection failed: {e}")
        raise

def main():
    try:
        conn = _lic_pg_conn()
        logger.info("Connected to database successfully.")
        
        # 1. Get '市级' Region ID
        region_id_city = None
        for row in conn.run("SELECT id, name FROM regions WHERE name LIKE '市级%'"):
            region_id_city = row[0]
            logger.info(f"Found City Region: {row[1]} ({row[0]})")
            break
        
        if not region_id_city:
            # Fallback if specific name differs
             for row in conn.run("SELECT id, name FROM regions WHERE name = '市级'"):
                region_id_city = row[0]
                break
        
        if not region_id_city:
            logger.error("Could not find '市级' region. Exiting.")
            return

        # 2. Get/Create Admin Department
        admin_dept_name = "系统管理部"
        admin_dept_code = "SYSADMIN"
        
        result = conn.run("SELECT id FROM service_departments WHERE code = :code", code=admin_dept_code)
        
        if result:
            admin_dept_id = result[0][0]
            logger.info(f"Found existing Admin Department: {admin_dept_id}")
            # Ensure it is bound to City Region
            conn.run("UPDATE service_departments SET region_id = :rid WHERE id = :id", rid=region_id_city, id=admin_dept_id)
        else:
            admin_dept_id = str(uuid.uuid4())
            logger.info(f"Creating new Admin Department: {admin_dept_id}")
            conn.run("""
                INSERT INTO service_departments (id, name, code, region_id, description, grade, unit_level)
                VALUES (:id, :name, :code, :rid, 'System Administrator Department', 100, 'municipal')
            """, id=admin_dept_id, name=admin_dept_name, code=admin_dept_code, rid=region_id_city)
            
        # 3. Assign 'admin' user to this department
        conn.run("""
            UPDATE auth_users 
            SET service_department_id = :dept_id,
                role = 'admin',
                grade = 100
            WHERE username = 'admin'
        """, dept_id=admin_dept_id)
        logger.info(f"Assigned 'admin' user to department {admin_dept_id}")

        # 4. Bind City-level Permis to this Department
        # Find permits in the city region
        permits = conn.run("""
            SELECT p.id, p.name 
            FROM region_permit_details rpd
            JOIN permits p ON p.id = rpd.permit_id
            WHERE rpd.region_id = :rid
        """, rid=region_id_city)
        
        permit_ids = [row[0] for row in permits]
        logger.info(f"Found {len(permit_ids)} permits in '市级' region.")
        
        if not permit_ids:
            logger.warning("No permits found to bind!")
            return

        # Upsert permit_sources
        # We want to ensure specific records exist for these permits
        updated_count = 0
        inserted_count = 0
        
        for pid in permit_ids:
            # Check if source exists
            existing = conn.run("""
                SELECT 1 FROM permit_sources WHERE region_id = :rid AND permit_id = :pid
            """, rid=region_id_city, pid=pid)
            
            if existing:
                conn.run("""
                    UPDATE permit_sources
                    SET bound_department_id = :dept_id,
                        uploader_department_id = COALESCE(uploader_department_id, :dept_id),
                        updated_at = NOW()
                    WHERE region_id = :rid AND permit_id = :pid
                """, dept_id=admin_dept_id, rid=region_id_city, pid=pid)
                updated_count += 1
            else:
                conn.run("""
                    INSERT INTO permit_sources (
                        region_id, permit_id, source_type, source_name, 
                        uploader_department_id, bound_department_id, created_at, updated_at
                    ) VALUES (
                        :rid, :pid, 'system_init', 'Initial Binding',
                        :dept_id, :dept_id, NOW(), NOW()
                    )
                """, rid=region_id_city, pid=pid, dept_id=admin_dept_id)
                inserted_count += 1
                
        logger.info(f"Binding complete. Updated: {updated_count}, Inserted: {inserted_count}")
        
    except Exception as e:
        logger.error(f"Error: {e}")
        import traceback
        traceback.print_exc()
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    main()