"""Run the project's fixed aggregate-only feasibility query safely.

This runner accepts no arbitrary SQL. It executes the reviewed query inside the
same TLS-protected, read-only transaction used by the private exporters and
prints only its aggregate result table.
"""

from __future__ import annotations

import sys
from pathlib import Path

import psycopg2

import export_inspection_batch as secure_export


PROJECT_ROOT = Path(__file__).resolve().parents[1]
QUERY_PATH = PROJECT_ROOT / "sql/10_episode_cohort_feasibility.sql"


def reviewed_query() -> str:
    sql = QUERY_PATH.read_text(encoding="utf-8")
    begin = "BEGIN TRANSACTION READ ONLY;"
    rollback = "ROLLBACK;"
    if sql.count(begin) != 1 or sql.count(rollback) != 1:
        raise RuntimeError("The reviewed feasibility query wrappers changed")
    return sql.replace(begin, "", 1).rsplit(rollback, 1)[0].strip()


def main() -> int:
    try:
        connection = secure_export.connect_read_only()
    except (ValueError, RuntimeError, psycopg2.Error) as exc:
        print(
            f"Secure read-only connection failed: {type(exc).__name__}",
            file=sys.stderr,
        )
        return 1

    try:
        with connection.cursor() as cursor:
            cursor.execute(reviewed_query())
            columns = [description.name for description in cursor.description]
            rows = cursor.fetchall()
        connection.rollback()
    except psycopg2.Error as exc:
        connection.rollback()
        print(
            f"Aggregate feasibility query failed: "
            f"{exc.diag.message_primary or type(exc).__name__}",
            file=sys.stderr,
        )
        return 1
    finally:
        connection.close()

    widths = [len(column) for column in columns]
    rendered = [["" if value is None else str(value) for value in row] for row in rows]
    for row in rendered:
        widths = [max(width, len(value)) for width, value in zip(widths, row)]

    print("  ".join(value.ljust(width) for value, width in zip(columns, widths)))
    print("  ".join("-" * width for width in widths))
    for row in rendered:
        print("  ".join(value.ljust(width) for value, width in zip(row, widths)))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())