-- Create table for agencies
CREATE TABLE agencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    document VARCHAR(20) NOT NULL,
    mobile_phone VARCHAR(20) NOT NULL,
    conventional_phone VARCHAR(20),
    credit_limit DECIMAL(10, 2) DEFAULT 0,
    logo VARCHAR(255) DEFAULT '',
    state BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create table for products
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL DEFAULT '',
    state BOOLEAN DEFAULT TRUE,    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert records into products table
INSERT INTO products (name, description) VALUES 
('VUELOS', 'Servicios de vuelos'),
('HOTELES', 'Servicios de hoteles'),
('ACTIVIDADES', 'Servicios de actividades'),
('TRASLADOS', 'Servicios de traslados');

-- Create table for agency_product_percentages
CREATE TABLE agencies_product_percentages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    product_id INT NOT NULL,
    percentage DECIMAL(5, 2) NOT NULL DEFAULT 0,
    person_1  DECIMAL(5, 2) NOT NULL DEFAULT 0,
    person_2  DECIMAL(5, 2) NOT NULL DEFAULT 0,
    person_3  DECIMAL(5, 2) NOT NULL DEFAULT 0,
    person_4  DECIMAL(5, 2) NOT NULL DEFAULT 0,
    FOREIGN KEY (agency_id) REFERENCES agencies(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create table for user types
CREATE TABLE user_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL DEFAULT '',
    state BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert records into user_types table
INSERT INTO user_types (type_name, description) VALUES 
('ADMINISTRADOR', 'Usuario con todos los privilegios'),
('B2B', 'Usuario de negocio a negocio/agencias'),
('B2C', 'Usuario de negocio a consumidor final'),
('API', 'Usuario de consumo de servicios API');

-- Create table for users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document VARCHAR(20) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    mobile_phone VARCHAR(20) DEFAULT '',
    email VARCHAR(255) NOT NULL DEFAULT '',
    password VARCHAR(255) DEFAULT '',
    token VARCHAR(255),
    token_expiration TIMESTAMP,
    user_type_id INT NOT NULL DEFAULT 0,
    agency_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    state BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_type_id) REFERENCES user_types(id),
    FOREIGN KEY (agency_id) REFERENCES agencies(id)
);

-- Create table for customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document VARCHAR(20) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) DEFAULT '',
    address VARCHAR(255) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create table for carts
CREATE TABLE carts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    user_id INT,
    customer_id INT,
    product_id INT NOT NULL,
    checkin DATE NULL,
    checkout DATE NULL,
    from_place VARCHAR(255) DEFAULT '',
    to_place VARCHAR(255) DEFAULT '',
    adults INT DEFAULT 1,
    children INT DEFAULT 0,
    infants INT DEFAULT 0,
    handicapped INT DEFAULT 0,
    seniors INT DEFAULT 0,
    rooms INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiration TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 30 MINUTE),
    status ENUM('PENDIENTE', 'EN PROCESO', 'CANCELADO', 'EMITIDO') DEFAULT 'PENDIENTE',
    FOREIGN KEY (agency_id) REFERENCES agencies(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create table for cart details tokens
CREATE TABLE carts_details_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cart_id INT NOT NULL,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    
    FOREIGN KEY (cart_id) REFERENCES carts(id)
);

-- Create table for cart details children ages
CREATE TABLE carts_details_ages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cart_id INT NOT NULL,    
    child_age INT NOT NULL DEFAULT 0,
    FOREIGN KEY (cart_id) REFERENCES carts(id)
);

-- Create table for flight fees
CREATE TABLE flights_fees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pax INT NOT NULL,    
    fee_type ENUM('FIXED', 'PERCENTAGE') NOT NULL DEFAULT 'FIXED',
    amount DECIMAL(10, 2) NOT NULL DEFAULT 0    
);

-- Create table for payments
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cart_id INT NOT NULL,    
    amount DECIMAL(10, 2) NOT NULL,
    request_id INT NOT NULL,
    process_url VARCHAR(255) NOT NULL,
    reference VARCHAR(255) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('PENDING', 'APPROVED', 'FAILED', 'CANCEL', 'REJECTED') DEFAULT 'PENDING',
    FOREIGN KEY (cart_id) REFERENCES carts(id)
);