"""
Map Style Relational Schema and DDL for FreeExile.
Defines SQLite tables with strict foreign keys, CHECK constraints, WAL mode, and indices.
"""

from __future__ import annotations
import os
import sqlite3
from pathlib import Path
from typing import Union

SQL_PRAGMA_SETUP = """
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
"""

SQL_CREATE_TABLES = """
CREATE TABLE IF NOT EXISTS map_styles (
    style_id TEXT PRIMARY KEY,
    biome_code INTEGER NOT NULL UNIQUE CHECK (biome_code BETWEEN 1 AND 255),
    name_vi TEXT NOT NULL,
    name_en TEXT NOT NULL,
    theme_category TEXT NOT NULL CHECK (theme_category IN (
        'HOANG_DA_DA_NGOAI', 'DAM_LAY_DOC_CHUONG', 'HEM_NUI_COT_MAC',
        'HAM_MO_TA_QUAI', 'THAN_DIEN_PHE_TICH', 'HU_KHONG_DI_BIEN'
    )),
    theme_category_name TEXT NOT NULL,
    description TEXT NOT NULL,
    floor_color_hex TEXT NOT NULL,
    wall_color_hex TEXT NOT NULL,
    path_color_hex TEXT NOT NULL,
    liquid_color_hex TEXT NOT NULL,
    ambient_light_hex TEXT NOT NULL,
    fog_density REAL NOT NULL DEFAULT 0.30 CHECK (fog_density BETWEEN 0.0 AND 1.0),
    movement_modifier REAL NOT NULL DEFAULT 1.0 CHECK (movement_modifier > 0.0),
    hazard_damage INTEGER NOT NULL DEFAULT 0 CHECK (hazard_damage >= 0),
    hazard_slow_factor REAL NOT NULL DEFAULT 1.0 CHECK (hazard_slow_factor >= 1.0),
    asset_dir TEXT NOT NULL,
    monster_family TEXT NOT NULL,
    legacy_alias TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS map_style_tiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    style_id TEXT NOT NULL REFERENCES map_styles(style_id) ON DELETE CASCADE,
    tile_type_id INTEGER NOT NULL CHECK (tile_type_id BETWEEN 0 AND 19),
    tile_type_name TEXT NOT NULL,
    texture_filename TEXT NOT NULL,
    normal_filename TEXT NOT NULL,
    texture_width INTEGER NOT NULL DEFAULT 128 CHECK (texture_width IN (64, 128, 256, 512)),
    texture_height INTEGER NOT NULL DEFAULT 64 CHECK (texture_height IN (32, 64, 128, 256, 512)),
    is_seamless INTEGER NOT NULL DEFAULT 1 CHECK (is_seamless IN (0, 1)),
    elevation_px INTEGER NOT NULL DEFAULT 0,
    roughness REAL NOT NULL DEFAULT 0.8 CHECK (roughness BETWEEN 0.0 AND 1.0),
    base_color_fallback TEXT NOT NULL,
    UNIQUE(style_id, tile_type_id)
);

CREATE TABLE IF NOT EXISTS map_style_props (
    prop_id TEXT PRIMARY KEY,
    style_id TEXT NOT NULL REFERENCES map_styles(style_id) ON DELETE CASCADE,
    prop_name_vi TEXT NOT NULL,
    prop_name_en TEXT NOT NULL,
    sprite_key TEXT NOT NULL,
    sprite_path TEXT NOT NULL,
    spawn_weight REAL NOT NULL CHECK (spawn_weight > 0.0 AND spawn_weight <= 1.0),
    placement_rule TEXT NOT NULL CHECK (placement_rule IN (
        'FLOOR_EDGE', 'WALL_ADJACENT', 'DEAD_END', 'PATH_SIDE', 'ANY_FLOOR', 'ARENA_PERIMETER'
    )),
    blocks_movement INTEGER NOT NULL DEFAULT 0 CHECK (blocks_movement IN (0, 1)),
    footprint_w INTEGER NOT NULL DEFAULT 1 CHECK (footprint_w >= 1),
    footprint_h INTEGER NOT NULL DEFAULT 1 CHECK (footprint_h >= 1),
    elevation_z INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_map_styles_biome_code ON map_styles(biome_code);
CREATE INDEX IF NOT EXISTS idx_map_styles_theme ON map_styles(theme_category);
CREATE INDEX IF NOT EXISTS idx_map_style_tiles_style ON map_style_tiles(style_id);
CREATE INDEX IF NOT EXISTS idx_map_style_props_style ON map_style_props(style_id);
"""


def init_db(db_path: Union[Path, str]) -> sqlite3.Connection:
    """Initialize SQLite database with strict pragmas, tables, and indices idempotently."""
    path_str = str(db_path)
    if path_str != ":memory:":
        parent = os.path.dirname(os.path.abspath(path_str))
        if parent:
            os.makedirs(parent, exist_ok=True)
    conn = sqlite3.connect(path_str)
    conn.row_factory = sqlite3.Row
    conn.executescript(SQL_PRAGMA_SETUP)
    conn.executescript(SQL_CREATE_TABLES)
    conn.commit()
    return conn