"""SQLite storage layer: stores ONLY ciphertext records.

Record layout (all client-provided fields, opaque to the server):
  id          INTEGER PRIMARY KEY            server surrogate key
  uuid        TEXT UNIQUE NOT NULL           client-generated record id
  ciphertext  BLOB NOT NULL                  XChaCha20-Poly1305 payload (base64 in API)
  nonce       BLOB NOT NULL                  random 24-byte XChaCha nonce (base64 in API)
  salt        BLOB NOT NULL                  per-record Argon2id salt (base64 in API)
  version     INTEGER NOT NULL               monotonic counter, bump on every update
  created_at  TEXT NOT NULL                  first seen server time (UTC ISO)
  updated_at  TEXT NOT NULL                  last update server time (UTC ISO)

The server never sees plaintext: no field contains decrypted data and
nothing is logged except the UUID/version bookkeeping.
"""

import sqlite3
from datetime import datetime, timezone

DB_PATH = "instance/crushdb.sqlite3"


def _connect():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db():
    """Create tables if they do not exist yet."""
    import os
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    with _connect() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS records (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                uuid       TEXT    NOT NULL UNIQUE,
                ciphertext BLOB    NOT NULL,
                nonce      BLOB    NOT NULL,
                salt       BLOB    NOT NULL,
                version    INTEGER NOT NULL DEFAULT 1,
                created_at TEXT    NOT NULL,
                updated_at TEXT    NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_records_uuid ON records(uuid);
            """
        )


def _now():
    return datetime.now(timezone.utc).isoformat()


def _row_to_dict(row):
    if row is None:
        return None
    return {
        "uuid": row["uuid"],
        "ciphertext": row["ciphertext"],
        "nonce": row["nonce"],
        "salt": row["salt"],
        "version": row["version"],
        "created_at": row["created_at"],
        "updated_at": row["updated_at"],
    }


def list_records():
    with _connect() as conn:
        rows = conn.execute(
            "SELECT * FROM records ORDER BY id ASC"
        ).fetchall()
    return [_row_to_dict(r) for r in rows]


def get_record(uuid):
    with _connect() as conn:
        row = conn.execute(
            "SELECT * FROM records WHERE uuid = ?", (uuid,)
        ).fetchone()
    return _row_to_dict(row)


def create_record(uuid, ciphertext, nonce, salt):
    """Insert a new encrypted record. Returns the stored dict with server metadata."""
    now = _now()
    with _connect() as conn:
        cur = conn.execute(
            """
            INSERT INTO records (uuid, ciphertext, nonce, salt, version, created_at, updated_at)
            VALUES (?, ?, ?, ?, 1, ?, ?)
            """,
            (uuid, ciphertext, nonce, salt, now, now),
        )
        row_id = cur.lastrowid
        row = conn.execute("SELECT * FROM records WHERE id = ?", (row_id,)).fetchone()
    return _row_to_dict(row)


def update_record(uuid, ciphertext, nonce, salt, expected_version):
    """Replace ciphertext only if the client's version matches the stored one.

    Returns the updated record dict, or None if:
      - no record with that uuid exists
      - expected_version does not match (someone else updated / replay attack)
    """
    with _connect() as conn:
        row = conn.execute(
            "SELECT version FROM records WHERE uuid = ?", (uuid,)
        ).fetchone()
        if row is None or row["version"] != expected_version:
            return None
        cur = conn.execute(
            """
            UPDATE records
            SET ciphertext = ?, nonce = ?, salt = ?, version = version + 1, updated_at = ?
            WHERE uuid = ? AND version = ?
            """,
            (ciphertext, nonce, salt, _now(), uuid, expected_version),
        )
        if cur.rowcount == 0:
            return None
        row = conn.execute("SELECT * FROM records WHERE uuid = ?", (uuid,)).fetchone()
    return _row_to_dict(row)


def delete_record(uuid, expected_version):
    """Delete a record only if the client's version matches. Returns True on success."""
    with _connect() as conn:
        cur = conn.execute(
            "DELETE FROM records WHERE uuid = ? AND version = ?",
            (uuid, expected_version),
        )
    return cur.rowcount > 0