-- ============================================================ -- 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; -- ------------------------------------------------------------ -- USUARIOS ADMIN -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS admin_users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(60) NOT NULL UNIQUE, email VARCHAR(120) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, -- bcrypt role ENUM('superadmin','productos','operaciones') DEFAULT 'productos', active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, last_login DATETIME ); -- ------------------------------------------------------------ -- 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) ); -- ============================================================ -- DATOS INICIALES -- ============================================================ -- Admin por defecto (password: pts2024admin — cambiar inmediatamente) INSERT IGNORE INTO admin_users (username, email, password, role) VALUES ('admin', 'escribenos@peruviantravelservice.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.ucrm3a.We', 'superadmin'); -- 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);