const { pool } = require('../db');

const FAMILY_KEYS = new Set(['BLCS', 'BLOS', 'BLMC', 'BLPM', 'BLRM']);
const REQUIRED_HEADERS = ['id_op', 'numero_op', 'sku_produto', 'descricao_produto', 'quantidade_produzida', 'data_emissao', 'data_conclusao', 'situacao', 'observacoes'];

const normalizeText = (value) => String(value || '').replace(/\s+/g, ' ').trim();

const normalizeNumber = (value) => {
    const normalized = String(value || '').trim().replace(/\s/g, '');
    if (!normalized) return null;
    const decimal = normalized.includes(',')
        ? normalized.replace(/\./g, '').replace(',', '.')
        : normalized;
    const number = Number(decimal);
    return Number.isFinite(number) && number > 0 ? number : null;
};

const normalizeDate = (value) => {
    const raw = String(value || '').trim();
    const iso = raw.match(/^(\d{4})-(\d{2})-(\d{2})/);
    if (iso) return `${iso[1]}-${iso[2]}-${iso[3]}`;
    const br = raw.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})/);
    return br ? `${br[3]}-${br[2].padStart(2, '0')}-${br[1].padStart(2, '0')}` : null;
};

const normalizeHeader = (header) => String(header || '')
    .replace(/^\uFEFF/, '')
    .normalize('NFD')
    .replace(/\p{Diacritic}/gu, '')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, '_');

const csvCell = (value) => {
    const normalized = value === null || value === undefined ? '' : String(value);
    return /[",\r\n]/.test(normalized) ? `"${normalized.replace(/"/g, '""')}"` : normalized;
};

const parseCsv = (csv) => {
    const rows = [];
    let row = [];
    let field = '';
    let quoted = false;

    for (let index = 0; index < csv.length; index += 1) {
        const character = csv[index];
        const next = csv[index + 1];
        if (character === '"') {
            if (quoted && next === '"') {
                field += '"';
                index += 1;
            } else {
                quoted = !quoted;
            }
        } else if (character === ',' && !quoted) {
            row.push(field);
            field = '';
        } else if ((character === '\n' || character === '\r') && !quoted) {
            if (character === '\r' && next === '\n') index += 1;
            row.push(field);
            if (row.some(value => value.length > 0)) rows.push(row);
            row = [];
            field = '';
        } else {
            field += character;
        }
    }

    row.push(field);
    if (row.some(value => value.length > 0)) rows.push(row);
    if (quoted) throw new Error('CSV possui aspas não finalizadas.');
    return rows;
};

const extractObservationFields = (notes) => {
    const raw = String(notes || '');
    const lineValue = (pattern) => raw.match(pattern)?.[1]?.trim() || '';
    const supplier = lineValue(/(?:^|\n)\s*FORNECEDOR\s*:\s*([^\r\n]+)/i);
    const lotCode = lineValue(/(?:^|\n)\s*(?:-\s*)?LOTE\s*:?[\t ]*([^\r\n]+)/i);
    const yieldMatch = raw.match(/RENDIMENTO\s*:\s*([\d.,]+)(?:\s*[-–]\s*([\d.,]+)\s*KG)?/i);
    const yieldPiecesPerKg = normalizeNumber(yieldMatch?.[1]);
    const bobbinMatch = raw.match(/(?:QUANTIDADE\s+DE\s+ROLOS|ROLOS|BOBINAS)\s*:\s*(\d+(?:[.,]\d+)?)(?:\s*\(\s*X\s*(\d+(?:[.,]\d+)?)\s*\))?(?:\s*[-–]\s*([\d.,]+)\s*KG)?/i);
    const rollBase = normalizeNumber(bobbinMatch?.[1]);
    const rollMultiplier = normalizeNumber(bobbinMatch?.[2]) || 1;
    const explicitRolls = rollBase ? rollBase * rollMultiplier : null;
    const fabricKg = normalizeNumber(raw.match(/(?:QUILOS?\s+DE\s+MALHA|QUILOS?\s+MALHA)\s*:\s*([\d.,]+)(?:\s*KG)?/i)?.[1])
        || normalizeNumber(bobbinMatch?.[3])
        || normalizeNumber(yieldMatch?.[2])
        || normalizeNumber(raw.match(/(?:^|\n)\s*KG\s*:\s*([\d.,]+)(?:\s*KG)?/i)?.[1]);
    const ribKg = normalizeNumber(raw.match(/(?:QUILOS?\s+DE\s+(?:GOLA|RIBANA|VIEZ(?:\s*\([^)]*\))?)|RIBANA)\s*:\s*([\d.,]+)(?:\s*KG)?/i)?.[1]);

    return { supplier, lotCode, yieldPiecesPerKg, rollQuantity: explicitRolls, fabricKg, ribKg };
};

const parseFinalizedProductionOrderCsv = (csv) => {
    if (typeof csv !== 'string' || !csv.trim()) {
        const error = new Error('Selecione um arquivo CSV de OPs finalizadas.');
        error.statusCode = 400;
        throw error;
    }

    const rows = parseCsv(csv);
    if (rows.length < 2) {
        const error = new Error('O CSV não possui OPs para importar.');
        error.statusCode = 400;
        throw error;
    }

    const headers = rows[0].map(normalizeHeader);
    const missingHeaders = REQUIRED_HEADERS.filter(header => !headers.includes(header));
    if (missingHeaders.length) {
        const error = new Error(`CSV sem colunas obrigatórias: ${missingHeaders.join(', ')}.`);
        error.statusCode = 400;
        throw error;
    }

    const headerIndex = Object.fromEntries(headers.map((header, index) => [header, index]));
    const records = [];
    const issues = [];

    rows.slice(1).forEach((row, rowOffset) => {
        const line = rowOffset + 2;
        const value = (header) => row[headerIndex[header]] || '';
        const tinyId = normalizeText(value('id_op'));
        const number = normalizeText(value('numero_op'));
        const productSku = normalizeText(value('sku_produto')).toUpperCase();
        const productDescription = normalizeText(value('descricao_produto'));
        const quantity = normalizeNumber(value('quantidade_produzida'));
        const status = normalizeText(value('situacao')).toLowerCase();
        const notes = String(value('observacoes') || '').trim();

        if (!tinyId || !number || !productSku || !productDescription || !quantity) {
            issues.push({ line, message: 'OP sem identificação, SKU, produto ou quantidade válida.' });
            return;
        }
        if (!/^finalizad[ao]$/.test(status)) {
            issues.push({ line, number, message: 'Ignorada: a OP não está Finalizada.' });
            return;
        }

        const familyKey = productSku.split('.')[0];
        const extracted = extractObservationFields(notes);
        const supplier = normalizeText(value('fornecedor')) || extracted.supplier;
        const lotCode = normalizeText(value('lote')) || extracted.lotCode;
        const rollQuantity = normalizeNumber(value('quantidade_rolos')) || extracted.rollQuantity;
        const fabricKg = normalizeNumber(value('quilos_malha')) || extracted.fabricKg;
        const ribKg = normalizeNumber(value('quilos_ribana')) || extracted.ribKg;
        const yieldPiecesPerKg = normalizeNumber(value('rendimento')) || extracted.yieldPiecesPerKg;
        records.push({
            tinyId,
            number,
            productSku,
            productDescription,
            quantity,
            issueDate: normalizeDate(value('data_emissao')),
            completedDate: normalizeDate(value('data_conclusao')),
            orderReference: normalizeText(value('pedido_relacionado')),
            notes,
            familyKey: FAMILY_KEYS.has(familyKey) ? familyKey : null,
            supplier,
            lotCode,
            rollQuantity,
            fabricKg,
            ribKg,
            yieldPiecesPerKg
        });
    });

    return { records, issues, totalRows: rows.length - 1 };
};

const refreshYieldRecommendations = async (client) => {
    await client.query('DELETE FROM production_yield_recommendations;');
    await client.query('DELETE FROM production_yield_supplier_recommendations;');
    await client.query(`
        INSERT INTO production_yield_recommendations (
            family_key, sample_count, yield_sample_count, roll_sample_count,
            avg_yield_pieces_per_kg, avg_kg_per_roll, recommended_units_per_roll,
            last_completed_date, updated_at
        )
        SELECT
            split_part(product_sku, '.', 1) AS family_key,
            COUNT(*)::int,
            COUNT(yield_pieces_per_kg)::int,
            COUNT(*) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0)::int,
            AVG(yield_pieces_per_kg),
            AVG(fabric_kg / roll_quantity) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0),
            AVG(yield_pieces_per_kg * (fabric_kg / roll_quantity)) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0 AND yield_pieces_per_kg > 0),
            MAX(completed_date),
            CURRENT_TIMESTAMP
        FROM production_orders
        WHERE status = 'finished'
          AND split_part(product_sku, '.', 1) IN ('BLCS', 'BLOS', 'BLMC', 'BLPM', 'BLRM')
        GROUP BY split_part(product_sku, '.', 1);
    `);
    await client.query(`
        INSERT INTO production_yield_supplier_recommendations (
            family_key, supplier, sample_count, yield_sample_count, roll_sample_count,
            avg_yield_pieces_per_kg, avg_kg_per_roll, recommended_units_per_roll,
            last_completed_date, updated_at
        )
        SELECT
            split_part(product_sku, '.', 1) AS family_key,
            supplier,
            COUNT(*)::int,
            COUNT(yield_pieces_per_kg)::int,
            COUNT(*) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0)::int,
            AVG(yield_pieces_per_kg),
            AVG(fabric_kg / roll_quantity) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0),
            AVG(yield_pieces_per_kg * (fabric_kg / roll_quantity)) FILTER (WHERE fabric_kg > 0 AND roll_quantity > 0 AND yield_pieces_per_kg > 0),
            MAX(completed_date),
            CURRENT_TIMESTAMP
        FROM production_orders
        WHERE status = 'finished'
          AND supplier IS NOT NULL
          AND split_part(product_sku, '.', 1) IN ('BLCS', 'BLOS', 'BLMC', 'BLPM', 'BLRM')
        GROUP BY split_part(product_sku, '.', 1), supplier;
    `);
};

const listProductionOrderImportData = async ({ runsPage = 1, runsPageSize = 5 } = {}) => {
    const normalizedRunsPageSize = Math.max(1, Math.min(Number(runsPageSize) || 5, 20));
    const normalizedRunsPage = Math.max(1, Number(runsPage) || 1);
    const runsOffset = (normalizedRunsPage - 1) * normalizedRunsPageSize;
    const [runs, runsCount, recommendations, supplierRecommendations] = await Promise.all([
        pool.query(`SELECT id, filename, status, total_rows, imported_count, updated_count, skipped_count, failed_count, error_message, started_at, completed_at FROM production_order_import_runs ORDER BY started_at DESC LIMIT $1 OFFSET $2;`, [normalizedRunsPageSize, runsOffset]),
        pool.query('SELECT COUNT(*)::int AS count FROM production_order_import_runs;'),
        pool.query(`SELECT family_key, sample_count, yield_sample_count, roll_sample_count, avg_yield_pieces_per_kg, avg_kg_per_roll, recommended_units_per_roll, last_completed_date, updated_at FROM production_yield_recommendations ORDER BY family_key;`),
        pool.query(`SELECT family_key, supplier, sample_count, yield_sample_count, roll_sample_count, avg_yield_pieces_per_kg, avg_kg_per_roll, recommended_units_per_roll, last_completed_date, updated_at FROM production_yield_supplier_recommendations ORDER BY family_key, supplier;`)
    ]);
    return {
        runs: runs.rows.map(row => ({
            id: row.id, filename: row.filename, status: row.status, totalRows: row.total_rows,
            imported: row.imported_count, updated: row.updated_count, skipped: row.skipped_count,
            failed: row.failed_count, errorMessage: row.error_message || '', startedAt: row.started_at,
            completedAt: row.completed_at
        })),
        runsTotal: Number(runsCount.rows[0]?.count || 0),
        runsPage: normalizedRunsPage,
        runsPageSize: normalizedRunsPageSize,
        recommendations: recommendations.rows.map(row => ({
            familyKey: row.family_key, sampleCount: Number(row.sample_count), yieldSampleCount: Number(row.yield_sample_count),
            rollSampleCount: Number(row.roll_sample_count), avgYieldPiecesPerKg: row.avg_yield_pieces_per_kg === null ? null : Number(row.avg_yield_pieces_per_kg),
            avgKgPerRoll: row.avg_kg_per_roll === null ? null : Number(row.avg_kg_per_roll),
            recommendedUnitsPerRoll: row.recommended_units_per_roll === null ? null : Number(row.recommended_units_per_roll),
            lastCompletedDate: row.last_completed_date || null, updatedAt: row.updated_at
        })),
        supplierRecommendations: supplierRecommendations.rows.map(row => ({
            familyKey: row.family_key, supplier: row.supplier, sampleCount: Number(row.sample_count),
            yieldSampleCount: Number(row.yield_sample_count), rollSampleCount: Number(row.roll_sample_count),
            avgYieldPiecesPerKg: row.avg_yield_pieces_per_kg === null ? null : Number(row.avg_yield_pieces_per_kg),
            avgKgPerRoll: row.avg_kg_per_roll === null ? null : Number(row.avg_kg_per_roll),
            recommendedUnitsPerRoll: row.recommended_units_per_roll === null ? null : Number(row.recommended_units_per_roll),
            lastCompletedDate: row.last_completed_date || null, updatedAt: row.updated_at
        }))
    };
};

const exportMissingProductionOrderDataCsv = async () => {
    const result = await pool.query(`
        SELECT tiny_id, number, product_sku, product_description, quantity, issue_date, completed_date, notes,
            order_reference, supplier, lot_code, roll_quantity, fabric_kg, rib_kg, yield_pieces_per_kg
        FROM production_orders
        WHERE status = 'finished'
          AND (
              supplier IS NULL OR lot_code IS NULL OR roll_quantity IS NULL
              OR fabric_kg IS NULL OR yield_pieces_per_kg IS NULL
          )
        ORDER BY completed_date DESC NULLS LAST, id DESC;
    `);
    const headers = [
        'id_op', 'numero_op', 'sku_produto', 'descricao_produto', 'quantidade_produzida', 'data_emissao',
        'data_conclusao', 'situacao', 'observacoes', 'pedido_relacionado', 'fornecedor', 'lote',
        'quantidade_rolos', 'quilos_malha', 'quilos_ribana', 'rendimento', 'campos_pendentes'
    ];
    const rows = result.rows.map((row) => {
        const missing = [
            !row.supplier && 'fornecedor',
            !row.lot_code && 'lote',
            !row.roll_quantity && 'quantidade_rolos',
            !row.fabric_kg && 'quilos_malha',
            !row.yield_pieces_per_kg && 'rendimento'
        ].filter(Boolean).join(', ');
        return [
            row.tiny_id, row.number, row.product_sku, row.product_description, row.quantity, row.issue_date,
            row.completed_date, 'Finalizada', row.notes, row.order_reference, row.supplier, row.lot_code,
            row.roll_quantity, row.fabric_kg, row.rib_kg, row.yield_pieces_per_kg, missing
        ];
    });

    return {
        filename: `ops-finalizadas-pendentes-${new Date().toISOString().slice(0, 10)}.csv`,
        totalRows: rows.length,
        csv: [headers, ...rows].map(row => row.map(csvCell).join(',')).join('\r\n')
    };
};

const importFinalizedProductionOrderCsv = async ({ filename, csv }) => {
    const parsed = parseFinalizedProductionOrderCsv(csv);
    const client = await pool.connect();
    const safeFilename = normalizeText(filename) || 'ops-finalizadas.csv';
    let runId;
    let imported = 0;
    let updated = 0;
    let failed = 0;

    try {
        await client.query('BEGIN');
        const run = await client.query(`
            INSERT INTO production_order_import_runs (filename, status, total_rows, skipped_count)
            VALUES ($1, 'running', $2, $3)
            RETURNING id;
        `, [safeFilename, parsed.totalRows, parsed.issues.length]);
        runId = run.rows[0].id;

        for (const record of parsed.records) {
            try {
                await client.query('SAVEPOINT production_order_import_record;');
                const existing = await client.query('SELECT id FROM production_orders WHERE tiny_id = $1 OR number = $2 LIMIT 1;', [record.tinyId, record.number]);
                const payload = JSON.stringify({ source: 'olist_finalized_op_csv', importedAt: new Date().toISOString(), record });
                if (existing.rows[0]) {
                    await client.query(`
                        UPDATE production_orders SET tiny_id = $1, number = $2, status = 'finished', order_reference = NULLIF($3, ''), issue_date = $4,
                            completed_date = $5, product_sku = $6, product_description = $7, quantity = $8, unit = 'UN', integration_status = 'Olist CSV',
                            notes = NULLIF($9, ''), supplier = NULLIF($10, ''), lot_code = NULLIF($11, ''), roll_quantity = $12, fabric_kg = $13, rib_kg = $14,
                            yield_pieces_per_kg = $15, tiny_payload = $16::jsonb, updated_at = CURRENT_TIMESTAMP
                        WHERE id = $17;
                    `, [record.tinyId, record.number, record.orderReference, record.issueDate, record.completedDate, record.productSku, record.productDescription, record.quantity, record.notes, record.supplier, record.lotCode, record.rollQuantity, record.fabricKg, record.ribKg, record.yieldPiecesPerKg, payload, existing.rows[0].id]);
                    updated += 1;
                } else {
                    await client.query(`
                        INSERT INTO production_orders (tiny_id, number, status, order_reference, issue_date, completed_date, product_sku, product_description, quantity, unit, integration_status, notes, supplier, lot_code, roll_quantity, fabric_kg, rib_kg, yield_pieces_per_kg, tiny_payload)
                        VALUES ($1, $2, 'finished', NULLIF($3, ''), $4, $5, $6, $7, $8, 'UN', 'Olist CSV', NULLIF($9, ''), NULLIF($10, ''), NULLIF($11, ''), $12, $13, $14, $15, $16::jsonb);
                    `, [record.tinyId, record.number, record.orderReference, record.issueDate, record.completedDate, record.productSku, record.productDescription, record.quantity, record.notes, record.supplier, record.lotCode, record.rollQuantity, record.fabricKg, record.ribKg, record.yieldPiecesPerKg, payload]);
                    imported += 1;
                }
                await client.query('RELEASE SAVEPOINT production_order_import_record;');
            } catch (error) {
                await client.query('ROLLBACK TO SAVEPOINT production_order_import_record;');
                failed += 1;
                parsed.issues.push({ number: record.number, message: error.message });
            }
        }

        await refreshYieldRecommendations(client);
        await client.query(`
            UPDATE production_order_import_runs
            SET status = $1, imported_count = $2, updated_count = $3, skipped_count = $4, failed_count = $5, error_message = $6, completed_at = CURRENT_TIMESTAMP
            WHERE id = $7;
        `, [failed ? 'completed_with_errors' : 'completed', imported, updated, parsed.issues.length, failed, parsed.issues.slice(0, 20).map(issue => `OP ${issue.number || `linha ${issue.line}`}: ${issue.message}`).join('\n') || null, runId]);
        await client.query('COMMIT');
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }

    return { runId, totalRows: parsed.totalRows, imported, updated, skipped: parsed.issues.length, failed, issues: parsed.issues.slice(0, 20), ...(await listProductionOrderImportData()) };
};

module.exports = {
    exportMissingProductionOrderDataCsv,
    extractObservationFields,
    importFinalizedProductionOrderCsv,
    listProductionOrderImportData,
    parseCsv,
    parseFinalizedProductionOrderCsv
};