"""Estado do Publicador em data/publisher/publisher.db (SQLite): perfis conectados, jobs de envio e os `state` pendentes do OAuth. Segredos (tokens, client secret) NÃO ficam aqui: vão para o cofre (vault.py).""" from __future__ import annotations import json import sqlite3 import threading from datetime import datetime, timezone from pathlib import Path from typing import Any # Cada item é uma migração; `PRAGMA user_version` guarda quantas já rodaram. SCHEMA = [ """ CREATE TABLE profiles ( profile_id TEXT PRIMARY KEY, platform TEXT NOT NULL CHECK (platform IN ('youtube', 'tiktok', 'instagram')), mode TEXT CHECK (mode IN ('official', 'session')), status TEXT NOT NULL DEFAULT 'disconnected' CHECK (status IN ('disconnected', 'connecting', 'connected', 'needs_login', 'expired', 'error')), display_name TEXT, handle TEXT, connected_at TEXT, expires_at TEXT, last_check_at TEXT, last_error TEXT, meta_json TEXT NOT NULL DEFAULT '{}', updated_at TEXT NOT NULL ); CREATE TABLE jobs ( job_id TEXT PRIMARY KEY, profile_id TEXT NOT NULL, platform TEXT NOT NULL, input_json TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('queued', 'running', 'done', 'failed', 'needs_login', 'canceled')), step TEXT NOT NULL DEFAULT '', progress REAL NOT NULL DEFAULT 0, remote_id TEXT, url TEXT, draft INTEGER NOT NULL DEFAULT 0, error TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX jobs_status_created ON jobs (status, created_at); CREATE TABLE oauth_states ( state TEXT PRIMARY KEY, platform TEXT NOT NULL, profile_id TEXT NOT NULL, verifier TEXT NOT NULL, created_at REAL NOT NULL, expires_at REAL NOT NULL, used INTEGER NOT NULL DEFAULT 0 ); """, # v2: idempotência do POST /v1/jobs (clientRef do painel) e o code_verifier do PKCE sai do disco (fica só em # memória; o state vale 10 min). """ ALTER TABLE jobs ADD COLUMN client_ref TEXT; CREATE UNIQUE INDEX jobs_client_ref ON jobs (client_ref); UPDATE oauth_states SET verifier = ''; """, ] INTERRUPTED = "Envio interrompido (o Publicador foi fechado no meio) — tente de novo." def now() -> str: return datetime.now(timezone.utc).isoformat(timespec="seconds").replace("+00:00", "Z") class Database: def __init__(self, path: Path): path.parent.mkdir(parents=True, exist_ok=True) self.lock = threading.RLock() self.conn = sqlite3.connect(path, check_same_thread=False, isolation_level=None) self.conn.row_factory = sqlite3.Row self.conn.executescript("PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 3000;") self._migrate() # Job que estava rodando quando o serviço caiu: o envio morreu junto. Não dá para saber se chegou à rede, # então vira 'failed' e o usuário decide se tenta de novo (o painel mostra o botão). self.interrupted_on_start = [r["job_id"] for r in self.all("SELECT job_id FROM jobs WHERE status = 'running'")] self.run("UPDATE jobs SET status = 'failed', error = ?, step = 'Interrompido', updated_at = ? WHERE status = 'running'", INTERRUPTED, now()) def _migrate(self) -> None: version = self.conn.execute("PRAGMA user_version").fetchone()[0] for v in range(version, len(SCHEMA)): with self.lock: self.conn.execute("BEGIN") try: for statement in _split(SCHEMA[v]): self.conn.execute(statement) self.conn.execute(f"PRAGMA user_version = {v + 1}") self.conn.execute("COMMIT") except Exception: self.conn.execute("ROLLBACK") raise def run(self, sql: str, *params: Any) -> sqlite3.Cursor: with self.lock: return self.conn.execute(sql, params) def all(self, sql: str, *params: Any) -> list[dict]: with self.lock: return [dict(r) for r in self.conn.execute(sql, params).fetchall()] def get(self, sql: str, *params: Any) -> dict | None: with self.lock: row = self.conn.execute(sql, params).fetchone() return dict(row) if row else None def close(self) -> None: with self.lock: self.conn.close() def _split(script: str) -> list[str]: # executescript faria COMMIT implícito no meio da transação; executamos comando por comando. return [s.strip() for s in script.split(";") if s.strip()] def loads(raw: str | None, default: Any) -> Any: try: return json.loads(raw) if raw else default except json.JSONDecodeError: return default