import sys
import re
import csv

LICENSE_PLATE_MASKS: dict[str, list[str]] = {
    "RU": [
        r"[АВЕКМНОРСТУХABEKMHOPCTYX]\d{3}[АВЕКМНОРСТУХABEKMHOPCTYX]{2}\d{2,3}",
        r"[АВЕКМНОРСТУХABEKMHOPCTYX]{2}\d{5,6}",
        r"\d{4}[АВЕКМНОРСТУХABEKMHOPCTYX]{2}\d{2,3}",
        r"[АВЕКМНОРСТУХABEKMHOPCTYX]\d{6,7}",
        r"\d{4}[АВЕКМНОРСТУХABEKMHOPCTYX]\d{2,3}",
    ],
    "KZ": [
        r"\d{3}[A-Z]{2}\d{2}",
        r"\d{3}[A-Z]{3}\d{2}",
        r"\d{4}[A-Z]{2}",
        r"\d{6}",
        r"\d{5}",
    ],
    "BY": [
        r"\d{4}[ABEIHKMOPCTX]{2}-\d",
        r"E\d{3}[ABEIHKMOPCTX]{2}-\d",
        r"[ABEIHKMOPCTX]{2}\d{4}-\d",
        r"[ABEIHKMOPCTX]{2}E\d{3}-\d",
        r"[ABEIHKMOPCTX]{2}-\d{5}",
        r"\dTAX\d{4}",
    ],
    "AM": [
        r"\d{2}[A-Z]{2}\d{3}",
    ],
    "AZ": [
        r"\d{2}-[A-Z]{2}-\d{3}",
    ],
    "KG": [
        r"\d{5}[A-Z]{2}",
        r"\d{5}[A-Z]{3}",
    ],
    "MD": [
        r"[A-Z]{3}\d{3}",
        r"\d{3}[A-Z]{3}",
    ],
    "TJ": [
        r"\d{4}[ABDEJKMHOPCTYXZ]{2}\d{2}",
        r"\d{3}[ABDEJKMHOPCTYXZ]{2}\d{2}",
        r"\d{3}[ABDEJKMHOPCTYXZ]\d{2}",
        r"\d{4}M\d{2}",
    ],
    "UZ": [
        r"\d{5}[A-HJ-Z]{3}",
        r"\d{3}[A-HJ-Z]{3}\d{2}",
        r"\d{2}[A-HJ-Z]\d{3}[A-HJ-Z]{2}",
        r"\d{5}[A-HJ-Z]{2}",
    ],
}

class CarRecord:
    def __init__(self, car_id, license_plate, country, color, analytics, comment):
        self.car_id = car_id
        self.license_plate = license_plate
        self.country = country
        self.color = color
        self.analytics = analytics
        self.comment = comment

def find_country_by_plate(plate: str) -> str:
    plate = plate.strip().upper()
    for country, masks in LICENSE_PLATE_MASKS.items():
        for mask in masks:
            if re.fullmatch(mask, plate):
                return country
    return "RU"

def escape_sql(value: str) -> str:
    return value.replace("'", "''")

f = open(sys.argv[1], mode="r", encoding="utf-8")
dump_file = open(sys.argv[2], mode="w", encoding="utf-8")


dump_file.write("""
CREATE TABLE evi.car
    (
        car_id text COLLATE pg_catalog."default" NOT NULL,
        license_plate text COLLATE pg_catalog."default" NOT NULL,
        country text COLLATE pg_catalog."default" NOT NULL,
        color text COLLATE pg_catalog."default" NOT NULL,
        analytics boolean NOT NULL,
        comment text COLLATE pg_catalog."default"
    );
\n""")

reader = csv.reader(f)

for row in reader:
    if not row:
        continue

    car_id, license_plate, _, color, analytics, comment = row[:6]

    country = find_country_by_plate(license_plate)

    comment = comment.strip()
    if comment.startswith("[") and comment.endswith("]"):
        comment = comment[1:-1].strip()

    is_analytics = (
        "true" if analytics.lower() in ("t", "true", "1") else "false"
    )

    sql_id = escape_sql(car_id)
    sql_plate = escape_sql(license_plate)
    sql_country = escape_sql(country)
    sql_color = escape_sql(color)

    sql_comment = f"'{escape_sql(comment)}'" if comment else "NULL"

    dump_file.write(
        f"INSERT INTO evi.car (car_id, license_plate, country, color, analytics, comment) "
        f"VALUES ('{sql_id}', '{sql_plate}', '{sql_country}', '{sql_color}', {is_analytics}, {sql_comment});\n"
    )

f.close()
dump_file.close()