"""Разовая миграция старых данных в SQLite."""
from __future__ import annotations

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

from db import connect, init_db, now_iso

MSK_OFFSET = timedelta(hours=3)
MSG_ID_RE = re.compile(r"/(\d+)/?$")


def normalize_msk_date(s: str) -> str:
    """Преобразовать строку '2022-03-12 23:04' (МСК) в ISO-8601 UTC."""
    for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M"):
        try:
            dt = datetime.strptime(s, fmt)
            break
        except ValueError:
            continue
    else:
        raise ValueError(f"unsupported date format: {s!r}")
    dt_utc = (dt - MSK_OFFSET).replace(tzinfo=timezone.utc)
    return dt_utc.strftime("%Y-%m-%dT%H:%M:%SZ")


def normalize_unix_ts(ts: int) -> str:
    return datetime.fromtimestamp(ts, tz=timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


def _extract_post_id_from_link(link: str | None) -> int | None:
    if not link:
        return None
    m = MSG_ID_RE.search(link)
    return int(m.group(1)) if m else None


def import_personal_json(db_path: Path, src: Path, *, channel: str) -> int:
    """Импорт исторического JSON-архива личного канала. Возвращает число вставленных строк."""
    raw = json.loads(src.read_text(encoding="utf-8"))
    inserted = 0
    now = now_iso()
    with connect(db_path) as conn:
        # Убедимся, что канал есть в таблице channels (FK)
        conn.execute(
            "INSERT OR IGNORE INTO channels (username) VALUES (?)",
            (channel,),
        )
        for item in raw:
            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 (?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, ?, NULL, 0, ?, ?, NULL)
                """,
                (
                    channel,
                    item["post_id"],
                    normalize_msk_date(item["date"]),
                    item.get("text", "") or "",
                    item.get("link"),
                    item.get("views"),
                    item.get("forwards"),
                    item.get("reposts"),
                    item.get("comments"),
                    item.get("reactions"),
                    item.get("er"),
                    item.get("err"),
                    now,
                    now,
                ),
            )
            inserted += cur.rowcount
        conn.commit()
    return inserted


def import_mars_jsonl(db_path: Path, src: Path, *, channel: str) -> int:
    if not src.exists():
        return 0
    inserted = 0
    now = now_iso()
    with connect(db_path) as conn:
        conn.execute(
            "INSERT OR IGNORE INTO channels (username) VALUES (?)",
            (channel,),
        )
        for line in src.read_text(encoding="utf-8").splitlines():
            if not line.strip():
                continue
            try:
                item = json.loads(line)
            except json.JSONDecodeError as exc:
                print(f"  WARNING: skipping malformed line: {exc}")
                continue
            post_id = item.get("id")
            if post_id is None:
                post_id = _extract_post_id_from_link(item.get("link"))
            if post_id is None:
                continue
            date_iso = normalize_unix_ts(item["date"]) if isinstance(item.get("date"), int) else item["date"]
            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 (?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?, ?, ?, NULL, 0, ?, ?, NULL)
                """,
                (
                    channel, post_id, date_iso,
                    item.get("text", "") or "",
                    item.get("link"),
                    item.get("views"),
                    item.get("forwards"),
                    item.get("reposts"),
                    item.get("comments"),
                    item.get("reactions"),
                    item.get("er"),
                    item.get("err"),
                    now, now,
                ),
            )
            inserted += cur.rowcount
        conn.commit()
    return inserted


def main() -> None:
    script_dir = Path(__file__).resolve().parent
    vault_root = script_dir.parent.parent.parent
    data_dir = Path.home() / "Library" / "Application Support" / "tgstat-puller"
    data_dir.mkdir(parents=True, exist_ok=True)
    db_path = data_dir / "channels.db"
    init_db(db_path)

    personal_src = (
        vault_root / "projects" / "channel"
        / "{channel} {source} TGStat посты 2022-2026 – 2026-06-02.json"
    )
    if personal_src.exists():
        n = import_personal_json(db_path, personal_src, channel="natashhhh")
        print(f"natashhhh: импортировано {n} постов из {personal_src.name}")

    for ch in ("marsingru", "choooooooir"):
        src = script_dir / "data" / f"posts-{ch}.jsonl"
        n = import_mars_jsonl(db_path, src, channel=ch)
        print(f"{ch}: импортировано {n} постов из {src.name}")


if __name__ == "__main__":
    main()