-- ============================================================ -- quoERP — Módulo de Ventas y Facturación -- Migración: 003_sales_module.sql -- Versión: 1.1.0 | Fecha: 2026-06-04 -- ============================================================ -- Requiere: 001_create_all_tables.sql -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ───────────────────────────────────────────────────────────── -- AJUSTE 1: sale_order_lines — product_id nullable + campos nuevos -- Permite líneas libres (sin producto de catálogo) -- ───────────────────────────────────────────────────────────── ALTER TABLE sale_order_lines MODIFY COLUMN product_id INT UNSIGNED DEFAULT NULL, ADD COLUMN IF NOT EXISTS description VARCHAR(255) DEFAULT NULL AFTER product_id, ADD COLUMN IF NOT EXISTS product_name VARCHAR(200) DEFAULT NULL AFTER description; -- ───────────────────────────────────────────────────────────── -- AJUSTE 2: sale_orders — campos adicionales -- ───────────────────────────────────────────────────────────── ALTER TABLE sale_orders ADD COLUMN IF NOT EXISTS quote_id INT UNSIGNED DEFAULT NULL AFTER customer_id, ADD COLUMN IF NOT EXISTS delivery_date DATE DEFAULT NULL AFTER due_date, ADD COLUMN IF NOT EXISTS terms TEXT DEFAULT NULL AFTER notes; -- ───────────────────────────────────────────────────────────── -- TABLA 1: sale_quotes — Cotizaciones -- Flujo: borrador → enviada → aceptada/rechazada → convertida -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sale_quotes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, office_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED DEFAULT NULL COMMENT 'Puede ser nulo en cotizaciones a prospectos', number VARCHAR(20) NOT NULL COMMENT 'Formato: COT-YYYY-NNNNN', date DATE NOT NULL, expiry_date DATE DEFAULT NULL COMMENT 'Fecha de vencimiento de la oferta', subject VARCHAR(255) DEFAULT NULL COMMENT 'Asunto / descripción corta', status ENUM('borrador','enviada','aceptada','rechazada','vencida','convertida') NOT NULL DEFAULT 'borrador', notes TEXT DEFAULT NULL COMMENT 'Notas internas', terms TEXT DEFAULT NULL COMMENT 'Términos y condiciones para el cliente', subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, discount DECIMAL(12,2) NOT NULL DEFAULT 0, tax DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, created_by INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_sq_number (company_id, number), INDEX idx_company (company_id), INDEX idx_customer (customer_id), INDEX idx_status (status), INDEX idx_date (date), CONSTRAINT fk_sq_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT, CONSTRAINT fk_sq_office FOREIGN KEY (office_id) REFERENCES offices(id) ON DELETE RESTRICT, CONSTRAINT fk_sq_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Cotizaciones / proformas de venta'; -- ───────────────────────────────────────────────────────────── -- TABLA 2: sale_quote_lines — Líneas de cotización -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sale_quote_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, quote_id INT UNSIGNED NOT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, description VARCHAR(255) NOT NULL COMMENT 'Descripción del ítem (libre)', qty DECIMAL(12,4) NOT NULL DEFAULT 1, unit_price DECIMAL(12,4) NOT NULL DEFAULT 0, discount_pct DECIMAL(5,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00 COMMENT 'IVA Ecuador', subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_quote (quote_id), CONSTRAINT fk_sql_quote FOREIGN KEY (quote_id) REFERENCES sale_quotes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Líneas de detalle de cada cotización'; -- ───────────────────────────────────────────────────────────── -- TABLA 3: sale_invoices — Facturas / comprobantes de venta -- Los pagos se registran en sale_invoice_payments (múltiples por factura). -- paid_amount y payment_status son campos denormalizados que se recalculan -- automáticamente cada vez que se agrega o anula un pago. -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sale_invoices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, office_id INT UNSIGNED NOT NULL, order_id INT UNSIGNED DEFAULT NULL COMMENT 'Orden de venta origen (opcional)', customer_id INT UNSIGNED DEFAULT NULL, customer_name_snap VARCHAR(200) DEFAULT NULL COMMENT 'Snapshot del nombre del cliente', number VARCHAR(20) NOT NULL COMMENT 'Formato: FAC-YYYY-NNNNN', date DATE NOT NULL, due_date DATE DEFAULT NULL, -- Estado de cobro (recalculado automáticamente desde sale_invoice_payments) payment_status ENUM('pendiente','parcial','pagada','anulada') NOT NULL DEFAULT 'pendiente', subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, discount DECIMAL(12,2) NOT NULL DEFAULT 0, tax DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, -- Suma denormalizada de pagos activos (se actualiza con cada pago/anulación) paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT DEFAULT NULL, pdf_path VARCHAR(255) DEFAULT NULL, created_by INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_si_number (company_id, number), INDEX idx_company (company_id), INDEX idx_order (order_id), INDEX idx_customer (customer_id), INDEX idx_status (payment_status), INDEX idx_date (date), CONSTRAINT fk_si_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT, CONSTRAINT fk_si_office FOREIGN KEY (office_id) REFERENCES offices(id) ON DELETE RESTRICT, CONSTRAINT fk_si_order FOREIGN KEY (order_id) REFERENCES sale_orders(id) ON DELETE SET NULL, CONSTRAINT fk_si_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Facturas/comprobantes de venta. Los pagos se gestionan en sale_invoice_payments.'; -- ───────────────────────────────────────────────────────────── -- TABLA 4: sale_invoice_lines — Líneas de factura -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sale_invoice_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, invoice_id INT UNSIGNED NOT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, description VARCHAR(255) NOT NULL, qty DECIMAL(12,4) NOT NULL DEFAULT 1, unit_price DECIMAL(12,4) NOT NULL DEFAULT 0, discount_pct DECIMAL(5,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_invoice (invoice_id), CONSTRAINT fk_sil_invoice FOREIGN KEY (invoice_id) REFERENCES sale_invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Líneas de detalle de cada factura'; -- ───────────────────────────────────────────────────────────── -- TABLA 5: sale_invoice_payments — Pagos por factura -- Una factura puede tener múltiples pagos con distintos métodos. -- Cada pago puede llevar nota/referencia y un archivo comprobante. -- Los pagos pueden anularse individualmente dejando observación. -- ───────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sale_invoice_payments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, invoice_id INT UNSIGNED NOT NULL, method ENUM('efectivo','transferencia','cheque','tarjeta','otro') NOT NULL DEFAULT 'efectivo', amount DECIMAL(12,2) NOT NULL, payment_date DATE NOT NULL, -- Referencia / nota: N° cheque, N° transferencia, descripción, etc. note TEXT DEFAULT NULL, -- Comprobante adjunto (no obligatorio) attachment_path VARCHAR(255) DEFAULT NULL COMMENT 'Ruta en public/uploads/payments/', attachment_name VARCHAR(255) DEFAULT NULL COMMENT 'Nombre original del archivo', attachment_size INT UNSIGNED DEFAULT NULL COMMENT 'Tamaño en bytes', -- Estado del pago status ENUM('active','voided') NOT NULL DEFAULT 'active', void_reason TEXT DEFAULT NULL COMMENT 'Motivo de anulación', voided_at DATETIME DEFAULT NULL, voided_by INT UNSIGNED DEFAULT NULL, created_by INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_invoice (invoice_id), INDEX idx_status (status), INDEX idx_date (payment_date), CONSTRAINT fk_sip_invoice FOREIGN KEY (invoice_id) REFERENCES sale_invoices(id) ON DELETE CASCADE, CONSTRAINT fk_sip_voided_by FOREIGN KEY (voided_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pagos individuales de facturas. Soporta múltiples formas de pago, adjuntos y anulación con observación.'; SET FOREIGN_KEY_CHECKS = 1; -- ───────────────────────────────────────────────────────────── -- FIN DE MIGRACIÓN -- Tablas nuevas: 5 -- sale_quotes, sale_quote_lines, -- sale_invoices, sale_invoice_lines, -- sale_invoice_payments -- ALTER TABLE: sale_order_lines (product_id nullable + description + product_name) -- ALTER TABLE: sale_orders (quote_id + delivery_date + terms) -- ─────────────────────────────────────────────────────────────