# database/db.py
import asyncpg
import sys
import os
from config import DB_DSN

class Database:
    def __init__(self):
        self.pool = None

    async def connect(self):
        try:
            self.pool = await asyncpg.create_pool(
                dsn=DB_DSN, 
                min_size=5,   
                max_size=30,  
                command_timeout=60.0,
                # 🔥 THE SUPABASE POOLER FIXES
                statement_cache_size=0,
                server_settings={'search_path': 'public'}
            )
            print("✅ Connected to PostgreSQL successfully via Transaction Pooler.")
            
            await self.init_db()
            
        except Exception as e:
            print(f"❌ CRITICAL: Failed to connect to database: {e}")
            sys.exit(1)

    async def init_db(self):
        schema_path = os.path.join(os.path.dirname(__file__), '..', 'sql', 'schema.sql')
        
        try:
            with open(schema_path, 'r') as file:
                schema_sql = file.read()
                
            async with self.pool.acquire() as conn:
                # 1. Run the base schema file
                await conn.execute(schema_sql)
                
                # 2. ⚡ THE AUTOPATCH: Forcefully inject missing moderation columns
                await conn.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS is_banned BOOLEAN DEFAULT FALSE;")
                await conn.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS is_locked BOOLEAN DEFAULT FALSE;")
                
            print("✅ Database tables verified and patched with Moderation Columns.")
        except FileNotFoundError:
            print("❌ WARNING: sql/schema.sql file not found. Skipping table initialization.")
        except Exception as e:
            print(f"❌ CRITICAL: Failed to initialize tables: {e}")
            sys.exit(1)

    async def close(self):
        if self.pool:
            await self.pool.close()

db = Database()