"""Хранилище meta-puller: свои посты Instagram и Threads с метриками."""
from __future__ import annotations

import sqlite3
from contextlib import contextmanager
from datetime import date
from pathlib import Path

# Платформа → таблица постов. Таблицы разные, потому что наборы метрик
# принципиально не совпадают: у Instagram охват и сохранения, у Threads —
# репосты и цитаты.
TABLES = {"instagram": "ig_posts", "threads": "th_posts"}

# Поля, которые приходят вместе со списком постов (не из insights).
POST_FIELDS = {
    "instagram": ("post_id", "caption", "media_type", "media_product_type",
                  "permalink", "timestamp", "likes", "comments"),
    "threads": ("post_id", "text", "media_type", "is_quote_post",
                "permalink", "timestamp"),
}

SCHEMA = """
CREATE TABLE IF NOT EXISTS ig_posts (
    post_id TEXT PRIMARY KEY,
    caption TEXT,
    media_type TEXT,
    media_product_type TEXT,
    permalink TEXT,
    timestamp TEXT,
    likes INTEGER,
    comments INTEGER,
    views INTEGER,
    reach INTEGER,
    saved INTEGER,
    shares INTEGER,
    total_interactions INTEGER,
    follows INTEGER,
    profile_visits INTEGER,
    stats_updated_at TEXT,
    is_deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS th_posts (
    post_id TEXT PRIMARY KEY,
    text TEXT,
    media_type TEXT,
    is_quote_post INTEGER,
    permalink TEXT,
    timestamp TEXT,
    views INTEGER,
    likes INTEGER,
    replies INTEGER,
    reposts INTEGER,
    quotes INTEGER,
    shares INTEGER,
    stats_updated_at TEXT,
    is_deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS account_snapshots (
    platform TEXT NOT NULL,
    date TEXT NOT NULL,
    followers INTEGER,
    media_count INTEGER,
    username TEXT,
    PRIMARY KEY (platform, date)
);

CREATE TABLE IF NOT EXISTS runs (
    started_at TEXT NOT NULL,
    finished_at TEXT,
    status TEXT,
    error_summary TEXT
);

CREATE TABLE IF NOT EXISTS tokens (
    platform TEXT PRIMARY KEY,
    token TEXT NOT NULL,
    issued_at TEXT NOT NULL,
    expires_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_ig_timestamp ON ig_posts(timestamp);
CREATE INDEX IF NOT EXISTS idx_th_timestamp ON th_posts(timestamp);
"""


class UnknownPlatformError(ValueError):
    """Платформа не instagram и не threads."""


def _table(platform: str) -> str:
    try:
        return TABLES[platform]
    except KeyError:
        raise UnknownPlatformError(
            "неизвестная платформа {!r}, ожидались {}".format(
                platform, " или ".join(sorted(TABLES)))
        ) from None


@contextmanager
def connect(db_path: Path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


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


def _columns(db_path: Path, table: str) -> set:
    with connect(db_path) as conn:
        return {r["name"] for r in conn.execute(
            "PRAGMA table_info({})".format(table)).fetchall()}


def save_account_snapshot(db_path: Path, platform: str, date: str,
                          followers: int, media_count: int | None,
                          username: str | None = None) -> None:
    with connect(db_path) as conn:
        conn.execute(
            "INSERT INTO account_snapshots (platform, date, followers, media_count, username) "
            "VALUES (?, ?, ?, ?, ?) ON CONFLICT(platform, date) DO UPDATE SET "
            "followers = excluded.followers, media_count = excluded.media_count, "
            "username = COALESCE(excluded.username, account_snapshots.username)",
            (platform, date, followers, media_count, username),
        )


def account_state(db_path: Path, platform: str, today: str) -> dict:
    """Подписчики сейчас и прирост относительно ближайшего снапшота 6+ дней назад.

    Окно взято как у ТГ-архива: история бывает редкой, поэтому берём ближайший
    подходящий снапшот в пределах месяца и честно показываем его давность.
    """
    with connect(db_path) as conn:
        current = conn.execute(
            "SELECT date, followers, media_count, username FROM account_snapshots "
            "WHERE platform = ? ORDER BY date DESC LIMIT 1",
            (platform,),
        ).fetchone()
        if current is None:
            return {"followers": None, "media_count": None, "username": None,
                    "growth": None, "growth_days": None}
        earlier = conn.execute(
            "SELECT date, followers FROM account_snapshots "
            "WHERE platform = ? AND date <= date(?, '-6 days') "
            "AND date >= date(?, '-31 days') AND followers IS NOT NULL "
            "ORDER BY date DESC LIMIT 1",
            (platform, today, today),
        ).fetchone()

    state = {"followers": current["followers"], "media_count": current["media_count"],
             "username": current["username"], "growth": None, "growth_days": None}
    if earlier is not None and current["followers"] is not None:
        state["growth"] = current["followers"] - earlier["followers"]
        state["growth_days"] = (date.fromisoformat(current["date"])
                                - date.fromisoformat(earlier["date"])).days
    return state


def record_run(db_path: Path, started_at: str, finished_at: str,
               status: str, error_summary: str) -> None:
    with connect(db_path) as conn:
        conn.execute(
            "INSERT INTO runs (started_at, finished_at, status, error_summary) "
            "VALUES (?, ?, ?, ?)",
            (started_at, finished_at, status, error_summary),
        )


def should_alert(db_path: Path, streak: int = 3) -> bool:
    """True, если последние `streak` прогонов подряд упали.

    Одиночный сбой сети — обычное дело, будить из-за него не за чем.
    """
    with connect(db_path) as conn:
        rows = conn.execute(
            "SELECT status FROM runs ORDER BY rowid DESC LIMIT ?", (streak,),
        ).fetchall()
    return len(rows) == streak and all(r["status"] == "failed" for r in rows)


def save_token(db_path: Path, platform: str, token: str,
               issued_at: str, expires_at: str | None) -> None:
    with connect(db_path) as conn:
        conn.execute(
            "INSERT INTO tokens (platform, token, issued_at, expires_at) "
            "VALUES (?, ?, ?, ?) ON CONFLICT(platform) DO UPDATE SET "
            "token = excluded.token, issued_at = excluded.issued_at, "
            "expires_at = excluded.expires_at",
            (platform, token, issued_at, expires_at),
        )


def load_token(db_path: Path, platform: str):
    with connect(db_path) as conn:
        return conn.execute(
            "SELECT * FROM tokens WHERE platform = ?", (platform,)).fetchone()


def update_stats(db_path: Path, platform: str, post_id: str,
                 metrics: dict, now: str) -> None:
    """Записывает метрики из insights и отметку времени их обновления."""
    table = _table(platform)
    # Meta время от времени заводит новые метрики; чужая колонка не повод
    # ронять прогон — пишем то, под что есть место, остальное пропускаем.
    fields = [f for f in metrics if f in _columns(db_path, table)]
    if not fields:
        return
    assignments = ", ".join("{} = ?".format(f) for f in fields)
    with connect(db_path) as conn:
        conn.execute(
            "UPDATE {} SET {}, stats_updated_at = ? WHERE post_id = ?".format(
                table, assignments),
            [metrics[f] for f in fields] + [now, post_id],
        )


def posts_in_window(db_path: Path, platform: str, since: str) -> list:
    """Посты не старше `since` (ISO-строка), новые сверху."""
    table = _table(platform)
    with connect(db_path) as conn:
        return conn.execute(
            "SELECT * FROM {} WHERE timestamp >= ? ORDER BY timestamp DESC".format(table),
            (since,),
        ).fetchall()


def mark_deleted(db_path: Path, platform: str, post_id: str) -> None:
    table = _table(platform)
    with connect(db_path) as conn:
        conn.execute(
            "UPDATE {} SET is_deleted = 1 WHERE post_id = ?".format(table),
            (post_id,),
        )


def upsert_post(db_path: Path, platform: str, post: dict) -> bool:
    """Пишет пост. Возвращает True, если пост появился в базе впервые.

    Повторный вызов обновляет поля поста, не создавая дубля: у постов растут
    лайки и комментарии, и свежие цифры важнее первых.
    """
    table = _table(platform)
    fields = [f for f in POST_FIELDS[platform] if f in post]
    placeholders = ", ".join("?" for _ in fields)
    updates = ", ".join(
        "{0} = excluded.{0}".format(f) for f in fields if f != "post_id")
    with connect(db_path) as conn:
        existed = conn.execute(
            "SELECT 1 FROM {} WHERE post_id = ?".format(table),
            (post["post_id"],),
        ).fetchone() is not None
        conn.execute(
            "INSERT INTO {table} ({cols}) VALUES ({vals}) "
            "ON CONFLICT(post_id) DO UPDATE SET {updates}".format(
                table=table, cols=", ".join(fields),
                vals=placeholders, updates=updates),
            [post[f] for f in fields],
        )
    return not existed