# -*- coding: utf-8 -*-
"""
Strat de baza de date (SQLite), parametrizat pe CHESTIONAR.

Fiecare chestionar (din `forms.py`) are tabelul lui propriu; schema fiecaruia
se genereaza din definitia lui de intrebari. Toate functiile primesc `form`
(intrarea din registru) si folosesc `form["table"]` + `form["questions"].PAGES`.

WAL + busy_timeout fac DB rezistenta la accese simultane (anti-crash).
"""
import os
import re
import sqlite3
from datetime import datetime, timezone, timedelta

import forms

DB_PATH = os.environ.get("CHESTIONAR_DB", "chestionar.db")


# ---- timestamp in format JavaScript Date.toString() + fus orar Romania ----
_WD = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]
_MO = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
       "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]


def _last_sunday(year, month):
    d = datetime(year, month, 28)
    while (d + timedelta(days=1)).month == month:
        d += timedelta(days=1)
    while d.weekday() != 6:
        d -= timedelta(days=1)
    return d


def _ro_is_dst(dt_utc):
    y = dt_utc.year
    start = _last_sunday(y, 3).replace(hour=1, tzinfo=timezone.utc)
    end = _last_sunday(y, 10).replace(hour=1, tzinfo=timezone.utc)
    return start <= dt_utc < end


def ro_timestamp(dt_utc=None):
    """Ex: 'Wed Feb 18 2026 15:34:34 GMT+0200 (Eastern European Standard Time)'."""
    if dt_utc is None:
        dt_utc = datetime.now(timezone.utc)
    dst = _ro_is_dst(dt_utc)
    off = 3 if dst else 2
    local = dt_utc + timedelta(hours=off)
    name = "Eastern European Summer Time" if dst else "Eastern European Standard Time"
    wd = _WD[(local.weekday() + 1) % 7]
    return "%s %s %02d %d %02d:%02d:%02d GMT+0%d00 (%s)" % (
        wd, _MO[local.month - 1], local.day, local.year,
        local.hour, local.minute, local.second, off, name)


_TS_RE = re.compile(
    r"\w{3}\s+(\w{3})\s+(\d{1,2})\s+(\d{4})\s+(\d{2}):(\d{2}):(\d{2})\s+GMT([+-]\d{2})(\d{2})"
)


def parse_ro_timestamp(s):
    """Invers pentru ro_timestamp: string JS -> epoch (float). Fallback = acum."""
    if s:
        m = _TS_RE.search(s)
        if m:
            try:
                mon = _MO.index(m.group(1)) + 1
                day, year = int(m.group(2)), int(m.group(3))
                hh, mm, ss = int(m.group(4)), int(m.group(5)), int(m.group(6))
                off_h, off_m = int(m.group(7)), int(m.group(8))
                tz = timezone(timedelta(hours=off_h, minutes=off_m if off_h >= 0 else -off_m))
                return datetime(year, mon, day, hh, mm, ss, tzinfo=tz).timestamp()
            except (ValueError, IndexError):
                pass
    return datetime.now(timezone.utc).timestamp()


# ------------------------------------------------------------ definitie campuri
def iter_questions(form):
    for page in form["questions"].PAGES:
        for q in page["questions"]:
            yield q


def iter_fields(form):
    """(slug, eticheta, tip) pentru fiecare coloana de date, in ordinea formularului."""
    for q in iter_questions(form):
        if q["type"] == "grid":
            for row in q["rows"]:
                yield row["slug"], "%s [%s]" % (q["label"], row["label"]), "grid"
        else:
            yield q["slug"], q["label"], q["type"]


def columns(form):
    """Coloane de export/admin: (nume, eticheta). Prima = Marcaj de timp; fara ID/meta."""
    cols = [("created_at", "Marcaj de timp")]
    cols += [(slug, label) for slug, label, _ in iter_fields(form)]
    return cols


def _data_slugs(form):
    return [slug for slug, _, _ in iter_fields(form)]


# ------------------------------------------------------------------- conexiune
def get_conn():
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA busy_timeout=30000")
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    return conn


def _create_table(conn, form):
    cols_sql = ",\n    ".join('"%s" TEXT' % s for s in _data_slugs(form))
    conn.execute(
        'CREATE TABLE IF NOT EXISTS "%s" (\n' % form["table"]
        + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
        + "    created_at TEXT NOT NULL,\n"
        + "    created_ts REAL,\n"
        + "    struck INTEGER DEFAULT 0,\n"
        + "    " + cols_sql + "\n);"
    )
    existing = {r["name"] for r in conn.execute('PRAGMA table_info("%s")' % form["table"])}
    if "created_ts" not in existing:
        conn.execute('ALTER TABLE "%s" ADD COLUMN created_ts REAL' % form["table"])
    if "struck" not in existing:
        conn.execute('ALTER TABLE "%s" ADD COLUMN struck INTEGER DEFAULT 0' % form["table"])
    for s in _data_slugs(form):
        if s not in existing:
            conn.execute('ALTER TABLE "%s" ADD COLUMN "%s" TEXT' % (form["table"], s))
    for r in conn.execute('SELECT id, created_at FROM "%s" WHERE created_ts IS NULL' % form["table"]):
        conn.execute('UPDATE "%s" SET created_ts=? WHERE id=?' % form["table"],
                     (parse_ro_timestamp(r["created_at"]), r["id"]))


def init_db():
    """Creeaza/actualizeaza tabelele pentru TOATE chestionarele."""
    with get_conn() as conn:
        for form in forms.all_forms():
            _create_table(conn, form)


# ------------------------------------------------------------------- operatii
def _order():
    return "ORDER BY created_ts DESC, id DESC"


def insert_submission(form, values, conn=None):
    slugs = _data_slugs(form)
    data = {s: values.get(s, "") for s in slugs}
    data["created_at"] = values.get("created_at") or ro_timestamp()
    data["created_ts"] = parse_ro_timestamp(data["created_at"])
    cols = list(data.keys())
    placeholders = ", ".join("?" for _ in cols)
    col_list = ", ".join('"%s"' % c for c in cols)
    sql = 'INSERT INTO "%s" (%s) VALUES (%s)' % (form["table"], col_list, placeholders)
    params = [data[c] for c in cols]
    if conn is not None:
        return conn.execute(sql, params).lastrowid
    with get_conn() as c:
        return c.execute(sql, params).lastrowid


def import_rows(form, rows):
    n = 0
    with get_conn() as conn:
        for r in rows:
            insert_submission(form, r, conn=conn)
            n += 1
    return n


def fetch_all(form):
    with get_conn() as conn:
        rows = conn.execute('SELECT * FROM "%s" %s' % (form["table"], _order())).fetchall()
    return [dict(r) for r in rows]


def get_submission(form, sub_id):
    with get_conn() as conn:
        row = conn.execute('SELECT * FROM "%s" WHERE id=?' % form["table"], (sub_id,)).fetchone()
    return dict(row) if row else None


def update_submission(form, sub_id, values):
    allowed = set(_data_slugs(form)) | {"created_at"}
    items = [(k, v) for k, v in values.items() if k in allowed]
    if not items:
        return 0
    if any(k == "created_at" for k, _ in items):
        items.append(("created_ts", parse_ro_timestamp(dict(items)["created_at"])))
    set_sql = ", ".join('"%s"=?' % k for k, _ in items)
    params = [v for _, v in items] + [sub_id]
    with get_conn() as conn:
        return conn.execute('UPDATE "%s" SET %s WHERE id=?' % (form["table"], set_sql), params).rowcount


def delete_submission(form, sub_id):
    with get_conn() as conn:
        return conn.execute('DELETE FROM "%s" WHERE id=?' % form["table"], (sub_id,)).rowcount


def duplicate_submission(form, sub_id):
    src = get_submission(form, sub_id)
    if not src:
        return None
    vals = {s: src.get(s, "") for s in _data_slugs(form)}
    return insert_submission(form, vals)


def insert_blank(form):
    return insert_submission(form, {})


def toggle_struck(form, sub_id):
    with get_conn() as conn:
        cur = conn.execute(
            'UPDATE "%s" SET struck = CASE struck WHEN 1 THEN 0 ELSE 1 END WHERE id=?' % form["table"],
            (sub_id,))
        if not cur.rowcount:
            return None
        return conn.execute('SELECT struck FROM "%s" WHERE id=?' % form["table"], (sub_id,)).fetchone()["struck"]


def _filter_clause(form, q, col):
    if not q:
        return "", []
    like = "%" + q + "%"
    data_cols = _data_slugs(form) + ["created_at"]
    if col and col in data_cols:
        return 'WHERE "%s" LIKE ?' % col, [like]
    ors = " OR ".join('"%s" LIKE ?' % c for c in data_cols)
    return "WHERE (%s)" % ors, [like] * len(data_cols)


def count_filtered(form, q="", col=""):
    where, params = _filter_clause(form, q, col)
    with get_conn() as conn:
        return conn.execute('SELECT COUNT(*) n FROM "%s" %s' % (form["table"], where), params).fetchone()["n"]


def fetch_page(form, q="", col="", page=1, per_page=100):
    where, params = _filter_clause(form, q, col)
    offset = (max(page, 1) - 1) * per_page
    sql = 'SELECT * FROM "%s" %s %s LIMIT ? OFFSET ?' % (form["table"], where, _order())
    with get_conn() as conn:
        rows = conn.execute(sql, params + [per_page, offset]).fetchall()
    return [dict(r) for r in rows]


def count(form):
    with get_conn() as conn:
        return conn.execute('SELECT COUNT(*) AS n FROM "%s"' % form["table"]).fetchone()["n"]


if __name__ == "__main__":
    init_db()
    print("Baza de date initializata:", os.path.abspath(DB_PATH))
    for f in forms.all_forms():
        print("  %-12s tabel=%s coloane=%d" % (f["id"], f["table"], len(_data_slugs(f))))