"""Локальное хранилище на SQLite: токены порталов, курсоры поллинга,
очередь согласования и состояние редактирования.

ВНИМАНИЕ: файл store.sqlite содержит OAuth-токены доступа к твоим порталам.
Ограничь к нему доступ средствами ОС (права 600, отдельный пользователь и т.п.).
"""
from __future__ import annotations

import os
import sqlite3
import threading
import time
import uuid
from dataclasses import dataclass
from typing import Any

_DB_PATH = os.getenv("DB_PATH", "store.sqlite")
_lock = threading.Lock()


def _conn() -> sqlite3.Connection:
    c = sqlite3.connect(_DB_PATH)
    c.row_factory = sqlite3.Row
    return c


def init_db() -> None:
    with _lock, _conn() as c:
        c.executescript(
            """
            CREATE TABLE IF NOT EXISTS portals (
                domain         TEXT PRIMARY KEY,
                access_token   TEXT,
                refresh_token  TEXT,
                expires_at     INTEGER,
                member_id      TEXT,
                operator_id    TEXT,   -- user_id оператора (тебя) на портале
                bot_id         TEXT,   -- id зарегистрированного чат-бота
                app_token      TEXT    -- application_token для проверки событий
            );
            CREATE TABLE IF NOT EXISTS cursors (
                domain      TEXT,
                dialog_id   TEXT,
                last_msg_id INTEGER,
                PRIMARY KEY (domain, dialog_id)
            );
            CREATE TABLE IF NOT EXISTS pending (
                id          TEXT PRIMARY KEY,
                domain      TEXT,
                dialog_id   TEXT,        -- исходный чат, куда уйдёт ответ
                incoming    TEXT,        -- текст входящего (для показа)
                draft       TEXT,        -- текущий черновик
                status      TEXT,        -- new | sent | skipped
                created_at  INTEGER
            );
            CREATE TABLE IF NOT EXISTS edit_state (
                domain      TEXT,
                operator_id TEXT,
                pending_id  TEXT,
                PRIMARY KEY (domain, operator_id)
            );
            """
        )


# ---------- порталы / токены ----------

def upsert_portal_tokens(domain: str, access: str, refresh: str,
                         expires_in: int, member_id: str | None,
                         app_token: str | None) -> None:
    expires_at = int(time.time()) + int(expires_in) - 60
    with _lock, _conn() as c:
        c.execute(
            """INSERT INTO portals (domain, access_token, refresh_token, expires_at, member_id, app_token)
               VALUES (?,?,?,?,?,?)
               ON CONFLICT(domain) DO UPDATE SET
                 access_token=excluded.access_token,
                 refresh_token=excluded.refresh_token,
                 expires_at=excluded.expires_at,
                 member_id=COALESCE(excluded.member_id, portals.member_id),
                 app_token=COALESCE(excluded.app_token, portals.app_token)""",
            (domain, access, refresh, expires_at, member_id, app_token),
        )


def set_portal_field(domain: str, field: str, value: Any) -> None:
    assert field in {"operator_id", "bot_id", "app_token"}
    with _lock, _conn() as c:
        c.execute(f"UPDATE portals SET {field}=? WHERE domain=?", (value, domain))


def get_portal(domain: str) -> dict | None:
    with _lock, _conn() as c:
        row = c.execute("SELECT * FROM portals WHERE domain=?", (domain,)).fetchone()
        return dict(row) if row else None


def all_portal_domains() -> list[str]:
    with _lock, _conn() as c:
        return [r["domain"] for r in c.execute("SELECT domain FROM portals").fetchall()]


def find_domain_by_member(member_id: str) -> str | None:
    with _lock, _conn() as c:
        row = c.execute("SELECT domain FROM portals WHERE member_id=?", (member_id,)).fetchone()
        return row["domain"] if row else None


# ---------- курсоры поллинга ----------

def get_cursor(domain: str, dialog_id: str) -> int:
    with _lock, _conn() as c:
        row = c.execute(
            "SELECT last_msg_id FROM cursors WHERE domain=? AND dialog_id=?",
            (domain, dialog_id),
        ).fetchone()
        return int(row["last_msg_id"]) if row else 0


def set_cursor(domain: str, dialog_id: str, last_msg_id: int) -> None:
    with _lock, _conn() as c:
        c.execute(
            """INSERT INTO cursors (domain, dialog_id, last_msg_id) VALUES (?,?,?)
               ON CONFLICT(domain, dialog_id) DO UPDATE SET last_msg_id=excluded.last_msg_id""",
            (domain, dialog_id, int(last_msg_id)),
        )


# ---------- очередь согласования ----------

@dataclass
class Pending:
    id: str
    domain: str
    dialog_id: str
    incoming: str
    draft: str
    status: str
    created_at: int


def create_pending(domain: str, dialog_id: str, incoming: str, draft: str) -> Pending:
    p = Pending(
        id=uuid.uuid4().hex[:12],
        domain=domain,
        dialog_id=dialog_id,
        incoming=incoming,
        draft=draft,
        status="new",
        created_at=int(time.time()),
    )
    with _lock, _conn() as c:
        c.execute(
            "INSERT INTO pending (id, domain, dialog_id, incoming, draft, status, created_at) VALUES (?,?,?,?,?,?,?)",
            (p.id, p.domain, p.dialog_id, p.incoming, p.draft, p.status, p.created_at),
        )
    return p


def get_pending(pending_id: str) -> Pending | None:
    with _lock, _conn() as c:
        row = c.execute("SELECT * FROM pending WHERE id=?", (pending_id,)).fetchone()
        return Pending(**dict(row)) if row else None


def update_pending(pending_id: str, *, draft: str | None = None, status: str | None = None) -> None:
    with _lock, _conn() as c:
        if draft is not None:
            c.execute("UPDATE pending SET draft=? WHERE id=?", (draft, pending_id))
        if status is not None:
            c.execute("UPDATE pending SET status=? WHERE id=?", (status, pending_id))


# ---------- состояние редактирования ----------

def set_edit_state(domain: str, operator_id: str, pending_id: str) -> None:
    with _lock, _conn() as c:
        c.execute(
            """INSERT INTO edit_state (domain, operator_id, pending_id) VALUES (?,?,?)
               ON CONFLICT(domain, operator_id) DO UPDATE SET pending_id=excluded.pending_id""",
            (domain, operator_id, pending_id),
        )


def pop_edit_state(domain: str, operator_id: str) -> str | None:
    with _lock, _conn() as c:
        row = c.execute(
            "SELECT pending_id FROM edit_state WHERE domain=? AND operator_id=?",
            (domain, operator_id),
        ).fetchone()
        if not row:
            return None
        c.execute("DELETE FROM edit_state WHERE domain=? AND operator_id=?", (domain, operator_id))
        return row["pending_id"]