"""Генерация выходных JSON и MD из SQLite."""
from __future__ import annotations

import json
import sqlite3
from datetime import datetime, timezone, timedelta
from pathlib import Path

from db import connect

MSK_OFFSET = timedelta(hours=3)


def build_json_records(db_path: Path, channel: str) -> list[dict]:
    """Собирает список постов из БД в формате, совместимом с историческим архивом."""
    with connect(db_path) as conn:
        rows = conn.execute(
            "SELECT * FROM posts WHERE channel = ? ORDER BY date DESC",
            (channel,),
        ).fetchall()

    records: list[dict] = []
    for r in rows:
        text = r["text"] or ""
        record = {
            "post_id": r["post_id"],
            "date": r["date"],
            "link": r["link"],
            "views": r["views"],
            "forwards": r["forwards"],
            "reposts": r["reposts"],
            "comments": r["comments"],
            "reactions": r["reactions"],
            "er": r["er"],
            "err": r["err"],
            "text_len": len(text),
            "word_count": len(text.split()),
            "text": text,
            "media_type": r["media_type"],
            "is_deleted": bool(r["is_deleted"]),
            "stats_updated_at": r["stats_updated_at"],
        }
        if r["views_growth"]:
            record["views_growth"] = json.loads(r["views_growth"])
        records.append(record)
    return records


def render_channel_json(db_path: Path, channel: str, out_path: Path) -> None:
    records = build_json_records(db_path, channel)
    out_path.parent.mkdir(parents=True, exist_ok=True)
    out_path.write_text(
        json.dumps(records, ensure_ascii=False, indent=2),
        encoding="utf-8",
    )


def _iso_utc_to_msk_str(iso: str) -> str:
    dt = datetime.fromisoformat(iso.replace("Z", "+00:00"))
    msk = dt.astimezone(timezone(MSK_OFFSET))
    return msk.strftime("%Y-%m-%d %H:%M")


def render_personal_md(db_path: Path, channel: str, out_path: Path, *, partial: bool) -> None:
    with connect(db_path) as conn:
        ch = conn.execute(
            "SELECT title, subscribers FROM channels WHERE username = ?",
            (channel,),
        ).fetchone()
        if ch is None:
            ch = {"title": "—", "subscribers": None}
        # прирост: ближайший снапшот возрастом 6+ дней (история может быть редкой —
        # показываем фактический интервал, максимум месяц назад)
        week_ago = conn.execute(
            """SELECT date, subscribers FROM channel_snapshots
               WHERE channel = ? AND date <= date('now', '-6 days')
                 AND date >= date('now', '-31 days') AND subscribers IS NOT NULL
               ORDER BY date DESC LIMIT 1""",
            (channel,),
        ).fetchone()

    records = build_json_records(db_path, channel)
    total = len(records)
    label = "частичное обновление" if partial else "полное"
    updated = datetime.now(tz=timezone.utc).strftime("%Y-%m-%d")

    subs = ch["subscribers"]
    subs_str = str(subs) if subs is not None else "—"
    if subs is not None and week_ago and week_ago["subscribers"] is not None:
        delta = subs - week_ago["subscribers"]
        span = (datetime.now(tz=timezone.utc).date()
                - datetime.strptime(week_ago["date"], "%Y-%m-%d").date()).days
        subs_str += f" ({delta:+d} за {span} дн.)"

    lines: list[str] = []
    lines.append(f"# TGStat архив постов канала @{channel}")
    lines.append("")
    lines.append(
        f"Обновлено: {updated} ({label}). Всего постов: {total}. "
        f"Подписчики: {subs_str}."
    )
    lines.append("")

    if records:
        top = sorted(records, key=lambda r: r.get("views") or 0, reverse=True)[:10]
        lines.append("## Топ-10 по просмотрам")
        for i, r in enumerate(top, 1):
            d = _iso_utc_to_msk_str(r["date"])
            preview = (r["text"] or "").replace("\n", " ").strip()
            if len(preview) > 120:
                preview = preview[:120] + "…"
            lines.append(f"{i}. **{r.get('views', '—')}** просмотров · {d} · [ссылка]({r.get('link') or ''})")
            if preview:
                lines.append(f"   > {preview}")
        lines.append("")

    lines.append("## Все посты (новые сверху)")
    lines.append("")
    for r in records:
        d = _iso_utc_to_msk_str(r["date"])
        link = r.get("link") or ""
        # NB: колонка `forwards` в БД хранит sharesCount (пересылки людьми — реальный
        # share-метрик), `reposts` = forwardsCount (репосты каналами, у личного канала
        # почти всегда 0). Показываем пересылки всегда, репосты — только если есть.
        metrics = [
            f"Просмотры: {r.get('views') if r.get('views') is not None else '—'}",
            f"Реакции: {r.get('reactions') if r.get('reactions') is not None else '—'}",
            f"Комменты: {r.get('comments') if r.get('comments') is not None else '—'}",
            f"Пересылки: {r.get('forwards') if r.get('forwards') is not None else '—'}",
        ]
        if r.get("reposts"):
            metrics.append(f"Репосты каналами: {r['reposts']}")
        if r.get("er") is not None:
            metrics.append(f"ER: {r['er']}%")
        if r.get("err") is not None:
            metrics.append(f"ERR: {r['err']}%")
        lines.append(f"### {d} — [{link}]({link})")
        lines.append("**" + " · ".join(metrics) + "**")
        text = (r["text"] or "").strip()
        if text:
            lines.append(text)
        else:
            lines.append("_(без текста, только медиа)_")
        lines.append("")

    out_path.parent.mkdir(parents=True, exist_ok=True)
    out_path.write_text("\n".join(lines), encoding="utf-8")


def cleanup_dated_snapshots(
    output_dir: Path,
    *,
    channel_username: str,
    keep_file: Path,
) -> None:
    """Удалить все файлы по маске для канала, кроме `keep_file`."""
    mask = f"{{mars}} {{source}} посты канала {channel_username} – *.json"
    for p in output_dir.glob(mask):
        if p.resolve() != keep_file.resolve():
            p.unlink()