#!/usr/bin/env python3
"""
TMED-II Phase III — Installation Tracking DB
Creates SQLite schema and loads data from cross_reference_output.json + Maquinas sheet.
"""

import json
import sqlite3
from pathlib import Path

BASE = Path("/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project")
DB_PATH = BASE / "tmed2_install.db"
JSON_PATH = BASE / "cross_reference_output.json"

# Maquinas sheet data (pre-extracted from Google Sheets)
# Case Line items from Maquinas sheet
CASE_LINE = [
    ("1", "고속MCC(WH50T) E", "High-Speed MCC (WH50T) - E", 21.0, "~2485x5445x3500", "Jun 22"),
    ("2", "고속MCC(WH50T) E", "High-Speed MCC (WH50T) - E", 21.0, "~2485x5445x3500", "Jun 22"),
    ("3", "고속MCC(WH50T) D", "High-Speed MCC (WH50T) - D", 21.0, "~2485x5445x3500", "Jun 22"),
    ("4", "고속MCC(WH50T) E", "High-Speed MCC (WH50T) - E", 21.0, "~2485x5445x3500", "Jun 22"),
    ("5", "고속MCC(WH50TB) D", "High-Speed MCC (WH50TB) - D", 21.0, "~2740x5445x3508", "Jun 22"),
    ("14", "리크테스터 투입부", "Leak Tester Infeed", 2.1, "", "Jun 28"),
    ("", "LEAK TESTER 전체계 #1", "Leak Tester (full sys #1)", 2.63, "4150x3250x3650", "Jul 1"),
    ("", "LEAK TESTER TMED II ST", "Leak Tester (TMED-II stn)", 12.6, "1000x1000x1000", "Jul 1"),
    ("15", "자동창고 (S/CRANE)", "Auto Storage (S/CRANE)", 6.3, "600x7500x2700", "Jun 28"),
    ("", "자동창고 RACK", "Auto Storage RACK", 15.75, "", "Jun 28"),
    ("", "컨베어 (투입/반출)", "Conveyor (in/out)", 2.1, "", "Jun 28"),
    ("", "EMS 및 부자재", "EMS + Accessories", 5.25, "", "Jun 28"),
    ("", "조립공급 버퍼컨베어", "Assy Supply Buffer Conv.", 3.15, "", "Jun 28"),
    ("", "내시경검사 룸", "Endoscope Inspection Room", 0.11, "", "Jun 28"),
    ("", "완성검사 ST.", "Final Inspection ST.", 3.15, "520x1100x6880", "Jun 28"),
]

# Assembly Line items from Maquinas sheet
ASSEMBLY_LINE = [
    ("1", "Pallet Shift'g", "Pallet Shifting", 5.25, "", "Jul 4"),
    ("2", "Carrier Pack Assy", "Carrier Pack Assembly", 5.25, "", "Jul 4"),
    ("3", "HSG Tight. M/C #1", "HSG Tightening M/C #1", 6.3, "", "Jul 4"),
    ("4", "HSG Tight. M/C #2", "HSG Tightening M/C #2", 6.3, "", "Jul 4"),
    ("5", "P2 Stator Tight.", "P2 Stator Tightening M/C", 6.3, "", "Jul 4"),
    ("6", "WET LEAK TESTER #3", "Wet Leak Tester #3", 21.0, "", "Jul 8"),
    ("7", "DUST CAP VISION", "Dust Cap Vision", 0.53, "", "Jul 4"),
    ("8", "IVT TEST PALLET", "IVT Test Line Pallet", 2.1, "", "Jul 4"),
    ("9", "UNDRESS CTRL PANEL", "Undress Control Panel", 0.53, "", "Jul 4"),
    ("10", "M/C RETOOLING", "M/C Retooling Materials", 10.5, "", "Jul 4"),
    ("11", "IVT BUFFER CON'V", "IVT Buffer Conveyor", 7.35, "", "Jul 4"),
    ("12", "P2 ROTOR DISASSY", "P2 Rotor Disassemble M/C", 10.5, "", "Jul 4"),
    ("13", "M/C RETOOLING (EOL)", "M/C Retooling Materials", 31.5, "", "Jul 4"),
    ("14", "V/B TESTER RETOOL", "V/B Tester Retooling", 10.5, "", "Jul 4"),
    ("15", "EOL #5 AT", "End-of-Line #5 AT", 21.0, "", "Jul 4"),
    ("16", "EOL #5 FT", "End-of-Line #5 FT", 21.0, "", "Jul 5"),
    ("17", "EOL #6 AT", "End-of-Line #6 AT", 21.0, "", "Jul 6"),
    ("18", "EOL #6 FT", "End-of-Line #6 FT", 21.0, "", "Jul 7"),
]


def create_schema(conn):
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS containers (
            id INTEGER PRIMARY KEY,
            supplier_prefix TEXT NOT NULL,
            container_num TEXT NOT NULL,
            container_type TEXT,
            line TEXT,
            vessel TEXT,
            sailing_date TEXT,
            est_arrival TEXT,
            forklift_class TEXT,
            unload_day INTEGER,
            unloaded BOOLEAN DEFAULT 0,
            unload_date TEXT,
            UNIQUE(supplier_prefix, container_num)
        );

        CREATE TABLE IF NOT EXISTS machines (
            id INTEGER PRIMARY KEY,
            line TEXT NOT NULL,
            line_sub TEXT,
            korean_name TEXT NOT NULL,
            english_name TEXT NOT NULL,
            item_no TEXT,
            weight_net_t REAL,
            weight_gross_t REAL,
            dims_lxwxh TEXT,
            container_id INTEGER REFERENCES containers(id),
            supplier TEXT,
            arrival_group TEXT,
            received BOOLEAN DEFAULT 0,
            installed BOOLEAN DEFAULT 0,
            electrical BOOLEAN DEFAULT 0,
            air BOOLEAN DEFAULT 0,
            commissioned BOOLEAN DEFAULT 0,
            received_date TEXT,
            installed_date TEXT,
            commissioned_date TEXT,
            notes TEXT
        );

        CREATE TABLE IF NOT EXISTS machine_photos (
            id INTEGER PRIMARY KEY,
            machine_id INTEGER REFERENCES machines(id),
            photo_type TEXT NOT NULL,
            minio_bucket TEXT DEFAULT 'tmed2-photos',
            minio_key TEXT NOT NULL,
            uploaded_at TEXT DEFAULT (datetime('now'))
        );

        CREATE INDEX IF NOT EXISTS idx_machines_line ON machines(line);
        CREATE INDEX IF NOT EXISTS idx_machines_container ON machines(container_id);
        CREATE INDEX IF NOT EXISTS idx_containers_unloaded ON containers(unloaded);
        CREATE INDEX IF NOT EXISTS idx_photos_machine ON machine_photos(machine_id);
    """)


def load_motor_containers(conn, data):
    """Load Motor Line containers from cross_reference_output.json."""
    ct_rows = data["containers"][1:]  # skip header
    ct_id_map = {}  # container_num → db id

    for row in ct_rows:
        # Headers: Supplier, Container #, Line, Container Type, Vessel, Sailing, Est. Arrival,
        #          Items, Net Wt, Gross Wt, Heaviest, Heaviest Name, Forklift, Unload Day, Items List
        container_num = row[1]
        # Extract prefix from container_num (DK-1 → DK, HWIA-5 → HWIA)
        parts = container_num.split("-", 1)
        prefix = parts[0]

        cur = conn.execute("""
            INSERT INTO containers (supplier_prefix, container_num, container_type, line,
                                   vessel, sailing_date, est_arrival, forklift_class, unload_day)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (prefix, container_num, row[3], row[2], row[4], row[5], row[6], row[12], int(row[13])))
        ct_id_map[container_num] = cur.lastrowid

    return ct_id_map


def load_motor_machines(conn, data, ct_id_map):
    """Load Motor Line machines from cross_reference_output.json."""
    cr_rows = data["crossref"][1:]  # skip header
    count = 0

    for row in cr_rows:
        # Headers: Item No, Line, Korean Name, English Name, Our Weight NET (T), Arrival,
        #          Found?, Container #s, Package Net, Package Gross, Delta, Max Piece,
        #          Max Dims, Flag, Notes
        korean = row[2]
        english = row[3]
        weight_net = float(row[4]) if row[4] else None
        arrival = row[5]
        containers_str = row[7]
        pkg_gross = float(row[9]) if row[9] else None
        dims = row[12]
        line_sub = "Winding" if any(c.startswith("DK") for c in containers_str.split(", ")) else "Post-treatment"

        # Find first matching container
        container_id = None
        if containers_str:
            first_ct = containers_str.split(",")[0].strip()
            container_id = ct_id_map.get(first_ct)

        arrival_group = "May 20" if arrival == "may20" else "Jun 15"

        conn.execute("""
            INSERT INTO machines (line, line_sub, korean_name, english_name, item_no,
                                 weight_net_t, weight_gross_t, dims_lxwxh,
                                 container_id, supplier, arrival_group)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, ("Motor Line", line_sub, korean, english, row[0],
              weight_net, pkg_gross, dims, container_id,
              "DKTEC" if line_sub == "Winding" else "Hyundai WIA",
              arrival_group))
        count += 1

    return count


def load_case_assembly(conn):
    """Pre-load Case + Assembly machines from Maquinas sheet data."""
    count = 0

    for item_no, kr_name, en_name, weight, dims, arrival in CASE_LINE:
        conn.execute("""
            INSERT INTO machines (line, line_sub, korean_name, english_name, item_no,
                                 weight_gross_t, dims_lxwxh, arrival_group)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, ("Case Line", "B-Line CNC", kr_name, en_name, item_no, weight, dims, arrival))
        count += 1

    for item_no, kr_name, en_name, weight, dims, arrival in ASSEMBLY_LINE:
        conn.execute("""
            INSERT INTO machines (line, line_sub, korean_name, english_name, item_no,
                                 weight_gross_t, dims_lxwxh, arrival_group)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, ("Assembly Line", "Main + Test", kr_name, en_name, item_no, weight, dims, arrival))
        count += 1

    return count


def main():
    print(f"Creating DB at {DB_PATH}")

    # Remove existing DB if present
    if DB_PATH.exists():
        DB_PATH.unlink()

    conn = sqlite3.connect(str(DB_PATH))

    print("Creating schema...")
    create_schema(conn)

    print("Loading Motor Line data from cross_reference_output.json...")
    data = json.load(open(JSON_PATH, encoding="utf-8"))
    ct_map = load_motor_containers(conn, data)
    print(f"  Containers: {len(ct_map)}")

    motor_count = load_motor_machines(conn, data, ct_map)
    print(f"  Motor machines: {motor_count}")

    print("Pre-loading Case + Assembly from Maquinas sheet...")
    ca_count = load_case_assembly(conn)
    print(f"  Case + Assembly machines: {ca_count}")

    conn.commit()

    # Verification
    print("\n=== VERIFICATION ===")
    for line, in conn.execute("SELECT DISTINCT line FROM machines ORDER BY line"):
        total = conn.execute("SELECT count(*) FROM machines WHERE line=?", (line,)).fetchone()[0]
        print(f"  {line}: {total} machines")

    ct_total = conn.execute("SELECT count(*) FROM containers").fetchone()[0]
    print(f"  Containers: {ct_total}")
    print(f"  Photos: {conn.execute('SELECT count(*) FROM machine_photos').fetchone()[0]}")

    # Sample queries from handoff
    print("\n=== Progress Dashboard (empty — no work done yet) ===")
    for row in conn.execute("""
        SELECT line, count(*) total,
          sum(received) received, sum(installed) installed,
          sum(electrical) electrical, sum(commissioned) commissioned
        FROM machines GROUP BY line
    """):
        print(f"  {row[0]:20s} total={row[1]:3d} recv={row[2]} inst={row[3]} elec={row[4]} comm={row[5]}")

    conn.close()
    print(f"\nDB saved: {DB_PATH}")


if __name__ == "__main__":
    main()