"""The only module that opens the database (PostgreSQL) and applies schema migrations.

SQL in this code base uses `?` placeholders (DB-API qmark style); `_translate` turns each statement into
psycopg's format once. Rows behave like sqlite3.Row (by name, by index, dict(row)), an INSERT reports
`lastrowid`, and ints are sent untyped so Postgres reads them as the column needs. Two roles: the app's role
reads and writes data only; the owner role applies migrations — `python -m app.db migrate` — and takes
snapshots before risky writes. With a single role, DATABASE_URL serves both.
"""
import re
import sys
from datetime import date, datetime
from decimal import Decimal
from functools import lru_cache
from pathlib import Path

import psycopg
from psycopg.adapt import Dumper, Loader
from psycopg.conninfo import conninfo_to_dict
from psycopg.sql import Identifier

from app import config

SYSTEM_DIR = Path(__file__).resolve().parent.parent
MIGRATIONS_DIR = SYSTEM_DIR / "migrations"
URL = config.get("DATABASE_URL")                       # the app's role: data only
OWNER_URL = config.get("DATABASE_OWNER_URL") or URL     # migrations, snapshots
SCHEMA = "public"                                # the tests give every test a schema of its own
TIMEZONE = "Europe/Vienna"
APP_ROLE = conninfo_to_dict(URL)["user"] if URL else None
DATA_TABLES = ("unit", "room", "company", "client", "company_key", "dismissed_finding", "guest", "invoice",
               "stay", "charge", "payment")
TABLES_WITH_ID = {"unit", "room", "company", "client", "guest", "invoice", "stay", "charge", "payment"}
IntegrityError = psycopg.IntegrityError
INSERT = re.compile(r"\s*INSERT\s+INTO\s+(\w+)", re.I)


class Row:
    """A result row like sqlite3.Row: row["name"] (any case), row[0], dict(row), iteration over the values."""
    __slots__ = ("_index", "_values")

    def __init__(self, index, values):
        self._index, self._values = index, values

    def keys(self):
        return [k for k in self._index if not k.startswith("\0")]

    def __getitem__(self, key):
        if isinstance(key, (int, slice)):
            return self._values[key]
        try:
            return self._values[self._index[key]]
        except KeyError:
            return self._values[self._index["\0" + key.lower()]]

    def __iter__(self):
        return iter(self._values)

    def __len__(self):
        return len(self._values)

    def __eq__(self, other):
        return isinstance(other, Row) and self.keys() == other.keys() and self._values == other._values

    def __hash__(self):
        return hash(tuple(self._values))

    def __repr__(self):
        return f"Row({dict(self)!r})"


def _row_factory(cursor):
    names = [c.name for c in cursor.description or ()]
    index = {n: i for i, n in enumerate(names)}
    index.update({"\0" + n.lower(): i for i, n in enumerate(names)})    # case-insensitive, like sqlite3.Row
    return lambda values: Row(index, values)


class _Untyped(Dumper):
    """Python ints and bools go to the server as untyped literals (bools as 1/0, the integer flag columns):
    Postgres then reads `'22'` as the text room number or the integer id, whichever the context needs."""
    oid = 0

    def dump(self, obj):
        return str(int(obj)).encode()


class _IsoText(Dumper):
    """Dates go as untyped ISO text: the date columns are text, and Postgres has no `text >= date`
    operator."""
    oid = 0

    def dump(self, obj):
        return (obj.isoformat(" ") if isinstance(obj, datetime) else obj.isoformat()).encode()


class _Numeric(Loader):
    """numeric -> int when integral, else float (charge.persons: 2, or 1.5)."""

    def load(self, data):
        d = Decimal(bytes(data).decode())
        return int(d) if d == d.to_integral_value() else float(d)


@lru_cache(maxsize=None)
def _translate(sql):
    """`?` -> `%s` and `%` -> `%%`. psycopg reads `%` everywhere — inside string literals and comments too —
    so every `%` is doubled; a `?` inside a literal or comment stays a question mark."""
    out, i, n = [], 0, len(sql)
    while i < n:
        ch = sql[i]
        if ch in "'\"":
            end = sql.find(ch, i + 1)
            end = n - 1 if end < 0 else end
            out.append(sql[i:end + 1].replace("%", "%%"))
            i = end + 1
            continue
        if sql.startswith("--", i):
            end = sql.find("\n", i)
            end = n if end < 0 else end
            out.append(sql[i:end].replace("%", "%%"))
            i = end
            continue
        out.append("%s" if ch == "?" else "%%" if ch == "%" else ch)
        i += 1
    return "".join(out)


class Cursor:
    """A cursor as sqlite3 offers one: fetchone/fetchall/iteration, lastrowid after an INSERT, rowcount."""

    def __init__(self, cur, returning):
        self._cur, self.lastrowid, self.rowcount = cur, None, cur.rowcount
        self._done = cur.description is None
        if returning:
            ids = cur.fetchall()
            self.lastrowid = ids[-1][0] if ids else None
            self._done = True

    def fetchone(self):
        return None if self._done else self._cur.fetchone()

    def fetchall(self):
        return [] if self._done else self._cur.fetchall()

    def __iter__(self):
        return iter(self.fetchall())


class Connection:
    """A psycopg connection with the sqlite3 interface this code is written against. `with conn:` commits
    or rolls back and keeps the connection open, as sqlite3 does; `close()` closes it."""

    def __init__(self, raw):
        self.raw = raw

    def execute(self, sql, params=()):
        m = INSERT.match(sql)
        returning = bool(m and m.group(1).lower() in TABLES_WITH_ID and not re.search(r"\bRETURNING\b", sql, re.I))
        if returning:
            sql = sql.rstrip().rstrip(";") + " RETURNING id"
        return Cursor(self.raw.execute(_translate(sql), tuple(params)), returning)

    def executescript(self, sql):
        self.raw.execute(sql)            # no parameters: Postgres takes several statements at once

    def commit(self):
        self.raw.commit()

    def rollback(self):
        self.raw.rollback()

    def close(self):
        self.raw.close()

    def __enter__(self):
        return self

    def __exit__(self, kind, exc, tb):
        self.raw.rollback() if kind else self.raw.commit()


def connect(owner=False, actor="cli"):
    """A connection as the app's role — or as the owner, for migrations, snapshots and maintenance. `actor`
    names whoever writes through it in the audit log (002): `web`, `cli`, ..."""
    url = OWNER_URL if owner else URL
    if not url:
        raise RuntimeError("no database URL: set DATABASE_URL (environment or .env)")
    actor = actor.replace("\\", "\\\\").replace(" ", "\\ ")      # libpq options split at unescaped spaces
    raw = psycopg.connect(url, options=f"-c search_path={SCHEMA} -c TimeZone={TIMEZONE} -c app.actor={actor}",
                          application_name="vermietungs-tool", row_factory=_row_factory)
    raw.adapters.register_dumper(int, _Untyped)
    raw.adapters.register_dumper(bool, _Untyped)
    raw.adapters.register_dumper(date, _IsoText)
    raw.adapters.register_dumper(datetime, _IsoText)
    raw.adapters.register_loader("numeric", _Numeric)
    return Connection(raw)


def _migrations():
    return {int(p.name[:3]): p for p in sorted(MIGRATIONS_DIR.glob("[0-9][0-9][0-9]_*.sql"))}


def pending_migrations(conn):
    exists = conn.execute("SELECT to_regclass('schema_version') IS NOT NULL").fetchone()[0]
    applied = {r[0] for r in conn.execute("SELECT version FROM schema_version")} if exists else set()
    return sorted(v for v in _migrations() if v not in applied)


def migrate(conn):
    """Apply every migration not yet recorded in schema_version, each in its own transaction (DDL is
    transactional in Postgres), then grant the app's role its data rights. Needs the owner role."""
    conn.execute("CREATE TABLE IF NOT EXISTS schema_version (version integer PRIMARY KEY, applied_at text "
                 "COLLATE \"C\" DEFAULT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'))")
    conn.commit()
    for version in pending_migrations(conn):
        conn.executescript(_migrations()[version].read_text(encoding="utf-8"))
        conn.execute("INSERT INTO schema_version (version) VALUES (?)", (version,))
        conn.commit()
    _grant(conn)


def _grant(conn):
    """The app's role may read and write the data tables — nothing else: no DDL, no TRUNCATE, and it cannot
    rewrite the schema history or the audit log (its trigger writes as the owner)."""
    me = conn.execute("SELECT current_user").fetchone()[0]
    if not APP_ROLE or APP_ROLE == me:
        return
    schema, role = Identifier(SCHEMA).as_string(conn.raw), Identifier(APP_ROLE).as_string(conn.raw)
    conn.executescript(f"GRANT USAGE ON SCHEMA {schema} TO {role};"
                       f"GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA {schema} TO {role};"
                       f"GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA {schema} TO {role};"
                       f"REVOKE INSERT, UPDATE, DELETE ON {schema}.schema_version, {schema}.audit_log FROM {role};"
                       f"REVOKE ALL ON SEQUENCE {schema}.audit_log_id_seq FROM {role};")
    conn.commit()


def snapshot(conn, label):
    """Copy every data table into a new schema `snap_<YYYYMMDD_HHMMSS>_<label>` — the backup before a risky
    write outside the app. Owner role. Returns the schema name."""
    name = f"snap_{datetime.now():%Y%m%d_%H%M%S}_{re.sub(r'[^a-z0-9]+', '_', label.lower()).strip('_')}"[:63]
    conn.execute(f'CREATE SCHEMA "{name}"')
    for table in DATA_TABLES:
        conn.execute(f'CREATE TABLE "{name}"."{table}" AS TABLE "{SCHEMA}"."{table}"')
    conn.commit()
    return name


def rows(conn, sql, params=()):
    return conn.execute(sql, params).fetchall()


def row(conn, sql, params=()):
    return conn.execute(sql, params).fetchone()


def execute(conn, sql, params=()):
    cur = conn.execute(sql, params)
    conn.commit()
    return cur.lastrowid


def main():
    """python -m app.db migrate | status | snapshot <label>"""
    command = sys.argv[1] if len(sys.argv) > 1 else "status"
    with connect(owner=True) as conn:
        if command == "migrate":
            migrate(conn)
        elif command == "snapshot":
            print(snapshot(conn, sys.argv[2] if len(sys.argv) > 2 else "manual"))
            return
        print("pending migrations:", pending_migrations(conn) or "none")
        print("snapshots:", [r[0] for r in conn.execute(
            "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'snap\\_%' ORDER BY 1")])


if __name__ == "__main__":
    main()