"""PostgreSQL logic."""

import os
import subprocess
from typing import Dict, List

import psycopg

import config


def _get_connection(db_credentials: Dict[str, str]) -> psycopg.Connection:
    """Return a psycopg connection using provided credentials.

    Args:
        db_credentials: Database credentials containing host, username, password.
    """
    return psycopg.connect(
        host=db_credentials["host"],
        user=db_credentials["username"],
        password=db_credentials["password"],
        dbname="postgres",
        autocommit=True,
    )


def _list_user_databases(credentials: Dict[str, str]) -> List[str]:
    """Return list of non-system database names.

    Args:
        credentials: Database credentials

    Returns:
        List of database names excluding system databases.
    """
    omitted = config.OMITTED_DATABASES.get("postgresql", [])

    base_query = [
        "SELECT datname FROM pg_database",
        "WHERE datistemplate = false",
        "AND datallowconn = true",
    ]
    params: List[str] = []
    if omitted:
        omitted_databases = ",".join(["%s"] * len(omitted))
        base_query.append(f"AND datname NOT IN ({omitted_databases})")
        params.extend(omitted)
    base_query.append("ORDER BY 1")
    sql = " ".join(base_query) + ";"

    with _get_connection(credentials) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, params if params else None)
            return [row[0] for row in cur.fetchall()]


def get_database_ddl(credentials: Dict[str, str]) -> str:
    """Get the schema DDL for all non-system PostgreSQL databases.

    Args:
        credentials: Database credentials containing host, username, password.

    Returns:
        Path to the output SQL file containing the aggregated schema DDL.
    """
    output_file = "/tmp/001_schema.sql"

    try:
        databases = _list_user_databases(credentials)
    except Exception as e:
        open(output_file, "w").close()
        raise e

    env = os.environ.copy()
    env["PGPASSWORD"] = credentials["password"]

    """
    Build and run pg_dump for each database
    Dump only schema (no data) from all *non-system* databases
    without including users, passwords, or global objects. We intentionally avoid
    `pg_dumpall` because it pulls in roles and global metadata we do not want.
    """
    with open(output_file, "w", encoding="utf-8") as out:
        for db in databases:
            out.write(f"-- ===== Database: {db} =====\n")
            cmd = [
                "pg_dump",
                f"--host={credentials['host']}",
                f"--username={credentials['username']}",
                "--schema-only",  # DDL only
                "--no-owner",  # Strip ownership
                "--no-privileges",  # Strip GRANTs
                "-N",
                "pg_catalog",  # Exclude system schemas
                "-N",
                "information_schema",
                "-N",
                "pg_toast",
                db,
            ]
            # Run pg_dump and append
            subprocess.run(cmd, stdout=out, check=True, env=env)
            out.write("\n\n")

    return output_file