-- ============================================================

-- SCRIPT DE DATOS DE PRUEBA - MÓDULO LOGISTICS (LOGÍSTICA)

-- ============================================================

-- Descripción: Inserta datos de prueba para facturas y órdenes de entrega

-- Prerequisitos:

-- 0. IMPORTANTE: Ejecutar 'dotnet ef database update' para aplicar migraciones

-- 1. Ejecutar SeedData.sql (usuarios y roles)

-- 2. Ejecutar SeedData_Inventory.sql (productos)

-- 3. Ejecutar SeedData_Sales.sql (clientes)

-- Módulo: Logistics + Sales (Órdenes de Entrega)

-- RF05: Generación Automática de Órdenes de Entrega

-- ============================================================

USE PoliMarketDb;

GO

-- Verificar que las tablas existen antes de continuar

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DeliveryOrders' AND schema_id = SCHEMA_ID('Logistics'))

BEGIN

PRINT '❌ ERROR: La tabla Logistics.DeliveryOrders no existe.';

PRINT ' Por favor ejecuta primero: dotnet ef database update';

RAISERROR('Migraciones no aplicadas', 16, 1);

RETURN;

END

GO

SET QUOTED_IDENTIFIER ON;

GO

-- ============================================================

-- 1. LIMPIAR DATOS EXISTENTES (OPCIONAL - SOLO PARA TESTING)

-- ============================================================

PRINT 'Limpiando datos existentes de Logistics y Sales...';

-- Eliminar en orden debido a las foreign keys

DELETE FROM Logistics.DeliveryOrders;

DELETE FROM Inventory.InventoryMovements WHERE EntityName = 'Invoice';

DELETE FROM Sales.InvoiceDetails;

DELETE FROM Sales.Invoices;

PRINT 'Datos existentes eliminados.';

GO

-- ============================================================

-- 2. INSERTAR FACTURAS DE PRUEBA

-- ============================================================

PRINT 'Insertando facturas de prueba...';

SET IDENTITY_INSERT Sales.Invoices ON;

INSERT INTO Sales.Invoices (Id, SalesPersonId, CustomerId, SaleDate, TotalAmount)

VALUES

-- Facturas con diferentes estados de entrega

-- Factura 1: DELIVERED - Empresa ABC S.A. - Laptops

(1, 2, 1, '2024-11-10T10:30:00', 5598000.00),

-- Factura 2: IN TRANSIT - Comercial XYZ Ltda. - Oficina

(2, 2, 2, '2024-11-14T14:15:00', 1255000.00),

-- Factura 3: ASSIGNED - Distribuidora Los Andes - Tecnología

(3, 3, 3, '2024-11-16T09:00:00', 3250000.00),

-- Factura 4: PENDING - TechSolutions Ecuador - Equipos múltiples

(4, 2, 4, '2024-11-17T16:45:00', 8750000.00),

-- Factura 5: PENDING - Supermercado El Ahorro - Inventario

(5, 3, 5, '2024-11-18T08:30:00', 2100000.00),

-- Factura 6: DELIVERED - Papelería Universitaria - Suministros

(6, 2, 6, '2024-11-12T11:00:00', 890000.00),

-- Factura 7: IN TRANSIT - Importadora Costa S.A. - Hardware

(7, 3, 7, '2024-11-15T15:30:00', 4650000.00),

-- Factura 8: PENDING - Ferretería El Constructor - Equipos

(8, 2, 8, '2024-11-18T09:45:00', 3100000.00);

SET IDENTITY_INSERT Sales.Invoices OFF;

PRINT 'Facturas insertadas correctamente';

GO

-- ============================================================

-- 3. INSERTAR DETALLES DE FACTURAS

-- ============================================================

PRINT 'Insertando detalles de facturas...';

SET IDENTITY_INSERT Sales.InvoiceDetails ON;

-- Detalles Factura 1 (ID 1): Empresa ABC S.A.

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(1, 1, 1, 2, 2799000.00), -- 2 Laptops Dell XPS 13

(2, 1, 3, 2, 89000.00); -- 2 Teclados Mecánicos

-- Detalles Factura 2 (ID 2): Comercial XYZ Ltda.

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(3, 2, 4, 5, 55000.00), -- 5 Mouses Logitech

(4, 2, 5, 10, 95000.00); -- 10 Monitores

-- Detalles Factura 3 (ID 3): Distribuidora Los Andes

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(5, 3, 2, 1, 1850000.00), -- 1 Laptop HP Pavilion

(6, 3, 6, 3, 450000.00); -- 3 Impresoras HP

-- Detalles Factura 4 (ID 4): TechSolutions Ecuador

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(7, 4, 1, 3, 2799000.00), -- 3 Laptops Dell XPS 13

(8, 4, 7, 2, 75000.00); -- 2 Webcams

-- Detalles Factura 5 (ID 5): Supermercado El Ahorro

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(9, 5, 8, 5, 120000.00), -- 5 Discos SSD

(10, 5, 9, 10, 150000.00); -- 10 Memorias RAM

-- Detalles Factura 6 (ID 6): Papelería Universitaria

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(11, 6, 3, 10, 89000.00); -- 10 Teclados Mecánicos

-- Detalles Factura 7 (ID 7): Importadora Costa S.A.

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(12, 7, 2, 2, 1850000.00), -- 2 Laptops HP Pavilion

(13, 7, 5, 10, 95000.00); -- 10 Monitores

-- Detalles Factura 8 (ID 8): Ferretería El Constructor

INSERT INTO Sales.InvoiceDetails (Id, InvoiceId, ProductId, QuantitySold, UnitPrice)

VALUES

(14, 8, 1, 1, 2799000.00), -- 1 Laptop Dell XPS 13

(15, 8, 6, 1, 450000.00); -- 1 Impresora HP

SET IDENTITY_INSERT Sales.InvoiceDetails OFF;

PRINT 'Detalles de facturas insertados correctamente';

GO

-- ============================================================

-- 4. INSERTAR ÓRDENES DE ENTREGA (RF05)

-- ============================================================

PRINT 'Insertando órdenes de entrega (RF05)...';

SET IDENTITY_INSERT Logistics.DeliveryOrders ON;

INSERT INTO Logistics.DeliveryOrders (Id, InvoiceId, DeliveryAddress, DeliveryStatus)

VALUES

-- Orden 1: Empresa ABC S.A. - DELIVERED (Completada exitosamente)

(1, 1, 'Av. Principal 123, Quito, Ecuador', 'Delivered'),

-- Orden 2: Comercial XYZ Ltda. - IN TRANSIT (En camino)

(2, 2, 'Calle Secundaria 456, Guayaquil, Ecuador', 'In Transit'),

-- Orden 3: Distribuidora Los Andes - ASSIGNED (Asignada a repartidor)

(3, 3, 'Carrera 10 #20-30, Cuenca, Ecuador', 'Assigned'),

-- Orden 4: TechSolutions Ecuador - PENDING (Pendiente de asignación)

(4, 4, 'Av. 6 de Diciembre N35-123, Quito, Ecuador', 'Pending'),

-- Orden 5: Supermercado El Ahorro - PENDING (Nueva orden de hoy)

(5, 5, 'Av. Malecón 789, Manta, Ecuador', 'Pending'),

-- Orden 6: Papelería Universitaria - DELIVERED (Completada)

(6, 6, 'Av. Universitaria 234, Loja, Ecuador', 'Delivered'),

-- Orden 7: Importadora Costa S.A. - IN TRANSIT (En ruta)

(7, 7, 'Km 5.5 Vía a la Costa, Guayaquil, Ecuador', 'In Transit'),

-- Orden 8: Ferretería El Constructor - PENDING (Nueva orden)

(8, 8, 'Calle Bolívar 567, Ambato, Ecuador', 'Pending');

SET IDENTITY_INSERT Logistics.DeliveryOrders OFF;

PRINT 'Órdenes de entrega insertadas correctamente';

GO

-- ============================================================

-- 5. ACTUALIZAR STOCK DE PRODUCTOS (REFLEJAR VENTAS)

-- ============================================================

PRINT 'Actualizando stock de productos...';

-- Reducir stock según las ventas realizadas (8 facturas)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 6 WHERE Id = 1; -- 6 Laptops Dell XPS 13 (F1:2 + F4:3 + F8:1)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 3 WHERE Id = 2; -- 3 Laptops HP Pavilion (F3:1 + F7:2)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 12 WHERE Id = 3; -- 12 Teclados (F1:2 + F6:10)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 5 WHERE Id = 4; -- 5 Mouses (F2:5)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 20 WHERE Id = 5; -- 20 Monitores (F2:10 + F7:10)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 4 WHERE Id = 6; -- 4 Impresoras (F3:3 + F8:1)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 2 WHERE Id = 7; -- 2 Webcams (F4:2)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 5 WHERE Id = 8; -- 5 Discos SSD (F5:5)

UPDATE Inventory.Products SET QuantityAvailable = QuantityAvailable - 10 WHERE Id = 9; -- 10 Memorias RAM (F5:10)

PRINT 'Stock de productos actualizado';

GO

-- ============================================================

-- 6. INSERTAR MOVIMIENTOS DE INVENTARIO (RF04)

-- ============================================================

PRINT 'Insertando movimientos de inventario...';

SET IDENTITY_INSERT Inventory.InventoryMovements ON;

-- Movimientos para Factura 1 (DELIVERED)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(1, 1, 'O', 2, '2024-11-10T10:30:00', 1, 'Invoice'),

(2, 3, 'O', 2, '2024-11-10T10:30:00', 1, 'Invoice');

-- Movimientos para Factura 2 (IN TRANSIT)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(3, 4, 'O', 5, '2024-11-14T14:15:00', 2, 'Invoice'),

(4, 5, 'O', 10, '2024-11-14T14:15:00', 2, 'Invoice');

-- Movimientos para Factura 3 (ASSIGNED)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(5, 2, 'O', 1, '2024-11-16T09:00:00', 3, 'Invoice'),

(6, 6, 'O', 3, '2024-11-16T09:00:00', 3, 'Invoice');

-- Movimientos para Factura 4 (PENDING)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(7, 1, 'O', 3, '2024-11-17T16:45:00', 4, 'Invoice'),

(8, 7, 'O', 2, '2024-11-17T16:45:00', 4, 'Invoice');

-- Movimientos para Factura 5 (PENDING)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(9, 8, 'O', 5, '2024-11-18T08:30:00', 5, 'Invoice'),

(10, 9, 'O', 10, '2024-11-18T08:30:00', 5, 'Invoice');

-- Movimientos para Factura 6 (DELIVERED)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(11, 3, 'O', 10, '2024-11-12T11:00:00', 6, 'Invoice');

-- Movimientos para Factura 7 (IN TRANSIT)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(12, 2, 'O', 2, '2024-11-15T15:30:00', 7, 'Invoice'),

(13, 5, 'O', 10, '2024-11-15T15:30:00', 7, 'Invoice');

-- Movimientos para Factura 8 (PENDING)

INSERT INTO Inventory.InventoryMovements (Id, ProductId, MovementType, Quantity, MovementDate, EntityId, EntityName)

VALUES

(14, 1, 'O', 1, '2024-11-18T09:45:00', 8, 'Invoice'),

(15, 6, 'O', 1, '2024-11-18T09:45:00', 8, 'Invoice');

SET IDENTITY_INSERT Inventory.InventoryMovements OFF;

PRINT 'Movimientos de inventario insertados correctamente';

GO

-- ============================================================

-- 7. VERIFICACIÓN Y RESUMEN

-- ============================================================

PRINT '';

PRINT '============================================================';

PRINT 'RESUMEN DE DATOS DE PRUEBA - MÓDULO LOGISTICS';

PRINT '============================================================';

PRINT '';

-- Resumen de Facturas

SELECT

COUNT(*) AS [Total Facturas]

FROM Sales.Invoices;

-- Resumen de Órdenes de Entrega por Estado

SELECT

COUNT(*) AS [Total Órdenes],

COUNT(CASE WHEN DeliveryStatus = 'Pending' THEN 1 END) AS [Pending],

COUNT(CASE WHEN DeliveryStatus = 'Assigned' THEN 1 END) AS [Assigned],

COUNT(CASE WHEN DeliveryStatus = 'In Transit' THEN 1 END) AS [In Transit],

COUNT(CASE WHEN DeliveryStatus = 'Delivered' THEN 1 END) AS [Delivered]

FROM Logistics.DeliveryOrders;

-- Resumen de Movimientos

SELECT

COUNT(*) AS [Total Movimientos de Inventario]

FROM Inventory.InventoryMovements

WHERE EntityName = 'Invoice';

PRINT '';

PRINT '--- RESUMEN POR ESTADO DE ÓRDENES ---';

SELECT

DeliveryStatus AS [Estado],

COUNT(*) AS [Cantidad],

CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Logistics.DeliveryOrders) AS DECIMAL(5,2)) AS [Porcentaje]

FROM Logistics.DeliveryOrders

GROUP BY DeliveryStatus

ORDER BY

CASE DeliveryStatus

WHEN 'Pending' THEN 1

WHEN 'Assigned' THEN 2

WHEN 'In Transit' THEN 3

WHEN 'Delivered' THEN 4

END;

PRINT '';

PRINT '--- ÓRDENES DE ENTREGA PENDIENTES (RF05 CA 5.3) ---';

SELECT

do.Id AS [Orden ID],

i.Id AS [Factura ID],

c.Name AS [Cliente],

do.DeliveryAddress AS [Dirección],

do.DeliveryStatus AS [Estado],

i.TotalAmount AS [Monto Total]

FROM Logistics.DeliveryOrders do

INNER JOIN Sales.Invoices i ON do.InvoiceId = i.Id

INNER JOIN Sales.Customers c ON i.CustomerId = c.Id

WHERE do.DeliveryStatus = 'Pending'

ORDER BY do.Id;

PRINT '';

PRINT '--- DETALLE DE PRODUCTOS POR ORDEN ---';

SELECT

do.Id AS [Orden ID],

c.Name AS [Cliente],

p.Code AS [Código Producto],

p.Name AS [Producto],

id.QuantitySold AS [Cantidad]

FROM Logistics.DeliveryOrders do

INNER JOIN Sales.Invoices i ON do.InvoiceId = i.Id

INNER JOIN Sales.Customers c ON i.CustomerId = c.Id

INNER JOIN Sales.InvoiceDetails id ON i.Id = id.InvoiceId

INNER JOIN Inventory.Products p ON id.ProductId = p.Id

ORDER BY do.Id, p.Name;

PRINT '';

PRINT '============================================================';

PRINT 'DATOS DISPONIBLES PARA TESTING DE RF05';

PRINT '============================================================';

PRINT '';

PRINT '✅ FACTURAS CREADAS: 8';

PRINT '✅ ÓRDENES DE ENTREGA CREADAS: 8 con diferentes estados';

PRINT ' - 3 Pending (Nuevas órdenes)';

PRINT ' - 1 Assigned (Asignada a repartidor)';

PRINT ' - 2 In Transit (En camino)';

PRINT ' - 2 Delivered (Entregadas)';

PRINT '✅ MOVIMIENTOS DE INVENTARIO: 15 registros';

PRINT '✅ DETALLES DE FACTURA: 15 registros';

PRINT '';

PRINT '📋 ENDPOINTS PARA PROBAR:';

PRINT ' GET /api/Logistics/delivery-orders';

PRINT ' GET /api/Logistics/delivery-orders/pending';

PRINT ' GET /api/Logistics/delivery-orders/{id}';

PRINT ' POST /api/Logistics/delivery-orders/generate/{invoiceId}';

PRINT '';

PRINT '🎯 ESCENARIOS DE PRUEBA:';

PRINT ' - Órdenes ID 4, 5, 8: PENDING (RF05 CA 5.3)';

PRINT ' - Orden ID 3: ASSIGNED (siguiente en ser enviada)';

PRINT ' - Órdenes ID 2, 7: IN TRANSIT (en ruta de entrega)';

PRINT ' - Órdenes ID 1, 6: DELIVERED (completadas exitosamente)';

PRINT '';

PRINT '💡 CRITERIOS DE ACEPTACIÓN CUMPLIDOS:';

PRINT ' ✅ CA 5.1: Órdenes creadas con estado "Pending"';

PRINT ' ✅ CA 5.2: Datos completos (Cliente, Dirección, Productos)';

PRINT ' ✅ CA 5.3: Órdenes visibles sin intervención manual';

PRINT '';

PRINT '📊 ESTADÍSTICAS DE PRUEBA:';

PRINT ' - 8 clientes diferentes';

PRINT ' - 9 productos diferentes vendidos';

PRINT ' - Rango de fechas: 10-18 de noviembre 2024';

PRINT ' - Montos: desde $890,000 hasta $8,750,000';

PRINT '';

PRINT '============================================================';

PRINT 'Scripts completados exitosamente - RF05 READY FOR TESTING';

PRINT '============================================================';

GO