import { mkdir } from 'node:fs/promises';
import { resolve } from 'node:path';
import ExcelJS from 'exceljs';
import { formatJalaliDateTime } from './jalali.js';
const colors = {
    navy: 'FF1F337A', blue: 'FF315899', cyan: 'FF60B4DF', white: 'FFFFFFFF',
    green: 'FFD1FAE5', yellow: 'FFFEF3C7', red: 'FFFEE2E2', slate: 'FFF1F5F9'
};
function styleSheet(sheet, widths) {
    sheet.views = [{ rightToLeft: true, state: 'frozen', ySplit: 1 }];
    sheet.autoFilter = { from: { row: 1, column: 1 }, to: { row: Math.max(1, sheet.rowCount), column: widths.length } };
    const header = sheet.getRow(1);
    header.height = 30;
    header.eachCell((cell) => {
        cell.font = { name: 'Vazirmatn', bold: true, color: { argb: colors.white } };
        cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: colors.navy } };
        cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
        cell.border = { bottom: { style: 'thin', color: { argb: colors.cyan } } };
    });
    widths.forEach((width, index) => { sheet.getColumn(index + 1).width = width; });
    sheet.eachRow((row, rowNumber) => {
        if (rowNumber === 1)
            return;
        row.font = { name: 'Vazirmatn', size: 10 };
        row.alignment = { vertical: 'middle', horizontal: 'right', wrapText: true };
        row.height = Math.min(54, Math.max(22, row.height ?? 22));
    });
    sheet.pageSetup = { orientation: 'landscape', fitToPage: true, fitToWidth: 1, fitToHeight: 0 };
}
export class ReportService {
    db;
    constructor(db) {
        this.db = db;
    }
    async preview(range) {
        const [deliveries, approvals, events, changes] = await Promise.all([
            this.db.query(`SELECT COUNT(DISTINCT invoice_id) AS count FROM delivery_requests WHERE approved_at BETWEEN $1 AND $2`, [range.startUtc, range.endUtc]),
            this.db.query(`SELECT COUNT(*) AS count FROM delivery_requests WHERE approved_at BETWEEN $1 AND $2`, [range.startUtc, range.endUtc]),
            this.db.query(`SELECT COUNT(*) AS count FROM delivery_events WHERE event_type <> 'approved' AND occurred_at BETWEEN $1 AND $2`, [range.startUtc, range.endUtc]),
            this.db.query(`SELECT COUNT(*) AS count FROM financial_changes WHERE created_at BETWEEN $1 AND $2`, [range.startUtc, range.endUtc])
        ]);
        return {
            deliveries: Number(deliveries.rows[0]?.count ?? 0), approvals: Number(approvals.rows[0]?.count ?? 0),
            warehouseEvents: Number(events.rows[0]?.count ?? 0), financialChanges: Number(changes.rows[0]?.count ?? 0)
        };
    }
    async build(range) {
        const workbook = new ExcelJS.Workbook();
        workbook.creator = 'سامانه تحویل کالای بازرگانی ماندگار';
        workbook.created = new Date();
        workbook.modified = new Date();
        workbook.properties.date1904 = false;
        const statusRows = await this.db.query(`SELECT i.id, i.invoice_number, i.customer_name,
        string_agg(DISTINCT rq.name, '، ') AS requesters,
        string_agg(DISTINCT dri.product_name || ' (' || dri.approved_quantity::text || ' ' || dri.unit || ')', '، ') AS item_summary,
        COALESCE(SUM(DISTINCT dri.approved_quantity), 0) AS approved_quantity,
        COALESCE(SUM(DISTINCT CASE WHEN dr.status = 'delivered' THEN dri.approved_quantity ELSE 0 END), 0) AS delivered_quantity,
        COALESCE((SELECT SUM(ivi.quantity) FROM invoice_version_items ivi WHERE ivi.invoice_version_id = i.operational_version_id), 0) AS total_quantity,
        string_agg(DISTINCT su.name, '، ') AS supply_managers,
        string_agg(DISTINCT wu.name, '، ') AS warehouse_operators,
        MIN(dr.approved_at) AS first_approval,
        MAX(CASE WHEN de.event_type IN ('delivered','redelivered') THEN de.occurred_at END) AS last_delivery,
        i.has_discrepancy,
        bool_or(dr.status IN ('pending','returned')) AS has_pending,
        bool_or(dr.whole_invoice_no_lines AND dr.status = 'delivered') AS has_whole_delivered
       FROM invoices i
       JOIN delivery_requests dr ON dr.invoice_id = i.id
       JOIN requesters rq ON rq.id = dr.requester_id
       JOIN users su ON su.id = dr.approved_by
       LEFT JOIN delivery_request_items dri ON dri.delivery_request_id = dr.id
       LEFT JOIN delivery_events de ON de.delivery_request_id = dr.id
       LEFT JOIN users wu ON wu.id = de.actor_id AND de.event_type <> 'approved'
       WHERE EXISTS (SELECT 1 FROM delivery_events x WHERE x.delivery_request_id = dr.id AND x.occurred_at BETWEEN $1 AND $2)
       GROUP BY i.id, i.invoice_number, i.customer_name, i.operational_version_id, i.has_discrepancy
       ORDER BY MIN(dr.approved_at)`, [range.startUtc, range.endUtc]);
        const statusSheet = workbook.addWorksheet('وضعیت تحویل');
        statusSheet.addRow(['شماره فاکتور', 'مشتری', 'درخواست‌کننده', 'نوع تحویل', 'خلاصه اقلام', 'مقدار تأییدشده', 'مقدار تحویل‌شده', 'مقدار باقیمانده', 'وضعیت', 'مدیر تأمین', 'انباردار', 'زمان تأیید', 'زمان تحویل', 'مغایرت']);
        for (const row of statusRows.rows) {
            const total = Number(row.total_quantity);
            const delivered = Number(row.delivered_quantity);
            const complete = row.has_whole_delivered || (total > 0 && delivered >= total);
            const partial = delivered > 0 && !complete;
            const status = row.has_pending ? 'در انتظار تحویل' : complete ? 'تحویل کامل' : partial ? 'تحویل جزئی' : 'ثبت‌نشده برای خروج';
            const added = statusSheet.addRow([
                row.invoice_number, row.customer_name ?? '—', row.requesters ?? '—', complete ? 'کامل' : 'جزئی', row.item_summary ?? 'تحویل کامل بدون جزئیات اقلام',
                Number(row.approved_quantity), delivered, Math.max(0, total - delivered), status, row.supply_managers ?? '—', row.warehouse_operators ?? '—',
                formatJalaliDateTime(row.first_approval), row.last_delivery ? formatJalaliDateTime(row.last_delivery) : '—', row.has_discrepancy ? 'دارد' : 'ندارد'
            ]);
            if (complete || partial) {
                added.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: complete ? colors.green : colors.yellow } };
            }
        }
        styleSheet(statusSheet, [18, 26, 24, 14, 45, 18, 18, 18, 20, 22, 22, 22, 22, 12]);
        const approvals = await this.db.query(`SELECT i.invoice_number, i.customer_name, rq.name AS requester, dr.delivery_type,
        dri.product_name, dri.approved_quantity, dri.unit, dr.approved_at, u.name AS manager, dr.status, dr.notes
       FROM delivery_requests dr JOIN invoices i ON i.id = dr.invoice_id JOIN requesters rq ON rq.id = dr.requester_id
       JOIN users u ON u.id = dr.approved_by LEFT JOIN delivery_request_items dri ON dri.delivery_request_id = dr.id
       WHERE dr.approved_at BETWEEN $1 AND $2 ORDER BY dr.approved_at, i.invoice_number`, [range.startUtc, range.endUtc]);
        const supplySheet = workbook.addWorksheet('ثبت خروج تأمین');
        supplySheet.addRow(['شماره فاکتور', 'مشتری', 'درخواست‌کننده', 'نوع تحویل', 'کالا', 'مقدار', 'واحد', 'مدیر تأمین', 'زمان تأیید', 'نتیجه انبار', 'یادداشت']);
        for (const row of approvals.rows)
            supplySheet.addRow([
                row.invoice_number, row.customer_name ?? '—', row.requester, row.delivery_type === 'complete' ? 'کامل' : 'جزئی', row.product_name ?? 'تمام فاکتور ـ جزئیات ناموجود',
                row.approved_quantity ? Number(row.approved_quantity) : '—', row.unit ?? '—', row.manager, formatJalaliDateTime(row.approved_at),
                { pending: 'در انتظار تحویل', delivered: 'تحویل‌شده', cancelled: 'لغوشده', returned: 'برگشت از تحویل' }[row.status] ?? row.status,
                row.notes ?? '—'
            ]);
        styleSheet(supplySheet, [18, 25, 23, 14, 32, 14, 13, 22, 22, 20, 32]);
        const warehouse = await this.db.query(`SELECT i.invoice_number, rq.name AS requester, de.event_type, u.name AS actor, de.occurred_at, de.reason,
        string_agg(dri.product_name || ' (' || dri.approved_quantity::text || ' ' || dri.unit || ')', '، ') AS products
       FROM delivery_events de JOIN delivery_requests dr ON dr.id = de.delivery_request_id
       JOIN invoices i ON i.id = dr.invoice_id JOIN requesters rq ON rq.id = dr.requester_id JOIN users u ON u.id = de.actor_id
       LEFT JOIN delivery_request_items dri ON dri.delivery_request_id = dr.id
       WHERE de.event_type <> 'approved' AND de.occurred_at BETWEEN $1 AND $2
       GROUP BY i.invoice_number, rq.name, de.event_type, u.name, de.occurred_at, de.reason, de.sequence
       ORDER BY de.occurred_at, de.sequence`, [range.startUtc, range.endUtc]);
        const warehouseSheet = workbook.addWorksheet('تاریخچه انبارداری');
        warehouseSheet.addRow(['شماره فاکتور', 'درخواست‌کننده', 'عملیات', 'اقلام تأییدشده', 'انباردار', 'زمان دقیق', 'دلیل']);
        const eventLabels = { delivered: 'تحویل کالا', cancelled: 'لغو تحویل', returned: 'برگشت از تحویل', redelivered: 'تحویل مجدد' };
        for (const row of warehouse.rows)
            warehouseSheet.addRow([
                row.invoice_number, row.requester, eventLabels[row.event_type] ?? row.event_type, row.products ?? 'تمام فاکتور ـ جزئیات ناموجود',
                row.actor, formatJalaliDateTime(row.occurred_at), row.reason ?? '—'
            ]);
        styleSheet(warehouseSheet, [18, 23, 19, 42, 22, 23, 35]);
        const financial = await this.db.query(`SELECT i.invoice_number, fc.created_at, fc.changes, im.original_filename AS filename, u.name AS importer
       FROM financial_changes fc JOIN invoices i ON i.id = fc.invoice_id JOIN imports im ON im.id = fc.import_id
       JOIN users u ON u.id = im.imported_by WHERE fc.created_at BETWEEN $1 AND $2 ORDER BY fc.created_at`, [range.startUtc, range.endUtc]);
        const financialSheet = workbook.addWorksheet('تغییرات مالی');
        financialSheet.addRow(['شماره فاکتور', 'زمان تغییر', 'فایل منبع', 'واردکننده', 'جزئیات قدیم/جدید']);
        for (const row of financial.rows)
            financialSheet.addRow([
                row.invoice_number, formatJalaliDateTime(row.created_at), row.filename, row.importer, JSON.stringify(row.changes)
            ]);
        styleSheet(financialSheet, [18, 23, 34, 22, 75]);
        return workbook;
    }
    async save(range, filename) {
        const outputDir = resolve('storage/reports');
        await mkdir(outputDir, { recursive: true });
        const path = resolve(outputDir, filename);
        await (await this.build(range)).xlsx.writeFile(path);
        return path;
    }
}
//# sourceMappingURL=report-service.js.map