"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.importCarCatalogWorkbook = importCarCatalogWorkbook;
exports.mapWorksheetRowToCarCatalogEntry = mapWorksheetRowToCarCatalogEntry;
const child_process_1 = require("child_process");
const SHARED_STRINGS_ENTRY = "xl/sharedStrings.xml";
const BIG_DATABASE_WORKSHEET_ENTRY = "xl/worksheets/sheet3.xml";
function importCarCatalogWorkbook(workbookPath) {
    const sharedStringsXml = readZipEntry(workbookPath, SHARED_STRINGS_ENTRY);
    const worksheetXml = readZipEntry(workbookPath, BIG_DATABASE_WORKSHEET_ENTRY);
    const sharedStrings = parseSharedStrings(sharedStringsXml);
    const rows = parseWorksheetRows(worksheetXml, sharedStrings);
    return rows.map(mapWorksheetRowToCarCatalogEntry).filter(isCatalogEntry);
}
function mapWorksheetRowToCarCatalogEntry(row) {
    const sourceUrl = toNullable(row.B);
    const make = toNullable(row.C);
    const model = toNullable(row.E);
    const yearValue = Number.parseInt(String(row.G || "").trim(), 10);
    if (!sourceUrl ||
        !sourceUrl.startsWith("http") ||
        !make ||
        !model ||
        !Number.isFinite(yearValue)) {
        return null;
    }
    return {
        sourceUrl,
        make,
        makeArabic: toNullable(row.D),
        model,
        modelArabic: toNullable(row.F),
        year: yearValue,
        image1Url: toNullable(row.H),
        image2Url: toNullable(row.I),
        priceUae: toNullable(row.J),
        priceKsa: toNullable(row.K),
        countryOfOrigin: toNullable(row.L),
        vehicleClass: toNullable(row.M),
        bodyStyles: toNullable(row.N),
        weight: toNullable(row.O),
        reviewGood: toNullable(row.P),
        reviewBad: toNullable(row.Q),
        overview: toNullable(row.R),
        reliability: toNullable(row.S),
        resaleValue: toNullable(row.T),
        knownProblems: toNullable(row.U),
        nhtsaDriverFrontalRating: toNullable(row.V),
        euroNcapOverallAdultRating: toNullable(row.W),
        engine: toNullable(row.X),
        gearbox: toNullable(row.Y),
        powerHp: toNullable(row.Z),
        torqueNm: toNullable(row.AA),
        fuelEconL100km: toNullable(row.AB),
        fuelEconKmL: toNullable(row.AC),
        zeroToHundredKphSec: toNullable(row.AD),
        topSpeedKph: toNullable(row.AE),
    };
}
function readZipEntry(workbookPath, entryPath) {
    return (0, child_process_1.execFileSync)("unzip", ["-p", workbookPath, entryPath], {
        encoding: "utf8",
        maxBuffer: 1024 * 1024 * 64,
    });
}
function parseSharedStrings(xml) {
    const values = [];
    const stringPattern = /<si\b[^>]*>([\s\S]*?)<\/si>/g;
    for (const match of xml.matchAll(stringPattern)) {
        const textMatches = Array.from(match[1].matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g));
        const value = textMatches.map((textMatch) => decodeXml(textMatch[1])).join("");
        values.push(value);
    }
    return values;
}
function parseWorksheetRows(xml, sharedStrings) {
    const rows = [];
    const rowPattern = /<row\b[^>]*>([\s\S]*?)<\/row>/g;
    for (const rowMatch of xml.matchAll(rowPattern)) {
        const row = {};
        const cellPattern = /<c\b([^>]*)r="([A-Z]+)\d+"([^>]*?)(?:\/>|>([\s\S]*?)<\/c>)/g;
        for (const cellMatch of rowMatch[1].matchAll(cellPattern)) {
            const column = cellMatch[2];
            const attrs = `${cellMatch[1]} ${cellMatch[3]}`;
            const typeMatch = attrs.match(/\bt="([^"]+)"/);
            row[column] = parseCellValue(cellMatch[4] || "", typeMatch?.[1], sharedStrings);
        }
        rows.push(row);
    }
    return rows;
}
function parseCellValue(cellXml, cellType, sharedStrings) {
    if (cellType === "inlineStr") {
        const textMatches = Array.from(cellXml.matchAll(/<t\b[^>]*>([\s\S]*?)<\/t>/g));
        return textMatches.map((match) => decodeXml(match[1])).join("");
    }
    const valueMatch = cellXml.match(/<v>([\s\S]*?)<\/v>/);
    if (!valueMatch) {
        return "";
    }
    if (cellType === "s") {
        const sharedIndex = Number.parseInt(valueMatch[1], 10);
        return sharedStrings[sharedIndex] || "";
    }
    return decodeXml(valueMatch[1]);
}
function decodeXml(value) {
    return value
        .replace(/&#(\d+);/g, (_, code) => String.fromCharCode(Number(code)))
        .replace(/&#x([0-9a-fA-F]+);/g, (_, code) => String.fromCharCode(parseInt(code, 16)))
        .replace(/&amp;/g, "&")
        .replace(/&lt;/g, "<")
        .replace(/&gt;/g, ">")
        .replace(/&quot;/g, '"')
        .replace(/&apos;/g, "'");
}
function toNullable(value) {
    const normalized = String(value || "").trim();
    return normalized || null;
}
function isCatalogEntry(entry) {
    return Boolean(entry);
}
//# sourceMappingURL=car-catalog-import.util.js.map