-- ============================================================================
-- AMIGO MX - Migración v2: pagos parciales, VAT, recibos
-- Ejecutar UNA SOLA VEZ:
-- mysql -u wwsist_mikes -p wwsist_amigo < database/migration-v2.sql
-- ============================================================================

USE wwsist_amigo;

-- Tabla de pagos: cada cobro (total o parcial) es una fila.
-- El id sirve como número de recibo consecutivo.
CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  tip DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_method ENUM('cash','card') NOT NULL,
  label VARCHAR(100) DEFAULT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_payments_date ON payments(created_at);

-- Marcar items pagados individualmente (para cuentas separadas)
ALTER TABLE order_items ADD COLUMN paid BOOLEAN NOT NULL DEFAULT FALSE;

-- Guardar el desglose de IVA al cerrar la orden
ALTER TABLE orders ADD COLUMN vat_base DECIMAL(10,2) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN vat_amount DECIMAL(10,2) DEFAULT NULL;

-- ----------------------------------------------------------------------------
-- BACKFILL: migrar órdenes ya cerradas al nuevo esquema
-- ----------------------------------------------------------------------------
INSERT INTO payments (order_id, amount, tip, payment_method, label, created_by, created_at)
SELECT id, subtotal, tip,
       IF(payment_method = 'card', 'card', 'cash'),
       'Migración',
       created_by,
       COALESCE(closed_at, created_at)
FROM orders
WHERE status = 'closed';

UPDATE order_items oi
JOIN orders o ON oi.order_id = o.id
SET oi.paid = TRUE
WHERE o.status = 'closed';

UPDATE orders
SET vat_base = ROUND(subtotal / 1.21, 2),
    vat_amount = ROUND(subtotal - (subtotal / 1.21), 2)
WHERE status = 'closed' AND vat_base IS NULL;