CREATE TABLE 
    continent 
    ( 
        id         bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at TIMESTAMP NULL, 
        deleted_at TIMESTAMP NULL, 
        active     TINYINT(1) DEFAULT 1 NOT NULL, 
        code       VARCHAR(2) NOT NULL, 
        name       VARCHAR(16) NOT NULL, 
        PRIMARY KEY (id) 
    );

CREATE TABLE 
    country 
    ( 
        id           bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at   TIMESTAMP NULL, 
        deleted_at   TIMESTAMP NULL, 
        active       TINYINT(1) DEFAULT 1 NOT NULL, 
        code         VARCHAR(2) NOT NULL, 
        iso3         VARCHAR(3) NOT NULL, 
        number       VARCHAR(3) NOT NULL, 
        continent_id bigint, 
        name         VARCHAR(64) NOT NULL, 
        full_name    VARCHAR(128) NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT country_continent_id_continent_id_fk FOREIGN KEY (continent_id) REFERENCES 
        `continent` (`id`), 
        INDEX country_continent_id_continent_id_fk (continent_id) 
    );

CREATE TABLE 
    subdivision 
    ( 
        id         bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at TIMESTAMP NULL, 
        deleted_at TIMESTAMP NULL, 
        active     TINYINT(1) DEFAULT 1 NOT NULL, 
        code       VARCHAR(12) NOT NULL, 
        country_id bigint NOT NULL, 
        name       VARCHAR(256) NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT subdivision_country_id_country_id_fk FOREIGN KEY (country_id) REFERENCES 
        `country` (`id`), 
        INDEX subdivision_country_id_country_id_fk (country_id) 
    );

CREATE TABLE 
    administrative_division 
    ( 
        id             bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL,
        updated_at     TIMESTAMP NULL, 
        deleted_at     TIMESTAMP NULL, 
        active         TINYINT(1) DEFAULT 1 NOT NULL, 
        code           VARCHAR(12) NOT NULL, 
        subdivision_id bigint  NOT NULL,
        name           VARCHAR(256) NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT administrative_division_subdivision_id_subdivision_id_fk FOREIGN KEY 
        (subdivision_id) REFERENCES `subdivision` (`id`), 
        INDEX administrative_division_subdivision_id_subdivision_id_fk (subdivision_id) 
    );

CREATE TABLE 
    type_master 
    ( 
        id            bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL,
        updated_at    TIMESTAMP NULL, 
        deleted_at    TIMESTAMP NULL, 
        active        TINYINT(1) DEFAULT 1 NOT NULL, 
        discriminator VARCHAR(60) NOT NULL, 
        short_name    VARCHAR(256) NOT NULL, 
        name          VARCHAR(256) NOT NULL, 
        `order`         INT DEFAULT 1 NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT chk_order_positive CHECK ((`order` > 0)) 
    );

CREATE TABLE 
    user 
    ( 
        id           bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()), 
        updated_at   TIMESTAMP NULL, 
        deleted_at   TIMESTAMP NULL, 
        active       TINYINT(1) DEFAULT 1 NOT NULL, 
        name         VARCHAR(60) NOT NULL, 
        email        VARCHAR(256) NOT NULL, 
        last_name    VARCHAR(60), 
        last_name_2  VARCHAR(60), 
        cell_phone   VARCHAR(20), 
        external_id  VARCHAR(256), 
        agreement_at TIMESTAMP NULL, 
        PRIMARY KEY (id)
    );

CREATE TABLE 
    display_tutorial 
    ( 
        id                       bigint NOT NULL AUTO_INCREMENT, 
        created_at               TIMESTAMP DEFAULT (NOW()) NULL, 
        updated_at               TIMESTAMP NULL, 
        deleted_at               TIMESTAMP NULL, 
        user_id                  bigint, 
        display_template_type_id bigint, 
        active                   TINYINT(1) DEFAULT 1 NOT NULL, 
        title                    VARCHAR(255), 
        caption                  VARCHAR(255), 
        image_url                VARCHAR(255), 
        `order`                    INT DEFAULT 1 NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT displaytutorial_fk1 FOREIGN KEY (user_id) REFERENCES `user` (`id`) , 
        CONSTRAINT displaytutorial_fk2 FOREIGN KEY (display_template_type_id) REFERENCES 
        `type_master` (`id`)
    ) ;

CREATE TABLE 
    profile 
    ( 
        id         bigint NOT NULL AUTO_INCREMENT, 
        created_at   TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at TIMESTAMP NULL, 
        deleted_at TIMESTAMP NULL, 
        active     TINYINT(1) DEFAULT 1 NOT NULL, 
        name       VARCHAR(60) NOT NULL, 
        short_name VARCHAR(256) NOT NULL, 
        PRIMARY KEY (id)
    );


CREATE TABLE 
    company 
    ( 
        id            bigint NOT NULL AUTO_INCREMENT, 
        created_at    TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at    TIMESTAMP NULL, 
        deleted_at    TIMESTAMP NULL, 
        active        TINYINT(1) DEFAULT 1 NOT NULL, 
        dni           VARCHAR(60) NOT NULL, 
        social_reason VARCHAR(256) NOT NULL, 
        PRIMARY KEY (id) 
    );

CREATE TABLE 
    company_subdivision 
    ( 
        company_id     bigint NOT NULL, 
        subdivision_id bigint NOT NULL, 
        PRIMARY KEY (company_id, subdivision_id), 
        CONSTRAINT companysubdivision_fk1 FOREIGN KEY (company_id) REFERENCES `company` (`id`) , 
        CONSTRAINT companysubdivision_fk2 FOREIGN KEY (subdivision_id) REFERENCES `subdivision` (`id`)
    );

CREATE TABLE 
    keyword 
    ( 
        id         bigint NOT NULL AUTO_INCREMENT, 
        created_at TIMESTAMP DEFAULT (NOW()) NOT NULL, 
        updated_at TIMESTAMP NULL, 
        deleted_at TIMESTAMP NULL, 
        active     TINYINT(1) DEFAULT 1 NOT NULL, 
        value      TEXT NOT NULL, 
        origin     VARCHAR(60) NOT NULL, 
        company_id bigint NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT keyword_company_id_company_id_fk FOREIGN KEY (company_id) REFERENCES `company` 
        (`id`), 
        INDEX keyword_company_id_company_id_fk (company_id) 
    );

CREATE TABLE 
    tender 
    ( 
        id          bigint NOT NULL AUTO_INCREMENT, 
        created_at  TIMESTAMP DEFAULT (NOW()), 
        updated_at  TIMESTAMP NULL, 
        deleted_at  TIMESTAMP NULL, 
        active      TINYINT(1) DEFAULT 1 NOT NULL, 
        code        VARCHAR(60) NOT NULL, 
        name        VARCHAR(256) NOT NULL, 
        description text NOT NULL, 
        city_id     bigint, 
        details json NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT tender_code_unique UNIQUE (code) 
    ) ;

CREATE TABLE 
    user_company 
    ( 
        company_id bigint NOT NULL, 
        user_id    bigint NOT NULL, 
        PRIMARY KEY (company_id, user_id), 
        CONSTRAINT usercompany_fk1 FOREIGN KEY (user_id) REFERENCES `user` (`id`) , 
        CONSTRAINT usercompany_fk2 FOREIGN KEY (company_id) REFERENCES `company` (`id`), 
        INDEX usercompany_fk1 (user_id) 
    );

CREATE TABLE 
    user_company_tender 
    ( 
        user_id      bigint NOT NULL, 
        company_id   bigint NOT NULL, 
        tender_id    bigint NOT NULL, 
        is_buy_agile TINYINT(1), 
        created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, 
        updated_at   TIMESTAMP NULL, 
        deleted_at   TIMESTAMP NULL, 
        active       TINYINT DEFAULT 1, 
        PRIMARY KEY (user_id, company_id, tender_id), 
        CONSTRAINT usercompanytender_fk1 FOREIGN KEY (user_id) REFERENCES `user` (`id`) , 
        CONSTRAINT usercompanytender_fk2 FOREIGN KEY (company_id) REFERENCES `company` (`id`) , 
        CONSTRAINT usercompanytender_fk3 FOREIGN KEY (tender_id) REFERENCES `tender` (`id`), 
        INDEX usercompanytender_fk2 (company_id), 
        INDEX usercompanytender_fk3 (tender_id) 
    );

CREATE TABLE 
    user_history_tender 
    ( 
        id          bigint NOT NULL AUTO_INCREMENT, 
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, 
        updated_at  TIMESTAMP NULL, 
        deleted_at  TIMESTAMP NULL, 
        active      TINYINT(1) DEFAULT 1 NOT NULL, 
        tender_id   bigint, 
        user_id     bigint NOT NULL, 
        code_tender VARCHAR(60), 
        PRIMARY KEY (id), 
        CONSTRAINT userhistory_new_fk1 FOREIGN KEY (tender_id) REFERENCES `tender` (`id`) , 
        CONSTRAINT userhistory_new_fk2 FOREIGN KEY (user_id) REFERENCES `user` (`id`)
    );

CREATE TABLE 
    user_profile 
    ( 
        user_id    bigint NOT NULL, 
        profile_id bigint NOT NULL, 
        PRIMARY KEY (user_id, profile_id), 
        CONSTRAINT user_profile_profile_id_profile_id_fk FOREIGN KEY (profile_id) REFERENCES 
        `profile` (`id`) , 
        CONSTRAINT user_profile_user_id_user_id_fk FOREIGN KEY (user_id) REFERENCES `user` (`id`)
    );

CREATE TABLE 
    user_device 
    ( 
        id         bigint NOT NULL AUTO_INCREMENT, 
        user_id    bigint NOT NULL, 
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL, 
        updated_at TIMESTAMP NULL, 
        deleted_at TIMESTAMP NULL, 
        active     TINYINT DEFAULT 1 NOT NULL, 
        token      text NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT userdevice_fk1 FOREIGN KEY (user_id) REFERENCES `user` (`id`), 
        INDEX userdevice_fk1 (user_id) 
    );

CREATE TABLE 
    user_request 
    ( 
        id                    bigint NOT NULL AUTO_INCREMENT, 
        created_at            TIMESTAMP DEFAULT (NOW()), 
        updated_at            TIMESTAMP NULL, 
        deleted_at            TIMESTAMP NULL, 
        active                TINYINT DEFAULT 1 NOT NULL, 
        company_id            bigint NOT NULL, 
        tender_id             bigint NOT NULL, 
        user_id               bigint NOT NULL, 
        state_request_type_id bigint NOT NULL, 
        PRIMARY KEY (id), 
        CONSTRAINT userrequest_fk1 FOREIGN KEY (company_id) REFERENCES `company` (`id`) , 
        CONSTRAINT userrequest_fk2 FOREIGN KEY (user_id) REFERENCES `user` (`id`) , 
        CONSTRAINT userrequest_fk3 FOREIGN KEY (tender_id) REFERENCES `tender` (`id`) , 
        CONSTRAINT userrequest_fk4 FOREIGN KEY (state_request_type_id) REFERENCES `type_master` (`id`)
    );

CREATE TABLE 
    user_campaign 
    ( 
        id              bigint NOT NULL AUTO_INCREMENT, 
        created_at      TIMESTAMP DEFAULT (NOW()) NULL, 
        updated_at      TIMESTAMP NULL, 
        deleted_at      TIMESTAMP NULL, 
        active          TINYINT DEFAULT 1 NOT NULL, 
        description     VARCHAR(60) NOT NULL, 
        title           VARCHAR(60) NOT NULL, 
        default_message VARCHAR(256) NOT NULL, 
        day_of_week     VARCHAR(60), 
        def_hour        SMALLINT NOT NULL, 
        def_minute      SMALLINT NOT NULL, 
        PRIMARY KEY (id) 
    );
