"""
Forensic Database Audit Script for data/map_styles.db.
Verifies PRAGMA integrity_check, foreign keys, journal_mode,
table schemas, record counts, palette formats, and constraint enforcement.
"""

import sqlite3
import os
import sys

DB_PATH = r"c:\Projects\FreeExile\data\map_styles.db"

def audit_database():
    if not os.path.exists(DB_PATH):
        print(f"FATAL: Database file does not exist: {DB_PATH}")
        sys.exit(1)

    db_size = os.path.getsize(DB_PATH)
    print(f"Database file size: {db_size} bytes")

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    # 1. PRAGMA integrity_check
    cur.execute("PRAGMA integrity_check;")
    integrity = cur.fetchall()
    print("PRAGMA integrity_check:", integrity)
    if integrity != [("ok",)]:
        print("VIOLATION: Database integrity check failed!")
        sys.exit(2)

    # 2. PRAGMA foreign_keys & foreign_key_check
    cur.execute("PRAGMA foreign_keys = ON;")
    cur.execute("PRAGMA foreign_key_check;")
    fk_violations = cur.fetchall()
    print("PRAGMA foreign_key_check:", fk_violations)
    if fk_violations:
        print(f"VIOLATION: Foreign key violations detected: {fk_violations}")
        sys.exit(3)

    # 3. PRAGMA journal_mode
    cur.execute("PRAGMA journal_mode;")
    jmode = cur.fetchone()[0]
    print("PRAGMA journal_mode:", jmode)
    if jmode.lower() != "wal":
        print("VIOLATION: Journal mode is not WAL!")
        sys.exit(3)

    # 4. Table counts
    cur.execute("SELECT count(*) FROM map_styles;")
    styles_count = cur.fetchone()[0]

    cur.execute("SELECT count(*) FROM map_style_tiles;")
    tiles_count = cur.fetchone()[0]

    cur.execute("SELECT count(*) FROM map_style_props;")
    props_count = cur.fetchone()[0]

    print(f"map_styles count: {styles_count} (Expected: 30)")
    print(f"map_style_tiles count: {tiles_count} (Expected: 600)")
    print(f"map_style_props count: {props_count} (Expected: 90)")

    if styles_count != 30 or tiles_count != 600 or props_count != 90:
        print(f"VIOLATION: Row count mismatch! styles={styles_count}, tiles={tiles_count}, props={props_count}")
        sys.exit(4)

    # 5. Data validation in map_styles
    cur.execute("SELECT style_id, biome_code, floor_color_hex, wall_color_hex, path_color_hex, liquid_color_hex, ambient_light_hex FROM map_styles ORDER BY biome_code;")
    styles_rows = cur.fetchall()
    codes = [r[1] for r in styles_rows]
    expected_codes = list(range(1, 31))
    if codes != expected_codes:
        print(f"VIOLATION: Biome codes do not cover 1..30 continuously! Found: {codes}")
        sys.exit(5)

    # Validate hex format
    for r in styles_rows:
        s_id, b_code, fl, wl, pa, lq, am = r
        for col, val in [("floor", fl), ("wall", wl), ("path", pa), ("liquid", lq), ("ambient", am)]:
            if not (val.startswith("#") and len(val) in (7, 9)):
                print(f"VIOLATION: Invalid hex color in {s_id} for {col}: {val}")
                sys.exit(6)

    # 6. Validate map_style_tiles distribution
    cur.execute("SELECT style_id, count(tile_type_id), min(tile_type_id), max(tile_type_id) FROM map_style_tiles GROUP BY style_id;")
    tile_groups = cur.fetchall()
    for tg in tile_groups:
        s_id, count, min_tc, max_tc = tg
        if count != 20 or min_tc != 0 or max_tc != 19:
            print(f"VIOLATION: Tile mapping in style {s_id} has count {count}, min {min_tc}, max {max_tc} (expected 20 tiles, 0..19)")
            sys.exit(7)

    # 7. Validate map_style_props distribution
    cur.execute("SELECT style_id, count(prop_id) FROM map_style_props GROUP BY style_id;")
    prop_groups = cur.fetchall()
    for pg in prop_groups:
        s_id, count = pg
        if count != 3:
            print(f"VIOLATION: Prop definitions in style {s_id} count {count} (expected 3)")
            sys.exit(8)

    # 8. Test constraint enforcement in a rollback transaction
    try:
        # Invalid style_id in map_style_tiles
        cur.execute("INSERT INTO map_style_tiles (style_id, tile_type_id, tile_type_name, texture_filename, normal_filename, base_color_fallback) VALUES ('NON_EXISTENT', 0, 'VOID', 'floor.png', 'floor_normal.png', '#000000');")
        print("VIOLATION: Foreign key constraint failed to prevent invalid style_id insertion into map_style_tiles!")
        sys.exit(9)
    except sqlite3.IntegrityError:
        print("Verified FK constraint on map_style_tiles: PASS")

    try:
        # Invalid biome_code (out of range: 999 violates CHECK (biome_code BETWEEN 1 AND 255))
        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 ('TEST_INVALID', 999, 'Test', 'Test', 'HOANG_DA_DA_NGOAI', 'Theme', 'Desc', '#000000', '#000000', '#000000', '#000000', '#000000', 'dir', 'fam');")
        print("VIOLATION: CHECK constraint failed to prevent out-of-range biome_code 999!")
        sys.exit(10)
    except sqlite3.IntegrityError:
        print("Verified CHECK constraint on biome_code: PASS")

    try:
        # Invalid theme_category violates CHECK
        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 ('TEST_INVALID_THEME', 99, 'Test', 'Test', 'INVALID_THEME', 'Theme', 'Desc', '#000000', '#000000', '#000000', '#000000', '#000000', 'dir', 'fam');")
        print("VIOLATION: CHECK constraint failed to prevent invalid theme_category!")
        sys.exit(11)
    except sqlite3.IntegrityError:
        print("Verified CHECK constraint on theme_category: PASS")

    conn.rollback()
    conn.close()

    print("\nOVERALL DATABASE VERDICT: CLEAN")

if __name__ == "__main__":
    audit_database()