"""SQLite-хранилище: схема, upsert, бэкап, runs."""
from __future__ import annotations

import json
import shutil
import sqlite3
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import Iterable

SCHEMA = """
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS channels (
  username             TEXT PRIMARY KEY,
  tg_id                INTEGER,
  title                TEXT,
  subscribers          INTEGER,
  last_pulled_at       TEXT
);

CREATE TABLE IF NOT EXISTS posts (
  channel              TEXT NOT NULL REFERENCES channels(username),
  post_id              INTEGER NOT NULL,
  date                 TEXT NOT NULL,
  text                 TEXT NOT NULL DEFAULT '',
  link                 TEXT,
  media_type           TEXT,
  views                INTEGER,
  forwards             INTEGER,
  reposts              INTEGER,
  comments             INTEGER,
  reactions            INTEGER,
  er                   REAL,
  err                  REAL,
  views_growth         TEXT,
  is_deleted           INTEGER NOT NULL DEFAULT 0,
  first_seen_at        TEXT NOT NULL,
  last_updated_at      TEXT NOT NULL,
  stats_updated_at     TEXT,
  PRIMARY KEY (channel, post_id)
);

CREATE INDEX IF NOT EXISTS idx_posts_date ON posts(channel, date);
CREATE INDEX IF NOT EXISTS idx_posts_stats_updated ON posts(stats_updated_at);

CREATE TABLE IF NOT EXISTS runs (
  started_at           TEXT PRIMARY KEY,
  finished_at          TEXT,
  status               TEXT,
  succeeded_channels   TEXT,
  failed_channels      TEXT,
  error_summary        TEXT
);

CREATE TABLE IF NOT EXISTS channel_snapshots (
  channel      TEXT NOT NULL REFERENCES channels(username),
  date         TEXT NOT NULL,
  subscribers  INTEGER,
  captured_at  TEXT NOT NULL,
  PRIMARY KEY (channel, date)
);
"""


def now_iso() -> str:
    return datetime.now(tz=timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


def connect(db_path: Path) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.row_factory = sqlite3.Row
    return conn


def init_db(db_path: Path) -> None:
    db_path.parent.mkdir(parents=True, exist_ok=True)
    with connect(db_path) as conn:
        conn.executescript(SCHEMA)
        conn.execute("PRAGMA user_version = 1")
        conn.commit()


def upsert_channel(
    db_path: Path,
    *,
    username: str,
    tg_id: int | None,
    title: str | None,
    subscribers: int | None,
) -> None:
    with connect(db_path) as conn:
        conn.execute(
            """
            INSERT INTO channels (username, tg_id, title, subscribers, last_pulled_at)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(username) DO UPDATE SET
              tg_id = excluded.tg_id,
              title = excluded.title,
              subscribers = excluded.subscribers,
              last_pulled_at = excluded.last_pulled_at
            """,
            (username, tg_id, title, subscribers, now_iso()),
        )
        conn.commit()


def insert_snapshot(
    db_path: Path,
    *,
    channel: str,
    subscribers: int | None,
    on_date: str | None = None,
) -> None:
    """Записать срез числа подписчиков.

    on_date='YYYY-MM-DD' — для исторического бэкфилла; None = сегодня (UTC).
    Один срез на канал на день; повтор в ту же дату обновляет строку."""
    now = now_iso()
    day = on_date or now[:10]
    with connect(db_path) as conn:
        conn.execute(
            """
            INSERT INTO channel_snapshots (channel, date, subscribers, captured_at)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(channel, date) DO UPDATE SET
              subscribers = excluded.subscribers,
              captured_at = excluded.captured_at
            """,
            (channel, day, subscribers, now),
        )
        conn.commit()


def insert_post(db_path: Path, channel: str, post: dict) -> int:
    """Вставить пост, если такого ещё нет. Возвращает 1, если вставлено, иначе 0."""
    now = now_iso()
    with connect(db_path) as conn:
        cur = conn.execute(
            """
            INSERT OR IGNORE INTO posts (
              channel, post_id, date, text, link, media_type, views, forwards,
              reposts, comments, reactions, er, err, views_growth,
              is_deleted, first_seen_at, last_updated_at, stats_updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, NULL)
            """,
            (
                channel,
                post["post_id"],
                post["date"],
                post.get("text", ""),
                post.get("link"),
                post.get("media_type"),
                post.get("views"),
                post.get("forwards"),
                post.get("reposts"),
                post.get("comments"),
                post.get("reactions"),
                post.get("er"),
                post.get("err"),
                json.dumps(post["views_growth"], ensure_ascii=False) if post.get("views_growth") else None,
                now,
                now,
            ),
        )
        conn.commit()
        return cur.rowcount


def update_post_stats(db_path: Path, channel: str, post_id: int, stats: dict) -> None:
    now = now_iso()
    growth_json = json.dumps(stats["views_growth"], ensure_ascii=False) if stats.get("views_growth") else None
    with connect(db_path) as conn:
        conn.execute(
            """
            UPDATE posts SET
              views = ?, forwards = ?, reposts = ?, comments = ?, reactions = ?,
              er = ?, err = ?, views_growth = ?,
              last_updated_at = ?, stats_updated_at = ?
            WHERE channel = ? AND post_id = ?
            """,
            (
                stats.get("views"),
                stats.get("forwards"),
                stats.get("reposts"),
                stats.get("comments"),
                stats.get("reactions"),
                stats.get("er"),
                stats.get("err"),
                growth_json,
                now,
                now,
                channel,
                post_id,
            ),
        )
        conn.commit()


def mark_deleted(db_path: Path, channel: str, post_id: int) -> None:
    with connect(db_path) as conn:
        conn.execute(
            "UPDATE posts SET is_deleted = 1, last_updated_at = ? WHERE channel = ? AND post_id = ?",
            (now_iso(), channel, post_id),
        )
        conn.commit()


def posts_in_window(
    db_path: Path, channel: str, *, days: int, now_iso_ts: str | None = None
) -> list[sqlite3.Row]:
    """Посты канала за последние `days` дней, не помеченные удалёнными."""
    now_dt = datetime.fromisoformat((now_iso_ts or now_iso()).replace("Z", "+00:00"))
    cutoff = (now_dt - timedelta(days=days)).strftime("%Y-%m-%dT%H:%M:%SZ")
    with connect(db_path) as conn:
        return conn.execute(
            """
            SELECT * FROM posts
            WHERE channel = ? AND is_deleted = 0 AND date >= ?
            ORDER BY date DESC
            """,
            (channel, cutoff),
        ).fetchall()


STALE_RUN_HOURS = 6  # прогон не длится дольше ~пары минут; висящий 'running' — упавший процесс


def start_run(db_path: Path, *, stale_after_hours: float = STALE_RUN_HOURS) -> str:
    """Начать новый run. Заодно закрывает зависшие 'running'-строки старше
    `stale_after_hours` как failed — иначе жёсткое падение процесса оставляет
    их без finished_at навсегда, и они не попадают в recent_runs() (алерт молчит)."""
    now = datetime.now(tz=timezone.utc)
    started_at = now.strftime("%Y-%m-%dT%H:%M:%S.%fZ")
    cutoff = (now - timedelta(hours=stale_after_hours)).strftime("%Y-%m-%dT%H:%M:%S.%fZ")
    with connect(db_path) as conn:
        conn.execute(
            """
            UPDATE runs SET
              finished_at = ?, status = 'failed',
              error_summary = 'stale run: процесс не завершился штатно (crash/kill), помечено при следующем старте'
            WHERE status = 'running' AND started_at < ?
            """,
            (now_iso(), cutoff),
        )
        conn.execute(
            "INSERT INTO runs (started_at, status) VALUES (?, 'running')",
            (started_at,),
        )
        conn.commit()
    return started_at


def finish_run(
    db_path: Path,
    *,
    started_at: str,
    status: str,
    succeeded: list[str],
    failed: list[str],
    error_summary: str = "",
) -> None:
    with connect(db_path) as conn:
        cur = conn.execute(
            """
            UPDATE runs SET
              finished_at = ?, status = ?,
              succeeded_channels = ?, failed_channels = ?, error_summary = ?
            WHERE started_at = ?
            """,
            (
                now_iso(),
                status,
                json.dumps(succeeded, ensure_ascii=False),
                json.dumps(failed, ensure_ascii=False),
                error_summary,
                started_at,
            ),
        )
        conn.commit()
        if cur.rowcount == 0:
            raise RuntimeError(f"finish_run: no run found for started_at={started_at!r}")


def backup_db(db_path: Path) -> None:
    if not db_path.exists():
        return
    shutil.copy2(db_path, db_path.with_suffix(db_path.suffix + ".bak"))


def recent_runs(db_path: Path, limit: int = 3) -> list[sqlite3.Row]:
    with connect(db_path) as conn:
        return conn.execute(
            "SELECT * FROM runs WHERE finished_at IS NOT NULL ORDER BY started_at DESC LIMIT ?",
            (limit,),
        ).fetchall()