app/database.py

import os
import ssl
import re
import sys
import logging
import urllib.parse
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.engine.url import make_url

def debug_print(msg):
    sys.stderr.write(f"--- DB_DEBUG: {msg} ---\n")
    sys.stderr.flush()

debug_print("Loading database.py")

BASE_DIR = os.path.dirname(os.path.abspath(__file__))

# Define default PostgreSQL fallback DBs for local development if variables missing
DEFAULT_AUTH_URL = "postgresql+pg8000://postgres:root@localhost:5432/auth_db"
DEFAULT_MANDI_URL = "postgresql+pg8000://postgres:root@localhost:5432/mandi_db"

# Retrieve DB URLs - strip whitespace as Render/Prisma/Neon can sometimes have it
AUTH_DATABASE_URL = os.getenv("AUTH_DATABASE_URL", DEFAULT_AUTH_URL).strip()
MANDI_DATABASE_URL = os.getenv("MANDI_DATABASE_URL", DEFAULT_MANDI_URL).strip()

# Helper to clean and format URLs
def format_db_url(name, url: str) -> str:
    if not url:
        debug_print(f"{name} is EMPTY")
        return ""
    
    url = url.strip()
    
    # If it looks like a key-value string (Supabase style), parse it
    if "user=" in url and "host=" in url:
        debug_print(f"Detected key-value format for {name}. Attempting to parse...")
        try:
            # Match assignments like key=value or key = value
            # We handle potential newlines or multiple spaces between pairs
            kv = {}
            # Use regex to find all key=value pairs, even if values have special chars
            matches = re.findall(r'(\w+)\s*=\s*([^\s]+)', url)
            for k, v in matches:
                kv[k.lower()] = v
            
            if all(k in kv for k in ['user', 'password', 'host', 'dbname']):
                port = kv.get('port', '5432')
                # Escape password to handle special chars like @ or :
                safe_password = urllib.parse.quote_plus(kv['password'])
                # For Supabase, we default to psycopg2
                url = f"postgresql+psycopg2://{kv['user']}:{safe_password}@{kv['host']}:{port}/{kv['dbname']}"
                debug_print(f"Parsed {name} into SQLAlchemy format (with encoded password).")
            else:
                debug_print(f"Incomplete key-value pairs for {name}: {list(kv.keys())}")
        except Exception as e:
            debug_print(f"Failed to parse key-value string for {name}: {e}")

    # Standardize dialect
    is_supabase = "supabase" in url.lower()
    dialect = "+psycopg2" if is_supabase else "+pg8000"
    
    # Standardize scheme using regex to be robust against variations
    if re.match(r"^postgres(ql)?(\+\w+)?://", url):
        url = re.sub(r"^postgres(ql)?(\+\w+)?://", f"postgresql{dialect}://", url, count=1)
    elif not url.startswith("postgresql"):
        # If it doesn't have a protocol at all after parsing attempts, we assume it's just raw
        # but create_engine will still fail later if it's not a URL.
        pass
    
    return url

# Retrieve and clean DB URLs
AUTH_RAW = os.getenv("AUTH_DATABASE_URL", DEFAULT_AUTH_URL)
MANDI_RAW = os.getenv("MANDI_DATABASE_URL", DEFAULT_MANDI_URL)

AUTH_DATABASE_URL = format_db_url("AUTH", AUTH_RAW)
MANDI_DATABASE_URL = format_db_url("MANDI", MANDI_RAW)

if not AUTH_DATABASE_URL:
    raise ValueError("AUTH_DATABASE_URL is not set or empty.")
if not MANDI_DATABASE_URL:
    raise ValueError("MANDI_DATABASE_URL is not set or empty.")

# Args for Postgres
# We add pool_recycle=1800 to recycle connections older than 30 minutes, 
# preventing them from being dropped quietly by the database server.
auth_engine_args = {"pool_size": 10, "max_overflow": 20, "pool_pre_ping": True, "pool_recycle": 1800, "connect_args": {}}
mandi_engine_args = {"pool_size": 20, "max_overflow": 30, "pool_pre_ping": True, "pool_recycle": 1800, "connect_args": {}}

# For Remote DBs, we handle SSL context manually ONLY for pg8000
# Psycopg2 (Supabase) handles SSL via the connection string (?sslmode=require)
def apply_ssl_if_needed(url: str, engine_args: dict):
    # Only apply to external hosts
    is_external = any(host in url for host in ["neon.tech", "supabase", "aws.com", "elephantsql.com"])
    
    if is_external:
        # If using pg8000, we must strip params and use ssl_context
        if "pg8000" in url:
            cleaned_url = url.split("?")[0]
            ssl_context = ssl.create_default_context()
            ssl_context.check_hostname = False
            ssl_context.verify_mode = ssl.CERT_NONE
            engine_args["connect_args"] = {"ssl_context": ssl_context}
            return cleaned_url
        
        # If using psycopg2 (Supabase)
        if "psycopg2" in url:
            # Render networking can be tricky with Supabase IPv6 on port 5432
            # Connection pooler on 6543 is generally more stable.
            # We automatically switch to 6543 ONLY if we're on Render (detected by RENDER env var)
            
            # Ensure sslmode=require is present for security and stability
            if "sslmode" not in url:
                separator = "&" if "?" in url else "?"
                url = f"{url}{separator}sslmode=require"
            
            if ":6543" in url:
                debug_print("Using Supabase Pooler (6543). Ensuring compatibility parameters.")
                pass
                
    return url

AUTH_DATABASE_URL = apply_ssl_if_needed(AUTH_DATABASE_URL, auth_engine_args)
MANDI_DATABASE_URL = apply_ssl_if_needed(MANDI_DATABASE_URL, mandi_engine_args)

def safe_create_engine(name, url, args):
    try:
        # Pre-validate with make_url
        u = make_url(url)
        debug_print(f"Creating {name} engine (Driver: {u.drivername}, Host: {u.host}, Port: {u.port})")
        
        # We DON'T test connection here because it might block app startup 
        # or fail if network is temporarily down. SQLAlchemy handles reconnection.
        engine = create_engine(url, **args)
        debug_print(f"Engine {name} created successfully.")
        return engine
    except Exception as e:
        debug_print(f"CRITICAL ERROR in {name} engine creation: {str(e)}")
        # We still return the engine if possible or raise if it's a structural error
        raise e

auth_engine = safe_create_engine("AUTH", AUTH_DATABASE_URL, auth_engine_args)
mandi_engine = safe_create_engine("MANDI", MANDI_DATABASE_URL, mandi_engine_args)

AuthSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=auth_engine)
MandiSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=mandi_engine)

AuthBase = declarative_base()
MandiBase = declarative_base()

def get_auth_db():
    db = AuthSessionLocal()
    try:
        yield db
    finally:
        db.close()

def get_mandi_db():
    db = MandiSessionLocal()
    try:
        yield db
    finally:
        db.close()