"""Initialise the Polleo Demand database from db/schema.sql.

Usage (with `docker compose up -d` already running). Run as a module
from the project root so the `db` package imports resolve:

    python -m db.init_db

Reads db/schema.sql, runs it as a single transaction, then prints a
summary of every table that now exists in the public schema. schema.sql
starts with DROP TABLE IF EXISTS ... CASCADE for every object, so this
script is idempotent — re-running drops and recreates the whole schema.

Exits non-zero on any error so it can be wired into CI / a Makefile.
"""
from __future__ import annotations

import sys
from pathlib import Path

from db.connection import get_connection, is_db_available

SCHEMA_PATH = Path(__file__).parent / "schema.sql"


def main() -> int:
    if not SCHEMA_PATH.exists():
        print(f"ERROR: schema file not found at {SCHEMA_PATH}", file=sys.stderr)
        return 2

    if not is_db_available():
        print(
            "ERROR: PostgreSQL is not reachable. Start it with:\n"
            "    docker compose up -d\n"
            "and verify the container is healthy with:\n"
            "    docker compose ps",
            file=sys.stderr,
        )
        return 1

    sql = SCHEMA_PATH.read_text(encoding="utf-8")
    print(f"Executing {SCHEMA_PATH} ({len(sql):,} bytes)...")

    conn = get_connection()
    conn.autocommit = False
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(f"ERROR while executing schema: {e}", file=sys.stderr)
        conn.close()
        return 1

    # Summary: list tables (and views) the schema produced.
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT table_name, table_type
            FROM information_schema.tables
            WHERE table_schema = 'public'
            ORDER BY table_type, table_name
            """
        )
        rows = cur.fetchall()
        cur.execute(
            """
            SELECT matviewname
            FROM pg_matviews
            WHERE schemaname = 'public'
            ORDER BY matviewname
            """
        )
        matviews = [r[0] for r in cur.fetchall()]
    conn.close()

    tables = [r[0] for r in rows if r[1] == "BASE TABLE"]
    views = [r[0] for r in rows if r[1] == "VIEW"]

    print()
    print(f"Tables ({len(tables)}):")
    for t in tables:
        print(f"  OK  {t}")
    print()
    print(f"Views ({len(views)}):")
    for v in views:
        print(f"  OK  {v}")
    if matviews:
        print()
        print(f"Materialized views ({len(matviews)}):")
        for v in matviews:
            print(f"  OK  {v}")

    print()
    print("Schema initialised successfully.")
    return 0


if __name__ == "__main__":
    sys.exit(main())