import { Knex } from "knex";
import {BUSINESSAPP, CATALOG, ROLE, VISUALIZATION, SITE, ID, TENANT_ID, DESCRIPTOR, CREATED_AT, DESCRIPTOR_SCHEMA_ID, DESCRIPTOR_SCHEMA_VERSION,
        TYPE, BASE_URL, PATH, BUSINESS_APP_ID, TITLE, EDITABLE, CASCADE, CATALOG_VISUALIZATIONS, ROLE_BUSINESS_APPS, ROLE_CATALOGS, SITE_CATALOGS,
        SITE_ROLES, CATALOG_ID, ROLE_ID, VISUALIZATION_ID, SITE_ID, INSTANCE_ID} from "../../../utils/globals"

export function up(knex: Knex): Promise<any> {
    return knex.schema
        .createTable(BUSINESSAPP, function (table) {
            table.string(ID).notNullable();
            table.string(TENANT_ID).notNullable();
            table.string(INSTANCE_ID).notNullable();
            table.timestamp(CREATED_AT).defaultTo(knex.fn.now());
            table.string(DESCRIPTOR_SCHEMA_ID);
            table.string(DESCRIPTOR_SCHEMA_VERSION).notNullable();
            table.jsonb(DESCRIPTOR).notNullable();
        })
        .alterTable(BUSINESSAPP, function (table) {
            table.primary([ID], {constraintName: 'BusinessApp_pkey'});
        })
        .createTable(CATALOG, function (table){
            table.string(ID).notNullable();
            table.string(TENANT_ID).notNullable();
            table.string(INSTANCE_ID).notNullable();
            table.timestamp(CREATED_AT).defaultTo(knex.fn.now());
            table.text(TITLE).notNullable();
        })
        .alterTable(CATALOG, function (table) {
            table.primary([ID], {constraintName: 'Catalog_pkey'});
        })
        .createTable(ROLE, function (table){
            table.string(ID).notNullable();
            table.string(TENANT_ID).notNullable();
            table.string(INSTANCE_ID).notNullable();
            table.timestamp(CREATED_AT).defaultTo(knex.fn.now());
            table.text(TITLE).notNullable();
            table.boolean(EDITABLE).notNullable();
        })
        .alterTable(ROLE, function (table) {
            table.primary([ID], {constraintName: 'Role_pkey'});
        })
        .createTable(VISUALIZATION, function (table) {
            table.string(ID).notNullable();
            table.string(TENANT_ID).notNullable();
            table.string(INSTANCE_ID).notNullable();
            table.timestamp(CREATED_AT).defaultTo(knex.fn.now());
            table.string(DESCRIPTOR_SCHEMA_ID);
            table.string(DESCRIPTOR_SCHEMA_VERSION).notNullable();
            table.jsonb(DESCRIPTOR).notNullable();
            table.string(TYPE).notNullable();
            table.string(BASE_URL);
            table.string(PATH);
            table.string(BUSINESS_APP_ID).notNullable();
        })
        .alterTable(VISUALIZATION, function (table) {
            table.primary([ID], {constraintName: 'Visualization_pkey'});
            table.foreign([BUSINESS_APP_ID], 'Visualization_BusinessAppId_fkey').references([ID]).inTable(BUSINESSAPP).onUpdate(CASCADE).onDelete(CASCADE);
        })
        .createTable(SITE, function (table){
            table.string(ID).notNullable()
            table.string(TENANT_ID).notNullable();
            table.string(INSTANCE_ID).notNullable();
            table.timestamp(CREATED_AT).defaultTo(knex.fn.now());
            table.text(TITLE).notNullable();
        })
        .alterTable(SITE, function (table) {
            table.primary([ID], {constraintName: 'Site_pkey'});
        })
        .createTable(CATALOG_VISUALIZATIONS, function (table){
            table.string(CATALOG_ID).notNullable();
            table.string(VISUALIZATION_ID).notNullable();
        })
        .alterTable(CATALOG_VISUALIZATIONS, function (table) {
            table.primary([CATALOG_ID, VISUALIZATION_ID],{constraintName: 'Catalog-Visualizations_pkey'});
            table.foreign([CATALOG_ID], 'Catalog-Visualizations_CatalogId_fkey').references([ID]).inTable(CATALOG).onUpdate(CASCADE).onDelete(CASCADE);
            table.foreign([VISUALIZATION_ID], 'Catalog-Visualizations_VisualizationId_fkey').references([ID]).inTable(VISUALIZATION).onUpdate(CASCADE).onDelete(CASCADE);
        })
        .createTable(ROLE_BUSINESS_APPS, function (table){
            table.string(ROLE_ID).notNullable();
            table.string(BUSINESS_APP_ID).notNullable();
        })
        .alterTable(ROLE_BUSINESS_APPS, function (table) {
            table.primary([ROLE_ID, BUSINESS_APP_ID],{constraintName: 'Role-BusinessApps_pkey'});
            table.foreign([BUSINESS_APP_ID], 'Role-BusinessApps_BusinessAppId_fkey').references([ID]).inTable(BUSINESSAPP).onUpdate(CASCADE).onDelete(CASCADE);
            table.foreign([ROLE_ID], 'Role-BusinessApps_RoleId_fkey').references([ID]).inTable(ROLE).onUpdate(CASCADE).onDelete(CASCADE);
        })
        .createTable(ROLE_CATALOGS, function (table){
            table.string(ROLE_ID).notNullable();
            table.string(CATALOG_ID).notNullable();
        })
        .alterTable(ROLE_CATALOGS, function (table) {
            table.primary([ROLE_ID, CATALOG_ID],{constraintName: 'Role-Catalogs_pkey'});
            table.foreign([CATALOG_ID], 'Role-Catalogs_CatalogId_fkey').references([ID]).inTable(CATALOG).onUpdate(CASCADE).onDelete(CASCADE);
            table.foreign([ROLE_ID], 'Role-Catalogs_RoleId_fkey').references([ID]).inTable(ROLE).onUpdate(CASCADE).onDelete(CASCADE);
        })
        .createTable(SITE_CATALOGS, function (table){
            table.string(SITE_ID).notNullable();
            table.string(CATALOG_ID).notNullable();
        })
        .alterTable(SITE_CATALOGS, function (table) {
            table.primary([SITE_ID, CATALOG_ID],{constraintName: 'Site-Catalogs_pkey'});
            table.foreign([CATALOG_ID], 'Site-Catalogs_CatalogId_fkey').references([ID]).inTable(CATALOG).onUpdate(CASCADE).onDelete(CASCADE);
            table.foreign([SITE_ID], 'Site-Catalogs_SiteId_fkey').references([ID]).inTable(SITE).onUpdate(CASCADE).onDelete(CASCADE);
        })
        .createTable(SITE_ROLES, function (table){
            table.string(ROLE_ID).notNullable();
            table.string(SITE_ID).notNullable();
        })
        .alterTable(SITE_ROLES, function (table) {
            table.primary([ROLE_ID, SITE_ID],{constraintName: 'Site-Roles_pkey'});
            table.foreign([SITE_ID], 'Site-Roles_SiteId_fkey').references([ID]).inTable(SITE).onUpdate(CASCADE).onDelete(CASCADE);
        })

}

export function down(knex: Knex): Promise<any> {
    return knex.schema
        .raw(`DROP TABLE ${BUSINESSAPP}`)
        .raw(`DROP TABLE ${CATALOG}`)
        .raw(`DROP TABLE ${ROLE}`)
        .raw(`DROP TABLE ${VISUALIZATION}`)
        .raw(`DROP TABLE ${SITE}`)
        .raw(`DROP TABLE "${ROLE_BUSINESS_APPS}"`)
        .raw(`DROP TABLE "${ROLE_CATALOGS}"`)
        .raw(`DROP TABLE "${CATALOG_VISUALIZATIONS}"`)
        .raw(`DROP TABLE "${SITE_CATALOGS}"`)
        .raw(`DROP TABLE "${SITE_ROLES}"`)
}