"""
Postgres access layer for the MoonBloom analytics collector.

Reads DATABASE_URL from the environment (set via .env / docker-compose
env_file) and exposes a single idempotent upsert helper used by every
source module through collect.py.
"""

from __future__ import annotations

import os
from typing import Iterable, Sequence

import psycopg2
import psycopg2.extras


def get_connection():
    """Open a new connection using DATABASE_URL from the environment.

    Expected shape (see dashboard/.env.example):
      postgresql://dash:password@postgres:5432/moonbloom_dash
    """
    database_url = os.environ.get("DATABASE_URL")
    if not database_url:
        raise RuntimeError("DATABASE_URL is not set in the environment")
    return psycopg2.connect(database_url)


def upsert_rows(
    conn,
    table: str,
    rows: Sequence[dict],
    conflict_cols: Iterable[str],
) -> int:
    """Insert `rows` into `table`, updating on conflict.

    - `rows` is a list of dicts whose keys are column names matching the
      target table exactly (see dashboard/sql/schema.sql).
    - `conflict_cols` are the columns making up the table's PRIMARY KEY
      (used as the ON CONFLICT target).
    - All non-conflict columns present in the row dicts are updated on
      conflict (upsert semantics — safe to re-run the same window twice).
    - Fully parameterized (psycopg2.extras.execute_values) — no string
      formatting of values into SQL, so this is not vulnerable to
      injection even though table/column names are interpolated (those
      come only from our own trusted schema, never from row data).

    Returns the number of rows submitted (not necessarily the number of
    rows that changed, since some conflicts may update to identical
    values).
    """
    rows = list(rows)
    if not rows:
        return 0

    conflict_cols = list(conflict_cols)

    # Column order: keys of the first row dict. Every row must have the
    # exact same set of keys (callers build rows homogeneously per table).
    columns = list(rows[0].keys())
    missing = [set(columns) ^ set(r.keys()) for r in rows if set(r.keys()) != set(columns)]
    if missing:
        raise ValueError(
            f"upsert_rows: inconsistent row keys for table {table!r}; "
            f"expected columns {columns}, found mismatches: {missing[:3]}"
        )

    update_cols = [c for c in columns if c not in conflict_cols]

    col_ident = ", ".join(f'"{c}"' for c in columns)
    conflict_ident = ", ".join(f'"{c}"' for c in conflict_cols)

    if update_cols:
        set_clause = ", ".join(f'"{c}" = EXCLUDED."{c}"' for c in update_cols)
        on_conflict = f"ON CONFLICT ({conflict_ident}) DO UPDATE SET {set_clause}"
    else:
        # Table's only columns are the conflict/PK columns — nothing to
        # update, just skip duplicates.
        on_conflict = f"ON CONFLICT ({conflict_ident}) DO NOTHING"

    sql = f'INSERT INTO "{table}" ({col_ident}) VALUES %s {on_conflict}'

    template = "(" + ", ".join(f"%({c})s" for c in columns) + ")"

    with conn.cursor() as cur:
        psycopg2.extras.execute_values(cur, sql, rows, template=template, page_size=500)
    conn.commit()

    return len(rows)