#!/usr/bin/env python3
"""
Rebuild ammo_builtin.db from ammo_builtin.csv.

Room schema: BuiltinAmmoDatabase v4
Identity hash: auto-read from app/schemas/.../BuiltinAmmoDatabase/4.json

*** After any schema change: ***
  1. Build the project (./gradlew :app:compileDebugKotlin or full build)
  2. Re-run this script — the identity hash is read automatically from 4.json.

Run from this directory:
    python3 build-ammo-db.py

Outputs:  databases/ammo_builtin.db
"""

import csv
import json
import os
import sys
import sqlite3

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
CSV_PATH   = os.path.join(SCRIPT_DIR, "ammo_builtin.csv")
DB_DIR     = os.path.join(SCRIPT_DIR, "databases")
DB_PATH    = os.path.join(DB_DIR, "ammo_builtin.db")

# Auto-read identity hash from the Room-generated schema file.
SCHEMA_FILE = os.path.join(
    SCRIPT_DIR, "..", "..", "..", "..", "app", "schemas",
    "com.coldboreballisticsllc.scopedope.data.ammo.builtin.BuiltinAmmoDatabase",
    "4.json",
)
SCHEMA_FILE = os.path.normpath(SCHEMA_FILE)

IDENTITY_HASH = None
if os.path.exists(SCHEMA_FILE):
    try:
        with open(SCHEMA_FILE, encoding="utf-8") as f:
            schema = json.load(f)
        IDENTITY_HASH = schema.get("database", {}).get("identityHash")
    except Exception as e:
        print(f"WARNING: Could not parse {SCHEMA_FILE}: {e}")

if not IDENTITY_HASH:
    print("ERROR: Identity hash not found.")
    print(f"  Looked for: {SCHEMA_FILE}")
    print("  Build the project first: ./gradlew :app:compileDebugKotlin")
    print("  Then re-run this script.")
    sys.exit(1)

# Validate CSV columns before touching the database.
REQUIRED_COLUMNS = {"caliber", "name", "velocity_fps", "bc_g1", "bc_g7"}

with open(CSV_PATH, newline="", encoding="utf-8") as fh:
    reader = csv.DictReader(fh)
    field_names = set(reader.fieldnames or [])
    missing = REQUIRED_COLUMNS - field_names
    if missing:
        print(f"ERROR: ammo_builtin.csv is missing required columns: {missing}")
        sys.exit(1)

    rows = []
    for i, row in enumerate(reader, start=2):   # start=2: row 1 is the header
        try:
            rows.append((
                row["caliber"],
                row["name"],
                int(row["velocity_fps"]),
                float(row["bc_g1"]),
                float(row["bc_g7"]),
                0,                                              # is_user_defined
                float(row.get("bullet_weight_gr") or 0.0),
                float(row.get("mv_sensitivity_fps_per_f") or 0.0),
                float(row.get("mv_reference_temp_f") or 59.0),
                float(row.get("form_factor") or 0.0),
                float(row.get("bullet_diameter_mm") or 0.0),
                float(row.get("bullet_length_mm") or 0.0),
            ))
        except (ValueError, KeyError) as e:
            print(f"ERROR: CSV row {i} is malformed: {e}")
            print(f"  Row data: {dict(row)}")
            sys.exit(1)

os.makedirs(DB_DIR, exist_ok=True)

if os.path.exists(DB_PATH):
    os.remove(DB_PATH)

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

cur.executescript("""
CREATE TABLE IF NOT EXISTS `ammo` (
    `id`                       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `caliber`                  TEXT    NOT NULL,
    `name`                     TEXT    NOT NULL,
    `velocity_fps`             INTEGER NOT NULL,
    `bc_g1`                    REAL    NOT NULL,
    `bc_g7`                    REAL    NOT NULL,
    `is_user_defined`          INTEGER NOT NULL,
    `bullet_weight_gr`         REAL    NOT NULL DEFAULT 0.0,
    `mv_sensitivity_fps_per_f` REAL    NOT NULL DEFAULT 0.0,
    `mv_reference_temp_f`      REAL    NOT NULL DEFAULT 59.0,
    `form_factor`              REAL    NOT NULL DEFAULT 0.0,
    `bullet_diameter_mm`       REAL    NOT NULL DEFAULT 0.0,
    `bullet_length_mm`         REAL    NOT NULL DEFAULT 0.0
);

CREATE INDEX IF NOT EXISTS `index_ammo_caliber`
    ON `ammo` (`caliber`);

CREATE UNIQUE INDEX IF NOT EXISTS `index_ammo_caliber_name`
    ON `ammo` (`caliber`, `name`);

CREATE TABLE IF NOT EXISTS room_master_table (
    id            INTEGER PRIMARY KEY,
    identity_hash TEXT
);

PRAGMA user_version = 4;
""")

cur.execute(
    "INSERT OR REPLACE INTO room_master_table (id, identity_hash) VALUES (42, ?)",
    (IDENTITY_HASH,),
)

cur.executemany(
    "INSERT INTO ammo "
    "(caliber, name, velocity_fps, bc_g1, bc_g7, is_user_defined, "
    "bullet_weight_gr, mv_sensitivity_fps_per_f, mv_reference_temp_f, "
    "form_factor, bullet_diameter_mm, bullet_length_mm) "
    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    rows,
)

conn.commit()
conn.close()

print(f"Built {DB_PATH}")
print(f"  {len(rows)} ammo entries")
print(f"  Identity hash: {IDENTITY_HASH}")
