"""Model layer: env-driven DB path, migrations, ordering, reconciliation."""

import os
import sqlite3

from app import models


def test_db_path_honours_env_var():
    # Regression for audit point C1: the path the Dockerfile sets must win.
    assert models.db_path() == os.environ["KANBANTE_DB_PATH"]


def test_board_state_ignores_unknown_column_rows():
    # Regression for audit point I4: a legacy row must not KeyError the board.
    with models.get_db() as conn:
        conn.execute(
            "INSERT INTO board_state (repo_full_name, issue_number, column_name) "
            "VALUES ('a/b', 99, 'ancienne_colonne')"
        )
    state = models.get_board_state("a/b")
    assert all(cards == [] for cards in state.values())


def test_migration_from_v01_schema(tmp_path, monkeypatch):
    legacy = tmp_path / "legacy.db"
    conn = sqlite3.connect(legacy)
    conn.executescript(
        """
        CREATE TABLE board_state (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            repo_full_name TEXT NOT NULL,
            issue_number INTEGER NOT NULL,
            column_name TEXT NOT NULL DEFAULT 'backlog',
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            UNIQUE(repo_full_name, issue_number)
        );
        CREATE TABLE project_notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            repo_full_name TEXT NOT NULL,
            note TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            UNIQUE(repo_full_name)
        );
        INSERT INTO board_state (repo_full_name, issue_number, column_name)
            VALUES ('a/b', 1, 'todo'), ('a/b', 2, 'colonne_disparue');
        INSERT INTO project_notes (repo_full_name, note) VALUES ('a/b', 'salut');
        """
    )
    conn.commit()
    conn.close()

    monkeypatch.setenv("KANBANTE_DB_PATH", str(legacy))
    models.init_db()

    with models.get_db() as conn:
        board_cols = [r[1] for r in conn.execute("PRAGMA table_info(board_state)")]
        notes_cols = [r[1] for r in conn.execute("PRAGMA table_info(project_notes)")]
        remaining = conn.execute("SELECT COUNT(*) FROM board_state").fetchone()[0]
    assert "position" in board_cols
    assert "updated_at" in notes_cols
    assert remaining == 1  # valid row kept, unknown-column row dropped
    assert models.get_notes("a/b") == "salut"


def test_move_and_column_order_persisted():
    models.move_issue("a/b", 1, "todo")
    models.move_issue("a/b", 2, "todo")
    models.set_column_order("a/b", "todo", [2, 1])
    state = models.get_board_state("a/b")
    assert [c["issue_number"] for c in state["todo"]] == [2, 1]


def test_reconcile_closed_to_done_and_purge_deleted():
    models.move_issue("a/b", 1, "in_progress")
    models.move_issue("a/b", 2, "todo")
    models.move_issue("a/b", 3, "review")
    changed = models.reconcile_board_state("a/b", open_numbers={1}, closed_numbers={2})
    assert changed["closed_to_done"] == 1 and changed["purged"] == 1
    assert changed["closed_numbers"] == [2] and changed["purged_numbers"] == [3]
    state = models.get_board_state("a/b")
    assert [c["issue_number"] for c in state["in_progress"]] == [1]
    assert [c["issue_number"] for c in state["done"]] == [2]
    assert all(
        c["issue_number"] != 3 for cards in state.values() for c in cards
    )


def test_notes_roundtrip():
    assert models.get_notes("a/b") is None
    models.set_notes("a/b", "première version")
    models.set_notes("a/b", "deuxième version")
    assert models.get_notes("a/b") == "deuxième version"


def test_move_issue_rejects_invalid_column():
    import pytest

    with pytest.raises(ValueError):
        models.move_issue("a/b", 1, "pas_une_colonne")