"""comments.sqlite の世代バックアップ。

- `VACUUM INTO` で一貫したスナップショットを取る (稼働中の daemon と共存できる。 取得中は
  書き込みが数十秒待たされるが、 daemon 側は busy_timeout=30s で吸収する)
- スナップショットを `PRAGMA quick_check` で検証してから zstd 圧縮
- DB とは別の物理ディスクに置く (既定: ~/backups/stream-comments、 DB は sda / 既定先は sdb)
- 世代: 日次 7 / 週次 (日曜) 4 / 月次 (1 日) 12
- channels.toml と scripts/ も一緒に tar で保存 (設定とコードの復元用)
- `--offsite user@host:dir` を付けるとその日のスナップショットを scp で別マシンにも送る (14 日保持)
- `--gdrive remote:path` を付けると weekly / monthly を rclone で Google Drive (暗号化 remote) に同期

使い方 (systemd timer から):
    python3 scripts/backup_db.py [--dest DIR] [--offsite HOST:DIR]
"""
from __future__ import annotations

import argparse
import datetime as dt
import pathlib
import shutil
import sqlite3
import subprocess
import sys
import tarfile
import time

ROOT = pathlib.Path(__file__).resolve().parent.parent
DB = ROOT / "data" / "comments.sqlite"
KEEP = {"daily": 7, "weekly": 4, "monthly": 12}


def log(msg: str) -> None:
    print(f"[backup] {msg}", flush=True)


def snapshot(dest: pathlib.Path, stamp: str) -> pathlib.Path:
    tmp = dest / f".comments-{stamp}.sqlite.tmp"
    tmp.unlink(missing_ok=True)
    t = time.monotonic()
    conn = sqlite3.connect(f"file:{DB}?mode=ro", uri=True, timeout=120.0)
    try:
        conn.execute("PRAGMA busy_timeout = 120000")
        conn.execute("VACUUM INTO ?", (str(tmp),))
    finally:
        conn.close()
    log(f"VACUUM INTO {tmp.stat().st_size / 2**20:.0f} MiB in {time.monotonic() - t:.1f}s")
    chk = sqlite3.connect(str(tmp))
    try:
        res = chk.execute("PRAGMA quick_check").fetchone()[0]
        rows = chk.execute("SELECT count(*) FROM comments").fetchone()[0]
    finally:
        chk.close()
    if res != "ok":
        raise RuntimeError(f"quick_check failed: {res}")
    log(f"quick_check ok, rows={rows:,}")
    out = dest / f"comments-{stamp}.sqlite.zst"
    subprocess.run(["zstd", "-q", "-T0", "-10", "--rm", "-f", str(tmp), "-o", str(out)], check=True)
    log(f"compressed → {out.name} {out.stat().st_size / 2**20:.0f} MiB")
    return out


def config_tar(dest: pathlib.Path, stamp: str) -> pathlib.Path:
    out = dest / f"config-{stamp}.tar.gz"
    with tarfile.open(out, "w:gz") as tf:
        for name in ("channels.toml", "scripts", "requirements.txt"):
            p = ROOT / name
            if p.exists():
                tf.add(p, arcname=name, filter=lambda ti: None if "__pycache__" in ti.name or ti.name.endswith(".bak") else ti)
    return out


def rotate(dest: pathlib.Path, today: dt.date, snap: pathlib.Path, conf: pathlib.Path) -> None:
    tiers = ["daily"]
    if today.weekday() == 6:
        tiers.append("weekly")
    if today.day == 1:
        tiers.append("monthly")
    for tier in tiers:
        d = dest / tier
        d.mkdir(exist_ok=True)
        for f in (snap, conf):
            shutil.copy2(f, d / f.name)
    snap.unlink()
    conf.unlink()
    for tier, keep in KEEP.items():
        d = dest / tier
        if not d.is_dir():
            continue
        for prefix in ("comments-", "config-"):
            files = sorted(p for p in d.iterdir() if p.name.startswith(prefix))
            for old in files[:-keep]:
                old.unlink()
                log(f"rotate: removed {tier}/{old.name}")


SSH = ["-i", str(pathlib.Path.home() / ".ssh" / "id_backup_ed25519"), "-o", "BatchMode=yes",
       "-o", "ConnectTimeout=15", "-o", "StrictHostKeyChecking=accept-new"]
OFFSITE_KEEP_DAYS = 14


def offsite(daily: pathlib.Path, stamp: str, target: str) -> None:
    """その日のスナップショットと設定 tar を scp で別マシンへ送り、 古いものを消す。

    送り先に rsync が無くても動くよう scp + ssh だけで完結させる。
    """
    host, _, rdir = target.partition(":")
    rdir = rdir or "backups/stream-comments"
    files = [str(daily / f"comments-{stamp}.sqlite.zst"), str(daily / f"config-{stamp}.tar.gz")]
    subprocess.run(["ssh", *SSH, host, f"mkdir -p {rdir}"], check=True)
    subprocess.run(["scp", "-q", *SSH, *files, f"{host}:{rdir}/"], check=True)
    subprocess.run(
        ["ssh", *SSH, host,
         f"find {rdir} -maxdepth 1 -type f \\( -name 'comments-*' -o -name 'config-*' \\) -mtime +{OFFSITE_KEEP_DAYS} -delete"],
        check=True,
    )
    log(f"offsite copied → {host}:{rdir} (keep {OFFSITE_KEEP_DAYS} days)")


RCLONE = str(pathlib.Path.home() / "bin" / "rclone")


def gdrive(dest: pathlib.Path, remote: str) -> None:
    """週次・月次の世代だけを rclone で Google Drive (暗号化 remote) に同期する。

    日次まで送ると Drive 容量を食うので、 長期保持の weekly / monthly に絞る。
    remote は rclone の crypt remote (例: gdrive-crypt:stream-comments) を想定し、
    本文は Drive 上では暗号化されたファイル名・中身になる。
    """
    for tier in ("weekly", "monthly"):
        src = dest / tier
        if not src.is_dir():
            continue
        subprocess.run(
            [RCLONE, "sync", str(src), f"{remote.rstrip('/')}/{tier}", "--transfers", "2", "--stats-one-line", "-q"],
            check=True,
        )
    log(f"gdrive synced weekly/monthly → {remote}")


def main() -> int:
    ap = argparse.ArgumentParser()
    ap.add_argument("--dest", default=str(pathlib.Path.home() / "backups" / "stream-comments"))
    ap.add_argument("--gdrive", help="rclone remote (例: gdrive-crypt:stream-comments)。 weekly / monthly だけ送る")
    ap.add_argument("--offsite", help="送り先 (例: kuroneko@192.168.10.200:backups/stream-comments)")
    ns = ap.parse_args()
    dest = pathlib.Path(ns.dest)
    dest.mkdir(parents=True, exist_ok=True)
    today = dt.date.today()
    stamp = today.strftime("%Y%m%d")
    snap = snapshot(dest, stamp)
    conf = config_tar(dest, stamp)
    rotate(dest, today, snap, conf)
    if ns.offsite:
        offsite(dest / "daily", stamp, ns.offsite)
    if ns.gdrive:
        gdrive(dest, ns.gdrive)
    free = shutil.disk_usage(dest).free / 2**30
    log(f"done. free on backup disk: {free:.0f} GiB")
    return 0


if __name__ == "__main__":
    try:
        sys.exit(main())
    except Exception as e:  # noqa: BLE001 — timer の失敗を journal に確実に残す
        log(f"FAILED: {e!r}")
        sys.exit(1)