"""
Unit Tests for FreeExile 30 Map Styles SQLite Database and Relational Service.
Verifies schema, pragmas, indices, foreign keys, CHECK constraints, 30 records,
contiguous biome codes, color hex integrity, and MapStyleService query methods.
"""

from __future__ import annotations
import os
import re
import sqlite3
from pathlib import Path
from typing import Generator

import pytest

from server.world.map_style_schema import init_db
from server.world.map_style_service import MapStyleService
from server.world.map_style_types import (
    MapStyleDefinition,
    MapStyleTileMapping,
    MapStylePropDefinition,
)

HEX_COLOR_REGEX = re.compile(r"^#[0-9a-fA-F]{6}$")

VALID_THEMES = {
    "HOANG_DA_DA_NGOAI",
    "DAM_LAY_DOC_CHUONG",
    "HEM_NUI_COT_MAC",
    "HAM_MO_TA_QUAI",
    "THAN_DIEN_PHE_TICH",
    "HU_KHONG_DI_BIEN",
}

LEGACY_ALIASES = {
    1: "BLEACHED_BONE_CANYON",
    2: "SAVAGE_MANGROVE_SWAMP",
    3: "CRIMSON_BLOOD_FOREST",
    4: "OUTCAST_MINE_SHAFTS",
    5: "CORRUPTED_FIEND_RUINS",
}


@pytest.fixture(scope="module")
def db_path() -> Path:
    """Return canonical path to data/map_styles.db."""
    return Path(__file__).resolve().parent.parent.parent / "data" / "map_styles.db"


@pytest.fixture(scope="module")
def db_conn(db_path: Path) -> Generator[sqlite3.Connection, None, None]:
    """Provide a read connection to seeded data/map_styles.db."""
    assert db_path.exists(), f"Database file does not exist at {db_path}"
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    yield conn
    conn.close()


@pytest.fixture
def temp_test_db() -> Generator[sqlite3.Connection, None, None]:
    """Provide an isolated in-memory database with schema initialized."""
    conn = init_db(":memory:")
    yield conn
    conn.close()


@pytest.fixture(scope="module")
def map_service(db_path: Path) -> MapStyleService:
    """Provide MapStyleService connected to data/map_styles.db."""
    return MapStyleService(db_path=db_path)


def test_database_file_exists_and_non_empty(db_path: Path) -> None:
    """Verify that data/map_styles.db exists on disk and is non-empty."""
    assert db_path.is_file(), f"File {db_path} is missing."
    assert db_path.stat().st_size > 0, "Database file is empty (0 bytes)."


def test_database_schema_tables_and_indices(db_conn: sqlite3.Connection) -> None:
    """Verify master tables and query indices exist."""
    cur = db_conn.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
    tables = {row["name"] for row in cur.fetchall()}
    assert {"map_styles", "map_style_tiles", "map_style_props"}.issubset(tables)

    cur.execute("SELECT name FROM sqlite_master WHERE type = 'index'")
    indices = {row["name"] for row in cur.fetchall()}
    assert {
        "idx_map_styles_biome_code",
        "idx_map_styles_theme",
        "idx_map_style_tiles_style",
        "idx_map_style_props_style",
    }.issubset(indices)


def test_map_styles_record_count_exactly_thirty(db_conn: sqlite3.Connection) -> None:
    """Verify map_styles contains exactly 30 records."""
    cur = db_conn.cursor()
    cur.execute("SELECT count(*) AS cnt FROM map_styles")
    assert cur.fetchone()["cnt"] == 30


def test_biome_code_contiguous_range_one_to_thirty(db_conn: sqlite3.Connection) -> None:
    """Verify biome_code ranges 1 to 30 with no duplicates and no missing values."""
    cur = db_conn.cursor()
    cur.execute("SELECT biome_code FROM map_styles ORDER BY biome_code ASC")
    codes = [row["biome_code"] for row in cur.fetchall()]
    assert codes == list(range(1, 31))
    assert len(set(codes)) == 30


def test_required_columns_and_non_empty_values(db_conn: sqlite3.Connection) -> None:
    """Verify non-empty strings and valid color formats for all 30 records."""
    cur = db_conn.cursor()
    cur.execute("SELECT * FROM map_styles")
    rows = cur.fetchall()
    assert len(rows) == 30

    for r in rows:
        assert r["style_id"] and r["style_id"].startswith("STY_")
        assert r["name_vi"] and len(r["name_vi"].strip()) > 0
        assert r["name_en"] and len(r["name_en"].strip()) > 0
        assert r["theme_category"] in VALID_THEMES
        assert r["theme_category_name"] and len(r["theme_category_name"].strip()) > 0
        assert r["asset_dir"] and len(r["asset_dir"].strip()) > 0
        assert r["monster_family"] and len(r["monster_family"].strip()) > 0

        # Validate color formats
        for col in ("floor_color_hex", "wall_color_hex", "path_color_hex", "liquid_color_hex", "ambient_light_hex"):
            val = r[col]
            assert val is not None, f"Missing {col} for {r['style_id']}"
            assert HEX_COLOR_REGEX.match(val), f"Invalid HEX color '{val}' in {col} for {r['style_id']}"

        assert 0.0 <= r["fog_density"] <= 1.0
        assert r["movement_modifier"] > 0.0
        assert r["hazard_damage"] >= 0
        assert r["hazard_slow_factor"] >= 1.0


def test_theme_category_distribution_five_per_theme(db_conn: sqlite3.Connection) -> None:
    """Verify all 6 categories exist with exactly 5 styles each."""
    cur = db_conn.cursor()
    cur.execute("SELECT theme_category, count(*) AS cnt FROM map_styles GROUP BY theme_category")
    dist = {row["theme_category"]: row["cnt"] for row in cur.fetchall()}
    assert set(dist.keys()) == VALID_THEMES
    for theme, count in dist.items():
        assert count == 5, f"Theme {theme} expected 5 styles, found {count}"


def test_tile_mappings_table_integrity(db_conn: sqlite3.Connection) -> None:
    """Verify 600 tile mappings (20 per style) with valid filenames and fallbacks."""
    cur = db_conn.cursor()
    cur.execute("SELECT count(*) AS cnt FROM map_style_tiles")
    assert cur.fetchone()["cnt"] == 600

    cur.execute("SELECT style_id, count(*) AS cnt FROM map_style_tiles GROUP BY style_id")
    for row in cur.fetchall():
        assert row["cnt"] == 20, f"Style {row['style_id']} has {row['cnt']} tile mappings, expected 20"

    cur.execute("SELECT * FROM map_style_tiles LIMIT 20")
    for r in cur.fetchall():
        assert 0 <= r["tile_type_id"] <= 19
        assert r["texture_filename"].endswith(".png")
        assert r["normal_filename"].endswith(".png")
        assert HEX_COLOR_REGEX.match(r["base_color_fallback"])


def test_props_table_integrity(db_conn: sqlite3.Connection) -> None:
    """Verify decorative props integrity, valid weights, and placement rules."""
    cur = db_conn.cursor()
    cur.execute("SELECT count(*) AS cnt FROM map_style_props")
    assert cur.fetchone()["cnt"] >= 90

    cur.execute("SELECT * FROM map_style_props")
    allowed_rules = {"FLOOR_EDGE", "WALL_ADJACENT", "DEAD_END", "PATH_SIDE", "ANY_FLOOR", "ARENA_PERIMETER"}
    for p in cur.fetchall():
        assert 0.0 < p["spawn_weight"] <= 1.0
        assert p["placement_rule"] in allowed_rules
        assert p["footprint_w"] >= 1 and p["footprint_h"] >= 1
        assert p["sprite_path"].endswith(".png")


def test_foreign_key_pragmas_and_check(db_conn: sqlite3.Connection) -> None:
    """Verify that foreign keys pass with zero violations on existing database."""
    cur = db_conn.cursor()
    cur.execute("PRAGMA foreign_key_check")
    violations = cur.fetchall()
    assert len(violations) == 0, f"Foreign key check failed: {violations}"


def test_foreign_key_orphan_rejection(temp_test_db: sqlite3.Connection) -> None:
    """Verify inserting orphan rows into child tables raises IntegrityError."""
    cur = temp_test_db.cursor()
    # Inserting tile pointing to non-existent style must fail
    with pytest.raises(sqlite3.IntegrityError):
        cur.execute(
            "INSERT INTO map_style_tiles (style_id, tile_type_id, tile_type_name, texture_filename, "
            "normal_filename, base_color_fallback) VALUES ('STY_INVALID', 1, 'FLOOR', 'f.png', 'n.png', '#333333')"
        )

    # Inserting prop pointing to non-existent style must fail
    with pytest.raises(sqlite3.IntegrityError):
        cur.execute(
            "INSERT INTO map_style_props (prop_id, style_id, prop_name_vi, prop_name_en, sprite_key, "
            "sprite_path, spawn_weight, placement_rule) VALUES ('p_test', 'STY_INVALID', 'P', 'P', 'k', 'p.png', 0.5, 'ANY_FLOOR')"
        )


def test_foreign_key_cascade_deletion(temp_test_db: sqlite3.Connection) -> None:
    """Verify deleting a parent style cascades and deletes its tiles and props."""
    cur = temp_test_db.cursor()
    cur.execute(
        "INSERT INTO map_styles (style_id, biome_code, name_vi, name_en, theme_category, "
        "theme_category_name, description, floor_color_hex, wall_color_hex, path_color_hex, "
        "liquid_color_hex, ambient_light_hex, asset_dir, monster_family) "
        "VALUES ('STY_CASCADE_TEST', 199, 'Vi', 'En', 'HOANG_DA_DA_NGOAI', 'T', 'D', "
        "'#111111', '#222222', '#333333', '#444444', '#555555', 'dir', 'monsters')"
    )
    cur.execute(
        "INSERT INTO map_style_tiles (style_id, tile_type_id, tile_type_name, texture_filename, "
        "normal_filename, base_color_fallback) VALUES ('STY_CASCADE_TEST', 1, 'FLOOR', 'f.png', 'n.png', '#111111')"
    )
    cur.execute(
        "INSERT INTO map_style_props (prop_id, style_id, prop_name_vi, prop_name_en, sprite_key, "
        "sprite_path, spawn_weight, placement_rule) VALUES ('prop_cascade', 'STY_CASCADE_TEST', 'P', 'P', 'k', 'p.png', 0.5, 'ANY_FLOOR')"
    )
    temp_test_db.commit()

    cur.execute("DELETE FROM map_styles WHERE style_id = 'STY_CASCADE_TEST'")
    temp_test_db.commit()

    cur.execute("SELECT count(*) AS cnt FROM map_style_tiles WHERE style_id = 'STY_CASCADE_TEST'")
    assert cur.fetchone()["cnt"] == 0
    cur.execute("SELECT count(*) AS cnt FROM map_style_props WHERE style_id = 'STY_CASCADE_TEST'")
    assert cur.fetchone()["cnt"] == 0


def test_check_constraints_rejections(temp_test_db: sqlite3.Connection) -> None:
    """Verify CHECK constraints on biome_code, theme_category, fog_density, etc."""
    cur = temp_test_db.cursor()

    # Invalid biome_code = 0 (must be >= 1)
    with pytest.raises(sqlite3.IntegrityError):
        cur.execute(
            "INSERT INTO map_styles (style_id, biome_code, name_vi, name_en, theme_category, "
            "theme_category_name, description, floor_color_hex, wall_color_hex, path_color_hex, "
            "liquid_color_hex, ambient_light_hex, asset_dir, monster_family) "
            "VALUES ('STY_BAD_BIOME', 0, 'Vi', 'En', 'HOANG_DA_DA_NGOAI', 'T', 'D', '#111', '#222', '#333', '#444', '#555', 'a', 'm')"
        )

    # Invalid theme category
    with pytest.raises(sqlite3.IntegrityError):
        cur.execute(
            "INSERT INTO map_styles (style_id, biome_code, name_vi, name_en, theme_category, "
            "theme_category_name, description, floor_color_hex, wall_color_hex, path_color_hex, "
            "liquid_color_hex, ambient_light_hex, asset_dir, monster_family) "
            "VALUES ('STY_BAD_THEME', 150, 'Vi', 'En', 'INVALID_THEME', 'T', 'D', '#111', '#222', '#333', '#444', '#555', 'a', 'm')"
        )


def test_map_style_service_queries(map_service: MapStyleService) -> None:
    """Verify MapStyleService query methods for style, code, list, tiles, and props."""
    # Query by ID
    s1 = map_service.get_style("STY_01_HOANG_MANG_CO_LO")
    assert s1 is not None
    assert s1.biome_code == 6
    assert s1.name_vi == "Hoang Mãng Cổ Lộ"
    assert s1.palette.floor == "#3d3730"

    # Query by legacy alias
    for code, alias in LEGACY_ALIASES.items():
        s = map_service.get_style(alias)
        assert s is not None, f"Failed to retrieve style by legacy alias '{alias}'"
        assert s.biome_code == code, f"Alias {alias} expected code {code}, got {s.biome_code}"

    # Non-existent ID
    assert map_service.get_style("UNKNOWN_STYLE_XYZ") is None

    # Query by biome_code
    for code in range(1, 31):
        s = map_service.get_style_by_code(code)
        assert s is not None, f"Failed to retrieve style by biome code {code}"
        assert s.biome_code == code

    assert map_service.get_style_by_code(0) is None
    assert map_service.get_style_by_code(31) is None
    assert map_service.get_style_by_code(-1) is None

    # List styles
    all_styles = map_service.list_styles()
    assert len(all_styles) == 30
    assert [s.biome_code for s in all_styles] == list(range(1, 31))

    # Tiles query
    tiles = map_service.get_tiles_for_style("STY_01_HOANG_MANG_CO_LO")
    assert len(tiles) == 20
    assert all(isinstance(t, MapStyleTileMapping) for t in tiles)

    tile_floor = map_service.get_tile_mapping("STY_01_HOANG_MANG_CO_LO", 1)
    assert tile_floor is not None
    assert tile_floor.tile_code == 1
    assert tile_floor.tile_type_name == "FLOOR"

    # Props query
    props = map_service.get_props_for_style("STY_01_HOANG_MANG_CO_LO")
    assert len(props) >= 3
    assert all(isinstance(p, MapStylePropDefinition) for p in props)
    assert len(map_service.list_props("STY_01_HOANG_MANG_CO_LO")) == len(props)


def test_map_style_service_in_memory_instance() -> None:
    """Verify MapStyleService initialized with :memory: automatically seeds and operates."""
    mem_service = MapStyleService(db_path=":memory:")
    assert len(mem_service.list_styles()) == 30
    assert mem_service.get_style_by_code(1) is not None
    mem_service.close()