#!/usr/bin/env python
"""Test script for permit browser API"""
import sys
import os
import json

# Add project root to path
project_root = os.path.abspath(os.path.dirname(__file__))
sys.path.insert(0, project_root)

# Load .env
from lawrisk.utils.env_loader import load_env
load_env('.env', override=False)

# Import database connection
import pg8000

def test_permit_filtering():
    """Test the permit filtering functionality"""
    print("=" * 60)
    print("Testing Permit Browser API")
    print("=" * 60)
    print()

    # Connect to database
    conn = pg8000.connect(
        host=os.getenv('LIC_PG_HOST'),
        port=int(os.getenv('LIC_PG_PORT', '5432')),
        user=os.getenv('LIC_PG_USER'),
        password=os.getenv('LIC_PG_PASSWORD'),
        database=os.getenv('LIC_PG_DATABASE')
    )
    cur = conn.cursor()

    # Test 1: Check regions
    print("1. Testing regions...")
    cur.execute("SELECT id, name FROM regions ORDER BY name")
    regions = [{"id": str(r[0]), "name": str(r[1])} for r in cur.fetchall()]
    print(f"   Found {len(regions)} regions")
    for region in regions[:3]:
        print(f"   - {region['name']} (ID: {region['id'][:8]}...)")
    print()

    # Test 2: Check themes
    print("2. Testing themes...")
    cur.execute("SELECT id, name FROM themes ORDER BY name LIMIT 10")
    themes = [{"id": str(t[0]), "name": str(t[1])} for t in cur.fetchall()]
    print(f"   Found {len(themes)} themes")
    for theme in themes[:3]:
        print(f"   - {theme['name']} (ID: {theme['id'][:8]}...)")
    print()

    # Test 3: Check service departments
    print("3. Testing service departments...")
    cur.execute("SELECT id, name, code FROM service_departments ORDER BY name LIMIT 10")
    departments = [{"id": str(d[0]), "name": str(d[1]), "code": str(d[2])} for d in cur.fetchall()]
    print(f"   Found {len(departments)} departments")
    for dept in departments[:3]:
        print(f"   - {dept['name']} ({dept['code']})")
    print()

    # Test 4: Check permits
    print("4. Testing permits...")
    cur.execute("""
        SELECT COUNT(DISTINCT permit_id || '_' || region_id)
        FROM region_theme_permits
    """)
    permit_count = cur.fetchone()[0]
    print(f"   Total permit-region combinations: {permit_count}")
    print()

    # Test 5: Sample permit data
    print("5. Sample permit data...")
    cur.execute("""
        SELECT DISTINCT
            p.name AS permit_name,
            r.name AS region_name,
            t.name AS theme_name
        FROM region_theme_permits rtp
        JOIN permits p ON p.id = rtp.permit_id
        JOIN regions r ON r.id = rtp.region_id
        LEFT JOIN themes t ON t.id = rtp.theme_id
        ORDER BY p.name
        LIMIT 5
    """)
    sample_permits = cur.fetchall()
    for permit_name, region_name, theme_name in sample_permits:
        print(f"   - {permit_name}")
        print(f"     Region: {region_name}, Theme: {theme_name or 'N/A'}")
    print()

    # Test 6: Risk statistics
    print("6. Risk statistics...")
    cur.execute("""
        SELECT
            COUNT(DISTINCT rpr.permit_id || '_' || rpr.region_id) AS permits_with_risks,
            SUM(COUNT(rpr.risk_id)) OVER () AS total_risk_mappings
        FROM region_permit_risks rpr
    """)
    risk_stats = cur.fetchone()
    print(f"   Permits with risks: {risk_stats[0]}")
    print(f"   Total risk mappings: {risk_stats[1]}")
    print()

    conn.close()

    print("=" * 60)
    print("Test completed successfully!")
    print("=" * 60)
    print()
    print("Summary:")
    print(f"  - {len(regions)} regions available")
    print(f"  - {len(themes)} themes available")
    print(f"  - {len(departments)} departments available")
    print(f"  - {permit_count} permit-region combinations")
    print()
    print("The permit browser should work correctly with this data.")

if __name__ == "__main__":
    test_permit_filtering()