"""Acesso aos bancos: mensagens.db (coletor) e agente.db (estado do agente)."""
import json
import sqlite3
from datetime import datetime

from .config import DB_AGENTE, DB_MENSAGENS, FUSO

ESQUEMA = """
CREATE TABLE IF NOT EXISTS itens (
  id INTEGER PRIMARY KEY,
  tipo TEXT NOT NULL,              -- compromisso | tarefa | projeto
  titulo TEXT NOT NULL,
  descricao TEXT,
  inicio TEXT,                     -- compromisso: 'AAAA-MM-DDTHH:MM' ou 'AAAA-MM-DD'
  fim TEXT,
  prazo TEXT,                      -- tarefa: 'AAAA-MM-DD' ou 'AAAA-MM-DDTHH:MM'
  status TEXT NOT NULL,            -- aberto | a_confirmar | concluido | descartado
  confianca REAL,
  projeto TEXT,
  pessoas TEXT,                    -- JSON
  origem TEXT NOT NULL,            -- whatsapp | classroom | notion | manual
  origem_ref TEXT,                 -- chave estavel na origem (evita duplicar)
  evidencia TEXT,                  -- trecho/fonte que justifica o item
  evento_id TEXT,                  -- id do evento na agenda Agente
  msg_ts INTEGER,                  -- quando foi a mensagem que originou o item (WhatsApp)
  criado_em TEXT NOT NULL,
  atualizado_em TEXT NOT NULL,
  UNIQUE (origem, origem_ref)
);
CREATE TABLE IF NOT EXISTS midia_texto (
  chat TEXT, id TEXT, texto TEXT, PRIMARY KEY (chat, id)
);
CREATE TABLE IF NOT EXISTS progresso (chave TEXT PRIMARY KEY, valor TEXT);
CREATE TABLE IF NOT EXISTS notion (
  pagina_id TEXT PRIMARY KEY, titulo TEXT, conteudo TEXT, editado_em TEXT, lido_em TEXT
);
CREATE TABLE IF NOT EXISTS uso_llm (
  ts TEXT, etapa TEXT, modelo TEXT, entrada INTEGER, saida INTEGER
);
"""


def agora() -> str:
    return datetime.now(FUSO).isoformat(timespec="seconds")


def agente() -> sqlite3.Connection:
    con = sqlite3.connect(DB_AGENTE, timeout=30)
    con.row_factory = sqlite3.Row
    con.executescript(ESQUEMA)
    if "msg_ts" not in {r["name"] for r in con.execute("PRAGMA table_info(itens)")}:
        con.execute("ALTER TABLE itens ADD COLUMN msg_ts INTEGER")
    return con


def mensagens() -> sqlite3.Connection:
    con = sqlite3.connect(DB_MENSAGENS, timeout=30)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA busy_timeout = 30000")
    return con


def progresso(con, chave, padrao=None):
    r = con.execute("SELECT valor FROM progresso WHERE chave = ?", (chave,)).fetchone()
    return json.loads(r["valor"]) if r else padrao


def salvar_progresso(con, chave, valor):
    con.execute("INSERT OR REPLACE INTO progresso VALUES (?, ?)", (chave, json.dumps(valor)))
    con.commit()


def itens_abertos(con, tipos=("compromisso", "tarefa", "projeto")):
    q = f"SELECT * FROM itens WHERE status IN ('aberto','a_confirmar') AND tipo IN ({','.join('?' * len(tipos))})"
    return con.execute(q, tipos).fetchall()


def gravar_item(con, item: dict) -> int:
    """Insere ou atualiza um item pela chave (origem, origem_ref). Devolve o id."""
    agora_ = agora()
    campos = ["tipo", "titulo", "descricao", "inicio", "fim", "prazo", "status", "confianca",
              "projeto", "pessoas", "origem", "origem_ref", "evidencia", "msg_ts"]
    valores = {c: item.get(c) for c in campos}
    if isinstance(valores["pessoas"], list):
        valores["pessoas"] = json.dumps(valores["pessoas"], ensure_ascii=False)
    existente = None
    if valores["origem_ref"]:
        existente = con.execute("SELECT id, status FROM itens WHERE origem = ? AND origem_ref = ?",
                                (valores["origem"], valores["origem_ref"])).fetchone()
    if existente:
        if existente["status"] in ("concluido", "descartado") and valores["status"] in ("aberto", "a_confirmar"):
            valores["status"] = existente["status"]  # nao ressuscita o que o usuario fechou
        sets = ", ".join(f"{c} = ?" for c in campos)
        con.execute(f"UPDATE itens SET {sets}, atualizado_em = ? WHERE id = ?",
                    [valores[c] for c in campos] + [agora_, existente["id"]])
        con.commit()
        return existente["id"]
    cur = con.execute(
        f"INSERT INTO itens ({', '.join(campos)}, criado_em, atualizado_em) VALUES ({', '.join('?' * len(campos))}, ?, ?)",
        [valores[c] for c in campos] + [agora_, agora_])
    con.commit()
    return cur.lastrowid


def atualizar_item(con, item_id: int, **campos):
    if not campos:
        return
    if isinstance(campos.get("pessoas"), list):
        campos["pessoas"] = json.dumps(campos["pessoas"], ensure_ascii=False)
    sets = ", ".join(f"{c} = ?" for c in campos)
    con.execute(f"UPDATE itens SET {sets}, atualizado_em = ? WHERE id = ?", [*campos.values(), agora(), item_id])
    con.commit()