import sqlite3
from pathlib import Path

import pytest
from mars_bot.mentions_db import (
    is_cold_start,
    init,
    insert_or_ignore,
    mark_alerted,
    mark_cold_start_done,
    get_unalerted,
)


def test_is_cold_start_true_when_missing(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    assert is_cold_start(db_path) is True


def test_is_cold_start_true_when_empty_file(tmp_path: Path):
    """Пустой файл БД (нет таблицы mentions) — всё ещё cold start."""
    db_path = tmp_path / "mentions.db"
    db_path.write_bytes(b"")
    assert is_cold_start(db_path) is True


def test_is_cold_start_true_after_init_without_marker(tmp_path: Path):
    """init создал схему (включая meta), но маркер не выставлен — прогон холодный.

    Моделирует падение посреди первого cold start: БД есть, но не достроена.
    """
    db_path = tmp_path / "mentions.db"
    init(db_path)
    assert is_cold_start(db_path) is True


def test_is_cold_start_false_after_marker_set(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    init(db_path)
    with sqlite3.connect(db_path) as conn:
        mark_cold_start_done(conn)
    assert is_cold_start(db_path) is False


def test_init_creates_schema(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    init(db_path)
    assert db_path.exists()
    with sqlite3.connect(db_path) as conn:
        rows = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='mentions'"
        ).fetchall()
        assert rows == [("mentions",)]


def test_init_is_idempotent(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    init(db_path)
    init(db_path)  # не должно бросить


SAMPLE_ROW = {
    "post_id": 12345,
    "mars_channel": "marsingru",
    "mention_type": "channel",
    "source_channel_id": 9999,
    "source_username": "somechannel",
    "source_title": "Some Channel",
    "source_subscribers": 5000,
    "posted_at": 1700000000,
    "found_at": 1700000100,
    "post_link": "https://t.me/somechannel/42",
}


def _conn(tmp_path: Path) -> sqlite3.Connection:
    db_path = tmp_path / "mentions.db"
    init(db_path)
    return sqlite3.connect(db_path)


def test_insert_or_ignore_new_returns_true(tmp_path: Path):
    with _conn(tmp_path) as conn:
        assert insert_or_ignore(conn, SAMPLE_ROW) is True
        rows = conn.execute("SELECT post_id FROM mentions").fetchall()
        assert rows == [(12345,)]


def test_insert_or_ignore_duplicate_returns_false(tmp_path: Path):
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, SAMPLE_ROW)
        assert insert_or_ignore(conn, SAMPLE_ROW) is False
        rows = conn.execute("SELECT COUNT(*) FROM mentions").fetchall()
        assert rows == [(1,)]


def test_mark_alerted_updates_flag(tmp_path: Path):
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, SAMPLE_ROW)
        mark_alerted(conn, post_id=12345, mars_channel="marsingru")
        flag = conn.execute("SELECT alerted FROM mentions WHERE post_id=12345").fetchone()
        assert flag == (1,)


def test_get_unalerted_returns_only_zero(tmp_path: Path):
    row2 = {**SAMPLE_ROW, "post_id": 22222}
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, SAMPLE_ROW)
        insert_or_ignore(conn, row2)
        mark_alerted(conn, post_id=12345, mars_channel="marsingru")
        rows = get_unalerted(conn)
        assert [r["post_id"] for r in rows] == [22222]
        assert rows[0]["source_username"] == "somechannel"


def test_composite_key_same_post_different_mars_channel(tmp_path: Path):
    """Один пост, упомянувший два канала Марса → две отдельные строки и два алерта."""
    row_a = {**SAMPLE_ROW, "post_id": 555, "mars_channel": "marsingru"}
    row_b = {**SAMPLE_ROW, "post_id": 555, "mars_channel": "choooooooir"}
    with _conn(tmp_path) as conn:
        assert insert_or_ignore(conn, row_a) is True
        assert insert_or_ignore(conn, row_b) is True  # НЕ игнорируется — другой канал
        rows = conn.execute(
            "SELECT mars_channel FROM mentions WHERE post_id=555 ORDER BY mars_channel"
        ).fetchall()
        assert rows == [("choooooooir",), ("marsingru",)]


def test_composite_key_same_post_same_channel_ignored(tmp_path: Path):
    """Повтор той же пары (post_id, mars_channel) — по-прежнему дедупится."""
    with _conn(tmp_path) as conn:
        assert insert_or_ignore(conn, SAMPLE_ROW) is True
        assert insert_or_ignore(conn, SAMPLE_ROW) is False


def test_mark_alerted_scoped_to_channel(tmp_path: Path):
    """mark_alerted помечает только конкретную пару, не все строки поста."""
    row_a = {**SAMPLE_ROW, "post_id": 555, "mars_channel": "marsingru"}
    row_b = {**SAMPLE_ROW, "post_id": 555, "mars_channel": "choooooooir"}
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, row_a)
        insert_or_ignore(conn, row_b)
        mark_alerted(conn, post_id=555, mars_channel="marsingru")
        rows = dict(
            conn.execute(
                "SELECT mars_channel, alerted FROM mentions WHERE post_id=555"
            ).fetchall()
        )
        assert rows == {"marsingru": 1, "choooooooir": 0}


def test_insert_with_cold_alerted_flag(tmp_path: Path):
    """Cold start использует alerted=1 при вставке — проверяем что флаг принимается."""
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, SAMPLE_ROW, alerted=1)
        flag = conn.execute("SELECT alerted FROM mentions WHERE post_id=12345").fetchone()
        assert flag == (1,)


def test_get_unalerted_does_not_mutate_conn_row_factory(tmp_path: Path):
    """Regression: get_unalerted ранее ставил conn.row_factory = sqlite3.Row глобально."""
    row2 = {**SAMPLE_ROW, "post_id": 22222}
    with _conn(tmp_path) as conn:
        insert_or_ignore(conn, SAMPLE_ROW)
        insert_or_ignore(conn, row2)
        get_unalerted(conn)
        # После get_unalerted прямой запрос должен возвращать tuple, не Row.
        result = conn.execute("SELECT post_id FROM mentions WHERE post_id=12345").fetchone()
        assert result == (12345,)
        assert isinstance(result, tuple)
        assert not isinstance(result, sqlite3.Row)


# --- Миграция старой схемы (PRIMARY KEY только post_id) на составной ключ ---

_OLD_SCHEMA = """
CREATE TABLE mentions (
  post_id            INTEGER PRIMARY KEY,
  mars_channel       TEXT NOT NULL,
  mention_type       TEXT NOT NULL,
  source_channel_id  INTEGER,
  source_username    TEXT,
  source_title       TEXT,
  source_subscribers INTEGER,
  posted_at          INTEGER NOT NULL,
  found_at           INTEGER NOT NULL,
  post_link          TEXT NOT NULL,
  alerted            INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_posted_at ON mentions(posted_at);
"""


def _make_legacy_db(db_path: Path, rows: list[dict]) -> None:
    """Создаёт БД со СТАРОЙ схемой (single-column PK) и наполняет её."""
    db_path.parent.mkdir(parents=True, exist_ok=True)
    with sqlite3.connect(db_path) as conn:
        conn.executescript(_OLD_SCHEMA)
        for r in rows:
            conn.execute(
                """INSERT INTO mentions
                   (post_id, mars_channel, mention_type, source_channel_id,
                    source_username, source_title, source_subscribers,
                    posted_at, found_at, post_link, alerted)
                   VALUES (:post_id, :mars_channel, :mention_type, :source_channel_id,
                    :source_username, :source_title, :source_subscribers,
                    :posted_at, :found_at, :post_link, :alerted)""",
                {"alerted": 0, **r},
            )
        conn.commit()


def _pk_cols(db_path: Path) -> int:
    with sqlite3.connect(db_path) as conn:
        info = conn.execute("PRAGMA table_info(mentions)").fetchall()
        return sum(1 for row in info if row[5] > 0)


def test_migration_rebuilds_composite_pk_and_preserves_data(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    legacy = {**SAMPLE_ROW, "post_id": 111, "mars_channel": "marsingru", "alerted": 1}
    _make_legacy_db(db_path, [legacy])
    assert _pk_cols(db_path) == 1  # старая схема

    init(db_path)  # должна мигрировать

    assert _pk_cols(db_path) == 2  # составной ключ
    with sqlite3.connect(db_path) as conn:
        row = conn.execute(
            "SELECT post_id, mars_channel, alerted FROM mentions"
        ).fetchall()
        assert row == [(111, "marsingru", 1)]  # данные и флаг сохранены


def test_migration_old_alerted_rows_not_resent(tmp_path: Path):
    """Уже разосланные (alerted=1) записи после миграции НЕ попадают в re-send pass."""
    db_path = tmp_path / "mentions.db"
    _make_legacy_db(db_path, [
        {**SAMPLE_ROW, "post_id": 1, "mars_channel": "marsingru", "alerted": 1},
        {**SAMPLE_ROW, "post_id": 2, "mars_channel": "marsingru", "alerted": 0},
    ])
    init(db_path)
    with sqlite3.connect(db_path) as conn:
        unalerted = [r["post_id"] for r in get_unalerted(conn)]
    assert unalerted == [2]  # только реально недосланный


def test_migration_sets_cold_start_marker(tmp_path: Path):
    """Legacy-БД уже работала → после миграции cold start считается пройденным."""
    db_path = tmp_path / "mentions.db"
    _make_legacy_db(db_path, [
        {**SAMPLE_ROW, "post_id": 1, "mars_channel": "marsingru", "alerted": 1},
    ])
    # До init: legacy-БД без meta → is_cold_start=False (уже работала).
    assert is_cold_start(db_path) is False
    init(db_path)
    assert is_cold_start(db_path) is False  # маркер проставлен миграцией


def test_migration_is_idempotent(tmp_path: Path):
    db_path = tmp_path / "mentions.db"
    _make_legacy_db(db_path, [
        {**SAMPLE_ROW, "post_id": 1, "mars_channel": "marsingru", "alerted": 1},
    ])
    init(db_path)
    init(db_path)  # второй прогон не должен ни падать, ни дублировать
    with sqlite3.connect(db_path) as conn:
        count = conn.execute("SELECT COUNT(*) FROM mentions").fetchone()[0]
    assert count == 1
    assert _pk_cols(db_path) == 2