from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

import pytest

SHARED_DIR = Path(__file__).resolve().parent
if str(SHARED_DIR.parent) not in sys.path:
    sys.path.insert(0, str(SHARED_DIR.parent))

from Shared.storage.sort_storage import (  # noqa: E402
    DEFAULT_STORAGE_CODE,
    SCHEMA_VERSION,
    connect_database,
    derive_stdown_root,
    initialize_database,
)


REQUIRED_TABLES = {
    "schema_migrations",
    "collector_profile",
    "device_profile",
    "storage_roots",
    "social_accounts",
    "collection_profiles",
    "account_collection_rules",
    "posts",
    "post_stat_latest",
    "post_stat_history",
    "media_assets",
    "transcripts",
    "hook_segments",
    "categories",
    "post_category_assignments",
    "tags",
    "post_tag_assignments",
    "post_notes",
    "review_templates",
    "review_items",
    "post_reviews",
    "review_scores",
    "post_comments",
    "aggregate_definitions",
    "aggregate_accounts",
    "p2p_inventory",
    "sync_queue",
    "point_ledger_cache",
}


def test_fixed_stdown_root_and_schema_initialization(tmp_path: Path) -> None:
    st_root = tmp_path / "bundle" / "_St"
    st_root.mkdir(parents=True)
    db_path = st_root / "Shared" / "data" / "sort_local.sqlite3"

    result = initialize_database(db_path, st_root)

    expected_root = tmp_path / "bundle" / "_StDown" / "instagram"
    assert derive_stdown_root(st_root) == tmp_path / "bundle" / "_StDown"
    assert expected_root.is_dir()
    assert result["storage_root"] == str(expected_root.resolve())
    assert result["schema_version"] == SCHEMA_VERSION
    assert result["journal_mode"] == "wal"
    assert result["foreign_keys"] == 1

    conn = connect_database(db_path)
    try:
        tables = {
            row[0]
            for row in conn.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
            )
        }
        assert REQUIRED_TABLES.issubset(tables)

        root = conn.execute(
            "SELECT root_code, root_path, is_default FROM storage_roots"
        ).fetchone()
        assert root["root_code"] == DEFAULT_STORAGE_CODE
        assert root["root_path"] == str(expected_root.resolve())
        assert root["is_default"] == 1
    finally:
        conn.close()


def test_initialization_is_idempotent(tmp_path: Path) -> None:
    st_root = tmp_path / "_St"
    st_root.mkdir()
    db_path = st_root / "Shared" / "data" / "sort_local.sqlite3"

    initialize_database(db_path, st_root)
    initialize_database(db_path, st_root)

    conn = connect_database(db_path)
    try:
        assert conn.execute("SELECT COUNT(*) FROM storage_roots").fetchone()[0] == 1
        assert conn.execute("SELECT COUNT(*) FROM schema_migrations").fetchone()[0] == 1
        assert conn.execute("SELECT COUNT(*) FROM collector_profile").fetchone()[0] == 1
        assert conn.execute("SELECT COUNT(*) FROM device_profile").fetchone()[0] == 1
    finally:
        conn.close()


def test_posts_are_keyed_by_platform_and_shortcode(tmp_path: Path) -> None:
    st_root = tmp_path / "_St"
    st_root.mkdir()
    db_path = st_root / "Shared" / "data" / "sort_local.sqlite3"
    initialize_database(db_path, st_root)

    conn = connect_database(db_path)
    try:
        conn.execute(
            "INSERT INTO posts(platform, shortcode, post_url) VALUES ('instagram', 'ABC123', 'https://www.instagram.com/p/ABC123/')"
        )
        with pytest.raises(sqlite3.IntegrityError):
            conn.execute(
                "INSERT INTO posts(platform, shortcode, post_url) VALUES ('instagram', 'ABC123', 'https://www.instagram.com/reel/ABC123/')"
            )
    finally:
        conn.close()


def test_media_assets_store_paths_not_binary_blobs(tmp_path: Path) -> None:
    st_root = tmp_path / "_St"
    st_root.mkdir()
    db_path = st_root / "Shared" / "data" / "sort_local.sqlite3"
    initialize_database(db_path, st_root)

    conn = connect_database(db_path)
    try:
        columns = {
            row[1].lower(): row[2].upper()
            for row in conn.execute("PRAGMA table_info(media_assets)")
        }
        assert "relative_path" in columns
        assert "file_name" in columns
        assert "sha256" in columns
        assert all(col_type != "BLOB" for col_type in columns.values())
    finally:
        conn.close()


def test_invalid_st_root_is_rejected(tmp_path: Path) -> None:
    with pytest.raises(ValueError):
        derive_stdown_root(tmp_path / "not_st")