import { createHash, randomUUID } from 'node:crypto';
import { readFile } from 'node:fs/promises';
import { extname } from 'node:path';
import ExcelJS from 'exceljs';
import { ConflictError, NotFoundError } from '../domain/errors.js';
import { normalizeIdentifier, normalizePersian, toLatinDigits } from '../domain/normalization.js';
import { appendAudit } from './audit.js';
const columns = [
    { key: 'invoiceNumber', aliases: ['شماره پیش فاکتور', 'شماره پیش‌فاکتور', 'شماره پيش فاكتور', 'شماره پیش فاکتور'] },
    { key: 'financialDate', aliases: ['تاریخ پیش فاکتور', 'تاریخ پیش‌فاکتور', 'تاريخ پيش فاكتور'] },
    { key: 'customerCode', aliases: ['کد مشتری', 'كد مشتري'] },
    { key: 'customerName', aliases: ['عنوان مشتری', 'عنوان مشتري', 'نام مشتری'] },
    { key: 'saleType', aliases: ['نوع فروش'] },
    { key: 'productCode', aliases: ['کد کالا/خدمت', 'كد كالا/خدمت', 'کد کالا خدمت'] },
    { key: 'productName', aliases: ['کالا/خدمت', 'كالا/خدمت', 'نام کالا'] },
    { key: 'unit', aliases: ['واحد اصلی', 'واحد اصلي'] },
    { key: 'quantity', aliases: ['مقدار-اصلی', 'مقدار اصلی', 'مقدار-اصلي'] }
];
function canonicalHash(value) {
    return createHash('sha256').update(JSON.stringify(value)).digest('hex');
}
function cellText(cell) {
    const value = cell.value;
    if (value instanceof Date)
        return value.toISOString().slice(0, 10);
    if (value && typeof value === 'object' && 'formula' in value) {
        const result = value.result;
        return result === undefined || result === null ? '' : String(result).trim();
    }
    return cell.text?.trim() ?? String(value ?? '').trim();
}
function parseQuantity(cell) {
    if (typeof cell.value === 'number')
        return cell.value;
    const normalized = toLatinDigits(cellText(cell)).replace(/[,٬\s]/g, '');
    return Number(normalized);
}
function isPlausibleDate(value) {
    const normalized = toLatinDigits(value).trim();
    if (/^\d{4}[-/]\d{1,2}[-/]\d{1,2}$/.test(normalized))
        return true;
    if (/^\d{2}[-/]\d{1,2}[-/]\d{1,2}$/.test(normalized))
        return true;
    return /^\d{4}-\d{2}-\d{2}T/.test(normalized);
}
function pureNumber(value) {
    return /^\d+(?:[.,]\d+)?$/.test(toLatinDigits(value).replace(/\s/g, ''));
}
function findHeaders(sheet) {
    for (let rowNumber = 1; rowNumber <= Math.min(sheet.rowCount, 12); rowNumber += 1) {
        const positions = new Map();
        const row = sheet.getRow(rowNumber);
        row.eachCell({ includeEmpty: false }, (cell, columnNumber) => {
            const header = normalizePersian(cellText(cell));
            const definition = columns.find((column) => column.aliases.some((alias) => normalizePersian(alias) === header));
            if (definition)
                positions.set(definition.key, columnNumber);
        });
        if (positions.size === columns.length)
            return { row: rowNumber, positions };
    }
    throw new ConflictError('ستون‌های الزامی فایل مالی پیدا نشدند.', 'INVALID_WORKBOOK_HEADERS');
}
function buildGroups(rows) {
    const grouped = new Map();
    for (const row of rows) {
        const key = normalizeIdentifier(row.invoiceNumber);
        grouped.set(key, [...(grouped.get(key) ?? []), row]);
    }
    return [...grouped.entries()].map(([normalizedNumber, invoiceRows]) => {
        const first = invoiceRows[0];
        const identity = {
            invoiceNumber: first.invoiceNumber,
            financialDate: first.financialDate,
            customerCode: first.customerCode,
            customerName: first.customerName,
            saleType: first.saleType
        };
        const productGroups = new Map();
        for (const row of invoiceRows) {
            const code = normalizeIdentifier(row.productCode);
            productGroups.set(code, [...(productGroups.get(code) ?? []), row]);
        }
        const items = [...productGroups.values()].map((productRows) => ({
            productCode: productRows[0].productCode,
            productName: productRows[0].productName,
            unit: productRows[0].unit,
            quantity: productRows.reduce((total, row) => total + row.quantity, 0),
            originalRows: productRows.map((row) => row.rowNumber)
        })).sort((a, b) => normalizeIdentifier(a.productCode).localeCompare(normalizeIdentifier(b.productCode)));
        return {
            invoiceNumber: first.invoiceNumber,
            normalizedNumber,
            identity,
            items,
            fingerprint: canonicalHash({ identity: { ...identity, invoiceNumber: normalizedNumber }, items })
        };
    }).sort((a, b) => a.normalizedNumber.localeCompare(b.normalizedNumber));
}
export class FinancialWorkbookParser {
    static validateEnvelope(filename, mimeType, buffer, maxBytes) {
        const extension = extname(filename).toLowerCase();
        if (!['.xlsx', '.xlsm'].includes(extension))
            throw new ConflictError('فقط فایل‌های xlsx و xlsm پذیرفته می‌شوند.', 'INVALID_FILE_EXTENSION');
        const allowedMimes = new Set([
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel.sheet.macroenabled.12',
            'application/octet-stream',
            'application/zip'
        ]);
        if (!allowedMimes.has(mimeType.toLowerCase()))
            throw new ConflictError('نوع MIME فایل معتبر نیست.', 'INVALID_FILE_MIME');
        if (!buffer.length || buffer.length > maxBytes)
            throw new ConflictError('اندازه فایل خارج از محدوده مجاز است.', 'INVALID_FILE_SIZE');
        if (buffer[0] !== 0x50 || buffer[1] !== 0x4b)
            throw new ConflictError('ساختار فایل Office Open XML معتبر نیست.', 'INVALID_FILE_MAGIC');
    }
    static async parse(buffer) {
        const workbook = new ExcelJS.Workbook();
        await workbook.xlsx.load(buffer);
        const preferred = workbook.getWorksheet('Sheet');
        const sheet = preferred ?? workbook.worksheets.find((candidate) => {
            try {
                findHeaders(candidate);
                return true;
            }
            catch {
                return false;
            }
        });
        if (!sheet)
            throw new ConflictError('برگه حاوی قالب مالی پیدا نشد.', 'MISSING_FINANCIAL_SHEET');
        const headers = findHeaders(sheet);
        const rows = [];
        const errors = [];
        for (let rowNumber = headers.row + 1; rowNumber <= sheet.rowCount; rowNumber += 1) {
            const row = sheet.getRow(rowNumber);
            const cells = columns.map((column) => row.getCell(headers.positions.get(column.key)));
            const raw = cells.map((cell) => cellText(cell));
            if (raw.every((value) => value === ''))
                continue;
            const values = Object.fromEntries(columns.map((column, index) => [column.key, raw[index] ?? '']));
            const reasons = [];
            const formulaColumns = columns.filter((column, index) => {
                const value = cells[index].value;
                return value && typeof value === 'object' && 'formula' in value;
            });
            if (formulaColumns.length)
                reasons.push('وجود فرمول در ردیف داده مجاز نیست؛ فقط مقدار سلول پذیرفته می‌شود.');
            if (!values.invoiceNumber)
                reasons.push('شماره پیش‌فاکتور خالی است.');
            if (!values.financialDate || !isPlausibleDate(values.financialDate))
                reasons.push('تاریخ پیش‌فاکتور معتبر نیست.');
            if (!values.customerCode)
                reasons.push('کد مشتری خالی است.');
            if (!values.customerName || pureNumber(values.customerName))
                reasons.push('عنوان مشتری معتبر نیست.');
            if (!values.saleType || pureNumber(values.saleType))
                reasons.push('نوع فروش معتبر نیست.');
            if (!values.productCode)
                reasons.push('کد کالا/خدمت خالی است.');
            if (!values.productName || pureNumber(values.productName))
                reasons.push('عنوان کالا/خدمت معتبر نیست.');
            if (!values.unit || pureNumber(values.unit))
                reasons.push('واحد اصلی معتبر نیست.');
            const quantity = parseQuantity(cells[columns.findIndex((column) => column.key === 'quantity')]);
            if (!Number.isFinite(quantity) || quantity <= 0)
                reasons.push('مقدار اصلی باید عددی بزرگ‌تر از صفر باشد.');
            if (reasons.length) {
                errors.push({ rowNumber, rawValues: raw, reasons });
                continue;
            }
            rows.push({
                rowNumber,
                invoiceNumber: values.invoiceNumber,
                financialDate: values.financialDate,
                customerCode: values.customerCode,
                customerName: values.customerName,
                saleType: values.saleType,
                productCode: values.productCode,
                productName: values.productName,
                unit: values.unit,
                quantity,
                original: values
            });
        }
        const groups = buildGroups(rows);
        return { sheetName: sheet.name, headerRow: headers.row, rows, errors, groups };
    }
}
async function loadVersion(tx, versionId) {
    if (!versionId)
        return null;
    const version = await tx.query('SELECT identity_data FROM invoice_versions WHERE id = $1', [versionId]);
    if (!version.rowCount)
        return null;
    const items = await tx.query('SELECT product_code, product_name, unit, quantity FROM invoice_version_items WHERE invoice_version_id = $1 ORDER BY line_number', [versionId]);
    return { identity: version.rows[0].identity_data, items: items.rows.map((item) => ({ ...item, quantity: Number(item.quantity) })) };
}
function diffVersions(oldVersion, group) {
    if (!oldVersion)
        return { created: true, new: { identity: group.identity, items: group.items } };
    const fields = {};
    for (const [key, value] of Object.entries(group.identity)) {
        if (oldVersion.identity[key] !== value)
            fields[key] = { old: oldVersion.identity[key], new: value };
    }
    const oldItems = new Map(oldVersion.items.map((item) => [normalizeIdentifier(item.product_code), item]));
    const newItems = new Map(group.items.map((item) => [normalizeIdentifier(item.productCode), item]));
    const itemChanges = {};
    for (const code of new Set([...oldItems.keys(), ...newItems.keys()])) {
        const before = oldItems.get(code) ?? null;
        const after = newItems.get(code) ?? null;
        if (JSON.stringify(before) !== JSON.stringify(after))
            itemChanges[code] = { old: before, new: after };
    }
    return { fields, items: itemChanges };
}
export class FinancialImportService {
    db;
    constructor(db) {
        this.db = db;
    }
    async register(input) {
        const existing = await this.db.query(`SELECT id FROM imports WHERE kind = 'invoice' AND checksum_sha256 = $1`, [input.checksum]);
        if (existing.rowCount)
            return { id: existing.rows[0].id, duplicate: true };
        const id = randomUUID();
        await this.db.query(`INSERT INTO imports
       (id, kind, original_filename, stored_path, mime_type, file_size, checksum_sha256, imported_by, status)
       VALUES ($1,'invoice',$2,$3,$4,$5,$6,$7,'uploaded')`, [id, input.filename, input.storedPath, input.mimeType, input.fileSize, input.checksum, input.actorId]);
        return { id, duplicate: false };
    }
    async parseStored(importId) {
        const record = await this.db.query('SELECT stored_path FROM imports WHERE id = $1', [importId]);
        if (!record.rowCount)
            throw new NotFoundError('رکورد ورود فایل پیدا نشد.');
        return FinancialWorkbookParser.parse(await readFile(record.rows[0].stored_path));
    }
    async preview(parsed) {
        let newInvoices = 0;
        let updatedInvoices = 0;
        let unchangedInvoices = 0;
        let discrepancies = 0;
        for (const group of parsed.groups) {
            const invoice = await this.db.query(`SELECT i.financial_current_version_id, i.manual_current_version_id, v.fingerprint_sha256
         FROM invoices i LEFT JOIN invoice_versions v ON v.id = i.financial_current_version_id
         WHERE i.normalized_number = $1`, [group.normalizedNumber]);
            if (!invoice.rowCount)
                newInvoices += 1;
            else if (invoice.rows[0].fingerprint_sha256 === group.fingerprint)
                unchangedInvoices += 1;
            else
                updatedInvoices += 1;
            if (invoice.rows[0]?.manual_current_version_id && invoice.rows[0]?.fingerprint_sha256 !== group.fingerprint)
                discrepancies += 1;
        }
        const productCodes = new Set(parsed.rows.map((row) => normalizeIdentifier(row.productCode)));
        let changedProducts = 0;
        for (const code of productCodes) {
            const product = await this.db.query('SELECT id FROM products WHERE normalized_code = $1', [code]);
            if (!product.rowCount)
                changedProducts += 1;
        }
        return {
            validRows: parsed.rows.length,
            rejectedRows: parsed.errors.length,
            uniqueInvoices: parsed.groups.length,
            uniqueProducts: productCodes.size,
            newInvoices,
            updatedInvoices,
            unchangedInvoices,
            changedProducts,
            discrepancies
        };
    }
    async confirm(importId, parsed, audit) {
        const outcome = await this.db.transaction(async (tx) => {
            const importRow = await tx.query('SELECT status, imported_by, outcome FROM imports WHERE id = $1 FOR UPDATE', [importId]);
            if (!importRow.rowCount)
                throw new NotFoundError('رکورد ورود فایل پیدا نشد.');
            if (importRow.rows[0].status === 'completed')
                return importRow.rows[0].outcome;
            await tx.query(`UPDATE imports SET status = 'processing', started_at = COALESCE(started_at, now()), progress = 5 WHERE id = $1`, [importId]);
            for (const error of parsed.errors) {
                await tx.query(`INSERT INTO import_row_errors(id, import_id, sheet_name, row_number, raw_values, reasons)
           VALUES ($1,$2,$3,$4,$5::jsonb,$6::jsonb)`, [randomUUID(), importId, parsed.sheetName, error.rowNumber, JSON.stringify(error.rawValues), JSON.stringify(error.reasons)]);
            }
            const summary = {
                validRows: parsed.rows.length,
                rejectedRows: parsed.errors.length,
                uniqueInvoices: parsed.groups.length,
                uniqueProducts: new Set(parsed.rows.map((row) => normalizeIdentifier(row.productCode))).size,
                newInvoices: 0,
                updatedInvoices: 0,
                unchangedInvoices: 0,
                changedProducts: 0,
                discrepancies: 0
            };
            const productMap = new Map();
            for (const row of parsed.rows) {
                const code = normalizeIdentifier(row.productCode);
                if (productMap.has(code))
                    continue;
                const existing = await tx.query('SELECT id, name, unit FROM products WHERE normalized_code = $1', [code]);
                if (existing.rowCount) {
                    productMap.set(code, existing.rows[0].id);
                    if (existing.rows[0].name !== row.productName || existing.rows[0].unit !== row.unit) {
                        await tx.query('UPDATE products SET name = $2, normalized_name = $3, unit = $4, updated_at = now() WHERE id = $1', [
                            existing.rows[0].id, row.productName, normalizePersian(row.productName), row.unit
                        ]);
                        summary.changedProducts += 1;
                    }
                }
                else {
                    const id = randomUUID();
                    await tx.query(`INSERT INTO products(id, code, normalized_code, name, normalized_name, unit, created_by)
             VALUES ($1,$2,$3,$4,$5,$6,$7)`, [id, row.productCode, code, row.productName, normalizePersian(row.productName), row.unit, importRow.rows[0].imported_by]);
                    productMap.set(code, id);
                    summary.changedProducts += 1;
                }
            }
            for (const group of parsed.groups) {
                const existing = await tx.query(`SELECT i.id, i.origin, i.financial_current_version_id, i.manual_current_version_id,
            i.operational_version_id, i.manager_edited, v.fingerprint_sha256
           FROM invoices i LEFT JOIN invoice_versions v ON v.id = i.financial_current_version_id
           WHERE i.normalized_number = $1 FOR UPDATE OF i`, [group.normalizedNumber]);
                let invoiceId;
                let previousFinancial = null;
                let manualVersion = null;
                let managerEdited = false;
                if (!existing.rowCount) {
                    invoiceId = randomUUID();
                    await tx.query(`INSERT INTO invoices
             (id, invoice_number, normalized_number, origin, customer_code, customer_name, sale_type, financial_date, created_by)
             VALUES ($1,$2,$3,'financial',$4,$5,$6,$7,$8)`, [invoiceId, group.invoiceNumber, group.normalizedNumber, group.identity.customerCode, group.identity.customerName,
                        group.identity.saleType, group.identity.financialDate, importRow.rows[0].imported_by]);
                    summary.newInvoices += 1;
                }
                else {
                    const found = existing.rows[0];
                    invoiceId = found.id;
                    previousFinancial = found.financial_current_version_id;
                    manualVersion = found.manual_current_version_id;
                    managerEdited = found.manager_edited;
                    if (found.fingerprint_sha256 === group.fingerprint) {
                        summary.unchangedInvoices += 1;
                        continue;
                    }
                    summary.updatedInvoices += 1;
                }
                const next = await tx.query('SELECT COALESCE(MAX(version_number),0)+1 AS next FROM invoice_versions WHERE invoice_id = $1', [invoiceId]);
                const versionId = randomUUID();
                await tx.query(`INSERT INTO invoice_versions
           (id, invoice_id, import_id, version_number, source, identity_data, fingerprint_sha256, created_by)
           VALUES ($1,$2,$3,$4,'financial',$5::jsonb,$6,$7)`, [versionId, invoiceId, importId, Number(next.rows[0].next), JSON.stringify(group.identity), group.fingerprint, importRow.rows[0].imported_by]);
                for (const [index, item] of group.items.entries()) {
                    await tx.query(`INSERT INTO invoice_version_items
             (id, invoice_version_id, line_number, product_id, product_code, product_name, unit, quantity, original_data)
             VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9::jsonb)`, [randomUUID(), versionId, index + 1, productMap.get(normalizeIdentifier(item.productCode)) ?? null,
                        item.productCode, item.productName, item.unit, item.quantity, JSON.stringify({ rows: item.originalRows })]);
                }
                const changes = diffVersions(await loadVersion(tx, previousFinancial), group);
                if (previousFinancial) {
                    await tx.query(`INSERT INTO financial_changes(id, invoice_id, from_version_id, to_version_id, import_id, changes)
             VALUES ($1,$2,$3,$4,$5,$6::jsonb)`, [randomUUID(), invoiceId, previousFinancial, versionId, importId, JSON.stringify(changes)]);
                }
                let hasDiscrepancy = false;
                let operationalVersion = versionId;
                if (manualVersion || managerEdited) {
                    const comparisonId = manualVersion ?? existing.rows[0].operational_version_id;
                    const compared = await loadVersion(tx, comparisonId);
                    const differences = diffVersions(compared, group);
                    if (JSON.stringify(differences) !== JSON.stringify({ fields: {}, items: {} })) {
                        hasDiscrepancy = true;
                        operationalVersion = existing.rows[0].operational_version_id ?? comparisonId;
                        await tx.query(`INSERT INTO discrepancies
               (id, invoice_id, manual_version_id, financial_version_id, kind, differences)
               VALUES ($1,$2,$3,$4,'manual_financial',$5::jsonb)`, [randomUUID(), invoiceId, comparisonId, versionId, JSON.stringify(differences)]);
                        summary.discrepancies += 1;
                    }
                }
                const delivered = await tx.query(`SELECT dri.product_code, SUM(dri.approved_quantity) AS delivered
           FROM delivery_request_items dri JOIN delivery_requests dr ON dr.id = dri.delivery_request_id
           WHERE dr.invoice_id = $1 AND dr.status = 'delivered' GROUP BY dri.product_code`, [invoiceId]);
                const financialQty = new Map(group.items.map((item) => [normalizeIdentifier(item.productCode), item.quantity]));
                const overDelivered = delivered.rows.filter((row) => Number(row.delivered) > (financialQty.get(normalizeIdentifier(row.product_code)) ?? 0));
                if (overDelivered.length) {
                    hasDiscrepancy = true;
                    await tx.query(`INSERT INTO discrepancies(id, invoice_id, financial_version_id, kind, differences)
             VALUES ($1,$2,$3,'operational_financial',$4::jsonb)`, [randomUUID(), invoiceId, versionId, JSON.stringify({ overDelivered })]);
                    summary.discrepancies += 1;
                }
                await tx.query(`UPDATE invoices SET origin = CASE WHEN manual_current_version_id IS NULL THEN 'financial' ELSE 'mixed' END,
            invoice_number = $2, customer_code = $3, customer_name = $4, sale_type = $5, financial_date = $6,
            financial_current_version_id = $7, operational_version_id = $8, has_discrepancy = $9, updated_at = now()
           WHERE id = $1`, [invoiceId, group.invoiceNumber, group.identity.customerCode, group.identity.customerName,
                    group.identity.saleType, group.identity.financialDate, versionId, operationalVersion, hasDiscrepancy]);
            }
            await tx.query(`UPDATE imports SET status = 'completed', progress = 100, completed_at = now(), confirmed_at = now(),
          valid_rows = $2, rejected_rows = $3, new_invoices = $4, updated_invoices = $5,
          unchanged_invoices = $6, changed_products = $7, discrepancies = $8, outcome = $9::jsonb
         WHERE id = $1`, [importId, summary.validRows, summary.rejectedRows, summary.newInvoices, summary.updatedInvoices,
                summary.unchangedInvoices, summary.changedProducts, summary.discrepancies, JSON.stringify(summary)]);
            await appendAudit(tx, { ...audit, actorId: importRow.rows[0].imported_by }, {
                action: 'import.completed', entityType: 'import', entityId: importId, newValues: summary, importId
            });
            return summary;
        });
        return outcome;
    }
}
export function sha256(buffer) {
    return createHash('sha256').update(buffer).digest('hex');
}
//# sourceMappingURL=excel-import-service.js.map