
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE SISTEMA                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- LANGUAGE
-- ---------------------------------------------------------------------------------------------
-- Almacena Idiomas
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_language`
(
    `Id_Language`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Language_Code`             VARCHAR(2) NOT NULL,
    `Language_Name`             VARCHAR(250) NOT NULL,
    `Language_Content`          INT(1) NOT NULL,  -- 1: No para Contenido, 2: Activado para contenido
    `Language_Status`           INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo


    PRIMARY KEY (`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- COUNTRY
-- ---------------------------------------------------------------------------------------------
-- Almacena Paises
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_country`
(
    `Id_Country`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Country_Code`              VARCHAR(3) NOT NULL,
    `Country_Name`              VARCHAR(250) NOT NULL,
    `Country_Status`            INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público


    PRIMARY KEY (`Id_Country`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- STATE
-- ---------------------------------------------------------------------------------------------
-- Almacena Estados/Regiones de un País
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_state`
(
    `Id_State`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `State_Code`                VARCHAR(3) NOT NULL,
    `State_Name`                VARCHAR(250) NOT NULL,
    `State_Status`              INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    `Id_Country`                INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_State`),
    FOREIGN KEY (`Id_Country`)  REFERENCES `t_country`(`Id_Country`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- CITY
-- ---------------------------------------------------------------------------------------------
-- Almacena Ciudades/Provincias de un Estado/Region
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_city`
(
    `Id_City`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `City_Code`                 VARCHAR(3) NOT NULL,
    `City_Name`                 VARCHAR(250) NOT NULL,
    `City_Status`               INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    `Id_State`                  INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_City`),
    FOREIGN KEY (`Id_State`)    REFERENCES `t_state`(`Id_State`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE RECEIPT
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Comprobantes de Pago
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_receipt`
(
    `Id_TypeReceipt`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeReceipt_Name`                  VARCHAR(250) NOT NULL,
    `TypeReceipt_Abrv`                  VARCHAR(4) NOT NULL,
    `TypeReceipt_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público


    PRIMARY KEY (`Id_TypeReceipt`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE PROVIDER
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Proveesores
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_provider`
(
    `Id_TypeProvider`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeProvider_Name`                 VARCHAR(250) NOT NULL,
    `TypeProvider_Abrv`                 VARCHAR(4) NOT NULL,
    `TypeProvider_Lock`                 INT(1) NOT NULL,
    `TypeProvider_Status`               INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeProvider`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE ITEM
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Items de Almacén
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_item`
(
    `Id_TypeItem`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeItem_Name`                     VARCHAR(250) NOT NULL,
    `TypeItem_Abrv`                     VARCHAR(4) NOT NULL,
    `TypeItem_Status`                   INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeItem`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                           TABLAS DE SISTEMA CON TRADUCCIONES                                              //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- TYPE DOCUMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Documentos de Identidad
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_document`
(
    `Id_TypeDocument`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeDocument_Name`             VARCHAR(250) NOT NULL,
    `TypeDocument_Abrv`             VARCHAR(4) NOT NULL,
    `TypeDocument_Status`           INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo


    PRIMARY KEY (`Id_TypeDocument`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE DOCUMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Documentos
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_document_transl`
(
    `Id_TypeDocumentTransl`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeDocumentTransl_Title`              VARCHAR(250) NOT NULL,
    `Id_TypeDocument`                       INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeDocumentTransl`),
    FOREIGN KEY (`Id_TypeDocument`)         REFERENCES `t_type_document`(`Id_TypeDocument`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE PAYMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena Formas de Pago
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_payment`
(
    `Id_TypePayment`            INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypePayment_Name`          VARCHAR(250) NOT NULL,
    `TypePayment_Abrv`          VARCHAR(4) NOT NULL,
    `TypePayment_Status`        INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo


    PRIMARY KEY (`Id_TypePayment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE PAYMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de las Formas de Pago
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_payment_transl`
(
    `Id_TypePaymentTransl`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypePaymentTransl_Title`               VARCHAR(250) NOT NULL,
    `Id_TypePayment`                        INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypePaymentTransl`),
    FOREIGN KEY (`Id_TypePayment`)          REFERENCES `t_type_payment`(`Id_TypePayment`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE TOUR
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_tour`
(
    `Id_TypeTour`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeTour_Name`                     VARCHAR(250) NOT NULL,
    `TypeTour_Abrv`                     VARCHAR(4) NOT NULL,
    `TypeTour_Status`                   INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeTour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- CATEGORY TOUR
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_category_tour`
(
    `Id_CategoryTour`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `CategoryTour_Name`                     VARCHAR(250) NOT NULL,
    `CategoryTour_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_CategoryTour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE PAYMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de las Formas de Pago
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_tour_transl`
(
    `Id_TypeTourTransl`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeTourTransl_Title`              VARCHAR(250) NOT NULL,
    `Id_TypeTour`                       INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeTourTransl`),
    FOREIGN KEY (`Id_TypeTour`)             REFERENCES `t_type_tour`(`Id_TypeTour`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE UPGRADE
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Mejoras de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_upgrade`
(
    `Id_TypeUpgrade`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeUpgrade_Name`                  VARCHAR(250) NOT NULL,
    `TypeUpgrade_Abrv`                  VARCHAR(4) NOT NULL,
    `TypeUpgrade_Lock`                  INT(1) NOT NULL,
    `TypeUpgrade_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeUpgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE UPGRADE TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Upgrade de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_upgrade_transl`
(
    `Id_TypeUpgradeTransl`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeUpgradeTransl_Title`               VARCHAR(250) NOT NULL,
    `TypeUpgradeTransl_Description`         VARCHAR(250) NOT NULL,
    `Id_TypeUpgrade`                        INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeUpgradeTransl`),
    FOREIGN KEY (`Id_TypeUpgrade`)          REFERENCES `t_type_upgrade`(`Id_TypeUpgrade`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE EXTRA
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Extras de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_extra`
(
    `Id_TypeExtra`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeExtra_Name`                    VARCHAR(250) NOT NULL,
    `TypeExtra_Abrv`                    VARCHAR(4) NOT NULL,
    `TypeExtra_Lock`                    INT(1) NOT NULL,
    `TypeExtra_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_TypeExtra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE EXTRA TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Upgrade de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_extra_transl`
(
    `Id_TypeExtraTransl`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeExtraTransl_Title`                 VARCHAR(250) NOT NULL,
    `TypeExtraTransl_Description`           VARCHAR(250) NOT NULL,
    `Id_TypeExtra`                          INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeExtraTransl`),
    FOREIGN KEY (`Id_TypeExtra`)            REFERENCES `t_type_extra`(`Id_TypeExtra`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Equuipos de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_equipment`
(
    `Id_TypeEquipment`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeEquipment_Name`                    VARCHAR(250) NOT NULL,
    `TypeEquipment_Abrv`                    VARCHAR(4) NOT NULL,
    `TypeEquipment_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeEquipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE EQUIPMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Equipo de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_equipment_transl`
(
    `Id_TypeEquipmentTransl`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeEquipmentTransl_Title`             VARCHAR(250) NOT NULL,
    `TypeEquipmentTransl_Description`       VARCHAR(250) NOT NULL,
    `Id_TypeEquipment`                      INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeEquipmentTransl`),
    FOREIGN KEY (`Id_TypeEquipment`)        REFERENCES `t_type_equipment`(`Id_TypeEquipment`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TYPE RATE
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Equuipos de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_rate`
(
    `Id_TypeRate`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeRate_Name`                     VARCHAR(250) NOT NULL,
    `TypeRate_Abrv`                     VARCHAR(4) NOT NULL,
    `TypeRate_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    PRIMARY KEY (`Id_TypeRate`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE EQUIPMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Equipo de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_rate_transl`
(
    `Id_TypeRateTransl`                     INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeRateTransl_Title`                  VARCHAR(250) NOT NULL,
    `TypeRateTransl_Description`            VARCHAR(250) NOT NULL,
    `Id_TypeRate`                           INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeRateTransl`),
    FOREIGN KEY (`Id_TypeRate`)             REFERENCES `t_type_rate`(`Id_TypeRate`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ---------------------------------------------------------------------------------------------
-- TYPE ROOM
-- ---------------------------------------------------------------------------------------------
-- Almacena Tipos de Habitaciones
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_room`
(
    `Id_TypeRoom`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeRoom_Name`                 VARCHAR(250) NOT NULL,
    `TypeRoom_Abrv`                 VARCHAR(4) NOT NULL,
    `TypeRoom_Capacity`             INT(2) NOT NULL,
    `TypeRoom_Status`               INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_TypeRoom`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TYPE UPGRADE TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los Tipos de Upgrade de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_type_room_transl`
(
    `Id_TypeRoomTransl`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TypeRoomTransl_Title`              VARCHAR(250) NOT NULL,
    `TypeRoomTransl_Description`        VARCHAR(250) NOT NULL,
    `Id_TypeRoom`                       INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TypeRoomTransl`),
    FOREIGN KEY (`Id_TypeRoom`)             REFERENCES `t_type_room`(`Id_TypeRoom`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;







-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE PREGUNTAS                                                     //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- QUESTION
-- ---------------------------------------------------------------------------------------------
-- Almacena las preguntas que se haran a los pasajeros de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_question`
(
    `Id_Question`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Question_Name`                     VARCHAR(250) NOT NULL,
    `Question_Abrv`                     VARCHAR(4) NOT NULL,
    `Id_TypeProvider`                   INT(11) NOT NULL,
    `Question_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público


    PRIMARY KEY (`Id_Question`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- QUESTION TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de las preguntas a los pasajeros
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_question_transl`
(
    `Id_QuestionTransl`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `QuestionTransl_Title`              VARCHAR(250) NOT NULL,

    `Id_Question`                       INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_QuestionTransl`),
    FOREIGN KEY (`Id_Question`)             REFERENCES `t_question`(`Id_Question`),
    FOREIGN KEY (`Id_Language`)             REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE CREDENCIALES                                                  //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- CREDENTIAL
-- ---------------------------------------------------------------------------------------------
-- Almacena las preguntas que se haran a los pasajeros de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_credential`
(
    `Id_Credential`         INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Credential_key`        VARCHAR(50) NOT NULL,
    `Credential_Value`      TEXT NOT NULL,
        

    PRIMARY KEY (`Id_Credential`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE ADMINISTRADOR                                                 //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- DIRECTORY 
-- ---------------------------------------------------------------------------------------------
-- Almacena todas las Rutas del Sistema
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     15/09/2020
-- Update Date:     15/09/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       N/A
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_directory`
(
    `Id_Directory`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Directory_Name`                    VARCHAR(255) NOT NULL,
    `Directory_Url`                     VARCHAR(255),
    `Directory_Icon`                    VARCHAR(100),
    `Directory_Order`                   INT(11),
    `Directory_Delete`                  INT(1),
    `Directory_Update`                  INT(1),
    `Directory_Insert`                  INT(1),
    `Directory_Self`                    INT(11) NOT NULL,


    PRIMARY KEY (`Id_Directory`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- ROLE
-- ---------------------------------------------------------------------------------------------
-- Almacena los roles que pueden tener los administradores
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     22/07/2020
-- Update Date:     22/07/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_admin_role`
(
    `Id_AdminRole`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `AdminRole_Name`                    VARCHAR(100) NOT NULL,
    `AdminRole_Abrv`                    VARCHAR(4) NOT NULL,
    `AdminRole_Color`                   VARCHAR(10) NOT NULL,
    `AdminRole_Admin`                   INT(1) NOT NULL, -- 1 :: false - 2 :: true  
    `AdminRole_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Activo, 2: Inactivo

    PRIMARY KEY (`Id_AdminRole`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ADMIN ROLE AUTH
-- ---------------------------------------------------------------------------------------------
-- Almacena los permisos de los roles
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     22/07/2020
-- Update Date:     22/07/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      N/A
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_admin_role_auth`
(
    `Id_AdminRoleAuth`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `AdminRoleAuth_Insert`              INT(1) NOT NULL,
    `AdminRoleAuth_Update`              INT(1) NOT NULL,
    `AdminRoleAuth_Delete`              INT(1) NOT NULL,

    `Id_AdminRole`                      INT(11) UNSIGNED NOT NULL,
    `Id_Directory`                      INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_AdminRoleAuth`),
    FOREIGN KEY (`Id_AdminRole`)        REFERENCES `t_admin_role`(`Id_AdminRole`),
    FOREIGN KEY (`Id_Directory`)        REFERENCES `t_directory`(`Id_Directory`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ADMIN
-- ---------------------------------------------------------------------------------------------
-- Almacena los usuarios que administraran
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     15/09/2020
-- Update Date:     15/09/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_admin`
(
    `Id_Admin`                          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Admin_Name`                        VARCHAR(100) NOT NULL,
    `Admin_LastName`                    VARCHAR(100) NOT NULL, 
    `Admin_Abrv`                        VARCHAR(4) NOT NULL, 
    `Admin_NoDocument`                  VARCHAR(20) NOT NULL,
    `Admin_Password`                    VARCHAR(250) NOT NULL,
    `Admin_Email`                       VARCHAR(250) NOT NULL,
    `Admin_Address`                     VARCHAR(250) NOT NULL,
    `Admin_Phone`                       VARCHAR(50) NOT NULL,
    `Admin_Status`                      INT(1) NOT NULL,  -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_TypeDocument`                   INT(11) UNSIGNED NOT NULL,
    `Id_AdminRole`                      INT(11) UNSIGNED NOT NULL,
    `Id_Country`                        INT(11) UNSIGNED NOT NULL,
    `Id_State`                          INT(11) UNSIGNED NOT NULL,
    `Id_City`                           INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_Admin`),
    FOREIGN KEY (`Id_TypeDocument`)     REFERENCES `t_type_document`(`Id_TypeDocument`),
    FOREIGN KEY (`Id_AdminRole`)        REFERENCES `t_admin_role`(`Id_AdminRole`),
    FOREIGN KEY (`Id_Country`)          REFERENCES `t_country`(`Id_Country`),
    FOREIGN KEY (`Id_State`)            REFERENCES `t_state`(`Id_State`),
    FOREIGN KEY (`Id_City`)             REFERENCES `t_city`(`Id_City`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ADMIN SESSION
-- ------------------------------------------------------------------------
-- Almacena sesiones de administradores
-- ------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MORA FRISANCHO
-- Create Date:     22/07/2020
-- Update Date:     22/07/2020
-- ---------------------------------------------------------------------------------------------


CREATE TABLE `t_admin_session`
(
    `Id_AdminSession`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `AdminSession_IP`                   VARCHAR(18) NOT NULL,
    `AdminSession_DateStart`            DATETIME NOT NULL,
    `AdminSession_DateEnd`              DATETIME NOT NULL,
    `AdminSession_Token`                VARCHAR(250) NOT NULL,
    `AdminSession_Status`               INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_Admin`                          INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_AdminSession`),
    FOREIGN KEY (`Id_Admin`)            REFERENCES `t_admin`(`Id_Admin`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE PROVEEDORES                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- PROVEEDOR 
-- ---------------------------------------------------------------------------------------------
-- Almacena los tipos de proveedores
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_provider`
(
    `Id_Provider`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Provider_BusinessName`             VARCHAR(250) NOT NULL,
    `Provider_TradeName`                VARCHAR(250) NOT NULL,
    `Provider_NoDocument`               VARCHAR(50) NOT NULL,
    `Provider_Address`                  VARCHAR(250) NOT NULL,
    `Provider_Phone`                    VARCHAR(30) NOT NULL,
    `Provider_ScoreTotal`               INT(11) NOT NULL,
    `Provider_ScoreCant`                INT(11) NOT NULL,
    `Provider_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_TypeDocument`                   INT(11) UNSIGNED NOT NULL,
    `Id_TypeProvider`                   INT(11) UNSIGNED NOT NULL,
    `Id_Country`                        INT(11) UNSIGNED NOT NULL,
    `Id_State`                          INT(11) UNSIGNED NOT NULL,
    `Id_City`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Provider`),
    FOREIGN KEY (`Id_TypeDocument`)     REFERENCES `t_type_document`(`Id_TypeDocument`),
    FOREIGN KEY (`Id_TypeProvider`)     REFERENCES `t_type_provider`(`Id_TypeProvider`),
    FOREIGN KEY (`Id_Country`)          REFERENCES `t_country`(`Id_Country`),
    FOREIGN KEY (`Id_State`)            REFERENCES `t_state`(`Id_State`),
    FOREIGN KEY (`Id_City`)             REFERENCES `t_city`(`Id_City`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE ALMACEN                                                       //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- ITEM
-- ---------------------------------------------------------------------------------------------
-- Almacena los articulos que pueden estar en un almacen
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_item`
(
    `Id_Item`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Item_Name`                     VARCHAR(250) NOT NULL,
    `Item_Abrv`                     VARCHAR(4) NOT NULL,
    `Item_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_TypeItem`                   INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Item`),
    FOREIGN KEY (`Id_TypeItem`)     REFERENCES `t_type_item`(`Id_TypeItem`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- WAREHOUSE 
-- ---------------------------------------------------------------------------------------------
-- Almacena los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_warehouse`
(
    `Id_Warehouse`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Warehouse_Code`                    VARCHAR(50) NOT NULL,
    `Warehouse_Name`                    VARCHAR(250) NOT NULL,
    `Warehouse_Address`                 VARCHAR(250) NOT NULL,
    `Warehouse_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_Country`                        INT(11) UNSIGNED NOT NULL,
    `Id_State`                          INT(11) UNSIGNED NOT NULL,
    `Id_City`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Warehouse`),
    FOREIGN KEY (`Id_Country`)          REFERENCES `t_country`(`Id_Country`),
    FOREIGN KEY (`Id_State`)            REFERENCES `t_state`(`Id_State`),
    FOREIGN KEY (`Id_City`)             REFERENCES `t_city`(`Id_City`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ZONE
-- ---------------------------------------------------------------------------------------------
-- Almacena las zonas de los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_zone`
(
    `Id_Zone`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Zone_Name`                     VARCHAR(250) NOT NULL,
    `Zone_Abrv`                     VARCHAR(4) NOT NULL,
    `Zone_Status`                   INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_Warehouse`                  INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Zone`),
    FOREIGN KEY (`Id_Warehouse`)    REFERENCES `t_warehouse`(`Id_Warehouse`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- ---------------------------------------------------------------------------------------------
-- WAREHOUSE BALANCE
-- ---------------------------------------------------------------------------------------------
-- Almacena los saldos y precios de los articulos en los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_warehouse_balance`
(
    `Id_WarehouseBalance`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `WarehouseBalance_UnitPrice`        DECIMAL(12,2) NOT NULL, -- almacena el costo unitario de los articulos en el almacen
    `WarehouseBalance_Quantity`         INT(6) NOT NULL, -- indica el saldo disponible en el almacen de cada articulo
    `WarehouseBalance_Status`           INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_Warehouse`                      INT(11) UNSIGNED NOT NULL,
    `Id_Zone`                           INT(11) UNSIGNED NOT NULL,
    `Id_Item`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_WarehouseBalance`),
    FOREIGN KEY (`Id_Warehouse`)        REFERENCES `t_warehouse`(`Id_Warehouse`),
    FOREIGN KEY (`Id_Zone`)             REFERENCES `t_zone`(`Id_Zone`),
    FOREIGN KEY (`Id_Item`)             REFERENCES `t_item`(`Id_Item`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- REGISTRY CONFIGURATION 
-- ---------------------------------------------------------------------------------------------
-- Almacena la configuracion de los registros de los documentos que acreditan los movimientos de los articulos en los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_registry_configuration`
(
    `Id_RegistryConfiguration`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `RegistryConfiguration_Serie`       VARCHAR(30) NOT NULL,
    `RegistryConfiguration_Number`      VARCHAR(100) NOT NULL,
    `RegistryConfiguration_Status`      int(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo


    PRIMARY KEY (`Id_RegistryConfiguration`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- REGISTRY 
-- ---------------------------------------------------------------------------------------------
-- Almacena los registros de los documentos que acreditan los movimientos de los articulos en los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_registry`
(
    `Id_Registry`                               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Registry_DocumentDate`                     DATETIME NOT NULL,          -- FECHA DEL DOCUMENTO QUE SUSTENTA EL REGISTRO
    `Registry_DocumentSerie`                    VARCHAR(10) NOT NULL,       -- SERIE DEL DOCUMENTO
    `Registry_DocumentNumber`                   VARCHAR(100) NOT NULL,      -- NUMERO DE DOCUMENTO
    `Registry_Items`                            INT(4),                     -- indica la cantidad de articulos
    `Registry_Type`                             INT(1),                     -- 2 entrada, 1 salida
    `Registry_Status`                           INT(1),                     -- indica si el comprobante esta 0 anulado, 1 pendiente, 2 confirmado, 3 devuelto

    `Registry_DateConfirm`                      DATETIME NOT NULL,
    `Registry_DateReturn`                       DATETIME NOT NULL,
    `Registry_DateNull`                         DATETIME NOT NULL,
        
    `Id_Provider`                               INT(11) UNSIGNED NOT NULL,
    `Id_Warehouse`                              INT(11) UNSIGNED NOT NULL,
    `Id_Group`                                  int(11) UNSIGNED NOT NULL,
    `Id_RegistryConfiguration`                  int(11) UNSIGNED NOT NULL,

    `Id_Admin_Create`                           INT(11) UNSIGNED NOT NULL,
    `Id_Admin_Confirm`                          INT(11) UNSIGNED NOT NULL,
    `Id_Admin_Return`                           INT(11) UNSIGNED NOT NULL,
    `Id_Admin_Null`                             INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_Registry`),
    FOREIGN KEY (`Id_Provider`)                 REFERENCES `t_provider`(`Id_Provider`),
    FOREIGN KEY (`Id_Warehouse`)                REFERENCES `t_warehouse`(`Id_Warehouse`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- REGISTRY DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena el detalle del registro de los documentos que acreditan los movimientos de los articulos en los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_registry_detail`
(
    `Id_RegistryDetail`             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `RegistryDetail_Order`          INT(6) NOT  NULL,           -- orden del articulo en el documento
    `RegistryDetail_Quantity`       DECIMAL(12,2) NOT NULL,     -- CANTIDAD DEL PRODUCTO
    `RegistryDetail_Description`    VARCHAR(250),               -- nombre articulo en el comprobante
    `RegistryDetail_UnitPrice`      DECIMAL(12,2),              -- precio unitario
    `RegistryDetail_RefundQuantity` DECIMAL(12,2) NOT NULL,     -- CANTIDAD DEVUELTA DEL PRODUCTO
    `RegistryDetail_Observation`    TEXT NOT NULL,

    `Id_Registry`                   INT(11) UNSIGNED NOT NULL,
    `Id_WarehouseBalance`           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_RegistryDetail`),
    FOREIGN KEY (`Id_Registry`)         REFERENCES `t_registry`(`Id_Registry`),
    FOREIGN KEY (`Id_WarehouseBalance`) REFERENCES `t_warehouse_balance`(`Id_WarehouseBalance`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- WAREHOUSE TRANSACTION
-- ---------------------------------------------------------------------------------------------
-- Almacena los movimientos de los articulos en los almacenes
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_warehouse_transaction`
(
    `Id_WarehouseTransaction`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `WarehouseTransaction_Order`            INT(11) NOT NULL,       -- numero de movimiento en almacen de cada articulo
    `WarehouseTransaction_Date`             DATETIME NOT NULL,
    `WarehouseTransaction_Quantity`         INT(6) NOT NULL,        -- cantidad del movimiento
    `WarehouseTransaction_Balance`          INT(11) NOT NULL,       -- indica el saldo disponible despues del movimiento en el almacen
    `WarehouseTransaction_Type`             INT(1) NOT NULL,        -- 2: entrada - 1: salida

    `Id_Registry`                           INT(11) UNSIGNED NOT NULL,
    `Id_WarehouseBalance`                   INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_WarehouseTransaction`),
    FOREIGN KEY (`Id_Registry`)             REFERENCES `t_registry`(`Id_Registry`),
    FOREIGN KEY (`Id_WarehouseBalance`)     REFERENCES `t_warehouse_balance`(`Id_WarehouseBalance`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE UPGRADE                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- UPGRADE
-- ---------------------------------------------------------------------------------------------
-- Almacena las mejoras de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_upgrade`
(
    `Id_Upgrade`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Upgrade_Code`                      VARCHAR(10) NOT NULL,
    `Upgrade_Name`                      VARCHAR(250) NOT NULL,
    `Upgrade_Feature`                   TEXT NOT NULL,
    `Upgrade_Price`                     DECIMAL(12,2) NOT NULL,
    `Upgrade_PriceConfidential`         DECIMAL(12,2) NOT NULL,
    `Upgrade_Status`                    INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Upgrade_Lock`                      INT(1) NOT NULL, -- 0: - 1: not - 2: yes

    `Id_TypeUpgrade`                    INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_Upgrade`),
    FOREIGN KEY (`Id_TypeUpgrade`)      REFERENCES `t_type_upgrade`(`Id_TypeUpgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- UPGRADE TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducción de las Mejoras
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_upgrade_transl`
(
    `Id_UpgradeTransl`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `UpgradeTransl_Title`               VARCHAR(250) NOT NULL,
    `Id_Upgrade`                        INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_UpgradeTransl`),
    FOREIGN KEY (`Id_Upgrade`)          REFERENCES `t_upgrade`(`Id_Upgrade`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE EXTRA                                                     //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- EXTRA
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducción de las Mejoras
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_extra`
(
    `Id_Extra`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Extra_Code`                    VARCHAR(10) NOT NULL,
    `Extra_Name`                    VARCHAR(250) NOT NULL,
    `Extra_Feature`                 TEXT NOT NULL,
    `Extra_Price`                   DECIMAL(12,2) NOT NULL,
    `Extra_PriceConfidential`       DECIMAL(12,2) NOT NULL,
    `Extra_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Extra_Lock`                    INT(1) NOT NULL, -- 0: , 1: not - 2: yes

    `Id_TypeExtra`                  INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_Extra`),
    FOREIGN KEY (`Id_TypeExtra`)    REFERENCES `t_type_extra`(`Id_TypeExtra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- EXTRA TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducción de las Mejoras
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_extra_transl`
(
    `Id_ExtraTransl`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ExtraTransl_Title`                 VARCHAR(250) NOT NULL,
    `Id_Extra`                          INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_ExtraTransl`),
    FOREIGN KEY (`Id_Extra`)            REFERENCES `t_extra`(`Id_Extra`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE EQUIPOS                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena los equipos de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_equipment`
(
    `Id_Equipment`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Equipment_Code`                    VARCHAR(10) NOT NULL,
    `Equipment_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    `Id_Item`                           INT(11) UNSIGNED NOT NULL,
    `Id_TypeEquipment`                  INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Equipment`),
    FOREIGN KEY (`Id_Item`)             REFERENCES `t_item`(`Id_Item`),
    FOREIGN KEY (`Id_TypeEquipment`)    REFERENCES `t_type_equipment`(`Id_TypeEquipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- EQUIPMENT RATE
-- ---------------------------------------------------------------------------------------------
-- Almacena los equipos de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_equipment_rate`
(
    `Id_EquipmentRate`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `EquipmentRate_DayStart`                INT(3) NOT NULL,
    `EquipmentRate_DayEnd`                  INT(3) NOT NULL,
    `EquipmentRate_Price`                   DECIMAL(12,2) NOT NULL,
    `EquipmentRate_PriceConfidential`       DECIMAL(12,2) NOT NULL,

    `Id_Equipment`                          INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_EquipmentRate`),
    FOREIGN KEY (`Id_Equipment`)            REFERENCES `t_equipment`(`Id_Equipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- EQUIPMENT TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducción de los Equipos
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_equipment_transl`
(
    `Id_EquipmentTransl`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `EquipmentTransl_Title`             VARCHAR(250) NOT NULL,
    `Id_Equipment`                      INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_EquipmentTransl`),
    FOREIGN KEY (`Id_Equipment`)        REFERENCES `t_equipment`(`Id_Equipment`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE TOURS                                                     //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- TOUR
-- ---------------------------------------------------------------------------------------------
-- Almacena los tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour`
(
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Tour_Code`                         VARCHAR(50) NOT NULL,
    `Tour_Name`                         VARCHAR(250) NOT NULL,
    `Tour_Duration`                     INT(3) NOT NULL,
    `Tour_MinPax`                       INT(2) NOT NULL,    -- CANTIDAD MINIMA DE PASAJERO PARA REALIZAR UNA RESERVA
    `Tour_Availability`                 INT(3) NOT NULL,    -- CANTIDAD DE CUPOS DISPONIBLES
    `Tour_AvailabilityStatus`           INT(1) NOT NULL,    -- 0: , 1: not, 2: yes
    `Tour_Anticipation`                 INT(3) NOT NULL,    -- DIAS O MESES DE ANTICIPACION DE COMPRA
    `Tour_AnticipationType`             INT(1) NOT NULL,    -- 0 :: - 1 :: HORAS - 2 :: DIAS - 3 :: MESES   
    `Tour_Color`                        VARCHAR(10) NOT NULL,
    `Tour_Score`                        DECIMAL(5,2) NOT NULL,
    `Tour_Status`                       INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Id_CategoryTour`                   INT(11) UNSIGNED NOT NULL,
    `Id_TypeTour`                       INT(11) UNSIGNED NOT NULL,
    `Id_Country`                        INT(11) UNSIGNED NOT NULL,
    `Id_State`                          INT(11) UNSIGNED NOT NULL,
    `Id_City`                           INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_Tour`),
    FOREIGN KEY (`Id_CategoryTour`)     REFERENCES `t_category_tour`(`Id_CategoryTour`),
    FOREIGN KEY (`Id_TypeTour`)         REFERENCES `t_type_tour`(`Id_TypeTour`),
    FOREIGN KEY (`Id_Country`)          REFERENCES `t_country`(`Id_Country`),
    FOREIGN KEY (`Id_State`)            REFERENCES `t_state`(`Id_State`),
    FOREIGN KEY (`Id_City`)             REFERENCES `t_city`(`Id_City`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR LOCK
-- ---------------------------------------------------------------------------------------------
-- Almacena bloqueos de Tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_lock`
(
    `Id_TourLock`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourLock_DateStart`                DATE NOT NULL,
    `TourLock_DateEnd`                  DATE NOT NULL,
    `TourLock_Remark`                   TEXT NOT NULL,

    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,

        
    PRIMARY KEY (`Id_TourLock`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_transl`
(
    `Id_TourTransl`                     INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourTransl_Title`                  VARCHAR(250) NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TourTransl`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- TOUR ACTIVITY
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_activity`
(
    `Id_TourActivity`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourActivity_Order`                INT(3) NOT NULL,
    `TourActivity_Title`                VARCHAR(250) NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL, 

    PRIMARY KEY (`Id_TourActivity`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TOUR ACTIVITY DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena los detalles del las actividades de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_activity_detail`
(
    `Id_TourActivityDetail`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourActivityDetail_Code`               VARCHAR(100) NOT NULL,
    `TourActivityDetail_Time`               TIME NOT NULL,
    `TourActivityDetail_Description`        TEXT NOT NULL,
    `Id_TourActivity`                       INT(11) UNSIGNED NOT NULL, 

    PRIMARY KEY (`Id_TourActivityDetail`),
    FOREIGN KEY (`Id_TourActivity`)         REFERENCES `t_tour_activity`(`Id_TourActivity`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- TOUR QUESTION
-- ---------------------------------------------------------------------------------------------
-- Almacena las preguntas para los reviews de un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_question`
(
    `Id_TourQuestion`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourQuestion_Order`                INT(3) NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL, 
    `Id_Question`                       INT(11) UNSIGNED NOT NULL, 

    PRIMARY KEY (`Id_TourQuestion`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_Question`)         REFERENCES `t_question`(`Id_Question`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR ITEM
-- ---------------------------------------------------------------------------------------------
-- Almacena los items necesarios para un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_item`
(
    `Id_TourItem`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourItem_Quantity`                 INT(3) NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL, 
    `Id_Item`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TourItem`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_Item`)             REFERENCES `t_item`(`Id_Item`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR UPGRADE TYPE
-- ---------------------------------------------------------------------------------------------
-- Almacena las categorias de Ugrade de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_upgrade_type`
(
    `Id_TourUpgradeType`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourUpgradeType_Description`       VARCHAR(250) NOT NULL,
    `TourUpgradeType_Order`             INT(3) NOT NULL,
    `TourUpgradeType_Multiple`          INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `TourUpgradeType_VisibleEndorser`   INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `TourUpgradeType_VisibleUser`       INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `TourUpgradeType_Status`            INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,
    `Id_TypeUpgrade`                    INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_TourUpgradeType`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_TypeUpgrade`)      REFERENCES `t_type_upgrade`(`Id_TypeUpgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR UPGRADE
-- ---------------------------------------------------------------------------------------------
-- Almacena los Upgrades de una categoria de Upgrade de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_upgrade`
(
    `Id_TourUpgrade`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourUpgrade_Order`                 INT(3) NOT NULL,
    `TourUpgrade_Select`                INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourUpgrade_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público
    `Id_TourUpgradeType`                INT(11) UNSIGNED NOT NULL,
    `Id_Upgrade`                        INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_TourUpgrade`),
    FOREIGN KEY (`Id_TourUpgradeType`)  REFERENCES `t_tour_upgrade_type`(`Id_TourUpgradeType`),
    FOREIGN KEY (`Id_Upgrade`)          REFERENCES `t_upgrade`(`Id_Upgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- TOUR EXTRA TYPE
-- ---------------------------------------------------------------------------------------------
-- Almacena las categorias de Extras de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_extra_type`
(
    `Id_TourExtraType`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourExtraType_Description`         VARCHAR(250) NOT NULL,
    `TourExtraType_Order`               INT(3) NOT NULL,
    `TourExtraType_Multiple`            INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourExtraType_VisibleEndorser`     INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourExtraType_VisibleUser`         INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourExtraType_Status`              INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,
    `Id_TypeExtra`                      INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_TourExtraType`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_TypeExtra`)        REFERENCES `t_type_extra`(`Id_TypeExtra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- TOUR EXTRA
-- ---------------------------------------------------------------------------------------------
-- Almacena las categorias de Extras de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_extra`
(
    `Id_TourExtra`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourExtra_Order`                   INT(3) NOT NULL,
    `TourExtra_Select`                  INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourExtra_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Id_TourExtraType`                  INT(11) UNSIGNED NOT NULL,
    `Id_Extra`                          INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_TourExtra`),
    FOREIGN KEY (`Id_TourExtraType`)    REFERENCES `t_tour_extra_type`(`Id_TourExtraType`),
    FOREIGN KEY (`Id_Extra`)            REFERENCES `t_extra`(`Id_Extra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- TOUR EQUIPMENT TYPE
-- ---------------------------------------------------------------------------------------------
-- Almacena las categorias de Extras de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_equipment_type`
(
    `Id_TourEquipmentType`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourEquipmentType_Order`           INT(3) NOT NULL,
    `TourEquipmentType_Multiple`        INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourEquipmentType_VisibleEndorser` INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourEquipmentType_VisibleUser`     INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourEquipmentType_Status`          INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,
    `Id_TypeEquipment`                  INT(11) UNSIGNED NOT NULL, 
        
    PRIMARY KEY (`Id_TourEquipmentType`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_TypeEquipment`)    REFERENCES `t_type_equipment`(`Id_TypeEquipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- TOUR EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena las categorias de Extras de un Tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_tour_equipment`
(
    `Id_TourEquipment`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourEquipment_Order`                   INT(3) NOT NULL,
    `TourEquipment_Select`                  INT(1) NOT NULL, -- 0: , 1: NOT,  2: YES
    `TourEquipment_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Id_TourEquipmentType`                  INT(11) UNSIGNED NOT NULL,
    `Id_Equipment`                          INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_TourEquipment`),
    FOREIGN KEY (`Id_TourEquipmentType`)    REFERENCES `t_tour_equipment_type`(`Id_TourEquipmentType`),
    FOREIGN KEY (`Id_Equipment`)            REFERENCES `t_equipment`(`Id_Equipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE PAQUETES                                                  //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- PACKAGE
-- ---------------------------------------------------------------------------------------------
-- Almacena los paquetes 
-- ---------------------------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MORA FRISANCHO
-- Create Date:     13/08/2020
-- Update Date:     13/08/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package`
(
    `Id_Package`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Package_Code`                      VARCHAR(50)  NOT NULL,
    `Package_Name`                      VARCHAR(250) NOT NULL,
    `Package_UrlThumbnail`              VARCHAR(500) NOT NULL,
    `Package_Duration`                  INT(3) NOT NULL,
    `Package_Availability`              INT(3) NOT NULL,
    `Package_PaxRequired`               INT(1) NOT NULL, -- 1 :: NOT - 2 :: YES
    `Package_Status`                    INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Package_Endorser`                  INT(1) NOT NULL, -- 1 :: NOT - 2 :: YES

    PRIMARY KEY (`Id_Package`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PACKAGE TRANSL
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los tpaquetes
-- ---------------------------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MORA FRISANCHO
-- Create Date:     13/08/2020
-- Update Date:     13/08/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_transl`
(
    `Id_PackageTransl`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageTransl_Title`               VARCHAR(250) NOT NULL,
    `Id_Package`                        INT(11) UNSIGNED NOT NULL, 
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_PackageTransl`),
    FOREIGN KEY (`Id_Package`)          REFERENCES `t_package`(`Id_Package`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PACKAGE TOUR
-- ---------------------------------------------------------------------------------------------
-- Almacena los tours de los tpaquetes
-- ---------------------------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MORA FRISANCHO
-- Create Date:     13/08/2020
-- Update Date:     13/08/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_tour`
(
    `Id_PackageTour`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageTour_Order`                 INT(3) NOT NULL,
    `PackageTour_Price`                 DECIMAL(12,2) NOT NULL,
    `Id_Package`                        INT(11) UNSIGNED NOT NULL, 
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_PackageTour`),
    FOREIGN KEY (`Id_Package`)          REFERENCES `t_package`(`Id_Package`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PACKAGE RATE
-- ---------------------------------------------------------------------------------------------
-- Almacena las tarifas de los paquetes
-- ---------------------------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MORA FRISANCHO
-- Create Date:     13/08/2020
-- Update Date:     13/08/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_rate`
(
    `Id_PackageRate`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageRate_PaxStart`                  INT(3) NOT NULL,
    `PackageRate_PaxEnd`                    INT(3) NOT NULL,
    `PackageRate_Price`                     DECIMAL(12,2) NOT NULL,
    `PackageRate_PriceConfidential`         DECIMAL(12,2) NOT NULL,
    `PackageRate_Type`                      INT(1) NOT NULL, -- 0: , 1: COMPARTIDO, 2: PRIVADO
    `PackageRate_Status`                    INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    `Id_Package`                            INT(11) UNSIGNED NOT NULL,
    `Id_TypeRate`                           INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_PackageRate`),
    FOREIGN KEY (`Id_Package`)          REFERENCES `t_package`(`Id_Package`),
    FOREIGN KEY (`Id_TypeRate`)         REFERENCES `t_type_rate`(`Id_TypeRate`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PACKAGE FEE
-- ---------------------------------------------------------------------------------------------
-- Almacena las traducciones de los tours
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_fee`
(
    `Id_PackageFee`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageFee_Amount`             DECIMAL(12,2) NOT NULL,
    `PackageFee_Full`               INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `PackageFee_Type`               INT(1) NOT NULL, -- 0: , 1: PERCENTAGE, 2: FIXED
    `Id_Package`                    INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_PackageFee`),
    FOREIGN KEY (`Id_Package`)  REFERENCES `t_package`(`Id_Package`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- ---------------------------------------------------------------------------------------------
-- PACKAGE TOUR UPGRADE
-- ---------------------------------------------------------------------------------------------
-- Almacena el upgrade incluído en el PackageTour 
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS CURI LUQUE
-- Create Date:     24/11/2020
-- Update Date:     24/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_tour_upgrade`
(
    `Id_PackageTourUpgrade`             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageTourUpgrade_Passenger`      INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `Id_PackageTour`                    INT(11) UNSIGNED NOT NULL,
    `Id_Upgrade`                        INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_PackageTourUpgrade`),
    FOREIGN KEY (`Id_PackageTour`)          REFERENCES `t_package_tour`(`Id_PackageTour`),
    FOREIGN KEY (`Id_Upgrade`)              REFERENCES `t_upgrade`(`Id_Upgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- PACKAGE TOUR EXTRA
-- ---------------------------------------------------------------------------------------------
-- Almacena el extra incluído en el PackageTour 
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS CURI LUQUE
-- Create Date:     24/11/2020
-- Update Date:     24/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_tour_extra`
(
    `Id_PackageTourExtra`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageTourExtra_Passenger`        INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `Id_PackageTour`                    INT(11) UNSIGNED NOT NULL,
    `Id_Extra`                          INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_PackageTourExtra`),
    FOREIGN KEY (`Id_PackageTour`)          REFERENCES `t_package_tour`(`Id_PackageTour`),
    FOREIGN KEY (`Id_Extra`)                REFERENCES `t_extra`(`Id_Extra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PACKAGE TOUR EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena el equipamiento incluído en el PackageTour 
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS CURI LUQUE
-- Create Date:     24/11/2020
-- Update Date:     24/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_package_tour_equipment`
(
    `Id_PackageTourEquipment`           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PackageTourEquipment_Passenger`    INT(1) NOT NULL, -- 0: , 1: NOT, 2: YES
    `Id_PackageTour`                    INT(11) UNSIGNED NOT NULL,
    `Id_Equipment`                      INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_PackageTourEquipment`),
    FOREIGN KEY (`Id_PackageTour`)          REFERENCES `t_package_tour`(`Id_PackageTour`),
    FOREIGN KEY (`Id_Equipment`)            REFERENCES `t_equipment`(`Id_Equipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;









-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE USUARIO                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- USER COUNTRY
-- ---------------------------------------------------------------------------------------------
-- Almacena LOS Paises De LOS Usuarios
-- ---------------------------------------------------------------------------------------------
-- Author:          MIGUEL ANGEL MIORA FRISANCHO
-- Create Date:     13/08/2020
-- Update Date:     13/08/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_user_country`
(
    `Id_UserCountry`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `UserCountry_Name`                      VARCHAR(250) NOT NULL,
    `UserCountry_Code`                      VARCHAR(3) NOT NULL,
    `UserCountry_PhoneCode`                 VARCHAR(4) NOT NULL, 
    `UserCountry_Img`                       VARCHAR(250) NOT NULL,
    `UserCountry_Status`                    INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_UserCountry`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- USER
-- ---------------------------------------------------------------------------------------------
-- Almacena Usuarios de la APP 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     26/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_user`
(
    `Id_User`                           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `User_DateCreate`                   DATETIME NOT NULL,
    `User_Name`                         VARCHAR(100) NOT NULL,
    `User_LastName`                     VARCHAR(100) NOT NULL, 
    `User_Email`                        VARCHAR(250) NOT NULL,
    `User_Password`                     VARCHAR(250) NOT NULL,
    `User_PhoneCode`                    VARCHAR(4) NOT NULL,
    `User_Phone`                        VARCHAR(20) NOT NULL,
    `User_NoDocument`                   VARCHAR(50) NOT NULL,
    `User_Address`                      VARCHAR(250) NOT NULL,

    `User_ContactName`                  VARCHAR(250) NOT NULL, 
    `User_ContactPhone`                 VARCHAR(20) NOT NULL,

    `User_Type`                         INT(1) NOT NULL, -- web 1, endosador 2
    `User_Status`                       INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
        
    `Id_TypeDocument`                   INT(11) UNSIGNED NOT NULL,
    `Id_UserCountry`                    INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_User`),
    FOREIGN KEY (`Id_UserCountry`)      REFERENCES `t_user_country`(`Id_UserCountry`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ---------------------------------------------------------------------------------------------
-- USER PACKAGE RATE
-- ---------------------------------------------------------------------------------------------
-- Almacena Precios especiales del usuario
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS LOEL CURI LUQUE
-- Create Date:     26/11/2020
-- Update Date:     26/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_user_package_rate`
(
    `Id_UserPackageRate`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `UserPackageRate_Discount`          DECIMAL(12,2) NOT NULL,
        
    `Id_User`                           INT(11) UNSIGNED NOT NULL,
    `Id_PackageRate`                    INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_UserPackageRate`),
    FOREIGN KEY (`Id_User`)             REFERENCES `t_user`(`Id_User`),
    FOREIGN KEY (`Id_PackageRate`)      REFERENCES `t_package_rate`(`Id_PackageRate`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- USER SESSION
-- ---------------------------------------------------------------------------------------------
-- Almacena Usuarios de la APP 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_user_session`
(
    `Id_UserSession`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `UserSession_DateStart`             DATETIME NOT NULL,
    `UserSession_DateEnd`               DATETIME NOT NULL,
    `UserSession_Token`                 VARCHAR(250) NOT NULL,
    `UserSession_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo

    `Id_User`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_UserSession`),
    FOREIGN KEY (`Id_User`)             REFERENCES `t_user`(`Id_User`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE RESERVA                                                   //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- BOOKING
-- ---------------------------------------------------------------------------------------------
-- Almacena las Reservas
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking`
(
    `Id_Booking`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Booking_Date`                      DATETIME NOT NULL,
    `Booking_DateStart`                 DATE NOT NULL,
    `Booking_DateBriefing`              DATETIME NOT NULL,
    `Booking_PlaceBriefing`             VARCHAR(250) NOT NULL,
    `Booking_Code`                      VARCHAR(100) NOT NULL,
    `Booking_HistoryChanges`            TEXT NOT NULL,
    `Booking_Hotel`                     VARCHAR(250) NOT NULL,
    `Booking_Observation`               TEXT NOT NULL,
    `Booking_Receipt`                   INT(1) NOT NULL, -- 0 :: - 1 :: NOT - 2 :: YES
    `Booking_Type`                      INT(1) NOT NULL, -- 1 :: RESERVAS WEB - 2 :: RESERVAS DESDE SISTEMA
    `Booking_LastMinute`                INT(1) NOT NULL, -- 1 :: NORMAL - 2 :: ULTIMA HORA
    `Booking_Status`                    INT(1) NOT NULL, -- 0 :: ANULADO - 1 :: PENDIENTE - 2 :: CONFIRMADO - 3 :: FINALIZADO - -1 :: ELIMINADO
    `Booking_ManuallyConfirmed`         INT(1) NOT NULL, -- 0 :: - 1 :: NOT - 2 :: YES
    `Booking_InPerson`                  INT(1) NOT NULL, -- 1 :: Por correo o sistema - 2 :: En persona
    `Booking_Token`                     VARCHAR(100) NOT NULL,
        
        
    `Id_Booking_Replace`                INT(11) UNSIGNED NOT NULL,
    `Id_Admin`                          INT(11) NOT NULL,
    `Id_GuideLanguage`                  INT(11) UNSIGNED NOT NULL,
    `Id_Language`                       INT(11) UNSIGNED NOT NULL,
    `Id_User`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Booking`),
    FOREIGN KEY (`Id_GuideLanguage`)    REFERENCES `t_language`(`Id_Language`),
    FOREIGN KEY (`Id_Language`)         REFERENCES `t_language`(`Id_Language`),
    FOREIGN KEY (`Id_User`)             REFERENCES `t_user`(`Id_User`),
    INDEX (`Id_Booking_Replace`),
    INDEX (`Booking_Status`),
    INDEX (`Booking_Token`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                               TABLAS DE RESERVA PASAJERO                                                  //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena los pasajeros de una reserva
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_passenger`
(
    `Id_Passenger`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Passenger_DateCreate`              DATETIME NOT NULL,
    `Passenger_DateEdit`                DATETIME NOT NULL,
    `Passenger_Name`                    VARCHAR(100) NOT NULL,
    `Passenger_LastName`                VARCHAR(100) NOT NULL, 
    `Passenger_Email`                   VARCHAR(250) NOT NULL,
    `Passenger_Phone`                   VARCHAR(50) NOT NULL,
    `Passenger_Gender`                  INT(1) NOT NULL,
    `Passenger_DOB`                     DATE NOT NULL,
    `Passenger_NoDocument`              VARCHAR(20) NOT NULL DEFAULT '00000000',
    `Passenger_Restriction`             TEXT(20) NOT NULL,
    `Passenger_Extras`                  TEXT(20) NOT NULL,
    `Passenger_ImgDocument1`            VARCHAR(250),
    `Passenger_ImgDocument2`            VARCHAR(250),
    `Passenger_Token`                   VARCHAR(250) NOT NULL,
    `Passenger_Status`                  INT(1) NOT NULL, -- 0: Eliminado, 1: Inactivo, 2: Activo
    `Passenger_PaymentStatus`           INT(1) NOT NULL, -- 1 :: NO PAGADO - 2 :: PAGADO
    `Passenger_InfoStatus`              INT(1) NOT NULL, -- 1 :: INCOMPLETO - 2 :: COMPLETO

    `Id_Booking`                        INT(11) UNSIGNED NOT NULL,
    `Id_TypeDocument`                   INT(11) UNSIGNED NOT NULL,
    `Id_UserCountry`                    INT(11) UNSIGNED NOT NULL,
    `Id_TypeRate`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Passenger`),
    FOREIGN KEY (`Id_Booking`)          REFERENCES `t_booking`(`Id_Booking`),
    FOREIGN KEY (`Id_TypeDocument`)     REFERENCES `t_type_document`(`Id_TypeDocument`),
    FOREIGN KEY (`Id_UserCountry`)      REFERENCES `t_user_country`(`Id_UserCountry`),
    FOREIGN KEY (`Id_TypeRate`)         REFERENCES `t_type_rate`(`Id_TypeRate`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- BOOKING
-- ---------------------------------------------------------------------------------------------
-- Almacena las Reservas
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_tour`
(
    `Id_BookingTour`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingTour_DateStart`             DATE NOT NULL,
    `BookingTour_PickUp`                TIME NOT NULL,
    `BookingTour_Type`                  INT(1) NOT NULL,        -- 1 :: compartido - 2 :: privado
    `BookingTour_NoPax`                 INT(3) NOT NULL,    
    `BookingTour_Name`                  VARCHAR(250) NOT NULL,  -- Nombre del paquete al momento de la reserva
    `BookingTour_TourName`              VARCHAR(250) NOT NULL,
    `BookingTour_Group`                 INT(2) NOT NULL,
    `BookingTour_TourDuration`          INT(3) NOT NULL,
    `BookingTour_Status`                INT(1) NOT NULL,        -- 1 :: anulado - 2 :: activo
        
    `Id_Booking`                        INT(11) UNSIGNED NOT NULL,
    `Id_Package`                        INT(11) UNSIGNED NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_BookingTour`),
    FOREIGN KEY (`Id_Booking`)          REFERENCES `t_booking`(`Id_Booking`),
    FOREIGN KEY (`Id_Package`)          REFERENCES `t_package`(`Id_Package`),
    FOREIGN KEY (`Id_Tour`)             REFERENCES `t_tour`(`Id_Tour`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- BOOKING TOUR PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena los pasajeros por tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_tour_passenger`
(
    `Id_BookingTourPassenger`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingTourPassenger_Lock`             INT(1) NOT NULL, -- 0 :: - 1 :: not - 2 :: yes
    `BookingTourPassenger_Reportable`       INT(1) NOT NULL, -- 0 :: - 1 :: not - 2 :: yes
    `BookingTourPassenger_Hotel`            VARCHAR(250) NOT NULL,
    `BookingTourPassenger_Remark`           TEXT NOT NULL,
    `BookingTourPassenger_Price`            DECIMAL(12,2) NOT NULL,
    `BookingTourPassenger_Full`             DECIMAL(12,2) NOT NULL,
    `BookingTourPassenger_Status`           INT(1) NOT NULL, -- 1 :: anulado - 2 :: activo - 0 :: no viajo - 3 :: dio alcance
    `BookingTourPassenger_PaymentStatus`    INT(1) NOT NULL, -- 1 :: NO PAGADO - 2 :: PAGADO

    `Id_BookingTour`                        INT(11) UNSIGNED NOT NULL,
    `Id_Passenger`                          INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_BookingTourPassenger`),
    FOREIGN KEY (`Id_BookingTour`)          REFERENCES `t_booking_tour`(`Id_BookingTour`),
    FOREIGN KEY (`Id_Passenger`)            REFERENCES `t_passenger`(`Id_Passenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                       TABLAS DE RESERVA UPGRADES / EXTRASYUIPOS                                           //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- BOOKING UPGRADE
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_upgrade`
(
    `Id_BookingUpgrade`                     INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingUpgrade_Quantity`               INT(3) NOT NULL,
    `BookingUpgrade_Price`                  DECIMAL(12,2) NOT NULL,
    `BookingUpgrade_Total`                  DECIMAL(12,2) NOT NULL,
    `BookingUpgrade_Feature`                TEXT,
    `BookingUpgrade_Included`               INT(1) NOT NULL, -- 0 :: Not Included - 2 :: Included

    `Id_Provider`                           INT(11) NOT NULL,
    `Id_BookingTour`                        INT(11) UNSIGNED NOT NULL,
    `Id_Upgrade`                            INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_BookingUpgrade`),
    FOREIGN KEY (`Id_BookingTour`)          REFERENCES `t_booking_tour`(`Id_BookingTour`),
    FOREIGN KEY (`Id_Upgrade`)              REFERENCES `t_upgrade`(`Id_Upgrade`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- BOOKING UPGRADE PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_upgrade_passenger`
(
    `Id_BookingUpgradePassenger`            INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingUpgradePassenger_Date`          DATETIME NOT NULL,      -- FECHA DE CREACIÓN
    `BookingUpgradePassenger_OnlyReturn`    INT(1) NOT NULL,        -- 2 :: Sólo DateReturn - 1 :: AMBAS FECHAS
    `BookingUpgradePassenger_DateEntry`     DATETIME NOT NULL,      -- FECHA DE INGRESO
    `BookingUpgradePassenger_Datereturn`    DATETIME NOT NULL,      -- FECHA DE RETORNO
    `BookingUpgradePassenger_DatePurchase`  DATE NOT NULL,          -- FECHA DE COMPRA
    `BookingUpgradePassenger_Carrier`       TEXT(250) NOT NULL,     -- NOMBRE DE PROVEEDOR
    `BookingUpgradePassenger_Code`          TEXT(100) NOT NULL,     -- CODIGO DEL TICKET
    `BookingUpgradePassenger_Amount`        DECIMAL(12,2) NOT NULL, -- MONTO PAGADO
    `BookingUpgradePassenger_Remark`        TEXT NOT NULL,          -- OBSERVACIONES
    `BookingUpgradePassenger_Status`        INT(1) NOT NULL,        -- 1 :: PENDIENTE - 2 :: COMPRADO - 0 :: CANCELADO 
        
    `Id_Admin`                              INT(11) NOT NULL,

    `Id_BookingUpgrade`                     INT(11) UNSIGNED NOT NULL,
    `Id_BookingTourPassenger`               INT(11) UNSIGNED NOT NULL,  

    PRIMARY KEY (`Id_BookingUpgradePassenger`),
    FOREIGN KEY (`Id_BookingUpgrade`)       REFERENCES `t_booking_upgrade`(`Id_BookingUpgrade`),
    FOREIGN KEY (`Id_BookingTourPassenger`) REFERENCES `t_booking_tour_passenger`(`Id_BookingTourPassenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ---------------------------------------------------------------------------------------------
-- BOOKING EXTRA
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_extra`
(
    `Id_BookingExtra`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingExtra_Quantity`                 INT(3) NOT NULL,
    `BookingExtra_Price`                    DECIMAL(12,2) NOT NULL,
    `BookingExtra_Total`                    DECIMAL(12,2) NOT NULL,
    `BookingExtra_Feature`                  TEXT,
    `BookingExtra_Included`                 INT(1) NOT NULL, -- 0 :: Not Included - 2 :: Included

    `Id_Provider`                           INT(11) NOT NULL,
    `Id_BookingTour`                        INT(11) UNSIGNED NOT NULL,
    `Id_Extra`                              INT(11) UNSIGNED NOT NULL,
        
    PRIMARY KEY (`Id_BookingExtra`),
    FOREIGN KEY (`Id_BookingTour`)          REFERENCES `t_booking_tour`(`Id_BookingTour`),
    FOREIGN KEY (`Id_Extra`)                REFERENCES `t_extra`(`Id_Extra`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- BOOKING EXTRA PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_extra_passenger`
(
    `Id_BookingExtraPassenger`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingExtraPassenger_Date`            DATETIME NOT NULL,      -- FECHA DE CREACIÓN
    `BookingExtraPassenger_DateEntryStart`  DATETIME NOT NULL,      -- FECHA Y HORA DEL INICIO DEL INGRESO
    `BookingExtraPassenger_DateEntryEnd`    DATETIME NOT NULL,      -- FECHA Y HORA DEL FINAL DEL INGRESO
    `BookingExtraPassenger_DatePurchase`    DATE NOT NULL,          -- FECHA DE COMPRA
    `BookingExtraPassenger_Code`            TEXT(100) NOT NULL,     -- CODIGO DEL TICKET
    `BookingExtraPassenger_Amount`          DECIMAL(12,2) NOT NULL, -- MONTO PAGADO
    `BookingExtraPassenger_Remark`          TEXT NOT NULL,          -- OBSERVACIONES
    `BookingExtraPassenger_Status`          INT(1) NOT NULL,        -- 1 :: PENDIENTE - 2 :: COMPRADO - 0 :: CANCELADO 
    `Id_Admin`                              INT(11) NOT NULL,

    `Id_BookingExtra`                       INT(11) UNSIGNED NOT NULL,
    `Id_BookingTourPassenger`               INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_BookingExtraPassenger`),
    FOREIGN KEY (`Id_BookingExtra`)         REFERENCES `t_booking_extra`(`Id_BookingExtra`),
    FOREIGN KEY (`Id_BookingTourPassenger`) REFERENCES `t_booking_tour_passenger`(`Id_BookingTourPassenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ---------------------------------------------------------------------------------------------
-- BOOKING EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_equipment`
(
    `Id_BookingEquipment`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingEquipment_Quantity`             INT(3) NOT NULL,
    `BookingEquipment_Price`                DECIMAL(12,2) NOT NULL,
    `BookingEquipment_Total`                DECIMAL(12,2) NOT NULL,
    `BookingEquipment_Included`             INT(1) NOT NULL, -- 0 :: Not Included - 2 :: Included

    `Id_BookingTour`                        INT(11) UNSIGNED NOT NULL,
    `Id_Equipment`                          INT(11) UNSIGNED NOT NULL, 
        
    PRIMARY KEY (`Id_BookingEquipment`),
    FOREIGN KEY (`Id_BookingTour`)          REFERENCES `t_booking_tour`(`Id_BookingTour`),
    FOREIGN KEY (`Id_Equipment`)            REFERENCES `t_equipment`(`Id_Equipment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- BOOKING EQUIPMENT PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_equipment_passenger`
(
    `Id_BookingEquipmentPassenger`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Id_BookingEquipment`                   INT(11) UNSIGNED NOT NULL,
    `Id_BookingTourPassenger`               INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_BookingEquipmentPassenger`),
    FOREIGN KEY (`Id_BookingEquipment`)     REFERENCES `t_booking_equipment`(`Id_BookingEquipment`),
    FOREIGN KEY (`Id_BookingTourPassenger`) REFERENCES `t_booking_tour_passenger`(`Id_BookingTourPassenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                               TABLAS DE RESERVA HABITACIONES                                              //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- ROOM
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_room`
(
    `Id_Room`                           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Room_Observation`                  TEXT NOT NULL,
        
    `Id_Booking`                        INT(11) UNSIGNED NOT NULL,
    `Id_TypeRoom`                       INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Room`),
    FOREIGN KEY (`Id_Booking`)          REFERENCES `t_booking`(`Id_Booking`),
    FOREIGN KEY (`Id_TypeRoom`)         REFERENCES `t_type_room`(`Id_TypeRoom`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ROOM PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_room_passenger`
(
    `Id_RoomPassenger`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Id_Room`                           INT(11) UNSIGNED NOT NULL,
    `Id_Passenger`                      INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_RoomPassenger`),
    FOREIGN KEY (`Id_Room`)             REFERENCES `t_room`(`Id_Room`),
    FOREIGN KEY (`Id_Passenger`)        REFERENCES `t_passenger`(`Id_Passenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE RESERVA VUELOS                                                //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- FLIGHT
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_flight`
(
    `Id_Flight`                         INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Flight_DateDeparture`              DATETIME NOT NULL,
    `Flight_DateArrival`                DATETIME NOT NULL,
    `Flight_Origin`                     VARCHAR(100) NOT NULL,
    `Flight_Destination`                VARCHAR(100) NOT NULL,
    `Flight_Number`                     VARCHAR(20) NOT NULL,
    `Flight_Carrier`                    VARCHAR(100) NOT NULL,
    `Flight_Observation`                TEXT NOT NULL,
    `Flight_Type`                       INT(1) NOT NULL, -- 1 :: TRANSFER IN - 2 :: TRANSFER OUT
        
    `Id_Booking`                        INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Flight`),
    FOREIGN KEY (`Id_Booking`)          REFERENCES `t_booking`(`Id_Booking`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- ROOM PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_flight_passenger`
(
    `Id_FlightPassenger`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Id_Flight`                         INT(11) UNSIGNED NOT NULL,
    `Id_Passenger`                      INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_FlightPassenger`),
    FOREIGN KEY (`Id_Flight`)           REFERENCES `t_flight`(`Id_Flight`),
    FOREIGN KEY (`Id_Passenger`)        REFERENCES `t_passenger`(`Id_Passenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                   TABLAS DE RESERVA INVOICE                                               //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- INVOICE
-- ---------------------------------------------------------------------------------------------
-- Almacena las Facturas de una Reserva
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_invoice`
(
    `Id_Invoice`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Invoice_Date`                      DATETIME NOT NULL,
    `Invoice_Code`                      VARCHAR(100) NOT NULL,
    `Invoice_Total`                     DECIMAL(12,2) NOT NULL,
    `Invoice_TotalPay`                  DECIMAL(12,2) NOT NULL,
    `Invoice_TotalPending`              DECIMAL(12,2) NOT NULL,
    `Invoice_Status`                    INT(1) NOT NULL, -- No se usa

    `Id_Booking`                        INT(11) UNSIGNED NOT NULL,
    `Id_Passenger`                      INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_Invoice`),
    FOREIGN KEY (`Id_Booking`)          REFERENCES `t_booking`(`Id_Booking`), 
    FOREIGN KEY (`Id_Passenger`)        REFERENCES `t_passenger`(`Id_Passenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- INVOICE DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena el detalle de las Facturas de una Reserva
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_invoice_detail`
(
    `Id_InvoiceDetail`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `InvoiceDetail_Quantity`            INT(3) NOT NULL,
    `InvoiceDetail_Description`         VARCHAR(250) NOT NULL,
    `InvoiceDetail_Price`               DECIMAL(12,2) NOT NULL,
    `InvoiceDetail_Full`                DECIMAL(12,2) NOT NULL,
    `InvoiceDetail_Total`               DECIMAL(12,2) NOT NULL,
    `InvoiceDetail_Included`            INT(1) NOT NULL, -- 1 :: Not included - 2 :: Included
    
    -- 0 :: ID PAQUETE - 1 :: BOOKING UPGRADE - 2 :: BOOKING EXTRA
    -- - 3 :: BOOKING EQUIPMENT - 4 :: DETALLES MANUALES - 5 :: CANCELADOS - 9 :: IGV
    `InvoiceDetail_Type`                INT(1) NOT NULL,
    `InvoiceDetail_Group`               INT(5) NOT NULL,
    `InvoiceDetail_Status`              INT(1) NOT NULL, -- 1 :: pendiente o confirmado - 2 :: cancelado
    `Id`                                INT(11) UNSIGNED NOT NULL,

    `Id_Invoice`                        INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_InvoiceDetail`),
    FOREIGN KEY (`Id_Invoice`)          REFERENCES `t_invoice`(`Id_Invoice`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- INVOICE FEE
-- ---------------------------------------------------------------------------------------------
-- Almacena el detalle de las Facturas de una Reserva
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_invoice_fee`
(
    `Id_InvoiceFee`                     INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `InvoiceFee_Full`                   INT(1) NOT NULL,
    `InvoiceFee_Amount`                 DECIMAL(12,2) NOT NULL,
    `InvoiceFee_AmountPay`              DECIMAL(12,2) NOT NULL,
    `InvoiceFee_AmountPending`          DECIMAL(12,2) NOT NULL,
    `InvoiceFee_Type`                   INT(1) NOT NULL, -- 1 egreso 2 ingreso

    `Id_Invoice`                        INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_InvoiceFee`),
    FOREIGN KEY (`Id_Invoice`)          REFERENCES `t_invoice`(`Id_Invoice`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- CASH MOVEMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena el movimiento de los pagos en caja
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     25/09/2020
-- Update Date:     25/09/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_cash_movement`
(
    `Id_CashMovement`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `CashMovement_Date`                 DATETIME NOT NULL,
    `CashMovement_DatePayment`          DATE NOT NULL,
    `CashMovement_Amount`               DECIMAL(12,2) NOT NULL,
    `CashMovement_Currency`             VARCHAR(10) NOT NULL,   -- CODIGO DE MONEDA ISO 4217 :: PEN / USD
    `CashMovement_Name`                 VARCHAR(100) NOT NULL,  -- NOMBRES DEL TARJETAHABIENTE
    `CashMovement_LastName`             VARCHAR(100) NOT NULL,  -- APELLIDOS DEL TARJETAHABIENTE
    `CashMovement_Email`                VARCHAR(250) NOT NULL,  -- E-MAIL DEL TARJETAHABIENTE
    `CashMovement_Country`              VARCHAR(100) NOT NULL,  -- PAIS DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovement_City`                 VARCHAR(100) NOT NULL,  -- CIUDAD DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovement_Address`              VARCHAR(250) NOT NULL,  -- DIRECCION DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovement_Phone`                VARCHAR(20) NOT NULL,   -- TELEFONO DEL TARJETAHABIENTE
    `CashMovement_ReceiptNumber`        VARCHAR(100) NOT NULL,  -- CODIGO DE AUTORIZACION DEL CARGO
    `CashMovement_CardLast`             VARCHAR(10) NOT NULL,   -- ULTIMOS 4 DIGITOS DE LA TARJETA 0000
    `CashMovement_CardNumber`           VARCHAR(30) NOT NULL,   -- NUMERO ENMASCARADO DE TARJETA 0000-00xx-xxxx-0000
    `CashMovement_CardBrand`            VARCHAR(50) NOT NULL,   -- DESCRIPCION DE LA MARCA DE LA TARJETA (VISA - MC - AMEX - DN)
    `CashMovement_CardType`             VARCHAR(50) NOT NULL,   -- TIPO DE TARJETA USADA (CREDIT - DEBIT)
    `CashMovement_CardIssuer`           VARCHAR(50) NOT NULL,   -- NOMBRE DEL BANCO EMISOR DE LA TARJETA
    `CashMovement_Remark`               TEXT NOT NULL,          -- OBSERVACION DEL MOVIMIENTO DE CAJA
    `CashMovement_Type`                 INT(1) NOT NULL,        -- 1 :: EGRESO - 2 :: INGRESO

    `Id_TypePayment`                    INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_CashMovement`),
    FOREIGN KEY (`Id_TypePayment`)      REFERENCES `t_type_payment`(`Id_TypePayment`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- CASH MOVEMENT DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena los detalles de movimiento de los pagos en caja con los invoices
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS LOEL CURI LUQUE
-- Create Date:     09/11/2020
-- Update Date:     09/11/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_cash_movement_detail`
(
    `Id_CashMovementDetail`             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `CashMovementDetail_Amount`         DECIMAL(12,2) NOT NULL,
        
    `Id_Invoice`                        INT(11) UNSIGNED NOT NULL,
    `Id_CashMovement`                   INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_CashMovementDetail`),
    FOREIGN KEY (`Id_CashMovement`)     REFERENCES `t_cash_movement`(`Id_CashMovement`),
    FOREIGN KEY (`Id_Invoice`)          REFERENCES `t_invoice`(`Id_Invoice`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- INVOICE FEE DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena el detalle de las Facturas de una Reserva
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_invoice_fee_detail`
(
    `Id_InvoiceFeeDetail`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `InvoiceFeeDetail_Amount`               DECIMAL(12,2) NOT NULL,
    `InvoiceFeeDetail_Type`                 INT(1) NOT NULL, -- 1: Egreso - 2: Ingreso

    `Id_InvoiceFee`                         INT(11) UNSIGNED NOT NULL,
    `Id_CashMovement`                       INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_InvoiceFeeDetail`),
    FOREIGN KEY (`Id_InvoiceFee`)           REFERENCES `t_invoice_fee`(`Id_InvoiceFee`),
    FOREIGN KEY (`Id_CashMovement`)         REFERENCES `t_cash_movement`(`Id_CashMovement`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       TABLAS DE GRUPOS                                                    //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- GROUP
-- ---------------------------------------------------------------------------------------------
-- Almacena los grupos 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_group`
(
    `Id_Group`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Group_DateStart`           DATE NOT NULL,
    `Group_DateEnd`             DATE NOT NULL,
    `Group_Hour`                TIME NOT NULL,
    `Group_Name`                VARCHAR(250) NOT NULL,
    `Group_Origin`              VARCHAR(250) NOT NULL,
    `Group_Destination`         VARCHAR(250) NOT NULL,
    `Group_Type`                INT(1) NOT NULL,            -- 1 :: COMPARTIDO - 2 :: PRIVADO
    `Group_TourDuration`        INT(3) NOT NULL,
    `Group_Remark`              TEXT NOT NULL,
    `Group_MasterType`          INT(1) NOT NULL,            -- INDICA TIPO DE MAESTRO   0 :: INDEPENDIENTE  - 1 :: ESCLAVO  - 2 :: MAESTRO
    `Group_ServiceType`         INT(1) NOT NULL,            -- INDICA TIPO DE SERVICIO  1 :: REGULAR        - 2 :: ALCANCE  - 3 :: TRANSFER IN - 4 :: TRANSFER OUT
    `Group_Status`              INT(1) NOT NULL,            -- 0 :: ANULADO - 1 :: PENDIENTE - 2 :: CONFIRMADO - 3 :: FINALIZADO
        
    `Id_Master`                 INT(11) UNSIGNED NOT NULL,  -- Id_Group :: Id de registro maestro (de la misma tabla)
    `Id_Service`                INT(11) UNSIGNED NOT NULL,  -- ID DE TOUR O ID DE VUELO     

    PRIMARY KEY (`Id_Group`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ---------------------------------------------------------------------------------------------
-- GROUP ACTIVITY
-- ---------------------------------------------------------------------------------------------
-- Almacena las actividades de los grupos
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_group_activity`
(
    `Id_GroupActivity`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `GroupActivity_Date`            DATE NOT NULL,
    `GroupActivity_Title`           VARCHAR(250) NOT NULL,
        
    `Id_Group`                      INT(11) UNSIGNED NOT NULL, 

    PRIMARY KEY (`Id_GroupActivity`),
    FOREIGN KEY (`Id_Group`)        REFERENCES `t_group`(`Id_Group`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- GROUP ACTIVITY DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena los detalles de  las actividades de los grupos
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       *
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------
CREATE TABLE `t_group_activity_detail`
(
    `Id_GroupActivityDetail`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `GroupActivityDetail_Code`              VARCHAR(100) NOT NULL,
    `GroupActivityDetail_Date`              DATETIME NOT NULL,
    `GroupActivityDetail_Description`       TEXT NOT NULL,
    `Id_GroupActivity`                      INT(11) UNSIGNED NOT NULL, 

    PRIMARY KEY (`Id_GroupActivityDetail`),
    FOREIGN KEY (`Id_GroupActivity`)        REFERENCES `t_group_activity`(`Id_GroupActivity`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- GROUP PASSENGER
-- ---------------------------------------------------------------------------------------------
-- Almacena los pasajeros de un grupos 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_group_passenger`
(
    `Id_GroupPassenger`             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `GroupPassenger_Type`           INT(1) NOT NULL,    -- 1 :: BOOKING TOUR PASSENGER - 2 :: PASSENGER
    `Id_Group`                      INT(11) UNSIGNED NOT NULL,
    `Id`                            INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_GroupPassenger`),
    FOREIGN KEY (`Id_Group`)                REFERENCES `t_group`(`Id_Group`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- GROUP PROVIDER
-- ---------------------------------------------------------------------------------------------
-- Almacena los pasajeros de un grupos 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_group_provider`
(
    `Id_GroupProvider`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `GroupProvider_Type`            INT(1) NOT NULL, -- 2 :: PROVEEDOR A CARGO DEL GRUPO -- 1 :: PROVEDDOR CONVENCIONAL
    `GroupProvider_Remark`          TEXT NOT NULL,
    `Id_Group`                      INT(11) UNSIGNED NOT NULL,
    `Id_Provider`                   INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_GroupProvider`),
    FOREIGN KEY (`Id_Group`)        REFERENCES `t_group`(`Id_Group`),
    FOREIGN KEY (`Id_Provider`)     REFERENCES `t_provider`(`Id_Provider`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- GROUP EQUIPMENT
-- ---------------------------------------------------------------------------------------------
-- Almacena los pasajeros de un grupos 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_group_equipment`
(
    `Id_GroupEquipment`         INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `GroupEquipment_Quantity`   DECIMAL(12,2) NOT NULL,
    `GroupEquipment_Type`       INT(1) NOT NULL,
    `Id_Group`                  INT(11) UNSIGNED NOT NULL,
    `Id_Item`                   INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_GroupEquipment`),
    FOREIGN KEY (`Id_Group`)        REFERENCES `t_group`(`Id_Group`),
    FOREIGN KEY (`Id_Item`)         REFERENCES `t_item`(`Id_Item`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- PASSENGER REVIEW
-- ---------------------------------------------------------------------------------------------
-- Almacena las respuesta de un pasajero sobre un tour
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       OK
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_passenger_review`
(
    `Id_PassengerReview`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `PassengerReview_Date`              DATETIME NOT NULL,
    `PassengerReview_Score`             INT(2) NOT NULL,
    `PassengerReview_Comment`           TEXT,
    `PassengerReview_Provider`          TEXT,
    `Id_TypeProvider`                   INT(11),
        
    `Id_BookingTourPassenger`           INT(11) UNSIGNED NOT NULL,
    `Id_Tour`                           INT(11) UNSIGNED NOT NULL,
    `Id_Question`                       INT(11) UNSIGNED NOT NULL,
    `Id_Group`                          INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_PassengerReview`),
    FOREIGN KEY (`Id_BookingTourPassenger`) REFERENCES `t_booking_tour_passenger`(`Id_BookingTourPassenger`),
    FOREIGN KEY (`Id_Tour`)                 REFERENCES `t_tour`(`Id_Tour`),
    FOREIGN KEY (`Id_Group`)                REFERENCES `t_group`(`Id_Group`),
    FOREIGN KEY (`Id_Question`)             REFERENCES `t_question`(`Id_Question`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;






-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       NOTIFICACIONES                                                      //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- NOTIFICATION
-- ---------------------------------------------------------------------------------------------
-- Almacena los notificaciones 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_notification`
(
    `Id_Notification`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Notification_DateCreate`           DATETIME NOT NULL,
    `Notification_DateAccept`           DATETIME NOT NULL,
    `Notification_Description`          TEXT NOT NULL,
    `Notification_Type`                 INT(1) NOT NULL,    -- 1 :: BOOKING TOUR PASSENGER - 2 :: GROUP PROVIDER - 3 :: GROUP
    `Notification_Status`               INT(1) NOT NULL,
    `Id`                                INT(11) NOT NULL,
    `Id_Create`                         INT(11) NOT NULL,
    `Id_Accept`                         INT(11) NOT NULL,   
        

    PRIMARY KEY (`Id_Notification`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- //                                                                                                                           //
-- //                                                       LINKS DE PAGO                                                       //
-- //                                                                                                                           //
-- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-- ---------------------------------------------------------------------------------------------
-- SCHEDULE
-- ---------------------------------------------------------------------------------------------
-- Almacena los pagos programados 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_schedule`
(
    `Id_Schedule`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Schedule_DateCreate`           DATETIME NOT NULL,
    `Schedule_DateExpire`           DATETIME NOT NULL,
    `Schedule_DatePayment`          DATETIME NOT NULL,
    `Schedule_Description`          TEXT NOT NULL,
    `Schedule_Total`                DECIMAL(12,2) NOT NULL,
    `Schedule_Status`               INT(1) NOT NULL,
    `Schedule_Token`                VARCHAR(250) NOT NULL,

    `Id_Booking`                    INT(11) UNSIGNED NOT NULL,
    `Id_Admin`                      INT(11) UNSIGNED NOT NULL,
        

    PRIMARY KEY (`Id_Schedule`),
    FOREIGN KEY (`Id_Booking`)      REFERENCES `t_booking`(`Id_Booking`),
    FOREIGN KEY (`Id_Admin`)        REFERENCES `t_admin`(`Id_Admin`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- ---------------------------------------------------------------------------------------------
-- SCHEDULE DETAIL
-- ---------------------------------------------------------------------------------------------
-- Almacena los invoice de los pagos programados 
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_schedule_detail`
(
    `Id_ScheduleDetail`             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ScheduleDetail_Total`          DECIMAL(12,2) NOT NULL,
    `Id_Schedule`                   INT(11) UNSIGNED NOT NULL,
    `Id_Invoice`                    INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_ScheduleDetail`),
    FOREIGN KEY (`Id_Schedule`)     REFERENCES `t_schedule`(`Id_Schedule`),
    FOREIGN KEY (`Id_Invoice`)      REFERENCES `t_invoice`(`Id_Invoice`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;











-- ---------------------------------------------------------------------------------------------
-- HISTORY TABLE
-- ---------------------------------------------------------------------------------------------
-- Almacena las tablas donde se hacen los cambios
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_history_table`
(
    `Id_HistoryTable`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `HistoryTable_Name`             VARCHAR(100) NOT NULL,
    `HistoryTable_Title`            VARCHAR(100) NOT NULL,
    `HistoryTable_Procedure`        VARCHAR(250) NOT NULL,
        
    PRIMARY KEY (`Id_HistoryTable`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- HISTORY
-- ---------------------------------------------------------------------------------------------
-- Almacena los cambiso echos a las tablas
-- ---------------------------------------------------------------------------------------------
-- Author:          HECTOR MIRANDA CONZA
-- Create Date:     12/05/2020
-- Update Date:     12/05/2020
-- ---------------------------------------------------------------------------------------------
-- Api Owner:       * 
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_history`
(
    `Id_History`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `History_Date`              DATETIME NOT NULL,
    `History_ValueOld`          TEXT NOT NULL,
    `History_ValueNew`          TEXT NOT NULL,
    `History_Operation`         INT(1) NOT NULL, -- 1 :: INSERT / 2 :: UPDATE / 3 :: DELETE
    `Id_Record`                 INT(11) UNSIGNED NOT NULL,
    `Id_HistoryTable`           INT(11) UNSIGNED NOT NULL,
    `Id_Admin`                  INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_History`),
    FOREIGN KEY (`Id_HistoryTable`)     REFERENCES `t_history_table`(`Id_HistoryTable`),
    FOREIGN KEY (`Id_Admin`)            REFERENCES `t_admin`(`Id_Admin`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- ---------------------------------------------------------------------------------------------
-- BOOKING TEMP
-- ---------------------------------------------------------------------------------------------
-- Almacena booking temporales para realizar el pago
-- ---------------------------------------------------------------------------------------------
-- Author:          JESUS LOEL CURI LUQUE
-- Create Date:     27/04/2021
-- Update Date:     27/04/2021
-- ---------------------------------------------------------------------------------------------
-- Api Client:      *
-- ---------------------------------------------------------------------------------------------

CREATE TABLE `t_booking_temp`
(
    `Id_BookingTemp`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingTemp_Id_User`                   INT(11) UNSIGNED NOT NULL,
    `BookingTemp_User_Name`                 VARCHAR(100) NOT NULL,
    `BookingTemp_User_LastName`             VARCHAR(100) NOT NULL,
    `BookingTemp_User_Email`                VARCHAR(250) NOT NULL,
    `BookingTemp_User_Phone`                VARCHAR(20) NOT NULL,
    `BookingTemp_Id_UserCountry`            INT(11) UNSIGNED NOT NULL,
    `BookingTemp_AccessToken`               TEXT NOT NULL,
    `BookingTemp_Amount`                    DECIMAL(12,2) NOT NULL,
    `BookingTemp_Booking`                   TEXT NOT NULL,
    `BookingTemp_CashMovement`              TEXT NOT NULL,
    `BookingTemp_Token`                     TEXT NOT NULL,
    `BookingTemp_Date`                      DATETIME NOT NULL,
    `BookingTemp_ResponseMessage`           VARCHAR(250) NOT NULL,
    `BookingTemp_Status`                    INT(1) NOT NULL,
        
    PRIMARY KEY (`Id_BookingTemp`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `t_cash_movement_temp`
(
    `Id_CashMovementTemp`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `CashMovementTemp_Date`                 DATETIME NOT NULL,
    `CashMovementTemp_DatePayment`          DATETIME NOT NULL,
    `CashMovementTemp_Amount`               DECIMAL(12,2) NOT NULL,
    `CashMovementTemp_Currency`             VARCHAR(10) NOT NULL,   -- CODIGO DE MONEDA ISO 4217 :: PEN / USD
    `CashMovementTemp_Name`                 VARCHAR(100) NOT NULL,  -- NOMBRES DEL TARJETAHABIENTE
    `CashMovementTemp_LastName`             VARCHAR(100) NOT NULL,  -- APELLIDOS DEL TARJETAHABIENTE
    `CashMovementTemp_Email`                VARCHAR(250) NOT NULL,  -- E-MAIL DEL TARJETAHABIENTE
    `CashMovementTemp_Country`              VARCHAR(100) NOT NULL,  -- PAIS DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovementTemp_City`                 VARCHAR(100) NOT NULL,  -- CIUDAD DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovementTemp_Address`              VARCHAR(250) NOT NULL,  -- DIRECCION DE RESIDENCIA DEL TARJETAHABIENTE
    `CashMovementTemp_Phone`                VARCHAR(20) NOT NULL,   -- TELEFONO DEL TARJETAHABIENTE
    `CashMovementTemp_ReceiptNumber`        VARCHAR(100) NOT NULL,  -- CODIGO DE AUTORIZACION DEL CARGO
    `CashMovementTemp_CardLast`             VARCHAR(10) NOT NULL,   -- ULTIMOS 4 DIGITOS DE LA TARJETA 0000
    `CashMovementTemp_CardNumber`           VARCHAR(30) NOT NULL,   -- NUMERO ENMASCARADO DE TARJETA 0000-00xx-xxxx-0000
    `CashMovementTemp_CardBrand`            VARCHAR(50) NOT NULL,   -- DESCRIPCION DE LA MARCA DE LA TARJETA (VISA - MC - AMEX - DN)
    `CashMovementTemp_CardType`             VARCHAR(50) NOT NULL,   -- TIPO DE TARJETA USADA (CREDIT - DEBIT)
    `CashMovementTemp_CardIssuer`           VARCHAR(50) NOT NULL,   -- NOMBRE DEL BANCO EMISOR DE LA TARJETA
    `CashMovementTemp_Remark`               TEXT NOT NULL,          -- OBSERVACION DEL MOVIMIENTO DE CAJA
    `CashMovementTemp_Type`                 INT(1) NOT NULL,        -- 1 :: EGRESO - 2 :: INGRESO
    `CashMovementTemp_Invoices`             TEXT NOT NULL,
    `CashMovementTemp_AccessToken`          TEXT NOT NULL,
    `CashMovementTemp_ResponseMessage`      VARCHAR(250) NOT NULL,
    `CashMovementTemp_Status`               INT(1) NOT NULL,        -- 1 :: CANCELADO - 2 :: HABILITADO

    `Id_TypePayment`                        INT(11) UNSIGNED NOT NULL,
    `Id_Schedule`                           INT(11) UNSIGNED NOT NULL,


    PRIMARY KEY (`Id_CashMovementTemp`),
    INDEX(`Id_Schedule`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `t_niubiz_purchase`
(
    `Id_NiubizPurchase`                 INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `NiubizPurchase_type`               ENUM('CASH_MOVEMENT_TEMP', 'BOOKING_TEMP') NOT NULL,
    `Id`                                INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_NiubizPurchase`),
    INDEX(`Id`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `t_question`
ADD `Question_Type` INT(1) NOT NULL DEFAULT 1 AFTER `Question_Abrv`; -- 1: SCORE - 2: ANSWER
ALTER TABLE `t_question` ALTER `Question_Type` DROP DEFAULT;

ALTER TABLE `t_passenger_review`
ADD `PassengerReview_Answered` INT(1) NOT NULL DEFAULT 0 AFTER `PassengerReview_Date`; -- 0: NOT - 1: YES
ALTER TABLE `t_passenger_review` ALTER `PassengerReview_Answered` DROP DEFAULT;

ALTER TABLE `t_user_country`
ADD `UserCountry_Frequent` INT(1) NOT NULL DEFAULT 1 AFTER `UserCountry_Img`; -- 1: NO - 2: SÍ
ALTER TABLE `t_user_country` ALTER `UserCountry_Frequent` DROP DEFAULT;

ALTER TABLE `t_package`
ADD `Package_DOBRequired` INT(1) NOT NULL DEFAULT 1 AFTER `Package_PaxRequired`; -- 1 :: NOT - 2 :: YES
ALTER TABLE `t_package` ALTER `Package_DOBRequired` DROP DEFAULT;


CREATE TABLE `t_booking_relationship`
(
    `Id_BookingRelationship`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingRelationship_Name`                  VARCHAR(250) NOT NULL,
    `BookingRelationship_Abrv`                  VARCHAR(4) NOT NULL,
    `BookingRelationship_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_BookingRelationship`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `t_booking_referred_from`
(
    `Id_BookingReferredFrom`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `BookingReferredFrom_Name`                  VARCHAR(250) NOT NULL,
    `BookingReferredFrom_Abrv`                  VARCHAR(4) NOT NULL,
    `BookingReferredFrom_Status`                INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_BookingReferredFrom`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t_booking_relationship` (`Id_BookingRelationship`, `BookingRelationship_Name`, `BookingRelationship_Abrv`, `BookingRelationship_Status`) VALUE 
(1, 'Ninguno', 'NONE', 2),
(2, 'Pasajero Solo', 'LONE', 2),
(3, 'Grupo de amigos', 'FRND', 2),
(4, 'Pareja', 'PRJ', 2),
(5, 'Negocios', 'BUS', 2);
INSERT INTO `t_booking_referred_from` (`Id_BookingReferredFrom`, `BookingReferredFrom_Name`, `BookingReferredFrom_Abrv`, `BookingReferredFrom_Status`) VALUE 
(1, 'Ninguno', 'NONE', 2);


ALTER TABLE `t_booking_relationship`
ADD `BookingRelationship_Lock` INT(1) NOT NULL DEFAULT 1 AFTER `BookingRelationship_Abrv`; -- 1 :: NOT - 2 :: YES
ALTER TABLE `t_booking_relationship` ALTER `BookingRelationship_Lock` DROP DEFAULT;

ALTER TABLE `t_booking_referred_from`
ADD `BookingReferredFrom_Lock` INT(1) NOT NULL DEFAULT 1 AFTER `BookingReferredFrom_Abrv`; -- 1 :: NOT - 2 :: YES
ALTER TABLE `t_booking_referred_from` ALTER `BookingReferredFrom_Lock` DROP DEFAULT;


ALTER TABLE `t_booking`
ADD `Id_BookingRelationship` INT(11) UNSIGNED NOT NULL DEFAULT 1 AFTER `Id_User`;
ALTER TABLE `t_booking` ALTER `Id_BookingRelationship` DROP DEFAULT;

ALTER TABLE `t_booking`
ADD `Id_BookingReferredFrom` INT(11) UNSIGNED NOT NULL DEFAULT 1 AFTER `Id_BookingRelationship`;
ALTER TABLE `t_booking` ALTER `Id_BookingReferredFrom` DROP DEFAULT;

ALTER TABLE `t_booking`
ADD CONSTRAINT `FK_Id_BookingRelationship`
FOREIGN KEY (`Id_BookingRelationship`) REFERENCES `t_booking_relationship`(`Id_BookingRelationship`);

ALTER TABLE `t_booking`
ADD CONSTRAINT `FK_Id_BookingReferredFrom`
FOREIGN KEY (`Id_BookingReferredFrom`) REFERENCES `t_booking_referred_from`(`Id_BookingReferredFrom`);



CREATE TABLE `t_htl_camp`
(
    `Id_HtlCamp`                   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `HtlCamp_Name`                 VARCHAR(250) NOT NULL,
    `HtlCamp_Abrv`                 VARCHAR(10) NOT NULL,
    `HtlCamp_Availability`         INT(11) NOT NULL,
    `HtlCamp_Status`               INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    PRIMARY KEY (`Id_HtlCamp`),
    INDEX(`HtlCamp_Status`),
    INDEX(`HtlCamp_Name`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `t_htl_room`
(
    `Id_HtlRoom`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `HtlRoom_Number`              VARCHAR(250) NOT NULL,
    `HtlRoom_Status`              INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público
    `Id_HtlCamp`                  INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_HtlRoom`),
    INDEX(`HtlRoom_Status`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `t_htl_usage`
(
    `Id_HtlUsage`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `HtlUsage_DateUsage`        DATE NOT NULL,
    `HtlUsage_Status`           INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público
    `HtlUsage_Lock`             INT(1) NOT NULL, -- 1: NOT - 2: YES
    `HtlUsage_Name`             VARCHAR(250) NOT NULL,
    `HtlUsage_LastName`         VARCHAR(250) NOT NULL,
    `HtlUsage_Remark`           VARCHAR(250) NOT NULL,

    `Id_HtlRoom`                INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_HtlUsage`),
    INDEX(`HtlUsage_DateUsage`),
    INDEX(`HtlUsage_Status`),
    FOREIGN KEY (`Id_HtlRoom`) REFERENCES `t_htl_room`(`Id_HtlRoom`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `t_htl_usage_passenger`
(
    `Id_HtlUsagePassenger`      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `HtlUsagePassenger_Status`  INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público
    `Id_HtlUsage`               INT(11) UNSIGNED NOT NULL,
    `Id_BookingTourPassenger`   INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_HtlUsagePassenger`),
    FOREIGN KEY (`Id_HtlUsage`) REFERENCES `t_htl_usage`(`Id_HtlUsage`),
    FOREIGN KEY (`Id_BookingTourPassenger`) REFERENCES `t_booking_tour_passenger`(`Id_BookingTourPassenger`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `t_tour_htl_camp`
(
    `Id_TourHtlCamp`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TourHtlCamp_Day`             INT(1) NOT NULL,
    `TourHtlCamp_Status`          INT(1) NOT NULL, -- 0: Eliminado, 1: No público, 2: Público

    `Id_Tour`                     INT(11) UNSIGNED NOT NULL,
    `Id_HtlCamp`                  INT(11) UNSIGNED NOT NULL,

    PRIMARY KEY (`Id_TourHtlCamp`),
    INDEX(`TourHtlCamp_Status`)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE `t_booking`
ADD INDEX `INDEX_Booking_Status` (`Booking_Status`);
ALTER TABLE `t_booking`
ADD INDEX `INDEX_Booking_DateStart` (`Booking_DateStart`);
ALTER TABLE `t_booking`
ADD INDEX `INDEX_Booking_Type` (`Booking_Type`);
ALTER TABLE `t_booking`
ADD INDEX `INDEX_Booking_Code` (`Booking_Code`);


ALTER TABLE `t_booking_tour`
ADD INDEX `INDEX_BookingTour_DateStart` (`BookingTour_DateStart`);

ALTER TABLE `t_user`
ADD INDEX `INDEX_User_Type` (`User_Type`);