from __future__ import annotations

import json
from pathlib import Path
import sqlite3
import sys


DB_PATH = Path(__file__).resolve().parent / "toyota_epc_output" / "toyota_epc_validation.sqlite"


def serial_candidates(vin: str) -> list[str]:
    compact = "".join(ch for ch in vin.upper() if ch.isalnum())
    out = []
    for width in [7, 6]:
        if len(compact) >= width:
            tail = compact[-width:]
            if tail.isdigit():
                value = tail.zfill(7)
                if value not in out:
                    out.append(value)
    return out


def ym_in_range(ym: str | None, start: str, end: str) -> bool:
    if not ym:
        return True
    if start and ym < start:
        return False
    if end and end != "999999" and ym >= end:
        return False
    return True


def find_frame_month(conn: sqlite3.Connection, market: str, frame_code: str, serial_raw: str):
    serial_int = int(serial_raw)
    current = conn.execute(
        """
        SELECT *
        FROM epc_frame_month_point
        WHERE market = ? AND frame_code = ? AND serial_int <= ?
        ORDER BY year DESC, month DESC, serial_int DESC
        LIMIT 1
        """,
        (market, frame_code, serial_int),
    ).fetchone()
    next_point = conn.execute(
        """
        SELECT *
        FROM epc_frame_month_point
        WHERE market = ? AND frame_code = ? AND serial_int > ?
        ORDER BY year ASC, month ASC, serial_int ASC
        LIMIT 1
        """,
        (market, frame_code, serial_int),
    ).fetchone()
    if not current:
        return None
    return {
        "frame_serial": serial_raw,
        "production_month_epc": f"{current['year']}{current['month']}",
        "framno_rule": "serial >= month_start and serial < next_month_start",
        "month_start_point": {
            "market": current["market"],
            "source_file": current["source_file"],
            "row_no": current["row_no"],
            "byte_offset": current["byte_offset"],
            "frame_code": current["frame_code"],
            "year": current["year"],
            "month": current["month"],
            "serial_raw": current["serial_raw"],
            "serial_int": current["serial_int"],
        },
        "next_month_start_point": {
            "market": next_point["market"],
            "source_file": next_point["source_file"],
            "row_no": next_point["row_no"],
            "byte_offset": next_point["byte_offset"],
            "frame_code": next_point["frame_code"],
            "year": next_point["year"],
            "month": next_point["month"],
            "serial_raw": next_point["serial_raw"],
            "serial_int": next_point["serial_int"],
        }
        if next_point
        else None,
    }


def query(conn: sqlite3.Connection, vin: str, model_filter: str = ""):
    compact = "".join(ch for ch in vin.upper() if ch.isalnum())
    vin9 = compact[:9]
    model_filter = model_filter.upper().strip()
    rows = conn.execute(
        """
        SELECT DISTINCT
          d.market, d.source, d.source_file, d.row_no, d.byte_offset, d.record_len,
          d.catalog, d.vin_key, d.model, d.production_start, d.production_end,
          d.frame_code, d.spec_code, d.engine_epc, d.body, d.transmission, d.gear,
          d.steering, d.door, d.grade, d.turbo, d.destination,
          n.vehicle_name_epc, n.model_family, n.source_file AS shamei_file,
          n.row_no AS shamei_row_no, n.byte_offset AS shamei_offset
        FROM epc_vin_index i
        JOIN epc_vehicle_detail d
          ON d.market = i.market
         AND d.catalog = i.catalog
         AND d.vin_key = i.vin_key
         AND d.model = i.model
        LEFT JOIN epc_vehicle_name n
          ON n.market = d.market
         AND n.catalog = d.catalog
        WHERE (? = i.vin_key OR ? LIKE i.vin_key || '%')
          AND (? = '' OR i.model = ?)
        ORDER BY d.market, d.catalog, d.model, d.source, d.row_no
        """,
        (vin9, vin9, model_filter, model_filter),
    ).fetchall()

    results = []
    seen = set()
    for row in rows:
        for frame_serial in serial_candidates(compact):
            month = find_frame_month(conn, row["market"], row["frame_code"], frame_serial)
            production_month = month.get("production_month_epc") if month else None
            if not ym_in_range(production_month, row["production_start"], row["production_end"]):
                continue
            key = (row["source_file"], row["row_no"], frame_serial)
            if key in seen:
                continue
            seen.add(key)
            results.append(
                {
                    "input_vin": vin,
                    "vin9": vin9,
                    "market": row["market"],
                    "catalog": row["catalog"],
                    "vehicle_name_epc": row["vehicle_name_epc"] or "",
                    "model_family": row["model_family"] or "",
                    "vin_key": row["vin_key"],
                    "model": row["model"],
                    "production_start": row["production_start"],
                    "production_end": row["production_end"],
                    "frame_code": row["frame_code"],
                    "frame_serial": frame_serial,
                    "engine_epc": row["engine_epc"],
                    "spec_code": row["spec_code"],
                    "body": row["body"],
                    "transmission": row["transmission"],
                    "gear": row["gear"],
                    "steering": row["steering"],
                    "door": row["door"],
                    "grade": row["grade"],
                    "turbo": row["turbo"],
                    "destination": row["destination"],
                    "source_detail": {
                        "file": row["source_file"],
                        "row_no": row["row_no"],
                        "byte_offset": row["byte_offset"],
                        "record_len": row["record_len"],
                    },
                    "source_shamei": {
                        "file": row["shamei_file"],
                        "row_no": row["shamei_row_no"],
                        "byte_offset": row["shamei_offset"],
                        "record_len": 111,
                    }
                    if row["shamei_file"]
                    else None,
                    "framno_month": month,
                }
            )
    return results


def main() -> None:
    if not DB_PATH.exists():
        raise SystemExit(f"Database not found: {DB_PATH}")
    vin = sys.argv[1] if len(sys.argv) > 1 else "JTEBN99J900077939"
    model_filter = sys.argv[2] if len(sys.argv) > 2 else ""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    try:
        print(json.dumps(query(conn, vin, model_filter), ensure_ascii=False, indent=2))
    finally:
        conn.close()


if __name__ == "__main__":
    main()