from __future__ import annotations

import sqlite3
from pathlib import Path
import re
import time


EPC_ROOT = Path(r"D:\TMCEPCW3\epcdata")
OUT_DIR = Path(__file__).resolve().parent / "toyota_epc_output"
DB_PATH = OUT_DIR / "toyota_epc_validation.sqlite"
MARKETS = ["EU", "GR", "US", "JP"]


def decode_acd(data: bytes) -> bytes:
    return bytes(byte ^ 0xC8 for byte in data)


def text(raw: bytes) -> str:
    return raw.decode("latin1", errors="ignore").strip()


def rows_fixed(path: Path, length: int, encoded: bool = False):
    data = path.read_bytes()
    if encoded:
        data = decode_acd(data)
    for row_no, offset in enumerate(range(0, len(data), length), start=1):
        row = data[offset : offset + length]
        if len(row) == length:
            yield row_no, offset, row


def clean_frame(value: str) -> tuple[str, str]:
    compact = " ".join(value.split())
    match = re.match(r"^([A-Z0-9]+)\s*-\s*([A-Z0-9]+)$", compact)
    if match:
        return match.group(1), match.group(2)
    match = re.match(r"^([A-Z0-9]+)$", compact)
    if match:
        return match.group(1), ""
    return "", ""


def serial_int(value: str) -> int | None:
    digits = "".join(ch for ch in value if ch.isdigit())
    return int(digits) if digits else None


def exec_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;

        DROP TABLE IF EXISTS meta;
        DROP TABLE IF EXISTS epc_vin_index;
        DROP TABLE IF EXISTS epc_vehicle_detail;
        DROP TABLE IF EXISTS epc_vehicle_name;
        DROP TABLE IF EXISTS epc_frame_month_point;
        DROP TABLE IF EXISTS epc_catalog_frame_range;

        CREATE TABLE meta (
          key TEXT PRIMARY KEY,
          value TEXT NOT NULL
        );

        CREATE TABLE epc_vin_index (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          source TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          catalog TEXT NOT NULL,
          vin_key TEXT NOT NULL,
          model TEXT NOT NULL
        );

        CREATE TABLE epc_vehicle_detail (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          source TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          catalog TEXT NOT NULL,
          vin_key TEXT NOT NULL,
          model TEXT NOT NULL,
          production_start TEXT NOT NULL,
          production_end TEXT NOT NULL,
          frame_code TEXT NOT NULL,
          spec_code TEXT NOT NULL,
          engine_epc TEXT NOT NULL,
          body TEXT NOT NULL,
          transmission TEXT NOT NULL,
          gear TEXT NOT NULL,
          steering TEXT NOT NULL,
          door TEXT NOT NULL,
          grade TEXT NOT NULL,
          turbo TEXT NOT NULL,
          destination TEXT NOT NULL
        );

        CREATE TABLE epc_vehicle_name (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          series_code TEXT NOT NULL,
          vehicle_name_epc TEXT NOT NULL,
          catalog TEXT NOT NULL,
          model_family TEXT NOT NULL,
          vehicle_range_start TEXT NOT NULL,
          vehicle_range_end TEXT NOT NULL,
          release_code TEXT NOT NULL,
          flag TEXT NOT NULL
        );

        CREATE TABLE epc_frame_month_point (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          frame_code TEXT NOT NULL,
          year TEXT NOT NULL,
          month TEXT NOT NULL,
          serial_raw TEXT NOT NULL,
          serial_int INTEGER NOT NULL,
          suffix_raw TEXT NOT NULL
        );

        CREATE TABLE epc_catalog_frame_range (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          catalog TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          row_code TEXT NOT NULL,
          frame_code TEXT NOT NULL,
          start_serial TEXT NOT NULL,
          end_serial TEXT NOT NULL,
          start_serial_int INTEGER,
          end_serial_int INTEGER,
          start_frame_raw TEXT NOT NULL,
          end_frame_raw TEXT NOT NULL,
          plant TEXT NOT NULL
        );
        """
    )


def insert_many(conn: sqlite3.Connection, sql: str, rows) -> int:
    total = 0
    batch = []
    for row in rows:
        batch.append(row)
        if len(batch) >= 5000:
            conn.executemany(sql, batch)
            total += len(batch)
            batch.clear()
    if batch:
        conn.executemany(sql, batch)
        total += len(batch)
    conn.commit()
    return total


def vin_index_rows():
    for market in MARKETS:
        for source in ["JOHOCL", "JOHOVN"]:
            path = EPC_ROOT / market / f"{source}.ACD"
            if not path.exists():
                continue
            for row_no, offset, row in rows_fixed(path, 35, encoded=True):
                yield (
                    market,
                    source,
                    str(path),
                    row_no,
                    offset,
                    35,
                    text(row[0:6]),
                    text(row[6:15]),
                    text(row[15:35]),
                )


def vehicle_detail_rows():
    for market in MARKETS:
        for source in ["JOHOJP", "JOHOKT"]:
            path = EPC_ROOT / market / f"{source}.ACD"
            if not path.exists():
                continue
            for row_no, offset, row in rows_fixed(path, 186, encoded=True):
                yield (
                    market,
                    source,
                    str(path),
                    row_no,
                    offset,
                    186,
                    text(row[0:6]),
                    text(row[6:15]),
                    text(row[15:35]),
                    text(row[35:41]),
                    text(row[41:47]),
                    text(row[47:54]),
                    text(row[54:62]),
                    text(row[62:82]),
                    text(row[82:92]),
                    text(row[92:102]),
                    text(row[102:107]),
                    text(row[107:112]),
                    text(row[112:117]),
                    text(row[117:122]),
                    text(row[122:127]),
                    text(row[127:132]),
                )


def vehicle_name_rows():
    for market in MARKETS:
        path = EPC_ROOT / market / "SHAMEI.ACD"
        if not path.exists():
            continue
        for row_no, offset, row in rows_fixed(path, 111, encoded=True):
            yield (
                market,
                str(path),
                row_no,
                offset,
                111,
                text(row[0:3]),
                text(row[3:23]),
                text(row[23:29]),
                text(row[29:79]),
                text(row[79:85]),
                text(row[85:91]),
                text(row[91:99]),
                text(row[99:101]),
            )


def frame_month_rows():
    for market in MARKETS:
        path = EPC_ROOT / market / "FRAMNO.DAT"
        if not path.exists():
            continue
        for row_no, offset, row in rows_fixed(path, 99, encoded=False):
            frame_code = text(row[0:7])
            year = text(row[7:11])
            suffix_raw = text(row[95:99])
            for month in range(1, 13):
                raw = text(row[11 + (month - 1) * 7 : 11 + month * 7])
                value = serial_int(raw)
                if value is None:
                    continue
                yield (
                    market,
                    str(path),
                    row_no,
                    offset,
                    99,
                    frame_code,
                    year,
                    f"{month:02d}",
                    raw,
                    value,
                    suffix_raw,
                )


def catalog_frame_range_rows():
    for market in MARKETS:
        market_dir = EPC_ROOT / market
        if not market_dir.exists():
            continue
        for catalog_dir in sorted(path for path in market_dir.iterdir() if path.is_dir()):
            for path in sorted(catalog_dir.glob("KRK*.DAT")):
                for row_no, offset, row in rows_fixed(path, 68, encoded=False):
                    start_raw = text(row[8:23])
                    end_raw = text(row[28:43])
                    start_frame, start_serial = clean_frame(start_raw)
                    end_frame, end_serial = clean_frame(end_raw)
                    frame_code = start_frame or end_frame
                    yield (
                        market,
                        catalog_dir.name,
                        str(path),
                        row_no,
                        offset,
                        68,
                        text(row[0:8]),
                        frame_code,
                        start_serial,
                        end_serial,
                        serial_int(start_serial),
                        serial_int(end_serial),
                        start_raw,
                        end_raw,
                        text(row[48:68]),
                    )


def create_indexes(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE INDEX idx_vin_index_key ON epc_vin_index(vin_key, market);
        CREATE INDEX idx_vin_index_model ON epc_vin_index(market, catalog, model, vin_key);
        CREATE INDEX idx_vehicle_detail_main ON epc_vehicle_detail(market, catalog, model, vin_key);
        CREATE INDEX idx_vehicle_detail_frame ON epc_vehicle_detail(market, frame_code);
        CREATE INDEX idx_vehicle_name_catalog ON epc_vehicle_name(market, catalog);
        CREATE INDEX idx_frame_month ON epc_frame_month_point(market, frame_code, serial_int);
        CREATE INDEX idx_frame_month_ym ON epc_frame_month_point(market, frame_code, year, month);
        CREATE INDEX idx_catalog_frame ON epc_catalog_frame_range(market, catalog, frame_code);
        """
    )
    conn.commit()


def table_count(conn: sqlite3.Connection, table: str) -> int:
    return conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]


def main() -> None:
    if not EPC_ROOT.exists():
        raise SystemExit(f"EPC root not found: {EPC_ROOT}")
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    if DB_PATH.exists():
        DB_PATH.unlink()

    started = time.time()
    conn = sqlite3.connect(DB_PATH)
    try:
        exec_schema(conn)
        conn.executemany(
            "INSERT INTO meta(key, value) VALUES (?, ?)",
            [
                ("epc_root", str(EPC_ROOT)),
                ("created_at", time.strftime("%Y-%m-%d %H:%M:%S")),
                ("purpose", "Toyota EPC local VIN validation database, EPC raw fields only"),
            ],
        )

        counts = {}
        counts["epc_vin_index"] = insert_many(
            conn,
            """
            INSERT INTO epc_vin_index
            (market, source, source_file, row_no, byte_offset, record_len, catalog, vin_key, model)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            vin_index_rows(),
        )
        counts["epc_vehicle_detail"] = insert_many(
            conn,
            """
            INSERT INTO epc_vehicle_detail
            (market, source, source_file, row_no, byte_offset, record_len, catalog, vin_key, model,
             production_start, production_end, frame_code, spec_code, engine_epc, body, transmission,
             gear, steering, door, grade, turbo, destination)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            vehicle_detail_rows(),
        )
        counts["epc_vehicle_name"] = insert_many(
            conn,
            """
            INSERT INTO epc_vehicle_name
            (market, source_file, row_no, byte_offset, record_len, series_code, vehicle_name_epc,
             catalog, model_family, vehicle_range_start, vehicle_range_end, release_code, flag)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            vehicle_name_rows(),
        )
        counts["epc_frame_month_point"] = insert_many(
            conn,
            """
            INSERT INTO epc_frame_month_point
            (market, source_file, row_no, byte_offset, record_len, frame_code, year, month,
             serial_raw, serial_int, suffix_raw)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            frame_month_rows(),
        )
        counts["epc_catalog_frame_range"] = insert_many(
            conn,
            """
            INSERT INTO epc_catalog_frame_range
            (market, catalog, source_file, row_no, byte_offset, record_len, row_code, frame_code,
             start_serial, end_serial, start_serial_int, end_serial_int, start_frame_raw,
             end_frame_raw, plant)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            catalog_frame_range_rows(),
        )
        create_indexes(conn)
        elapsed = time.time() - started
        conn.executemany(
            "INSERT INTO meta(key, value) VALUES (?, ?)",
            [(f"count_{key}", str(value)) for key, value in counts.items()]
            + [("build_seconds", f"{elapsed:.2f}")],
        )
        conn.commit()
        print(f"db={DB_PATH}")
        for table in counts:
            print(f"{table}={table_count(conn, table)}")
        print(f"seconds={elapsed:.2f}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()