import psycopg2
import csv

# Database connection settings (customize according to your database)
conn = psycopg2.connect(
    dbname="SRP_DSS", 
    user="user2", 
    password="user2@srpdssdb", 
    host="srpsid-dss.gos.pk", 
    port="5432"
)
cur = conn.cursor()

# List of intersect tables
intersect_tables = ['health_facilities', 'settlements', 'railways', 'highways', 'main_roads', 'minor_roads']

# Step 2: Retrieve all tables that start with 'max_' in the schema
cur.execute("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'phase2' AND tablename LIKE 'max_%';")
max_tables = cur.fetchall()

# Step 3: Loop through max_ tables and intersect tables to build and execute queries
for max_table in max_tables:
    max_table = max_table[0]  # Extract table name from tuple
    for intersect_table in intersect_tables:
        # Dynamically construct the query
        query = "SELECT a.ha, COUNT(ST_Intersection(a.geometry, b.geom)) AS len, '{max_table}', '{intersect_table}' FROM phase2.{max_table} a, phase2.{intersect_table} b WHERE ST_Intersects(a.geometry, b.geom) AND a.ha BETWEEN 0 AND 5 GROUP BY a.ha";
        
        # Execute the dynamically created query
        cur.execute(query)
        results = cur.fetchall()

        # Save the results to a CSV file
        filename = {max_table} + '_vs_' + {intersect_table} + 'results.csv'
        with open(filename, mode='w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow(['ha', 'len', 'max_table', 'source_table'])  # Writing headers
            writer.writerows(results)  # Writing data

        print("Data saved to " + {filename})

# Closing the connection
cur.close()
conn.close()