"""
Cola local offline (SQLite) para ventas que no pudieron enviarse al servidor.

Cuando el servidor no responde, la pulsera se programa directamente via agente
y la venta queda encolada aqui. El sync_worker la envia en batch cuando el
servidor vuelve.

Schema de la tabla:
  session_id    TEXT PRIMARY KEY   (uuid4[:8] generado en el POS)
  package_id    TEXT
  package_name  TEXT
  payment_method TEXT
  amount        REAL
  cashier       TEXT
  minutes       INTEGER            (minutos totales del paquete)
  green_minutes INTEGER
  blue_minutes  INTEGER
  red_minutes   INTEGER
  programmed_at TEXT               (ISO datetime — cuando se programo el hardware)
  venue_id      TEXT
  queued_at     TEXT               (ISO datetime — cuando se encoló)
  synced        INTEGER DEFAULT 0  (0=pendiente, 1=ya enviado al servidor)
"""
import sqlite3
import os
from datetime import datetime

_DB_PATH = os.path.join(os.path.dirname(__file__), "data", "offline_queue.db")


def _connect() -> sqlite3.Connection:
    os.makedirs(os.path.dirname(_DB_PATH), exist_ok=True)
    conn = sqlite3.connect(_DB_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    return conn


def _ensure_table(conn: sqlite3.Connection):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS offline_sales (
            session_id     TEXT PRIMARY KEY,
            package_id     TEXT NOT NULL,
            package_name   TEXT NOT NULL,
            payment_method TEXT NOT NULL DEFAULT 'cash',
            amount         REAL NOT NULL DEFAULT 0,
            cashier        TEXT NOT NULL DEFAULT '',
            minutes        INTEGER NOT NULL DEFAULT 0,
            green_minutes  INTEGER NOT NULL DEFAULT 0,
            blue_minutes   INTEGER NOT NULL DEFAULT 0,
            red_minutes    INTEGER NOT NULL DEFAULT 0,
            programmed_at  TEXT NOT NULL,
            venue_id       TEXT NOT NULL DEFAULT '',
            queued_at      TEXT NOT NULL,
            synced         INTEGER NOT NULL DEFAULT 0
        )
    """)
    conn.commit()


# ---------------------------------------------------------------------------
# API publica
# ---------------------------------------------------------------------------

def enqueue(
    session_id: str,
    package_id: str,
    package_name: str,
    payment_method: str,
    amount: float,
    cashier: str,
    minutes: int,
    green_minutes: int,
    blue_minutes: int,
    red_minutes: int,
    programmed_at: str,
    venue_id: str = "",
):
    """Guarda una venta offline. Si el session_id ya existe, la ignora (idempotente)."""
    conn = _connect()
    try:
        _ensure_table(conn)
        conn.execute(
            """
            INSERT OR IGNORE INTO offline_sales
              (session_id, package_id, package_name, payment_method, amount,
               cashier, minutes, green_minutes, blue_minutes, red_minutes,
               programmed_at, venue_id, queued_at, synced)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,0)
            """,
            (
                session_id, package_id, package_name, payment_method, amount,
                cashier, minutes, green_minutes, blue_minutes, red_minutes,
                programmed_at, venue_id,
                datetime.now().isoformat(),
            ),
        )
        conn.commit()
    finally:
        conn.close()


def get_pending() -> list[dict]:
    """Retorna todas las ventas pendientes de sync (synced=0)."""
    conn = _connect()
    try:
        _ensure_table(conn)
        rows = conn.execute(
            "SELECT * FROM offline_sales WHERE synced = 0 ORDER BY queued_at"
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def mark_synced(session_ids: list[str]):
    """Marca las ventas como sincronizadas."""
    if not session_ids:
        return
    conn = _connect()
    try:
        _ensure_table(conn)
        placeholders = ",".join("?" * len(session_ids))
        conn.execute(
            f"UPDATE offline_sales SET synced=1 WHERE session_id IN ({placeholders})",
            session_ids,
        )
        conn.commit()
    finally:
        conn.close()


def count_pending() -> int:
    """Cantidad de ventas pendientes de sync."""
    conn = _connect()
    try:
        _ensure_table(conn)
        row = conn.execute(
            "SELECT COUNT(*) FROM offline_sales WHERE synced = 0"
        ).fetchone()
        return row[0] if row else 0
    finally:
        conn.close()
