-- ============================================
-- CONFIGURACIÓN Y VALIDACIÓN COMPLETA
-- ============================================

-- PASO 1: Crear reglas de comisión
-- ============================================
TRUNCATE TABLE commission_rules;

INSERT INTO commission_rules (product_line, payment_type, percentage, active, created_at) VALUES
(1, 'cash', 5.00, 1, NOW()),
(1, 'card', 4.50, 1, NOW()),
(1, 'transfer', 4.50, 1, NOW()),
(2, 'cash', 6.00, 1, NOW()),
(2, 'card', 5.50, 1, NOW()),
(7, 'cash', 10.00, 1, NOW()),
(7, 'card', 9.50, 1, NOW());

SELECT '✅ Reglas de comisión creadas' as status;

-- Ver reglas
SELECT
    cr.id,
    l.name as product_line,
    cr.payment_type,
    CONCAT(cr.percentage, '%') as percentage,
    IF(cr.active, 'Sí', 'No') as activa
FROM commission_rules cr
JOIN lines l ON l.id = cr.product_line
ORDER BY cr.product_line, cr.payment_type;

-- PASO 2: Inspeccionar ventas existentes
-- ============================================
SELECT '---' as '---';
SELECT '📊 VENTAS RECIENTES' as status;

SELECT
    s.id,
    s.uuid,
    s.amount,
    s.type,
    COUNT(p.id) as num_payments,
    s.created_at
FROM sales s
LEFT JOIN payments p ON p.sale = s.id
WHERE s.deleted_at IS NULL
GROUP BY s.id
ORDER BY s.id DESC
LIMIT 5;

-- PASO 3: Inspeccionar payments de última venta
-- ============================================
SELECT '---' as '---';
SELECT '💰 PAYMENTS DE ÚLTIMA VENTA' as status;

SET @last_sale = (SELECT id FROM sales WHERE deleted_at IS NULL ORDER BY id DESC LIMIT 1);

SELECT
    p.id,
    p.sale,
    p.payment_type,
    p.amount,
    p.status,
    p.account,
    p.received_at,
    p.created_at
FROM payments p
WHERE p.sale = @last_sale
AND p.deleted_at IS NULL
ORDER BY p.id;

-- PASO 4: Ver items de esa venta (para saber líneas)
-- ============================================
SELECT '---' as '---';
SELECT '📦 ITEMS DE LA VENTA' as status;

SELECT
    si.id,
    si.qty,
    i.name as item_name,
    l.name as line_name,
    si.final_price
FROM sale_items si
JOIN items i ON i.id = si.item
LEFT JOIN lines l ON l.id = i.line
WHERE si.sale = @last_sale
ORDER BY si.id;

-- PASO 5: Ver estructura de tabla payments (verificar campos)
-- ============================================
SELECT '---' as '---';
SELECT '🔍 ESTRUCTURA DE PAYMENTS' as status;

SHOW COLUMNS FROM payments;

-- PASO 6: ¿Hay comisiones ya generadas?
-- ============================================
SELECT '---' as '---';
SELECT '💸 COMISIONES EXISTENTES' as status;

SELECT
    c.id,
    c.payment,
    p.payment_type,
    l.name as product_line,
    c.base_amount,
    CONCAT(c.percentage, '%') as percentage,
    c.commission_amount,
    c.status
FROM commissions c
JOIN payments p ON p.id = c.payment
JOIN lines l ON l.id = c.product_line
WHERE p.sale = @last_sale
ORDER BY c.id;

-- Si no hay comisiones, mostrar mensaje
SELECT IF(
    (SELECT COUNT(*) FROM commissions c JOIN payments p ON p.id = c.payment WHERE p.sale = @last_sale) = 0,
    '⚠️  No hay comisiones generadas aún para esta venta',
    '✅ Hay comisiones generadas'
) as status_comisiones;

-- PASO 7: Ver facturas de la venta (si existen)
-- ============================================
SELECT '---' as '---';
SELECT '📄 FACTURAS' as status;

SELECT
    i.id,
    i.series,
    i.folio,
    i.total,
    i.status,
    i.created_at
FROM invoices i
WHERE i.sale = @last_sale
AND i.deleted_at IS NULL;

SELECT IF(
    (SELECT COUNT(*) FROM invoices WHERE sale = @last_sale AND deleted_at IS NULL) = 0,
    '⚠️  No hay facturas para esta venta',
    '✅ Hay facturas'
) as status_facturas;

-- RESUMEN FINAL
-- ============================================
SELECT '---' as '---';
SELECT '📊 RESUMEN' as status;

SELECT
    @last_sale as venta_id,
    s.amount as total_venta,
    COUNT(DISTINCT p.id) as num_payments,
    SUM(p.amount) as total_pagado,
    COUNT(DISTINCT c.id) as num_comisiones,
    SUM(c.commission_amount) as total_comisiones,
    COUNT(DISTINCT i.id) as num_facturas
FROM sales s
LEFT JOIN payments p ON p.sale = s.id AND p.deleted_at IS NULL
LEFT JOIN commissions c ON c.payment = p.id
LEFT JOIN invoices i ON i.sale = s.id AND i.deleted_at IS NULL
WHERE s.id = @last_sale
GROUP BY s.id, s.amount;
