import pg8000.dbapi as pg
import os

def check_query():
    conn_params = {
        "host": os.getenv("LIC_PG_HOST", "172.24.240.1"),
        "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"),
    }
    
    try:
        conn = pg.connect(**conn_params)
        cur = conn.cursor()
        
        print("Testing new query logic...")
        
        sql = """
            SELECT COUNT(*)
            FROM region_permit_details rpd
            JOIN permits p ON p.id = rpd.permit_id
            JOIN regions r ON r.id = rpd.region_id
            LEFT JOIN region_theme_permits rtp ON rtp.permit_id = rpd.permit_id AND rtp.region_id = rpd.region_id
            LEFT JOIN themes t ON t.id = rtp.theme_id
            WHERE r.name = '市级'
        """
        
        cur.execute(sql)
        count = cur.fetchone()[0]
        print(f"Count with new query: {count}")
        
        if count == 66:
            print("SUCCESS: New query finds all 66 permits.")
        else:
            print(f"FAILURE: New query found {count} permits, expected 66.")
            
        conn.close()
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    check_query()