ddl-script.sql

/* =============================================================================
   Base de datos relacional: AEROLÍNEA
   Motor destino : Microsoft SQL Server 2025
   Esquema       : airline (base de datos) / dbo (esquema)
   Modelo        : 5 tablas -> Aeropuertos, Vuelos, Pasajeros, Reservas, Equipajes
   Contenido     : DDL + datos sintéticos (120 vuelos y todo su detalle)

   Ejecución con el contenedor del tutorial:
     docker exec -i dab-mssql /opt/mssql-tools18/bin/sqlcmd \
       -S localhost -U sa -P "P@ssw0rd1" -C -i /tmp/01-airline-schema.sql

   El script es idempotente: elimina y vuelve a crear las tablas en cada corrida.
   Los datos sintéticos son deterministas (no usan RAND ni NEWID), por lo que
   dos ejecuciones producen exactamente el mismo conjunto de filas.
   ============================================================================= */

/* QUOTED_IDENTIFIER/ANSI_NULLS deben estar ON para poder crear el índice
   filtrado de asientos: sqlcmd los deja en OFF por omisión. */
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

/* ---------------------------------------------------------------------------
   1. Base de datos
   --------------------------------------------------------------------------- */
IF DB_ID('airline') IS NULL
BEGIN
    CREATE DATABASE airline;
END
GO

USE airline;
GO

/* ---------------------------------------------------------------------------
   2. Limpieza (orden inverso a las dependencias de clave foránea)
   --------------------------------------------------------------------------- */
DROP TABLE IF EXISTS dbo.Equipajes;
DROP TABLE IF EXISTS dbo.Reservas;
DROP TABLE IF EXISTS dbo.Vuelos;
DROP TABLE IF EXISTS dbo.Pasajeros;
DROP TABLE IF EXISTS dbo.Aeropuertos;
GO

/* ---------------------------------------------------------------------------
   3. Tabla 1: Aeropuertos
      Catálogo de estaciones. Un aeropuerto participa como origen o como
      destino de muchos vuelos (dos relaciones 1:N distintas hacia Vuelos).
   --------------------------------------------------------------------------- */
CREATE TABLE dbo.Aeropuertos
(
    AeropuertoId    INT             NOT NULL IDENTITY(1,1),
    CodigoIATA      CHAR(3)         NOT NULL,
    Nombre          NVARCHAR(120)   NOT NULL,
    Ciudad          NVARCHAR(80)    NOT NULL,
    Pais            NVARCHAR(80)    NOT NULL,
    ZonaHoraria     NVARCHAR(40)    NOT NULL,
    Activo          BIT             NOT NULL CONSTRAINT DF_Aeropuertos_Activo DEFAULT (1),
    CONSTRAINT PK_Aeropuertos       PRIMARY KEY CLUSTERED (AeropuertoId),
    CONSTRAINT UQ_Aeropuertos_IATA  UNIQUE (CodigoIATA),
    CONSTRAINT CK_Aeropuertos_IATA  CHECK (CodigoIATA LIKE '[A-Z][A-Z][A-Z]')
);
GO

/* ---------------------------------------------------------------------------
   4. Tabla 2: Pasajeros
      Persona identificada por su documento de viaje. Un pasajero puede tener
      muchas reservas (1:N hacia Reservas).
   --------------------------------------------------------------------------- */
CREATE TABLE dbo.Pasajeros
(
    PasajeroId          INT             NOT NULL IDENTITY(1,1),
    TipoDocumento       NVARCHAR(20)    NOT NULL CONSTRAINT DF_Pasajeros_TipoDoc DEFAULT (N'Pasaporte'),
    NumeroDocumento     NVARCHAR(20)    NOT NULL,
    Nombres             NVARCHAR(60)    NOT NULL,
    Apellidos           NVARCHAR(60)    NOT NULL,
    FechaNacimiento     DATE            NOT NULL,
    Genero              CHAR(1)         NULL,
    Email               NVARCHAR(120)   NULL,
    Telefono            NVARCHAR(25)    NULL,
    Nacionalidad        NVARCHAR(60)    NOT NULL,
    FechaAlta           DATETIME2(0)    NOT NULL CONSTRAINT DF_Pasajeros_FechaAlta DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT PK_Pasajeros             PRIMARY KEY CLUSTERED (PasajeroId),
    CONSTRAINT UQ_Pasajeros_Documento   UNIQUE (TipoDocumento, NumeroDocumento),
    CONSTRAINT CK_Pasajeros_TipoDoc     CHECK (TipoDocumento IN (N'Pasaporte', N'DNI', N'Cedula')),
    CONSTRAINT CK_Pasajeros_Genero      CHECK (Genero IN ('F', 'M', 'X')),
    CONSTRAINT CK_Pasajeros_Nacimiento  CHECK (FechaNacimiento >= '1920-01-01')
);
GO

/* ---------------------------------------------------------------------------
   5. Tabla 3: Vuelos
      Un vuelo operado en una fecha concreta. Referencia dos veces a
      Aeropuertos (origen y destino) y es padre de Reservas.
   --------------------------------------------------------------------------- */
CREATE TABLE dbo.Vuelos
(
    VueloId             INT             NOT NULL IDENTITY(1,1),
    NumeroVuelo         VARCHAR(7)      NOT NULL,
    AeropuertoOrigenId  INT             NOT NULL,
    AeropuertoDestinoId INT             NOT NULL,
    SalidaProgramada    DATETIME2(0)    NOT NULL,
    LlegadaProgramada   DATETIME2(0)    NOT NULL,
    Aeronave            NVARCHAR(30)    NOT NULL,
    CapacidadAsientos   SMALLINT        NOT NULL,
    Estado              NVARCHAR(15)    NOT NULL CONSTRAINT DF_Vuelos_Estado DEFAULT (N'Programado'),
    CONSTRAINT PK_Vuelos                PRIMARY KEY CLUSTERED (VueloId),
    CONSTRAINT UQ_Vuelos_NumeroFecha    UNIQUE (NumeroVuelo, SalidaProgramada),
    CONSTRAINT FK_Vuelos_Origen         FOREIGN KEY (AeropuertoOrigenId)
        REFERENCES dbo.Aeropuertos (AeropuertoId),
    CONSTRAINT FK_Vuelos_Destino        FOREIGN KEY (AeropuertoDestinoId)
        REFERENCES dbo.Aeropuertos (AeropuertoId),
    CONSTRAINT CK_Vuelos_Ruta           CHECK (AeropuertoOrigenId <> AeropuertoDestinoId),
    CONSTRAINT CK_Vuelos_Horario        CHECK (LlegadaProgramada > SalidaProgramada),
    CONSTRAINT CK_Vuelos_Capacidad      CHECK (CapacidadAsientos BETWEEN 20 AND 600),
    CONSTRAINT CK_Vuelos_Estado         CHECK (Estado IN (N'Programado', N'Embarcando', N'En vuelo',
                                                          N'Aterrizado', N'Retrasado', N'Cancelado'))
);
GO

CREATE INDEX IX_Vuelos_Salida  ON dbo.Vuelos (SalidaProgramada) INCLUDE (NumeroVuelo, Estado);
CREATE INDEX IX_Vuelos_Origen  ON dbo.Vuelos (AeropuertoOrigenId, SalidaProgramada);
CREATE INDEX IX_Vuelos_Destino ON dbo.Vuelos (AeropuertoDestinoId, SalidaProgramada);
GO

/* ---------------------------------------------------------------------------
   6. Tabla 4: Reservas (boleto)
      Resuelve la relación N:M entre Vuelos y Pasajeros y agrega los datos
      propios del boleto: asiento, clase y tarifa.
      - Un pasajero no puede aparecer dos veces en el mismo vuelo.
      - Un asiento no puede repetirse dentro del mismo vuelo (índice filtrado,
        permite varias reservas sin asiento asignado todavía).
   --------------------------------------------------------------------------- */
CREATE TABLE dbo.Reservas
(
    ReservaId       INT             NOT NULL IDENTITY(1,1),
    CodigoReserva   CHAR(6)         NOT NULL,
    VueloId         INT             NOT NULL,
    PasajeroId      INT             NOT NULL,
    Asiento         VARCHAR(4)      NULL,
    Clase           NVARCHAR(10)    NOT NULL CONSTRAINT DF_Reservas_Clase DEFAULT (N'Economy'),
    Tarifa          DECIMAL(10,2)   NOT NULL,
    Moneda          CHAR(3)         NOT NULL CONSTRAINT DF_Reservas_Moneda DEFAULT ('USD'),
    FechaReserva    DATETIME2(0)    NOT NULL,
    Estado          NVARCHAR(12)    NOT NULL CONSTRAINT DF_Reservas_Estado DEFAULT (N'Confirmada'),
    CONSTRAINT PK_Reservas              PRIMARY KEY CLUSTERED (ReservaId),
    CONSTRAINT UQ_Reservas_Codigo       UNIQUE (CodigoReserva),
    CONSTRAINT UQ_Reservas_VueloPasajero UNIQUE (VueloId, PasajeroId),
    CONSTRAINT FK_Reservas_Vuelo        FOREIGN KEY (VueloId)
        REFERENCES dbo.Vuelos (VueloId) ON DELETE CASCADE,
    CONSTRAINT FK_Reservas_Pasajero     FOREIGN KEY (PasajeroId)
        REFERENCES dbo.Pasajeros (PasajeroId),
    CONSTRAINT CK_Reservas_Clase        CHECK (Clase IN (N'Economy', N'Premium', N'Business')),
    CONSTRAINT CK_Reservas_Estado       CHECK (Estado IN (N'Confirmada', N'CheckIn', N'Abordado',
                                                          N'Cancelada', N'NoShow')),
    CONSTRAINT CK_Reservas_Tarifa       CHECK (Tarifa >= 0),
    CONSTRAINT CK_Reservas_Asiento      CHECK (Asiento LIKE '[0-9][A-F]'
                                            OR Asiento LIKE '[0-9][0-9][A-F]'
                                            OR Asiento LIKE '[0-9][0-9][0-9][A-F]')
);
GO

CREATE UNIQUE INDEX UX_Reservas_VueloAsiento
    ON dbo.Reservas (VueloId, Asiento)
    WHERE Asiento IS NOT NULL;

CREATE INDEX IX_Reservas_Pasajero ON dbo.Reservas (PasajeroId) INCLUDE (VueloId, Estado);
GO

/* ---------------------------------------------------------------------------
   7. Tabla 5: Equipajes
      Cada pieza de equipaje pertenece a una reserva (1:N). Al borrar la
      reserva se borra su equipaje (ON DELETE CASCADE).
   --------------------------------------------------------------------------- */
CREATE TABLE dbo.Equipajes
(
    EquipajeId      INT             NOT NULL IDENTITY(1,1),
    ReservaId       INT             NOT NULL,
    Etiqueta        VARCHAR(10)     NOT NULL,
    Tipo            NVARCHAR(10)    NOT NULL,
    PesoKg          DECIMAL(5,2)    NOT NULL,
    Estado          NVARCHAR(12)    NOT NULL CONSTRAINT DF_Equipajes_Estado DEFAULT (N'Facturado'),
    FechaRegistro   DATETIME2(0)    NOT NULL,
    CONSTRAINT PK_Equipajes         PRIMARY KEY CLUSTERED (EquipajeId),
    CONSTRAINT UQ_Equipajes_Etiqueta UNIQUE (Etiqueta),
    CONSTRAINT FK_Equipajes_Reserva FOREIGN KEY (ReservaId)
        REFERENCES dbo.Reservas (ReservaId) ON DELETE CASCADE,
    CONSTRAINT CK_Equipajes_Tipo    CHECK (Tipo IN (N'Mano', N'Bodega', N'Especial')),
    CONSTRAINT CK_Equipajes_Peso    CHECK (PesoKg > 0 AND PesoKg <= 45),
    CONSTRAINT CK_Equipajes_Estado  CHECK (Estado IN (N'Facturado', N'Embarcado', N'Entregado',
                                                      N'Extraviado', N'Demorado'))
);
GO

CREATE INDEX IX_Equipajes_Reserva ON dbo.Equipajes (ReservaId);
GO

/* =============================================================================
   DATOS SINTÉTICOS
   ============================================================================= */

/* ---------------------------------------------------------------------------
   8. Aeropuertos (20 estaciones reales de América y Europa)
   --------------------------------------------------------------------------- */
INSERT INTO dbo.Aeropuertos (CodigoIATA, Nombre, Ciudad, Pais, ZonaHoraria)
VALUES
    ('BOG', N'El Dorado',                    N'Bogotá',        N'Colombia',        N'America/Bogota'),
    ('MDE', N'José María Córdova',           N'Medellín',      N'Colombia',        N'America/Bogota'),
    ('CTG', N'Rafael Núñez',                 N'Cartagena',     N'Colombia',        N'America/Bogota'),
    ('MEX', N'Benito Juárez',                N'Ciudad de México', N'México',       N'America/Mexico_City'),
    ('CUN', N'Cancún',                       N'Cancún',        N'México',          N'America/Cancun'),
    ('LIM', N'Jorge Chávez',                 N'Lima',          N'Perú',            N'America/Lima'),
    ('SCL', N'Arturo Merino Benítez',        N'Santiago',      N'Chile',           N'America/Santiago'),
    ('EZE', N'Ministro Pistarini',           N'Buenos Aires',  N'Argentina',       N'America/Argentina/Buenos_Aires'),
    ('GRU', N'Guarulhos',                    N'São Paulo',     N'Brasil',          N'America/Sao_Paulo'),
    ('PTY', N'Tocumen',                      N'Ciudad de Panamá', N'Panamá',       N'America/Panama'),
    ('SJO', N'Juan Santamaría',              N'San José',      N'Costa Rica',      N'America/Costa_Rica'),
    ('UIO', N'Mariscal Sucre',               N'Quito',         N'Ecuador',         N'America/Guayaquil'),
    ('MIA', N'Miami International',          N'Miami',         N'Estados Unidos',  N'America/New_York'),
    ('JFK', N'John F. Kennedy',              N'Nueva York',    N'Estados Unidos',  N'America/New_York'),
    ('LAX', N'Los Angeles International',    N'Los Ángeles',   N'Estados Unidos',  N'America/Los_Angeles'),
    ('YYZ', N'Toronto Pearson',              N'Toronto',       N'Canadá',          N'America/Toronto'),
    ('MAD', N'Adolfo Suárez Barajas',        N'Madrid',        N'España',          N'Europe/Madrid'),
    ('BCN', N'Josep Tarradellas El Prat',    N'Barcelona',     N'España',          N'Europe/Madrid'),
    ('CDG', N'Charles de Gaulle',            N'París',         N'Francia',         N'Europe/Paris'),
    ('LHR', N'Heathrow',                     N'Londres',       N'Reino Unido',     N'Europe/London');
GO

/* ---------------------------------------------------------------------------
   9. Pasajeros (1 200 personas generadas por combinación de catálogos)
      Los índices usan n, n/20 y n/400 para que las 1 200 combinaciones de
      nombre + apellidos sean distintas entre sí.
   --------------------------------------------------------------------------- */
;WITH Numeros AS
(
    SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
),
Base AS
(
    SELECT
        n,
        Nombre    = CHOOSE((n % 20) + 1,
                        N'Ana', N'Carlos', N'María', N'Juan', N'Lucía', N'Andrés', N'Valentina', N'Diego',
                        N'Camila', N'Santiago', N'Isabella', N'Mateo', N'Sofía', N'Sebastián', N'Daniela',
                        N'Felipe', N'Gabriela', N'Nicolás', N'Paula', N'Ricardo'),
        Apellido1 = CHOOSE(((n / 20) % 20) + 1,
                        N'García', N'Rodríguez', N'Martínez', N'López', N'González', N'Pérez', N'Sánchez',
                        N'Ramírez', N'Torres', N'Flores', N'Rivera', N'Gómez', N'Díaz', N'Vargas',
                        N'Castro', N'Romero', N'Herrera', N'Medina', N'Ortiz', N'Silva'),
        Apellido2 = CHOOSE(((n / 400) % 20) + 1,
                        N'Moreno', N'Jiménez', N'Ruiz', N'Álvarez', N'Muñoz', N'Rojas', N'Navarro',
                        N'Cabrera', N'Delgado', N'Peña', N'Cortés', N'Guerrero', N'Ibarra', N'Salazar',
                        N'Mendoza', N'Fuentes', N'Aguilar', N'Bravo', N'Cardona', N'Duarte'),
        Pais      = CHOOSE(((n * 3)  % 10) + 1,
                        N'Colombia', N'México', N'Perú', N'Chile', N'Argentina',
                        N'Brasil', N'España', N'Estados Unidos', N'Panamá', N'Ecuador'),
        TipoDoc   = CHOOSE((n % 3) + 1, N'Pasaporte', N'DNI', N'Cedula'),
        Genero    = CHOOSE((n % 3) + 1, 'F', 'M', 'X')
    FROM Numeros
)
INSERT INTO dbo.Pasajeros
    (TipoDocumento, NumeroDocumento, Nombres, Apellidos, FechaNacimiento, Genero,
     Email, Telefono, Nacionalidad, FechaAlta)
SELECT
    TipoDoc,
    CONCAT(LEFT(TipoDoc, 2), RIGHT(CONCAT('0000000', 1000000 + (n * 7919) % 8999999), 7)),
    Nombre,
    CONCAT(Apellido1, N' ', Apellido2),
    DATEADD(DAY, -((n * 137) % 18000) - 6570, CAST('2026-01-01' AS DATE)),   -- 18 a 67 años
    Genero,
    LOWER(CONCAT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Nombre, N'í', N'i'), N'á', N'a'), N'é', N'e'), N'ó', N'o'), N'ú', N'u'),
        '.',
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Apellido1, N'í', N'i'), N'á', N'a'), N'é', N'e'), N'ó', N'o'), N'ú', N'u'),
        n, '@correo-demo.test')),
    CONCAT('+', 30 + (n % 60), ' ', 600000000 + (n * 4177) % 99999999),
    Pais,
    DATEADD(DAY, -((n * 29) % 900), CAST('2026-07-01' AS DATETIME2(0)))
FROM Base;
GO

/* ---------------------------------------------------------------------------
   10. Vuelos (120 vuelos entre 2026-07-01 y 2026-09-28)
       Ruta, aeronave y horario se derivan del número de fila -> deterministas.
   --------------------------------------------------------------------------- */
;WITH Numeros AS
(
    SELECT TOP (120) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
),
Rutas AS
(
    SELECT
        n,
        Origen  = ((n * 7)  % 20) + 1,
        DestinoRaw = ((n * 13) % 20) + 1,
        Modelo  = (n % 4) + 1,
        Salida  = DATEADD(MINUTE, ((n * 97) % 96) * 15,
                  DATEADD(DAY, (n * 5) % 90, CAST('2026-07-01' AS DATETIME2(0)))),
        DuracionMin = 65 + ((n * 37) % 9) * 35
    FROM Numeros
),
Vuelo AS
(
    SELECT
        n,
        Origen,
        Destino = CASE WHEN DestinoRaw = Origen THEN (DestinoRaw % 20) + 1 ELSE DestinoRaw END,
        Salida,
        DuracionMin,
        Aeronave  = CHOOSE(Modelo, N'Airbus A320neo', N'Boeing 737-800', N'Embraer E190', N'Boeing 787-9'),
        Capacidad = CAST(CHOOSE(Modelo, 180, 189, 100, 296) AS SMALLINT)
    FROM Rutas
)
INSERT INTO dbo.Vuelos
    (NumeroVuelo, AeropuertoOrigenId, AeropuertoDestinoId, SalidaProgramada,
     LlegadaProgramada, Aeronave, CapacidadAsientos, Estado)
SELECT
    CONCAT('XA', RIGHT(CONCAT('000', 100 + n), 4)),
    Origen,
    Destino,
    Salida,
    DATEADD(MINUTE, DuracionMin, Salida),
    Aeronave,
    Capacidad,
    CASE
        WHEN n % 25 = 0 THEN N'Cancelado'
        WHEN n % 11 = 0 THEN N'Retrasado'
        WHEN Salida <  CAST('2026-07-31' AS DATETIME2(0)) THEN N'Aterrizado'
        ELSE N'Programado'
    END
FROM Vuelo;
GO

/* ---------------------------------------------------------------------------
   11. Reservas
       Ocupación del 55% al 92% de la capacidad de cada aeronave.
       El asiento se calcula por posición (filas de 6 butacas: A-F) por lo que
       nunca se repite dentro del vuelo, y el pasajero se elige con un paso
       coprimo con 1 200 para no repetirlo dentro del mismo vuelo.
       Las 12 primeras butacas son Business y las 18 siguientes Premium.
   --------------------------------------------------------------------------- */
;WITH Numeros AS
(
    SELECT TOP (300) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS k
    FROM sys.all_objects
),
Detalle AS
(
    SELECT
        v.VueloId,
        v.SalidaProgramada,
        v.Estado AS EstadoVuelo,
        k.k,
        PasajeroId = ((v.VueloId * 137 + k.k * 11) % 1200) + 1,
        Fila       = ((k.k - 1) / 6) + 1,
        Letra      = CHAR(65 + ((k.k - 1) % 6)),
        Clase      = CASE WHEN k.k <= 12 THEN N'Business'
                          WHEN k.k <= 30 THEN N'Premium'
                          ELSE N'Economy' END,
        Rn         = ROW_NUMBER() OVER (ORDER BY v.VueloId, k.k)
    FROM dbo.Vuelos AS v
    CROSS JOIN Numeros AS k
    WHERE k.k <= (v.CapacidadAsientos * (55 + ((v.VueloId * 7) % 38))) / 100
)
INSERT INTO dbo.Reservas
    (CodigoReserva, VueloId, PasajeroId, Asiento, Clase, Tarifa, Moneda, FechaReserva, Estado)
SELECT
    /* código alfanumérico único: Rn convertido a base 36 en 4 dígitos, con prefijo 'R' + letra */
    CONCAT(
        'R',
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', ((Rn / 1679616) % 26) + 1, 1),
        SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', ((Rn / 46656) % 36) + 1, 1),
        SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', ((Rn / 1296)  % 36) + 1, 1),
        SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', ((Rn / 36)    % 36) + 1, 1),
        SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', ( Rn          % 36) + 1, 1)
    ),
    VueloId,
    PasajeroId,
    CONCAT(Fila, Letra),
    Clase,
    CAST(
        (CASE Clase WHEN N'Business' THEN 620 WHEN N'Premium' THEN 340 ELSE 145 END
         + ((VueloId * 17 + k * 7) % 120)
        ) + ((VueloId * 23 + k) % 100) / 100.0 AS DECIMAL(10,2)),
    'USD',
    DATEADD(DAY, -(5 + ((VueloId * 7 + k * 3) % 85)), SalidaProgramada),
    CASE
        WHEN EstadoVuelo = N'Cancelado'                     THEN N'Cancelada'
        WHEN (VueloId * 3 + k) % 29 = 0                     THEN N'Cancelada'
        WHEN (VueloId * 5 + k) % 31 = 0                     THEN N'NoShow'
        WHEN SalidaProgramada < CAST('2026-07-31' AS DATETIME2(0)) THEN N'Abordado'
        WHEN SalidaProgramada < DATEADD(DAY, 2, CAST('2026-07-31' AS DATETIME2(0))) THEN N'CheckIn'
        ELSE N'Confirmada'
    END
FROM Detalle;
GO

/* ---------------------------------------------------------------------------
   12. Equipajes
       1 pieza de mano para casi todas las reservas activas y 0-2 piezas de
       bodega según la clase y un patrón determinista.
   --------------------------------------------------------------------------- */
;WITH Piezas AS
(
    SELECT TOP (3) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS p
    FROM sys.all_objects
),
Detalle AS
(
    SELECT
        r.ReservaId,
        r.Clase,
        r.Estado AS EstadoReserva,
        r.FechaReserva,
        v.SalidaProgramada,
        p.p,
        Tipo = CASE WHEN p.p = 1 THEN N'Mano'
                    WHEN (r.ReservaId + p.p) % 17 = 0 THEN N'Especial'
                    ELSE N'Bodega' END,
        Rn   = ROW_NUMBER() OVER (ORDER BY r.ReservaId, p.p)
    FROM dbo.Reservas AS r
    INNER JOIN dbo.Vuelos AS v ON v.VueloId = r.VueloId
    CROSS JOIN Piezas AS p
    WHERE r.Estado <> N'Cancelada'
      AND (
            p.p = 1                                                             -- equipaje de mano
         OR (p.p = 2 AND (r.ReservaId % 4) <> 0)                                -- ~75% factura 1 maleta
         OR (p.p = 3 AND r.Clase IN (N'Business', N'Premium') AND r.ReservaId % 3 = 0)
          )
)
INSERT INTO dbo.Equipajes (ReservaId, Etiqueta, Tipo, PesoKg, Estado, FechaRegistro)
SELECT
    ReservaId,
    CONCAT('XA', RIGHT(CONCAT('00000000', Rn), 8)),
    Tipo,
    CASE Tipo
        WHEN N'Mano'     THEN CAST(3.0  + ((ReservaId * 7  + p) % 70)  / 10.0 AS DECIMAL(5,2))   --  3.0 - 9.9
        WHEN N'Especial' THEN CAST(12.0 + ((ReservaId * 11 + p) % 280) / 10.0 AS DECIMAL(5,2))   -- 12.0 - 39.9
        ELSE                  CAST(8.0  + ((ReservaId * 13 + p) % 220) / 10.0 AS DECIMAL(5,2))   --  8.0 - 29.9
    END,
    CASE
        WHEN (ReservaId * 3 + p) % 211 = 0                                  THEN N'Extraviado'
        WHEN (ReservaId * 5 + p) % 97  = 0                                  THEN N'Demorado'
        WHEN SalidaProgramada < CAST('2026-07-31' AS DATETIME2(0))          THEN N'Entregado'
        WHEN SalidaProgramada < DATEADD(DAY, 1, CAST('2026-07-31' AS DATETIME2(0))) THEN N'Embarcado'
        ELSE N'Facturado'
    END,
    DATEADD(MINUTE, -(90 + ((ReservaId + p) % 60)), SalidaProgramada)
FROM Detalle;
GO
/* =============================================================================
   14. Creacion de vistas
   ============================================================================= */
create view pasajero_vuelo_view as SELECT
    v.NumeroVuelo,
    Ruta        = CONCAT(o.CodigoIATA, '-', d.CodigoIATA),
    v.SalidaProgramada,
    Pasajero    = CONCAT(p.Nombres, ' ', p.Apellidos),
    r.Asiento,
    r.Clase,
    r.Estado,
    Piezas      = (SELECT COUNT(*) FROM dbo.Equipajes e WHERE e.ReservaId = r.ReservaId),
    PesoKg      = (SELECT ISNULL(SUM(e.PesoKg), 0) FROM dbo.Equipajes e WHERE e.ReservaId = r.ReservaId)
FROM dbo.Vuelos AS v
INNER JOIN dbo.Aeropuertos AS o ON o.AeropuertoId = v.AeropuertoOrigenId
INNER JOIN dbo.Aeropuertos AS d ON d.AeropuertoId = v.AeropuertoDestinoId
INNER JOIN dbo.Reservas    AS r ON r.VueloId      = v.VueloId
INNER JOIN dbo.Pasajeros   AS p ON p.PasajeroId   = r.PasajeroId;
GO
-- 2) Rutas más rentables
create view dbo.RutasRentables_view as
SELECT TOP (10)
    Ruta       = CONCAT(o.CodigoIATA, ' -> ', d.CodigoIATA),
    Vuelos     = COUNT(DISTINCT v.VueloId),
    Pasajeros  = COUNT(r.ReservaId),
    Ingreso    = SUM(r.Tarifa),
    TarifaProm = CAST(AVG(r.Tarifa) AS DECIMAL(10,2))
FROM dbo.Vuelos AS v
INNER JOIN dbo.Aeropuertos AS o ON o.AeropuertoId = v.AeropuertoOrigenId
INNER JOIN dbo.Aeropuertos AS d ON d.AeropuertoId = v.AeropuertoDestinoId
INNER JOIN dbo.Reservas    AS r ON r.VueloId      = v.VueloId
WHERE r.Estado <> N'Cancelada'
GROUP BY o.CodigoIATA, d.CodigoIATA
GO

/* =============================================================================
   14. Creacion de Stored Procedures
   ============================================================================= */
CREATE PROCEDURE dbo.sp_ObtenerTopPasajerosPorNacionalidad
    @Nacionalidad NVARCHAR(50)
AS
BEGIN
    -- Desactiva los mensajes de filas afectadas para mejorar el rendimiento
    SET NOCOUNT ON;

    SELECT TOP (10)
        Pasajero    = CONCAT(p.Nombres, ' ', p.Apellidos),
        p.Nacionalidad,
        Vuelos      = COUNT(*),
        Gasto       = SUM(r.Tarifa),
        KgFacturado = (SELECT ISNULL(SUM(e.PesoKg), 0)
                       FROM dbo.Equipajes e
                       INNER JOIN dbo.Reservas r2 ON r2.ReservaId = e.ReservaId
                       WHERE r2.PasajeroId = p.PasajeroId 
                         AND e.Tipo <> N'Mano')
    FROM dbo.Pasajeros AS p
    INNER JOIN dbo.Reservas AS r ON r.PasajeroId = p.PasajeroId
    WHERE r.Estado <> N'Cancelada' 
      AND p.Nacionalidad = @Nacionalidad
    GROUP BY p.PasajeroId, p.Nombres, p.Apellidos, p.Nacionalidad
    ORDER BY COUNT(*) DESC, SUM(r.Tarifa) DESC;
END;
GO

-- Equipaje extraviado o demorado, con a quién y en qué vuelo reclamarlo
CREATE PROCEDURE dbo.sp_EquipajeExtraviadoDemorado
    @NumeroVuelo NVARCHAR(10) = NULL
AS
BEGIN
SELECT
    e.Etiqueta,
    e.Estado,
    e.Tipo,
    e.PesoKg,
    Pasajero  = CONCAT(p.Nombres, ' ', p.Apellidos),
    Contacto  = p.Email,
    v.NumeroVuelo,
    Ruta      = CONCAT(o.CodigoIATA, '-', d.CodigoIATA),
    v.SalidaProgramada
FROM dbo.Equipajes AS e
INNER JOIN dbo.Reservas    AS r ON r.ReservaId    = e.ReservaId
INNER JOIN dbo.Pasajeros   AS p ON p.PasajeroId   = r.PasajeroId
INNER JOIN dbo.Vuelos      AS v ON v.VueloId      = r.VueloId
INNER JOIN dbo.Aeropuertos AS o ON o.AeropuertoId = v.AeropuertoOrigenId
INNER JOIN dbo.Aeropuertos AS d ON d.AeropuertoId = v.AeropuertoDestinoId
WHERE e.Estado IN (N'Extraviado', N'Demorado')
  AND  v.NumeroVuelo = @NumeroVuelo
ORDER BY v.SalidaProgramada;
END;
GO

/* =============================================================================
   16. Verificación
   ============================================================================= */
SELECT 'Aeropuertos' AS Tabla, COUNT(*) AS Filas FROM dbo.Aeropuertos
UNION ALL SELECT 'Pasajeros',  COUNT(*) FROM dbo.Pasajeros
UNION ALL SELECT 'Vuelos',     COUNT(*) FROM dbo.Vuelos
UNION ALL SELECT 'Reservas',   COUNT(*) FROM dbo.Reservas
UNION ALL SELECT 'Equipajes',  COUNT(*) FROM dbo.Equipajes;
GO

-- Ocupación, ingreso y equipaje por vuelo (primeros 10 vuelos)
SELECT TOP (10)
    v.NumeroVuelo,
    Ruta            = CONCAT(o.CodigoIATA, ' -> ', d.CodigoIATA),
    v.SalidaProgramada,
    v.Estado,
    v.CapacidadAsientos,
    res.Reservas,
    res.IngresoEstimado,
    eq.PiezasEquipaje,
    eq.PesoTotalKg
FROM dbo.Vuelos AS v
INNER JOIN dbo.Aeropuertos AS o ON o.AeropuertoId = v.AeropuertoOrigenId
INNER JOIN dbo.Aeropuertos AS d ON d.AeropuertoId = v.AeropuertoDestinoId
CROSS APPLY (
    SELECT Reservas = COUNT(*), IngresoEstimado = ISNULL(SUM(r.Tarifa), 0)
    FROM dbo.Reservas AS r
    WHERE r.VueloId = v.VueloId AND r.Estado <> N'Cancelada'
) AS res
CROSS APPLY (
    SELECT PiezasEquipaje = COUNT(*), PesoTotalKg = ISNULL(SUM(e.PesoKg), 0)
    FROM dbo.Equipajes AS e
    INNER JOIN dbo.Reservas AS r2 ON r2.ReservaId = e.ReservaId
    WHERE r2.VueloId = v.VueloId
) AS eq
ORDER BY v.SalidaProgramada;
GO