from __future__ import annotations

import sqlite3
import time
from pathlib import Path


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", "JPIO", "FL")


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


def format_oe(value: str) -> str:
    compact = "".join(ch for ch in value.upper() if ch.isalnum())
    if len(compact) == 10:
        return f"{compact[:5]}-{compact[5:]}"
    return compact


def fixed_rows(path: Path, length: int):
    data = path.read_bytes()
    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 daihin_rows():
    for market in MARKETS:
        path = EPC_ROOT / market / "DAIHIN.DAT"
        if not path.is_file():
            continue
        for row_no, offset, row in fixed_rows(path, 41):
            old_raw = text(row[0:10])
            new_raw = text(row[17:27])
            if len(old_raw) != 10 or len(new_raw) != 10:
                continue
            yield (
                market,
                "DAIHIN.DAT",
                str(path),
                row_no,
                offset,
                41,
                format_oe(old_raw),
                old_raw,
                text(row[12:18]),
                format_oe(new_raw),
                new_raw,
                text(row[29:31]),
                text(row[31:39]),
                text(row[39:41]),
            )


def exec_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS meta (
          key TEXT PRIMARY KEY,
          value TEXT NOT NULL
        );

        DROP TABLE IF EXISTS epc_part_substitution;

        CREATE TABLE epc_part_substitution (
          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,
          old_oe TEXT NOT NULL,
          old_oe_raw TEXT NOT NULL,
          effective_to_raw TEXT NOT NULL,
          new_oe TEXT NOT NULL,
          new_oe_raw TEXT NOT NULL,
          relation_code TEXT NOT NULL,
          effective_from_raw TEXT NOT NULL,
          sequence_code TEXT NOT NULL
        );
        """
    )
    conn.commit()


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


def create_indexes(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE INDEX idx_part_sub_old ON epc_part_substitution(old_oe, market);
        CREATE INDEX idx_part_sub_new ON epc_part_substitution(new_oe, market);
        CREATE INDEX idx_part_sub_pair ON epc_part_substitution(old_oe, new_oe, market);
        """
    )
    conn.commit()


def main() -> None:
    started = time.time()
    conn = sqlite3.connect(DB_PATH)
    try:
        exec_schema(conn)
        count = insert_many(
            conn,
            """
            INSERT INTO epc_part_substitution
            (market, source, source_file, row_no, byte_offset, record_len,
             old_oe, old_oe_raw, effective_to_raw, new_oe, new_oe_raw,
             relation_code, effective_from_raw, sequence_code)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            daihin_rows(),
        )
        create_indexes(conn)
        elapsed = time.time() - started
        conn.executemany(
            "INSERT OR REPLACE INTO meta(key, value) VALUES (?, ?)",
            [
                ("count_epc_part_substitution", str(count)),
                ("substitution_extended_at", time.strftime("%Y-%m-%d %H:%M:%S")),
                ("substitution_build_seconds", f"{elapsed:.2f}"),
            ],
        )
        conn.commit()
        print(f"epc_part_substitution={count}")
        print(f"seconds={elapsed:.2f}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()