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

const STATUS_LABELS = {
    open: 'Em aberto',
    in_progress: 'Em andamento',
    finished: 'Finalizada',
    canceled: 'Cancelada'
};

const normalizeStatus = (status) => {
    const normalizedStatus = String(status || 'open').trim().toLowerCase();
    if (['open', 'em_aberto', 'em aberto', 'aberta'].includes(normalizedStatus)) return 'open';
    if (['in_progress', 'andamento', 'em andamento'].includes(normalizedStatus)) return 'in_progress';
    if (['finished', 'finalizada', 'finalizado'].includes(normalizedStatus)) return 'finished';
    if (['canceled', 'cancelada', 'cancelado', 'cancelled'].includes(normalizedStatus)) return 'canceled';
    return normalizedStatus || 'open';
};

const normalizeDateParam = (value) => {
    if (!value) return null;
    const normalizedValue = String(value).trim();
    const isoMatch = normalizedValue.match(/^(\d{4})[-/](\d{1,2})[-/](\d{1,2})/);
    if (isoMatch) {
        const [, year, month, day] = isoMatch;
        const date = new Date(`${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}T00:00:00`);
        return Number.isNaN(date.getTime()) ? null : `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
    }

    const brMatch = normalizedValue.match(/^(\d{1,2})[-/](\d{1,2})[-/](\d{4})/);
    if (brMatch) {
        const [, day, month, year] = brMatch;
        const date = new Date(`${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}T00:00:00`);
        return Number.isNaN(date.getTime()) ? null : `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
    }

    return null;
};

const formatDate = (value) => {
    if (!value) return null;
    if (value instanceof Date && !Number.isNaN(value.getTime())) {
        return value.toISOString().slice(0, 10);
    }
    return String(value).slice(0, 10);
};

const normalizeText = (value) => String(value || '').trim();

const normalizeQuantity = (value) => {
    const normalizedValue = typeof value === 'string' && value.includes(',')
        ? value.replace(/\./g, '').replace(',', '.')
        : value;
    const quantity = Number(normalizedValue);
    if (!Number.isFinite(quantity) || quantity <= 0) return 0;
    return quantity;
};

const normalizeNullableQuantity = (value) => {
    const quantity = normalizeQuantity(value);
    return quantity || null;
};

const normalizeInteger = (value) => {
    const number = Number(value);
    return Number.isInteger(number) ? number : null;
};

const normalizeSku = (value) => normalizeText(value).toUpperCase();

const normalizeUnit = (value) => normalizeText(value).toLowerCase();

const normalizeCategoryName = (name) => normalizeText(name)
    .normalize('NFD')
    .replace(/\p{Diacritic}/gu, '')
    .toUpperCase();

const classifyCatalogCategoryName = (name, type) => {
    const normalizedName = normalizeCategoryName(name);
    if (type === 'finished_product') {
        if (/\bDTF\b/.test(normalizedName)) return 'DTF';
        if (/\b(?:MOLETOM|CANGURU)\b/.test(normalizedName)) return 'Moletom';
        if (/\b(?:OVERSIZE|OVERSIZED)\b/.test(normalizedName)) return 'Oversized';
        if (/\bINFANTIL\b/.test(normalizedName)) return 'Camiseta infantil';
        if (/\b(?:BONE|ACESSORIO|ACESSORIOS)\b/.test(normalizedName)) return 'Acessórios';
        return 'Camiseta regular';
    }
    if (/\b(?:MALHA|TECIDO|RIBANA|FIO)\b/.test(normalizedName)) return 'Malha';
    if (/\b(?:ETIQUETA|TAG|EMBALAGEM|SACO|SACOLA)\b/.test(normalizedName)) return 'Embalagens';
    if (/\b(?:ATACADOR|ILHOS|LINHA|FITA)\b/.test(normalizedName)) return 'Aviamentos';
    if (/\bDTF\b/.test(normalizedName)) return 'DTF';
    return 'Insumos gerais';
};

const ensureCategoryId = async (client, name) => {
    const result = await client.query(`
        INSERT INTO catalog_categories (name, description, updated_at)
        VALUES ($1, $2, CURRENT_TIMESTAMP)
        ON CONFLICT (name) DO UPDATE
        SET updated_at = catalog_categories.updated_at
        RETURNING id;
    `, [name, 'Categoria criada automaticamente pela sincronização de produção.']);

    return result.rows[0].id;
};

const upsertCatalogProductFromSync = async (client, { sku, name, type, categoryName, notes }) => {
    const normalizedSku = normalizeSku(sku);
    const normalizedName = normalizeText(name);
    if (!normalizedSku || !normalizedName) return null;

    const categoryId = await ensureCategoryId(client, categoryName || classifyCatalogCategoryName(normalizedName, type));
    const result = await client.query(`
        INSERT INTO catalog_products (
            type, sku, name, category_id, notes, updated_at
        )
        VALUES ($1, $2, $3, $4, $5, CURRENT_TIMESTAMP)
        ON CONFLICT (sku) DO UPDATE
        SET type = EXCLUDED.type,
            name = EXCLUDED.name,
            category_id = COALESCE(catalog_products.category_id, EXCLUDED.category_id),
            notes = COALESCE(NULLIF(catalog_products.notes, ''), EXCLUDED.notes),
            updated_at = CURRENT_TIMESTAMP
        RETURNING id;
    `, [
        type,
        normalizedSku,
        normalizedName,
        categoryId,
        normalizeText(notes) || null
    ]);

    return result.rows[0].id;
};

const upsertConsumptionReferencesFromComponents = async (client, orderId, order, components) => {
    if (!order.productSku || !components.length) {
        return { referenceCount: 0, skippedReferenceCount: components.length };
    }

    const productId = await upsertCatalogProductFromSync(client, {
        sku: order.productSku,
        name: order.productDescription,
        type: 'finished_product',
        categoryName: classifyCatalogCategoryName(order.productDescription, 'finished_product'),
        notes: `Sincronizado da OP ${order.number || order.tinyId || orderId}.`
    });

    if (!productId) return { referenceCount: 0, skippedReferenceCount: components.length };

    let referenceCount = 0;
    let skippedReferenceCount = 0;

    for (const component of components) {
        const materialId = await upsertCatalogProductFromSync(client, {
            sku: component.componentSku,
            name: component.componentName,
            type: 'raw_material',
            categoryName: classifyCatalogCategoryName(component.componentName, 'raw_material'),
            notes: `Sincronizado da composição da OP ${order.number || order.tinyId || orderId}.`
        });

        if (!materialId || !component.quantityPerUnit) {
            skippedReferenceCount += 1;
            continue;
        }

        const unit = normalizeUnit(component.unit);
        const generalYield = unit === 'kg' ? 1 / component.quantityPerUnit : null;

        const existingReferenceResult = await client.query(`
            SELECT id
            FROM consumption_references
            WHERE product_id = $1
              AND COALESCE(material_product_id, 0) = $2
              AND COALESCE(color, '') = ''
              AND COALESCE(source, 'manual') = 'tiny_op'
            LIMIT 1;
        `, [productId, materialId]);

        if (existingReferenceResult.rows.length) {
            await client.query(`
                UPDATE consumption_references
                SET general_yield = $1,
                    consumption_quantity = $2,
                    consumption_unit = $3,
                    last_production_order_id = $4,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = $5;
            `, [
                generalYield,
                component.quantityPerUnit,
                component.unit || null,
                orderId,
                existingReferenceResult.rows[0].id
            ]);
        } else {
            await client.query(`
                INSERT INTO consumption_references (
                    product_id, material_product_id, general_yield, consumption_quantity,
                    consumption_unit, source, last_production_order_id, updated_at
                )
                VALUES ($1, $2, $3, $4, $5, 'tiny_op', $6, CURRENT_TIMESTAMP);
            `, [
                productId,
                materialId,
                generalYield,
                component.quantityPerUnit,
                component.unit || null,
                orderId
            ]);
        }

        referenceCount += 1;
    }

    return { referenceCount, skippedReferenceCount };
};

const mapComponent = (component) => ({
    id: component.id,
    componentTinyId: component.component_tiny_id || '',
    componentSku: component.component_sku || '',
    componentName: component.component_name || '',
    quantityPerUnit: Number(component.quantity_per_unit || 0),
    totalQuantity: Number(component.total_quantity || 0),
    unit: component.unit || ''
});

const mapStep = (step) => ({
    id: step.id,
    stepNumber: step.step_number === null ? null : Number(step.step_number),
    name: step.name || '',
    startDate: formatDate(step.start_date),
    endDate: formatDate(step.end_date),
    status: step.status || '',
    color: step.color || ''
});

const mapProductionOrderRow = (row) => {
    const status = normalizeStatus(row.status);

    return {
        id: row.id,
        tinyId: row.tiny_id || '',
        number: row.number || '',
        status,
        statusLabel: STATUS_LABELS[status] || row.status || 'Em aberto',
        orderReference: row.order_reference || '',
        issueDate: formatDate(row.issue_date),
        expectedDate: formatDate(row.expected_date),
        productSku: row.product_sku || '',
        productDescription: row.product_description || '',
        quantity: Number(row.quantity || 0),
        unit: row.unit || 'UN',
        integrationStatus: row.integration_status || '',
        notes: row.notes || '',
        supplier: row.supplier || '',
        lotCode: row.lot_code || '',
        rollQuantity: row.roll_quantity === null ? null : Number(row.roll_quantity),
        fabricKg: row.fabric_kg === null ? null : Number(row.fabric_kg),
        ribKg: row.rib_kg === null ? null : Number(row.rib_kg),
        yieldPiecesPerKg: row.yield_pieces_per_kg === null ? null : Number(row.yield_pieces_per_kg),
        markers: Array.isArray(row.markers) ? row.markers.filter(Boolean) : [],
        components: Array.isArray(row.components) ? row.components.map(mapComponent) : [],
        steps: Array.isArray(row.steps) ? row.steps.map(mapStep) : [],
        createdAt: row.created_at || null,
        updatedAt: row.updated_at || null
    };
};

const getOrderById = async (id, client = pool) => {
    const result = await client.query(`
        SELECT
            po.id,
            po.tiny_id,
            po.number,
            po.status,
            po.order_reference,
            po.issue_date,
            po.expected_date,
            po.product_sku,
            po.product_description,
            po.quantity,
            po.unit,
            po.integration_status,
            po.notes,
            po.supplier,
            po.lot_code,
            po.roll_quantity,
            po.fabric_kg,
            po.rib_kg,
            po.yield_pieces_per_kg,
            po.created_at,
            po.updated_at,
            COALESCE(
                (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                            'label', marker.label,
                            'color', marker.color
                        )
                        ORDER BY marker.label
                    )
                    FROM production_order_markers marker
                    WHERE marker.production_order_id = po.id
                ),
                '[]'::json
            ) as markers,
            COALESCE(
                (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                            'id', component.id,
                            'component_tiny_id', component.component_tiny_id,
                            'component_sku', component.component_sku,
                            'component_name', component.component_name,
                            'quantity_per_unit', component.quantity_per_unit,
                            'total_quantity', component.total_quantity,
                            'unit', component.unit
                        )
                        ORDER BY component.id
                    )
                    FROM production_order_components component
                    WHERE component.production_order_id = po.id
                ),
                '[]'::json
            ) as components,
            COALESCE(
                (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                            'id', step.id,
                            'step_number', step.step_number,
                            'name', step.name,
                            'start_date', step.start_date,
                            'end_date', step.end_date,
                            'status', step.status,
                            'color', step.color
                        )
                        ORDER BY step.step_number NULLS LAST, step.id
                    )
                    FROM production_order_steps step
                    WHERE step.production_order_id = po.id
                ),
                '[]'::json
            ) as steps
        FROM production_orders po
        WHERE po.id = $1;
    `, [id]);

    return result.rows[0] ? mapProductionOrderRow(result.rows[0]) : null;
};

const baseProductionOrderSelect = `
    SELECT
        po.id,
        po.tiny_id,
        po.number,
        po.status,
        po.order_reference,
        po.issue_date,
        po.expected_date,
        po.product_sku,
        po.product_description,
        po.quantity,
        po.unit,
        po.integration_status,
        po.notes,
        po.supplier,
        po.lot_code,
        po.roll_quantity,
        po.fabric_kg,
        po.rib_kg,
        po.yield_pieces_per_kg,
        po.created_at,
        po.updated_at,
        COALESCE(
            (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'label', marker.label,
                        'color', marker.color
                    )
                    ORDER BY marker.label
                )
                FROM production_order_markers marker
                WHERE marker.production_order_id = po.id
            ),
            '[]'::json
        ) as markers,
        COALESCE(
            (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'id', component.id,
                        'component_tiny_id', component.component_tiny_id,
                        'component_sku', component.component_sku,
                        'component_name', component.component_name,
                        'quantity_per_unit', component.quantity_per_unit,
                        'total_quantity', component.total_quantity,
                        'unit', component.unit
                    )
                    ORDER BY component.id
                )
                FROM production_order_components component
                WHERE component.production_order_id = po.id
            ),
            '[]'::json
        ) as components,
        COALESCE(
            (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'id', step.id,
                        'step_number', step.step_number,
                        'name', step.name,
                        'start_date', step.start_date,
                        'end_date', step.end_date,
                        'status', step.status,
                        'color', step.color
                    )
                    ORDER BY step.step_number NULLS LAST, step.id
                )
                FROM production_order_steps step
                WHERE step.production_order_id = po.id
            ),
            '[]'::json
        ) as steps
    FROM production_orders po
`;

const listProductionOrders = async (filters = {}) => {
    const params = [];
    const where = [];
    const normalizedStart = normalizeDateParam(filters.start);
    const normalizedEnd = normalizeDateParam(filters.end);
    const normalizedSearch = String(filters.search || '').trim();

    if (normalizedStart) {
        params.push(normalizedStart);
        where.push(`COALESCE(po.issue_date, po.expected_date, po.created_at::date) >= $${params.length}::date`);
    }

    if (normalizedEnd) {
        params.push(normalizedEnd);
        where.push(`COALESCE(po.issue_date, po.expected_date, po.created_at::date) <= $${params.length}::date`);
    }

    if (normalizedSearch) {
        params.push(`%${normalizedSearch}%`);
        where.push(`(
            po.number ILIKE $${params.length}
            OR po.order_reference ILIKE $${params.length}
            OR po.product_sku ILIKE $${params.length}
            OR po.product_description ILIKE $${params.length}
        )`);
    }

    const result = await pool.query(`
        ${baseProductionOrderSelect}
        ${where.length ? `WHERE ${where.join(' AND ')}` : ''}
        ORDER BY
            COALESCE(po.issue_date, po.created_at::date) DESC,
            CASE WHEN po.number ~ '^\\d+$' THEN po.number::bigint ELSE NULL END DESC NULLS LAST,
            po.id DESC;
    `, params);

    const orders = result.rows.map(mapProductionOrderRow);
    const counts = orders.reduce((nextCounts, order) => {
        nextCounts.all += 1;
        nextCounts[order.status] = (nextCounts[order.status] || 0) + 1;
        return nextCounts;
    }, {
        all: 0,
        open: 0,
        in_progress: 0,
        finished: 0,
        canceled: 0
    });

    return { orders, counts };
};

const createProductionOrders = async (orders = []) => {
    const client = await pool.connect();

    try {
        await client.query('BEGIN');
        const created = [];
        const skipped = [];

        for (const order of orders) {
            const productDescription = normalizeText(order.productDescription);
            const productSku = normalizeText(order.productSku);
            const orderReference = normalizeText(order.orderReference);
            const quantity = normalizeQuantity(order.quantity);

            if (!productDescription || !quantity) {
                skipped.push({ productSku, productDescription, reason: 'invalid_payload' });
                continue;
            }

            const duplicateResult = await client.query(`
                SELECT id
                FROM production_orders
                WHERE COALESCE(order_reference, '') = $1
                  AND COALESCE(product_sku, '') = $2
                  AND status IN ('open', 'in_progress')
                LIMIT 1;
            `, [orderReference, productSku]);

            if (duplicateResult.rows.length) {
                skipped.push({ productSku, productDescription, reason: 'duplicate_open_order' });
                continue;
            }

            const insertResult = await client.query(`
                INSERT INTO production_orders (
                    tiny_id,
                    number,
                    status,
                    order_reference,
                    issue_date,
                    expected_date,
                    product_sku,
                    product_description,
                    quantity,
                    unit,
                    integration_status,
                    tiny_payload
                )
                VALUES (
                    NULL,
                    NULL,
                    $1,
                    $2,
                    $3,
                    $4,
                    $5,
                    $6,
                    $7,
                    $8,
                    $9,
                    $10::jsonb
                )
                RETURNING id;
            `, [
                normalizeStatus(order.status || 'open'),
                orderReference,
                normalizeDateParam(order.issueDate) || formatDate(new Date()),
                normalizeDateParam(order.expectedDate),
                productSku,
                productDescription,
                quantity,
                normalizeText(order.unit) || 'UN',
                normalizeText(order.integrationStatus) || 'Local',
                JSON.stringify(order.metadata || {})
            ]);

            const orderId = insertResult.rows[0].id;
            await client.query(`
                UPDATE production_orders
                SET number = $1, updated_at = CURRENT_TIMESTAMP
                WHERE id = $2;
            `, [normalizeText(order.number) || `OP-${String(orderId).padStart(5, '0')}`, orderId]);

            const markers = Array.isArray(order.markers) ? order.markers : [];
            for (const marker of markers) {
                const label = normalizeText(marker.label);
                if (!label) continue;
                await client.query(`
                    INSERT INTO production_order_markers (production_order_id, label, color)
                    VALUES ($1, $2, $3)
                    ON CONFLICT (production_order_id, label) DO UPDATE SET color = EXCLUDED.color;
                `, [orderId, label, normalizeText(marker.color) || null]);
            }

            const createdOrder = await getOrderById(orderId, client);
            if (createdOrder) created.push(createdOrder);
        }

        await client.query('COMMIT');
        return { created, skipped };
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
};

const resolveTinyOrderPayload = (payload = {}) => {
    const order = payload.order && typeof payload.order === 'object' ? payload.order : payload;
    const quantity = normalizeQuantity(order.quantity);
    const productDescription = normalizeText(order.productDescription || order.productName || order.produto || order.descricao);

    if (!productDescription || !quantity) {
        const error = new Error('Produto e quantidade da OP são obrigatórios.');
        error.statusCode = 400;
        throw error;
    }

    return {
        tinyId: normalizeText(order.tinyId || order.tiny_id || order.idTiny || order.id),
        number: normalizeText(order.number || order.numero || order.numeroOp || order.productionOrderNumber),
        status: normalizeStatus(order.status || order.situacao || 'in_progress'),
        orderReference: normalizeText(order.orderReference || order.reference || order.plano || order.pedidos),
        issueDate: normalizeDateParam(order.issueDate || order.date || order.data),
        expectedDate: normalizeDateParam(order.expectedDate || order.dataPrevista),
        productSku: normalizeText(order.productSku || order.sku || order.codigo || order.codigoSku),
        productDescription,
        quantity,
        unit: normalizeText(order.unit || order.unidade) || 'UN',
        integrationStatus: normalizeText(order.integrationStatus) || 'Tiny',
        notes: normalizeText(order.notes || order.observations || order.observacoes),
        supplier: normalizeText(order.supplier || order.fornecedor),
        lotCode: normalizeText(order.lotCode || order.lote),
        rollQuantity: normalizeNullableQuantity(order.rollQuantity || order.quantidadeRolos),
        fabricKg: normalizeNullableQuantity(order.fabricKg || order.quilosMalha),
        ribKg: normalizeNullableQuantity(order.ribKg || order.quilosRibana),
        yieldPiecesPerKg: normalizeNullableQuantity(order.yieldPiecesPerKg || order.rendimento),
        rawPayload: payload
    };
};

const normalizeComponents = (components = [], orderQuantity = 0) => (
    Array.isArray(components) ? components : []
).map(component => {
    const quantityPerUnit = normalizeQuantity(component.quantityPerUnit || component.quantity || component.quantidade);
    const totalQuantity = normalizeQuantity(component.totalQuantity || component.quantidadeTotal) || quantityPerUnit * orderQuantity;

    return {
        componentTinyId: normalizeText(component.componentTinyId || component.idComponente || component.id_componente || component.id),
        componentSku: normalizeText(component.componentSku || component.sku || component.codigo || component.code),
        componentName: normalizeText(component.componentName || component.name || component.nome || component.produto),
        quantityPerUnit,
        totalQuantity,
        unit: normalizeText(component.unit || component.unidade)
    };
}).filter(component => component.componentName);

const normalizeSteps = (steps = []) => (
    Array.isArray(steps) ? steps : []
).map(step => ({
    stepNumber: normalizeInteger(step.stepNumber || step.number || step.nro || step.numero),
    name: normalizeText(step.name || step.etapa || step.posto || step.description),
    startDate: normalizeDateParam(step.startDate || step.dataInicio),
    endDate: normalizeDateParam(step.endDate || step.dataFim),
    status: normalizeText(step.status || step.situacao),
    color: normalizeText(step.color || step.cor)
})).filter(step => step.name);

const TINY_OLIST_V3_COMPOSITION_SOURCE = 'tiny_olist_v3';
const TINY_OLIST_V3_COMPOSITION_PREFIX = 'STRUCTURE-V3-';

const isTinyOlistV3Composition = (order) => order.tinyId.startsWith(TINY_OLIST_V3_COMPOSITION_PREFIX);

const getTinyProductIdFromCompositionReference = (tinyId) => (
    tinyId.slice(TINY_OLIST_V3_COMPOSITION_PREFIX.length)
);

const normalizeCompositionComponents = (components) => {
    const byIdentity = new Map();

    for (const component of components) {
        const componentIdentity = normalizeSku(component.componentSku)
            || normalizeText(component.componentTinyId)
            || normalizeText(component.componentName).toUpperCase();
        if (!componentIdentity) continue;

        byIdentity.set(componentIdentity, {
            ...component,
            componentSku: normalizeSku(component.componentSku),
            componentIdentity
        });
    }

    return [...byIdentity.values()];
};

const mapProductCompositionRow = (row) => ({
    id: Number(row.id),
    source: row.source,
    externalSourceId: row.external_source_id,
    finishedProductSku: row.finished_product_sku || '',
    finishedProductDescription: row.finished_product_description || '',
    finishedProductUnit: row.finished_product_unit || 'UN',
    finishedTinyProductId: row.finished_tiny_product_id || '',
    sourceMetadata: row.source_metadata || {},
    lastSyncedAt: row.last_synced_at || null,
    components: Array.isArray(row.components) ? row.components.map(component => ({
        id: Number(component.id),
        componentTinyId: component.component_tiny_id || '',
        componentSku: component.component_sku || '',
        componentName: component.component_name || '',
        quantityPerUnit: Number(component.quantity_per_unit || 0),
        unit: component.unit || '',
        productId: component.component_product_id || null
    })) : []
});

const findProductCompositions = async (productId, client = pool) => {
    const identity = normalizeText(productId);

    const result = await client.query(`
        SELECT
            composition.id,
            composition.source,
            composition.external_source_id,
            composition.finished_product_sku,
            composition.finished_product_description,
            composition.finished_product_unit,
            composition.finished_tiny_product_id,
            composition.source_metadata,
            composition.last_synced_at,
            COALESCE(
                (
                    SELECT JSON_AGG(
                        JSON_BUILD_OBJECT(
                            'id', component.id,
                            'component_tiny_id', component.component_tiny_id,
                            'component_sku', component.component_sku,
                            'component_name', component.component_name,
                            'quantity_per_unit', component.quantity_per_unit,
                            'unit', component.unit,
                            'component_product_id', COALESCE(
                                (
                                    SELECT stock_match.produto_id
                                    FROM stock stock_match
                                    WHERE stock_match.produto_id = component.component_tiny_id
                                       OR stock_match.produto_id = component.component_sku
                                    LIMIT 1
                                ),
                                (
                                    SELECT order_match.produto_id
                                    FROM orders order_match
                                    WHERE order_match.produto_id = component.component_tiny_id
                                       OR order_match.produto_id = component.component_sku
                                    LIMIT 1
                                )
                            )
                        )
                        ORDER BY component.id
                    )
                    FROM product_composition_components component
                    WHERE component.product_composition_id = composition.id
                ),
                '[]'::json
            ) AS components
        FROM product_compositions composition
        WHERE composition.source = $1
          AND (
              $2 = ''
              OR composition.finished_tiny_product_id = $2
              OR composition.finished_product_sku = UPPER($2)
          )
        ORDER BY composition.finished_product_sku, composition.finished_product_description;
    `, [TINY_OLIST_V3_COMPOSITION_SOURCE, identity]);

    return result.rows.map(mapProductCompositionRow);
};

const getProductComposition = async (productId, client = pool) => (
    (await findProductCompositions(productId, client))[0] || null
);

const listProductCompositions = async (client = pool) => findProductCompositions('', client);

const upsertTinyProductComposition = async (order, components) => {
    const finishedTinyProductId = getTinyProductIdFromCompositionReference(order.tinyId);
    const finishedProductSku = normalizeSku(order.productSku);
    const finishedProductIdentity = finishedProductSku || finishedTinyProductId;

    if (!finishedProductIdentity) {
        const error = new Error('SKU ou ID Tiny do produto acabado é obrigatório para a composição.');
        error.statusCode = 400;
        throw error;
    }

    const normalizedComponents = normalizeCompositionComponents(components);
    const client = await pool.connect();

    try {
        await client.query('BEGIN');
        const existingResult = await client.query(`
            SELECT id
            FROM product_compositions
            WHERE source = $1 AND finished_product_identity = $2
            LIMIT 1;
        `, [TINY_OLIST_V3_COMPOSITION_SOURCE, finishedProductIdentity]);

        let compositionId = existingResult.rows[0]?.id;
        const values = [
            order.tinyId,
            finishedProductIdentity,
            finishedProductSku || null,
            order.productDescription,
            order.unit,
            finishedTinyProductId || null,
            JSON.stringify({
                provider: 'Tiny/Olist V3',
                notes: order.notes || null,
                payload: order.rawPayload
            })
        ];

        if (compositionId) {
            await client.query(`
                UPDATE product_compositions
                SET external_source_id = $1,
                    finished_product_sku = $3,
                    finished_product_description = $4,
                    finished_product_unit = $5,
                    finished_tiny_product_id = $6,
                    source_metadata = $7::jsonb,
                    last_synced_at = CURRENT_TIMESTAMP,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = $8;
            `, [...values, compositionId]);
        } else {
            const insertResult = await client.query(`
                INSERT INTO product_compositions (
                    source, external_source_id, finished_product_identity,
                    finished_product_sku, finished_product_description, finished_product_unit,
                    finished_tiny_product_id, source_metadata, last_synced_at
                )
                VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb, CURRENT_TIMESTAMP)
                RETURNING id;
            `, [
                TINY_OLIST_V3_COMPOSITION_SOURCE,
                ...values
            ]);
            compositionId = insertResult.rows[0].id;
        }

        await client.query('DELETE FROM product_composition_components WHERE product_composition_id = $1;', [compositionId]);
        for (const component of normalizedComponents) {
            await client.query(`
                INSERT INTO product_composition_components (
                    product_composition_id, component_identity, component_tiny_id,
                    component_sku, component_name, quantity_per_unit, unit
                )
                VALUES ($1, $2, NULLIF($3, ''), NULLIF($4, ''), $5, $6, NULLIF($7, ''));
            `, [
                compositionId,
                component.componentIdentity,
                component.componentTinyId,
                component.componentSku,
                component.componentName,
                component.quantityPerUnit,
                component.unit
            ]);
        }

        await client.query('COMMIT');
        return {
            composition: await getProductComposition(finishedTinyProductId || finishedProductSku),
            componentCount: normalizedComponents.length
        };
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
};

const upsertTinyProductionOrderDetail = async (payload = {}) => {
    const order = resolveTinyOrderPayload(payload);
    const components = normalizeComponents(payload.components || payload.composition || payload.composicao, order.quantity);

    if (isTinyOlistV3Composition(order)) {
        return upsertTinyProductComposition(order, components);
    }

    const steps = normalizeSteps(payload.steps || payload.etapas);
    const client = await pool.connect();

    try {
        await client.query('BEGIN');

        const existingResult = await client.query(`
            SELECT id
            FROM production_orders
            WHERE ($1 <> '' AND tiny_id = $1)
               OR ($2 <> '' AND number = $2)
            ORDER BY CASE WHEN tiny_id = $1 THEN 0 ELSE 1 END
            LIMIT 1;
        `, [order.tinyId, order.number]);

        let orderId = existingResult.rows[0]?.id;

        if (orderId) {
            await client.query(`
                UPDATE production_orders
                SET tiny_id = COALESCE(NULLIF($1, ''), tiny_id),
                    number = COALESCE(NULLIF($2, ''), number),
                    status = $3,
                    order_reference = NULLIF($4, ''),
                    issue_date = $5,
                    expected_date = $6,
                    product_sku = NULLIF($7, ''),
                    product_description = $8,
                    quantity = $9,
                    unit = $10,
                    integration_status = $11,
                    notes = NULLIF($12, ''),
                    supplier = NULLIF($13, ''),
                    lot_code = NULLIF($14, ''),
                    roll_quantity = $15,
                    fabric_kg = $16,
                    rib_kg = $17,
                    yield_pieces_per_kg = $18,
                    tiny_payload = $19::jsonb,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = $20;
            `, [
                order.tinyId,
                order.number,
                order.status,
                order.orderReference,
                order.issueDate,
                order.expectedDate,
                order.productSku,
                order.productDescription,
                order.quantity,
                order.unit,
                order.integrationStatus,
                order.notes,
                order.supplier,
                order.lotCode,
                order.rollQuantity,
                order.fabricKg,
                order.ribKg,
                order.yieldPiecesPerKg,
                JSON.stringify(order.rawPayload),
                orderId
            ]);
        } else {
            const insertResult = await client.query(`
                INSERT INTO production_orders (
                    tiny_id, number, status, order_reference, issue_date, expected_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 (
                    NULLIF($1, ''), NULLIF($2, ''), $3, NULLIF($4, ''), $5, $6,
                    NULLIF($7, ''), $8, $9, $10, $11, NULLIF($12, ''),
                    NULLIF($13, ''), NULLIF($14, ''), $15, $16, $17, $18, $19::jsonb
                )
                RETURNING id;
            `, [
                order.tinyId,
                order.number,
                order.status,
                order.orderReference,
                order.issueDate,
                order.expectedDate,
                order.productSku,
                order.productDescription,
                order.quantity,
                order.unit,
                order.integrationStatus,
                order.notes,
                order.supplier,
                order.lotCode,
                order.rollQuantity,
                order.fabricKg,
                order.ribKg,
                order.yieldPiecesPerKg,
                JSON.stringify(order.rawPayload)
            ]);
            orderId = insertResult.rows[0].id;
        }

        await client.query('DELETE FROM production_order_components WHERE production_order_id = $1;', [orderId]);
        for (const component of components) {
            await client.query(`
                INSERT INTO production_order_components (
                    production_order_id, component_tiny_id, component_sku, component_name,
                    quantity_per_unit, total_quantity, unit
                )
                VALUES ($1, NULLIF($2, ''), NULLIF($3, ''), $4, $5, $6, NULLIF($7, ''));
            `, [
                orderId,
                component.componentTinyId,
                component.componentSku,
                component.componentName,
                component.quantityPerUnit,
                component.totalQuantity,
                component.unit
            ]);
        }

        await client.query('DELETE FROM production_order_steps WHERE production_order_id = $1;', [orderId]);
        for (const step of steps) {
            await client.query(`
                INSERT INTO production_order_steps (
                    production_order_id, step_number, name, start_date, end_date, status, color
                )
                VALUES ($1, $2, $3, $4, $5, NULLIF($6, ''), NULLIF($7, ''));
            `, [
                orderId,
                step.stepNumber,
                step.name,
                step.startDate,
                step.endDate,
                step.status,
                step.color
            ]);
        }

        const referenceSync = await upsertConsumptionReferencesFromComponents(client, orderId, order, components);

        await client.query('COMMIT');
        return {
            order: await getOrderById(orderId),
            componentCount: components.length,
            stepCount: steps.length,
            referenceCount: referenceSync.referenceCount,
            skippedReferenceCount: referenceSync.skippedReferenceCount
        };
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
};

const updateProductionOrderStatus = async (id, status) => {
    const normalizedStatus = normalizeStatus(status);

    if (!STATUS_LABELS[normalizedStatus]) {
        const error = new Error('Invalid production order status');
        error.statusCode = 400;
        throw error;
    }

    const result = await pool.query(`
        UPDATE production_orders
        SET status = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2
        RETURNING id;
    `, [normalizedStatus, id]);

    if (!result.rows.length) {
        const error = new Error('Production order not found');
        error.statusCode = 404;
        throw error;
    }

    return getOrderById(id);
};

module.exports = {
    createProductionOrders,
    getProductComposition,
    listProductCompositions,
    listProductionOrders,
    normalizeStatus,
    upsertTinyProductionOrderDetail,
    updateProductionOrderStatus
};