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")

HINMEI_FILES = (
    ("HINMEI01.DAT", "zh_cn", "gbk"),
    ("HINMEI03.DAT", "latin_03", "latin1"),
    ("HINMEI04.DAT", "latin_04", "latin1"),
    ("HINMEI05.DAT", "latin_05", "latin1"),
)


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 decode(raw: bytes, encoding: str = "latin1") -> str:
    return raw.decode(encoding, errors="ignore").replace("\x00", "").strip()


def normalize_oe(value: str) -> str:
    return "".join(ch for ch in value.upper() if ch.isalnum())


def format_oe(value: str) -> str:
    compact = normalize_oe(value)
    if len(compact) == 10:
        return f"{compact[:5]}-{compact[5:]}"
    return compact


def iter_catalog_dirs(market_dir: Path):
    if not market_dir.is_dir():
        return
    for path in sorted(market_dir.iterdir()):
        if path.is_dir():
            yield path


def part_index_rows():
    for market in MARKETS:
        market_dir = EPC_ROOT / market
        for catalog_dir in iter_catalog_dirs(market_dir):
            for path in sorted(catalog_dir.glob("FGI*.DAT")):
                for row_no, offset, row in fixed_rows(path, 22):
                    oe_raw = decode(row[0:10])
                    oe_compact = normalize_oe(oe_raw)
                    if len(oe_compact) != 10:
                        continue
                    yield (
                        market,
                        catalog_dir.name,
                        str(path),
                        row_no,
                        offset,
                        22,
                        format_oe(oe_compact),
                        oe_compact,
                        decode(row[12:18]),
                        decode(row[18:22]),
                    )


def part_name_rows():
    for market in MARKETS:
        market_dir = EPC_ROOT / market
        if not market_dir.is_dir():
            continue
        for file_name, language_code, encoding in HINMEI_FILES:
            path = market_dir / file_name
            if not path.is_file():
                continue
            for row_no, offset, row in fixed_rows(path, 66):
                reference_code = decode(row[0:6], encoding)
                part_name = decode(row[6:66], encoding)
                if not reference_code or not part_name:
                    continue
                yield (
                    market,
                    file_name,
                    language_code,
                    encoding,
                    str(path),
                    row_no,
                    offset,
                    66,
                    reference_code,
                    part_name,
                )


def figure_image_rows():
    for market in MARKETS:
        market_dir = EPC_ROOT / market
        for catalog_dir in iter_catalog_dirs(market_dir):
            for path in sorted(catalog_dir.glob("BZI*.DAT")):
                for row_no, offset, row in fixed_rows(path, 37):
                    text = row[:34].decode("ascii", errors="ignore")
                    figure_group = text[0:4].strip()
                    image_code = text[8:15].strip()
                    if not figure_group or not image_code:
                        continue
                    yield (
                        market,
                        catalog_dir.name,
                        str(path),
                        row_no,
                        offset,
                        37,
                        figure_group,
                        text[4:8].strip(),
                        image_code,
                        text[15:21].strip(),
                        text[21:27].strip(),
                        text[27:30].strip(),
                        text[30:34].strip(),
                        int.from_bytes(row[34:37], "little"),
                    )


def image_index_rows():
    for market in MARKETS:
        market_dir = EPC_ROOT / market
        for catalog_dir in iter_catalog_dirs(market_dir):
            for path in sorted(catalog_dir.glob("EMI*.DAT")):
                for image_index, (row_no, offset, row) in enumerate(fixed_rows(path, 12)):
                    text = row.decode("ascii", errors="ignore")
                    image_code = text[5:12].strip()
                    if not image_code:
                        continue
                    yield (
                        market,
                        catalog_dir.name,
                        str(path),
                        row_no,
                        offset,
                        12,
                        text[0:1].strip(),
                        text[1:5].strip(),
                        image_code,
                        image_index,
                    )


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 exec_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;

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

        DROP TABLE IF EXISTS epc_part_index;
        DROP TABLE IF EXISTS epc_part_name;
        DROP TABLE IF EXISTS epc_figure_image;
        DROP TABLE IF EXISTS epc_image_index;

        CREATE TABLE epc_part_index (
          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,
          oe_no TEXT NOT NULL,
          oe_raw TEXT NOT NULL,
          reference_code TEXT NOT NULL,
          figure_group TEXT NOT NULL
        );

        CREATE TABLE epc_part_name (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          market TEXT NOT NULL,
          language_file TEXT NOT NULL,
          language_code TEXT NOT NULL,
          encoding TEXT NOT NULL,
          source_file TEXT NOT NULL,
          row_no INTEGER NOT NULL,
          byte_offset INTEGER NOT NULL,
          record_len INTEGER NOT NULL,
          reference_code TEXT NOT NULL,
          part_name TEXT NOT NULL
        );

        CREATE TABLE epc_figure_image (
          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,
          figure_group TEXT NOT NULL,
          sequence TEXT NOT NULL,
          image_code TEXT NOT NULL,
          start_ym TEXT NOT NULL,
          end_ym TEXT NOT NULL,
          count_code TEXT NOT NULL,
          option_code TEXT NOT NULL,
          note_index INTEGER NOT NULL
        );

        CREATE TABLE epc_image_index (
          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,
          image_type TEXT NOT NULL,
          figure_group TEXT NOT NULL,
          image_code TEXT NOT NULL,
          image_index INTEGER NOT NULL
        );
        """
    )
    conn.commit()


def create_indexes(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE INDEX idx_part_index_oe ON epc_part_index(oe_no);
        CREATE INDEX idx_part_index_position ON epc_part_index(market, catalog, reference_code, figure_group);
        CREATE INDEX idx_part_name_ref ON epc_part_name(market, reference_code, language_file);
        CREATE INDEX idx_figure_image_main ON epc_figure_image(market, catalog, figure_group, image_code);
        CREATE INDEX idx_image_index_main ON epc_image_index(market, catalog, figure_group, image_code);
        """
    )
    conn.commit()


def table_count(conn: sqlite3.Connection, table: str) -> int:
    return int(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)
    started = time.time()
    conn = sqlite3.connect(DB_PATH)
    try:
        exec_schema(conn)
        counts = {}
        counts["epc_part_index"] = insert_many(
            conn,
            """
            INSERT INTO epc_part_index
            (market, catalog, source_file, row_no, byte_offset, record_len,
             oe_no, oe_raw, reference_code, figure_group)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            part_index_rows(),
        )
        counts["epc_part_name"] = insert_many(
            conn,
            """
            INSERT INTO epc_part_name
            (market, language_file, language_code, encoding, source_file,
             row_no, byte_offset, record_len, reference_code, part_name)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            part_name_rows(),
        )
        counts["epc_figure_image"] = insert_many(
            conn,
            """
            INSERT INTO epc_figure_image
            (market, catalog, source_file, row_no, byte_offset, record_len,
             figure_group, sequence, image_code, start_ym, end_ym, count_code,
             option_code, note_index)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            figure_image_rows(),
        )
        counts["epc_image_index"] = insert_many(
            conn,
            """
            INSERT INTO epc_image_index
            (market, catalog, source_file, row_no, byte_offset, record_len,
             image_type, figure_group, image_code, image_index)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            image_index_rows(),
        )
        create_indexes(conn)
        elapsed = time.time() - started
        rows = [(f"count_{key}", str(value)) for key, value in counts.items()]
        rows.extend(
            [
                ("parts_extended_at", time.strftime("%Y-%m-%d %H:%M:%S")),
                ("parts_build_seconds", f"{elapsed:.2f}"),
            ]
        )
        conn.executemany(
            "INSERT OR REPLACE INTO meta(key, value) VALUES (?, ?)",
            rows,
        )
        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()