-- ============================================================
-- PERUVIAN TRAVEL SERVICE — Motor de Cotización
-- Schema MySQL / MariaDB
-- Ejecutar como: mysql -u root -p pts_db < schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS pts_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pts_db;

-- ------------------------------------------------------------
-- CIRCUITOS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuits (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(40) NOT NULL UNIQUE,
    name            VARCHAR(200) NOT NULL,
    days            INT NOT NULL DEFAULT 1,
    nights          INT NOT NULL DEFAULT 0,
    description     TEXT,
    destinations    JSON,          -- ["Lima","Cusco","Arequipa"]
    search_tags     JSON,          -- ["machu picchu","colca","paracas"]
    valid_until     DATE,
    min_days_before INT DEFAULT 2,
    status          ENUM('active','draft','inactive') DEFAULT 'draft',
    created_by      INT,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
);

-- ------------------------------------------------------------
-- TARIFAS (por circuito, categoría, nacionalidad, tipo de hab.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuit_rates (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id      INT NOT NULL,
    category        ENUM('basic','tourist','tourist_mid','tourist_sup','first','first_sup','luxury') NOT NULL,
    nationality     ENUM('foreign','peruvian') NOT NULL,
    rate_single     DECIMAL(10,2) DEFAULT 0,
    rate_double     DECIMAL(10,2) DEFAULT 0,
    rate_triple     DECIMAL(10,2) DEFAULT 0,
    rate_child      DECIMAL(10,2) DEFAULT 0,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_circuit_cat_nat (circuit_id, category, nationality),
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- HOTELES POR CIRCUITO Y CATEGORÍA
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuit_hotels (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id  INT NOT NULL,
    category    ENUM('basic','tourist','tourist_mid','tourist_sup','first','first_sup','luxury') NOT NULL,
    destination VARCHAR(80) NOT NULL,
    hotel_name  VARCHAR(300),
    UNIQUE KEY uq_hotel (circuit_id, category, destination),
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- ITINERARIO
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuit_itinerary (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id  INT NOT NULL,
    day_number  INT NOT NULL,
    title       VARCHAR(200),
    description TEXT,
    UNIQUE KEY uq_day (circuit_id, day_number),
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- INCLUYE / NO INCLUYE
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuit_inclusions (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id  INT NOT NULL,
    type        ENUM('include','exclude') NOT NULL,
    item_order  INT DEFAULT 0,
    description TEXT NOT NULL,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- SUPLEMENTOS (por circuito)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS circuit_supplements (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id  INT,                -- NULL = suplemento global
    name        VARCHAR(150) NOT NULL,
    description VARCHAR(300),
    price       DECIMAL(10,2) DEFAULT 0,
    per_trip    TINYINT(1) DEFAULT 0,  -- 1 = aplica por tramo (x2)
    active      TINYINT(1) DEFAULT 1,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- TOURS OPCIONALES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS optional_tours (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id  INT,                -- NULL = global
    name        VARCHAR(150) NOT NULL,
    description VARCHAR(300),
    price       DECIMAL(10,2) DEFAULT 0,
    destination VARCHAR(80),
    active      TINYINT(1) DEFAULT 1,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- COMISIONES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS commissions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_type       ENUM('minorista','mayorista') NOT NULL UNIQUE,
    percentage      DECIMAL(5,2) NOT NULL DEFAULT 0,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------
-- FECHAS BLOQUEADAS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS blackout_dates (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    date_from   VARCHAR(10) NOT NULL,   -- MM-DD para anual, YYYY-MM-DD para específico
    date_to     VARCHAR(10) NOT NULL,
    type        ENUM('anual','especifico') DEFAULT 'anual',
    active      TINYINT(1) DEFAULT 1
);

-- ------------------------------------------------------------
-- COTIZACIONES GUARDADAS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS quotes (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    quote_ref       VARCHAR(20) NOT NULL UNIQUE,  -- QT-XXXXXX
    circuit_id      INT,
    user_type       ENUM('direct','minorista','mayorista') DEFAULT 'direct',
    agency_name     VARCHAR(150),
    pax_adults      INT DEFAULT 1,
    pax_children    INT DEFAULT 0,
    pax_infants     INT DEFAULT 0,
    nationality     ENUM('foreign','peruvian') DEFAULT 'foreign',
    room_type       ENUM('single','double','triple') DEFAULT 'double',
    category        VARCHAR(30),
    travel_date     DATE,
    total_amount    DECIMAL(12,2),
    supplements     JSON,
    optional_tours  JSON,
    flights         JSON,
    status          ENUM('pending','confirmed','cancelled','expired') DEFAULT 'pending',
    expires_at      DATETIME,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id)
);

-- ------------------------------------------------------------
-- RESERVAS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bookings (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    booking_ref     VARCHAR(20) NOT NULL UNIQUE,  -- RES-XXXXXX
    quote_id        INT,
    circuit_id      INT,
    user_type       ENUM('direct','minorista','mayorista') DEFAULT 'direct',
    agency_name     VARCHAR(150),
    travel_date     DATE,
    total_amount    DECIMAL(12,2),
    status          ENUM('pending','confirmed','cancelled') DEFAULT 'pending',
    notes           TEXT,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quote_id) REFERENCES quotes(id),
    FOREIGN KEY (circuit_id) REFERENCES circuits(id)
);

-- ------------------------------------------------------------
-- PASAJEROS DE RESERVA
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS booking_passengers (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    booking_id      INT NOT NULL,
    passenger_type  ENUM('adult','child','infant') DEFAULT 'adult',
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    doc_type        ENUM('DNI','Pasaporte','CE') DEFAULT 'Pasaporte',
    doc_number      VARCHAR(40),
    birth_date      DATE,
    nationality     VARCHAR(80),
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- LOG DE ACTIVIDAD
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT,
    action      ENUM('create','edit','delete','publish','login') NOT NULL,
    entity      VARCHAR(60),
    entity_id   INT,
    description VARCHAR(300),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admin_users(id)
);

-- Comisiones iniciales
INSERT IGNORE INTO commissions (user_type, percentage) VALUES ('minorista', 10.00);
INSERT IGNORE INTO commissions (user_type, percentage) VALUES ('mayorista', 20.00);

-- Fechas bloqueadas
INSERT IGNORE INTO blackout_dates (name, date_from, date_to, type) VALUES
('Semana Santa',        '03-25', '04-07', 'anual'),
('Fiestas Patrias',     '07-24', '07-29', 'anual'),
('Navidad y Año Nuevo', '12-24', '01-02', 'anual');

-- Circuito base
INSERT IGNORE INTO circuits (code, name, days, nights, destinations, search_tags, valid_until, min_days_before, status, created_by)
VALUES (
    'TP-EMA C012-002',
    'Perú entre Volcanes y Andes',
    12, 11,
    '["Lima","Paracas","Ica","Arequipa","Colca","Cusco","Machu Picchu"]',
    '["lima","paracas","ica","arequipa","colca","cusco","machu picchu","maras","moray","huacachina","montaña de colores"]',
    '2027-02-28', 2, 'active', 1
);

-- Tarifas del circuito C012 (ID=1)
INSERT IGNORE INTO circuit_rates (circuit_id, category, nationality, rate_single, rate_double, rate_triple, rate_child) VALUES
(1,'basic',       'foreign', 1621, 1197, 1114,  981),
(1,'tourist',     'foreign', 1756, 1263, 1177, 1044),
(1,'tourist_mid', 'foreign', 1890, 1345, 1283, 1150),
(1,'tourist_sup', 'foreign', 2046, 1401, 1339, 1206),
(1,'first',       'foreign', 2209, 1492, 1434, 1301),
(1,'first_sup',   'foreign', 2307, 1543, 1481, 1348),
(1,'luxury',      'foreign', 2886, 1865, 1714, 1581),
(1,'basic',       'peruvian', 1673, 1195, 1102, 1005),
(1,'tourist',     'peruvian', 1833, 1273, 1176, 1078),
(1,'tourist_mid', 'peruvian', 1991, 1370, 1301, 1204),
(1,'tourist_sup', 'peruvian', 2175, 1436, 1367, 1269),
(1,'first',       'peruvian', 2367, 1543, 1479, 1382),
(1,'first_sup',   'peruvian', 2482, 1604, 1534, 1437),
(1,'luxury',      'peruvian', 3166, 1984, 1810, 1713);

-- Suplementos globales
INSERT IGNORE INTO circuit_supplements (circuit_id, name, description, price, per_trip, active) VALUES
(1, 'Suplemento Tren Turístico',    'Ajuste tarifario ferroviario por tramo', 30.00, 1, 1),
(1, 'Tren Panorámico',             'Upgrade clase Panorámica por tramo',      45.00, 1, 1),
(1, 'Tren Panorámico Superior',    'Upgrade clase Superior por tramo',         65.00, 1, 1);

-- Tours opcionales
INSERT IGNORE INTO optional_tours (circuit_id, name, description, price, destination, active) VALUES
(1, 'Laguna Humantay',       'Caminata a laguna glaciar 4,200 msnm', 35.00, 'Cusco', 1),
(1, 'Trekking Ausangate',    'Circuito nivel moderado',               55.00, 'Cusco', 1),
(1, 'Noche cultural cusqueña','Show de danzas + cena',                40.00, 'Cusco', 1),
(1, 'Salineras adicional',   'Visita independiente Salineras Maras',  20.00, 'Cusco', 1);
