-- Property management tables
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'property_set') AND type in (N'U'))
CREATE TABLE property_set
(
    -- Represents a collection of properties valid for a specific time period
    id         INT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
    start_date DATETIME2 NOT NULL DEFAULT GETDATE(),
    end_date   DATETIME2 NULL,
    state      VARCHAR(8)   NOT NULL,
    CONSTRAINT chk_property_state_values CHECK (state IN ('DRAFT', 'VALID', 'INVALID', 'EXPIRED')),
    CONSTRAINT chk_property_date_range CHECK (end_date IS NULL OR end_date > start_date)
);
CREATE INDEX idx_dates ON property_set (start_date, end_date);
CREATE INDEX idx_property_set_id ON property_set (id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'system_property_type') AND type in (N'U'))
CREATE TABLE system_property_type
(
    -- Stores system-wide configuration property types
    id                  INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    name                NVARCHAR(255) NOT NULL,
    external_mapping_id VARCHAR(16),
    description         NVARCHAR(512) NOT NULL,
    property_group      VARCHAR(32)  NOT NULL,
    sequence_number     INT          NOT NULL,
    data_type           VARCHAR(16)  NOT NULL,
    validation_rule     VARCHAR(64),
    CONSTRAINT idx_external_mapping UNIQUE (external_mapping_id),
    CONSTRAINT chk_system_data_type_values CHECK (data_type IN
                                                    ('INT', 'PERCENTAGE', 'BOOLEAN', 'CURRENCY', 'ENUMERATION',
                                                     'TEXT'))
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'system_property') AND type in (N'U'))
CREATE TABLE system_property
(
    -- Stores system-wide configuration properties
    id                      INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    property_set_id         INT NOT NULL,
    system_property_type_id INT NOT NULL,
    property_value          NVARCHAR(500),
    FOREIGN KEY (property_set_id) REFERENCES property_set (id),
    FOREIGN KEY (system_property_type_id) REFERENCES system_property_type (id),
    CONSTRAINT idx_system_property_type_id_property_set UNIQUE (system_property_type_id, property_set_id)
);
CREATE INDEX idx_system_property_type_id ON system_property (system_property_type_id);
CREATE INDEX idx_system_property_set_id ON system_property (property_set_id);

-- country
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'country') AND type in (N'U'))
CREATE TABLE country
(
    id            INT          NOT NULL IDENTITY(1,1),
    iso_code      VARCHAR(2)      NOT NULL,
    region_code   VARCHAR(5)      NOT NULL,
    name          NVARCHAR(255) NOT NULL,
    is_deprecated BIT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT uk_country_iso_code UNIQUE (iso_code),
    CONSTRAINT chk_country_region_code
        CHECK (region_code IN ('EMEA', 'LATAM', 'APAC', 'NAM'))
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'country_property_type') AND type in (N'U'))
CREATE TABLE country_property_type
(
    id                  INT          NOT NULL IDENTITY(1,1),
    name                NVARCHAR(255) NOT NULL,
    external_mapping_id VARCHAR(16),
    data_type           VARCHAR(16)  NOT NULL,
    validation_rule     VARCHAR(64),
    description         NVARCHAR(512) NOT NULL,
    property_group      VARCHAR(32)  NOT NULL,
    sequence_number     INT          NOT NULL,
    is_required         BIT          NOT NULL DEFAULT 0,
    CONSTRAINT chk_country_data_type_values CHECK (data_type IN
                                                     ('INT', 'PERCENTAGE', 'BOOLEAN', 'CURRENCY', 'ENUMERATION',
                                                      'TEXT')),
    PRIMARY KEY (id)
);
CREATE INDEX idx_property_type_data_type ON country_property_type (data_type);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'country_property') AND type in (N'U'))
CREATE TABLE country_property
(
    id                       INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    country_id               INT NOT NULL,
    country_property_type_id INT NOT NULL,
    property_set_id          INT NOT NULL,
    property_value           NVARCHAR(500),
    FOREIGN KEY (country_id) REFERENCES country (id),
    FOREIGN KEY (country_property_type_id) REFERENCES country_property_type (id),
    FOREIGN KEY (property_set_id) REFERENCES property_set (id),
    CONSTRAINT idx_country_property UNIQUE (country_id, country_property_type_id, property_set_id)
);

-- Main table for user information
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_user') AND type in (N'U'))
CREATE TABLE sys_user
(
    id         INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    workday_id VARCHAR(32)     NOT NULL,
    email      VARCHAR(254) NOT NULL,
    firstname  NVARCHAR(100) NOT NULL,
    lastname   NVARCHAR(100) NOT NULL,
    is_active  BIT          NOT NULL DEFAULT 1,
    CONSTRAINT idx_user_email UNIQUE (email),
    CONSTRAINT idx_user_workday UNIQUE (workday_id)
);

-- Group definitions
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_group') AND type in (N'U'))
CREATE TABLE sys_group
(
    id                INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    group_name        NVARCHAR(64)  NOT NULL,
    group_description NVARCHAR(MAX) NOT NULL,
    CONSTRAINT idx_group_name UNIQUE (group_name)
);

-- Junction table for user-group assignments
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_user_group_mapping') AND type in (N'U'))
CREATE TABLE sys_user_group_mapping
(
    user_id  INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES sys_user (id),
    FOREIGN KEY (group_id) REFERENCES sys_group (id)
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_user_node') AND type in (N'U'))
CREATE TABLE sys_user_node
(
    id            INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    user_id       INT          NOT NULL,
    country_id    INT          NOT NULL,
    name          NVARCHAR(254) NOT NULL,
    address       NVARCHAR(500) NOT NULL,
    geo_lat       DECIMAL(8, 4) CHECK (geo_lat BETWEEN -90 AND 90),
    geo_lng       DECIMAL(8, 4) CHECK (geo_lng BETWEEN -180 AND 180),
    is_deprecated BIT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES sys_user (id),
    FOREIGN KEY (country_id) REFERENCES country (id)
);

-- Main table for application information
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_app') AND type in (N'U'))
CREATE TABLE sys_app
(
    id            INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    client_id     VARCHAR(255) NOT NULL UNIQUE,
    client_secret VARCHAR(255) NOT NULL,
    name          NVARCHAR(255) NOT NULL
);

-- Junction table for app-group assignments
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_app_group_mapping') AND type in (N'U'))
CREATE TABLE sys_app_group_mapping
(
    app_id   INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (app_id, group_id),
    FOREIGN KEY (app_id) REFERENCES sys_app (id),
    FOREIGN KEY (group_id) REFERENCES sys_group (id)
);

-- logistic nodes
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'node') AND type in (N'U'))
CREATE TABLE node
(
    id                   INT IDENTITY(1,1) PRIMARY KEY,
    country_id           INT          NOT NULL,
    name                 NVARCHAR(255) NOT NULL,
    address              NVARCHAR(500) NOT NULL,
    external_mapping_id  VARCHAR(32),
    predecessor_required BIT          NOT NULL DEFAULT 0,
    is_destination       BIT          NOT NULL,
    is_source            BIT          NOT NULL,
    is_intermediate      BIT          NOT NULL,
    geo_lat              DECIMAL(8, 4) CHECK (geo_lat BETWEEN -90 AND 90),
    geo_lng              DECIMAL(8, 4) CHECK (geo_lng BETWEEN -180 AND 180),
    updated_at           DATETIME2    NOT NULL DEFAULT GETDATE(),
    is_deprecated        BIT          NOT NULL DEFAULT 0,
    FOREIGN KEY (country_id) REFERENCES country (id)
);
CREATE INDEX idx_country_id ON node (country_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'node_predecessor_chain') AND type in (N'U'))
CREATE TABLE node_predecessor_chain
(
    id      INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    node_id INT NOT NULL,
    FOREIGN KEY (node_id) REFERENCES node (id)
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'node_predecessor_entry') AND type in (N'U'))
CREATE TABLE node_predecessor_entry
(
    id                        INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    node_id                   INT NOT NULL,
    node_predecessor_chain_id INT NOT NULL,
    sequence_number           INT NOT NULL CHECK (sequence_number > 0),
    FOREIGN KEY (node_id) REFERENCES node (id),
    FOREIGN KEY (node_predecessor_chain_id) REFERENCES node_predecessor_chain (id),
    CONSTRAINT uk_node_predecessor UNIQUE (node_predecessor_chain_id, sequence_number)
);
CREATE INDEX idx_node_predecessor ON node_predecessor_entry (node_predecessor_chain_id);
CREATE INDEX idx_sequence ON node_predecessor_entry (sequence_number);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'outbound_country_mapping') AND type in (N'U'))
CREATE TABLE outbound_country_mapping
(
    id         INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    node_id    INT NOT NULL,
    country_id INT NOT NULL,
    FOREIGN KEY (node_id) REFERENCES node (id),
    FOREIGN KEY (country_id) REFERENCES country (id),
    CONSTRAINT uk_node_id_country_id UNIQUE (node_id, country_id)
);
CREATE INDEX idx_ocm_node_id ON outbound_country_mapping (node_id);
CREATE INDEX idx_ocm_country_id ON outbound_country_mapping (country_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'distance_matrix') AND type in (N'U'))
CREATE TABLE distance_matrix
(
    id                INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    from_node_id      INT                     DEFAULT NULL,
    to_node_id        INT                     DEFAULT NULL,
    from_user_node_id INT                     DEFAULT NULL,
    to_user_node_id   INT                     DEFAULT NULL,
    from_geo_lat      DECIMAL(8, 4) CHECK (from_geo_lat BETWEEN -90 AND 90),
    from_geo_lng      DECIMAL(8, 4) CHECK (from_geo_lng BETWEEN -180 AND 180),
    to_geo_lat        DECIMAL(8, 4) CHECK (to_geo_lat BETWEEN -90 AND 90),
    to_geo_lng        DECIMAL(8, 4) CHECK (to_geo_lng BETWEEN -180 AND 180),
    distance          DECIMAL(15, 2) NOT NULL,
    updated_at        DATETIME2      NOT NULL DEFAULT GETDATE(),
    state             VARCHAR(10)       NOT NULL,
    FOREIGN KEY (from_node_id) REFERENCES node (id),
    FOREIGN KEY (to_node_id) REFERENCES node (id),
    FOREIGN KEY (from_user_node_id) REFERENCES sys_user_node (id),
    FOREIGN KEY (to_user_node_id) REFERENCES sys_user_node (id),
    CONSTRAINT chk_distance_matrix_state CHECK (state IN ('VALID', 'STALE')),
    CONSTRAINT chk_from_node_xor CHECK (
        (from_node_id IS NOT NULL AND from_user_node_id IS NULL) OR
        (from_node_id IS NULL AND from_user_node_id IS NOT NULL)
        ),
    CONSTRAINT chk_to_node_xor CHECK (
        (to_node_id IS NOT NULL AND to_user_node_id IS NULL) OR
        (to_node_id IS NULL AND to_user_node_id IS NOT NULL)
        ),
    CONSTRAINT uk_nodes_unique UNIQUE (from_node_id, to_node_id, from_user_node_id, to_user_node_id)
);
CREATE INDEX idx_from_to_nodes ON distance_matrix (from_node_id, to_node_id);
CREATE INDEX idx_user_from_to_nodes ON distance_matrix (from_user_node_id, to_user_node_id);

-- container rates
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'validity_period') AND type in (N'U'))
CREATE TABLE validity_period
(
    id         INT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
    start_date DATETIME2 NOT NULL DEFAULT GETDATE(),
    end_date   DATETIME2          DEFAULT NULL,
    renewals   INT                DEFAULT 0,
    state      VARCHAR(8)   NOT NULL CHECK (state IN ('DRAFT', 'VALID', 'INVALID', 'EXPIRED')),
    CONSTRAINT chk_validity_date_range CHECK (end_date IS NULL OR end_date > start_date)
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'container_rate') AND type in (N'U'))
CREATE TABLE container_rate
(
    id                  INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    from_node_id        INT            NOT NULL,
    to_node_id          INT            NOT NULL,
    container_rate_type VARCHAR(8) CHECK (container_rate_type IN ('RAIL', 'SEA', 'POST_RUN', 'ROAD')),
    rate_teu            DECIMAL(15, 2) NOT NULL,
    rate_feu            DECIMAL(15, 2) NOT NULL,
    rate_hc             DECIMAL(15, 2) NOT NULL,
    lead_time           INT            NOT NULL,
    validity_period_id  INT            NOT NULL,
    FOREIGN KEY (from_node_id) REFERENCES node (id),
    FOREIGN KEY (to_node_id) REFERENCES node (id),
    FOREIGN KEY (validity_period_id) REFERENCES validity_period (id),
    CONSTRAINT uk_container_rate_unique UNIQUE (from_node_id, to_node_id, validity_period_id, container_rate_type)
);
CREATE INDEX idx_cr_from_to_nodes ON container_rate (from_node_id, to_node_id);
CREATE INDEX idx_cr_validity_period_id ON container_rate (validity_period_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'country_matrix_rate') AND type in (N'U'))
CREATE TABLE country_matrix_rate
(
    id                 INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    from_country_id    INT            NOT NULL,
    to_country_id      INT            NOT NULL,
    rate               DECIMAL(15, 2) NOT NULL,
    validity_period_id INT            NOT NULL,
    FOREIGN KEY (from_country_id) REFERENCES country (id),
    FOREIGN KEY (to_country_id) REFERENCES country (id),
    FOREIGN KEY (validity_period_id) REFERENCES validity_period (id),
    CONSTRAINT uk_country_matrix_rate_unique UNIQUE (from_country_id, to_country_id, validity_period_id)
);
CREATE INDEX idx_cmr_from_to_country ON country_matrix_rate (from_country_id, to_country_id);
CREATE INDEX idx_cmr_validity_period_id ON country_matrix_rate (validity_period_id);

-- packaging and material
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'material') AND type in (N'U'))
CREATE TABLE material
(
    id                     INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    part_number            VARCHAR(12)     NOT NULL,
    normalized_part_number VARCHAR(12)     NOT NULL,
    hs_code                VARCHAR(11),
    name                   NVARCHAR(500) NOT NULL,
    is_deprecated          BIT          NOT NULL DEFAULT 0,
    CONSTRAINT uq_normalized_part_number UNIQUE (normalized_part_number)
);
CREATE INDEX idx_part_number ON material (part_number);
CREATE INDEX idx_normalized_part_number ON material (normalized_part_number);
CREATE INDEX idx_hs_code ON material (hs_code);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'packaging_dimension') AND type in (N'U'))
CREATE TABLE packaging_dimension
(
    id                       INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    type                     VARCHAR(3)               DEFAULT 'HU',
    length                   INT          NOT NULL,
    width                    INT          NOT NULL,
    height                   INT          NOT NULL,
    displayed_dimension_unit VARCHAR(2)               DEFAULT 'CM',
    weight                   INT          NOT NULL,
    displayed_weight_unit    VARCHAR(2)               DEFAULT 'KG',
    content_unit_count       INT          NOT NULL,
    is_deprecated            BIT          NOT NULL DEFAULT 0,
    CONSTRAINT chk_packaging_dimension_type_values CHECK (type IN ('SHU', 'HU')),
    CONSTRAINT chk_packaging_dimension_displayed_dimension_unit CHECK (displayed_dimension_unit IN ('MM', 'CM', 'M')),
    CONSTRAINT chk_packaging_dimension_displayed_weight_unit CHECK (displayed_weight_unit IN ('T', 'G', 'KG'))
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'packaging') AND type in (N'U'))
CREATE TABLE packaging
(
    id               INT     NOT NULL IDENTITY(1,1) PRIMARY KEY,
    supplier_node_id INT     NOT NULL,
    material_id      INT     NOT NULL,
    hu_dimension_id  INT     NOT NULL,
    shu_dimension_id INT     NOT NULL,
    is_deprecated    BIT     NOT NULL DEFAULT 0,
    FOREIGN KEY (supplier_node_id) REFERENCES node (id),
    FOREIGN KEY (material_id) REFERENCES material (id),
    FOREIGN KEY (hu_dimension_id) REFERENCES packaging_dimension (id),
    FOREIGN KEY (shu_dimension_id) REFERENCES packaging_dimension (id)
);
CREATE INDEX idx_pkg_material_id ON packaging (material_id);
CREATE INDEX idx_pkg_hu_dimension_id ON packaging (hu_dimension_id);
CREATE INDEX idx_pkg_shu_dimension_id ON packaging (shu_dimension_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'packaging_property_type') AND type in (N'U'))
CREATE TABLE packaging_property_type
(
    id                  INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    name                NVARCHAR(255) NOT NULL,
    external_mapping_id VARCHAR(16)  NOT NULL,
    description         NVARCHAR(255) NOT NULL,
    property_group      VARCHAR(32)  NOT NULL,
    sequence_number     INT          NOT NULL,
    data_type           VARCHAR(16),
    validation_rule     VARCHAR(64),
    is_required         BIT          NOT NULL DEFAULT 0,
    CONSTRAINT idx_packaging_property_type UNIQUE (external_mapping_id),
    CONSTRAINT chk_packaging_data_type_values CHECK (data_type IN
                                                       ('INT', 'PERCENTAGE', 'BOOLEAN', 'CURRENCY', 'ENUMERATION',
                                                        'TEXT'))
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'packaging_property') AND type in (N'U'))
CREATE TABLE packaging_property
(
    id                         INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    packaging_property_type_id INT NOT NULL,
    packaging_id               INT NOT NULL,
    property_value             NVARCHAR(500),
    FOREIGN KEY (packaging_property_type_id) REFERENCES packaging_property_type (id),
    FOREIGN KEY (packaging_id) REFERENCES packaging (id),
    CONSTRAINT idx_packaging_property_unique UNIQUE (packaging_property_type_id, packaging_id)
);
CREATE INDEX idx_pp_packaging_property_type_id ON packaging_property (packaging_property_type_id);
CREATE INDEX idx_pp_packaging_id ON packaging_property (packaging_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'premise') AND type in (N'U'))
CREATE TABLE premise
(
    id                          INT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
    material_id                 INT       NOT NULL,
    supplier_node_id            INT,
    user_supplier_node_id       INT,
    geo_lat                     DECIMAL(8, 4) CHECK (geo_lat BETWEEN -90 AND 90),
    geo_lng                     DECIMAL(8, 4) CHECK (geo_lng BETWEEN -180 AND 180),
    country_id                  INT       NOT NULL,
    packaging_id                INT                DEFAULT NULL,
    user_id                     INT       NOT NULL,
    created_at                  DATETIME2 NOT NULL DEFAULT GETDATE(),
    updated_at                  DATETIME2 NOT NULL DEFAULT GETDATE(),
    material_cost               DECIMAL(15, 2)     DEFAULT NULL,
    is_fca_enabled              BIT                DEFAULT 0,
    oversea_share               DECIMAL(8, 4)      DEFAULT NULL,
    hs_code                     VARCHAR(11)           DEFAULT NULL,
    tariff_measure              INT                DEFAULT NULL,
    tariff_rate                 DECIMAL(8, 4)      DEFAULT NULL,
    tariff_unlocked             BIT                DEFAULT 0,
    state                       VARCHAR(10)  NOT NULL DEFAULT 'DRAFT',
    individual_hu_length        INT,
    individual_hu_height        INT,
    individual_hu_width         INT,
    individual_hu_weight        INT,
    hu_displayed_dimension_unit VARCHAR(2)            DEFAULT 'MM',
    hu_displayed_weight_unit    VARCHAR(2)            DEFAULT 'KG',
    hu_unit_count               INT                DEFAULT NULL,
    hu_stackable                BIT                DEFAULT 1,
    hu_mixable                  BIT                DEFAULT 1,
    FOREIGN KEY (material_id) REFERENCES material (id),
    FOREIGN KEY (supplier_node_id) REFERENCES node (id),
    FOREIGN KEY (user_supplier_node_id) REFERENCES sys_user_node (id),
    FOREIGN KEY (packaging_id) REFERENCES packaging (id),
    FOREIGN KEY (user_id) REFERENCES sys_user (id),
    CONSTRAINT chk_premise_state_values CHECK (state IN ('DRAFT', 'COMPLETED', 'ARCHIVED')),
    CONSTRAINT chk_premise_displayed_dimension_unit CHECK (hu_displayed_dimension_unit IN ('MM', 'CM', 'M')),
    CONSTRAINT chk_premise_displayed_weight_unit CHECK (hu_displayed_weight_unit IN ('T', 'G', 'KG'))
);
CREATE INDEX idx_prem_material_id ON premise (material_id);
CREATE INDEX idx_prem_supplier_node_id ON premise (supplier_node_id);
CREATE INDEX idx_prem_packaging_id ON premise (packaging_id);
CREATE INDEX idx_prem_user_id ON premise (user_id);
CREATE INDEX idx_prem_user_supplier_node_id ON premise (user_supplier_node_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'premise_destination') AND type in (N'U'))
CREATE TABLE premise_destination
(
    id                  INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    premise_id          INT NOT NULL,
    annual_amount       INT,
    destination_node_id INT NOT NULL,
    is_d2d              BIT            DEFAULT 0,
    rate_d2d            DECIMAL(15, 2) DEFAULT NULL CHECK (rate_d2d >= 0),
    lead_time_d2d       INT            DEFAULT NULL CHECK (lead_time_d2d >= 0),
    repacking_cost      DECIMAL(15, 2) DEFAULT NULL CHECK (repacking_cost >= 0),
    handling_cost       DECIMAL(15, 2) DEFAULT NULL CHECK (handling_cost >= 0),
    disposal_cost       DECIMAL(15, 2) DEFAULT NULL CHECK (disposal_cost >= 0),
    geo_lat             DECIMAL(8, 4) CHECK (geo_lat BETWEEN -90 AND 90),
    geo_lng             DECIMAL(8, 4) CHECK (geo_lng BETWEEN -180 AND 180),
    country_id          INT NOT NULL,
    distance_d2d        DECIMAL(15, 2),
    FOREIGN KEY (premise_id) REFERENCES premise (id),
    FOREIGN KEY (country_id) REFERENCES country (id),
    FOREIGN KEY (destination_node_id) REFERENCES node (id)
);
CREATE INDEX idx_pd_destination_node_id ON premise_destination (destination_node_id);
CREATE INDEX idx_pd_premise_id ON premise_destination (premise_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'premise_route_node') AND type in (N'U'))
CREATE TABLE premise_route_node
(
    id                  INT          NOT NULL IDENTITY(1,1) PRIMARY KEY,
    node_id             INT     DEFAULT NULL,
    user_node_id        INT     DEFAULT NULL,
    name                NVARCHAR(255) NOT NULL,
    address             NVARCHAR(500),
    external_mapping_id VARCHAR(32)  NOT NULL,
    country_id          INT          NOT NULL,
    is_destination      BIT DEFAULT 0,
    is_intermediate     BIT DEFAULT 0,
    is_source           BIT DEFAULT 0,
    geo_lat             DECIMAL(8, 4) CHECK (geo_lat BETWEEN -90 AND 90),
    geo_lng             DECIMAL(8, 4) CHECK (geo_lng BETWEEN -180 AND 180),
    is_outdated         BIT DEFAULT 0,
    FOREIGN KEY (node_id) REFERENCES node (id),
    FOREIGN KEY (country_id) REFERENCES country (id),
    FOREIGN KEY (user_node_id) REFERENCES sys_user_node (id),
    CONSTRAINT chk_node CHECK (user_node_id IS NULL OR node_id IS NULL)
);
CREATE INDEX idx_prn_node_id ON premise_route_node (node_id);
CREATE INDEX idx_prn_user_node_id ON premise_route_node (user_node_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'premise_route') AND type in (N'U'))
CREATE TABLE premise_route
(
    id                     INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    premise_destination_id INT NOT NULL,
    is_fastest             BIT DEFAULT 0,
    is_cheapest            BIT DEFAULT 0,
    is_selected            BIT DEFAULT 0,
    FOREIGN KEY (premise_destination_id) REFERENCES premise_destination (id)
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'premise_route_section') AND type in (N'U'))
CREATE TABLE premise_route_section
(
    id                 INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    premise_route_id   INT NOT NULL,
    from_route_node_id INT NOT NULL,
    to_route_node_id   INT NOT NULL,
    list_position      INT NOT NULL,
    transport_type     VARCHAR(16) CHECK (transport_type IN ('RAIL', 'SEA', 'ROAD', 'POST_RUN')),
    rate_type          VARCHAR(16) CHECK (rate_type IN ('CONTAINER', 'MATRIX', 'NEAR_BY')),
    is_pre_run         BIT DEFAULT 0,
    is_main_run        BIT DEFAULT 0,
    is_post_run        BIT DEFAULT 0,
    is_outdated        BIT DEFAULT 0,
    CONSTRAINT fk_premise_route_section_premise_route_id FOREIGN KEY (premise_route_id) REFERENCES premise_route (id),
    FOREIGN KEY (from_route_node_id) REFERENCES premise_route_node (id),
    FOREIGN KEY (to_route_node_id) REFERENCES premise_route_node (id),
    CONSTRAINT chk_main_run CHECK (transport_type = 'ROAD' OR transport_type = 'POST_RUN' OR is_main_run = 1)
);
CREATE INDEX idx_prs_premise_route_id ON premise_route_section (premise_route_id);
CREATE INDEX idx_prs_from_route_node_id ON premise_route_section (from_route_node_id);
CREATE INDEX idx_prs_to_route_node_id ON premise_route_section (to_route_node_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'calculation_job') AND type in (N'U'))
CREATE TABLE calculation_job
(
    id                 INT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
    premise_id         INT       NOT NULL,
    calculation_date   DATETIME2 NOT NULL DEFAULT GETDATE(),
    validity_period_id INT       NOT NULL,
    property_set_id    INT       NOT NULL,
    job_state          VARCHAR(10)  NOT NULL CHECK (job_state IN ('CREATED', 'SCHEDULED', 'VALID', 'INVALID', 'EXCEPTION')),
    error_id           INT                DEFAULT NULL,
    user_id            INT       NOT NULL,
    FOREIGN KEY (premise_id) REFERENCES premise (id),
    FOREIGN KEY (validity_period_id) REFERENCES validity_period (id),
    FOREIGN KEY (property_set_id) REFERENCES property_set (id),
    FOREIGN KEY (user_id) REFERENCES sys_user (id)
);
CREATE INDEX idx_cj_premise_id ON calculation_job (premise_id);
CREATE INDEX idx_cj_validity_period_id ON calculation_job (validity_period_id);
CREATE INDEX idx_cj_property_set_id ON calculation_job (property_set_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'calculation_job_destination') AND type in (N'U'))
CREATE TABLE calculation_job_destination
(
    id                            INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    calculation_job_id            INT            NOT NULL,
    premise_destination_id        INT            NOT NULL,
    shipping_frequency            INT,
    total_cost                    DECIMAL(15, 2),
    annual_amount                 DECIMAL(15, 2),
    annual_risk_cost              DECIMAL(15, 2) NOT NULL,
    annual_chance_cost            DECIMAL(15, 2) NOT NULL,
    is_small_unit                 BIT        DEFAULT 0,
    annual_repacking_cost         DECIMAL(15, 2) NOT NULL,
    annual_handling_cost          DECIMAL(15, 2) NOT NULL,
    annual_disposal_cost          DECIMAL(15, 2) NOT NULL,
    operational_stock             DECIMAL(15, 2) NOT NULL,
    safety_stock                  DECIMAL(15, 2) NOT NULL,
    stocked_inventory             DECIMAL(15, 2) NOT NULL,
    in_transport_stock            DECIMAL(15, 2) NOT NULL,
    stock_before_payment          DECIMAL(15, 2) NOT NULL,
    annual_capital_cost           DECIMAL(15, 2) NOT NULL,
    annual_storage_cost           DECIMAL(15, 2) NOT NULL,
    custom_value                  DECIMAL(15, 2) NOT NULL,
    custom_duties                 DECIMAL(15, 2) NOT NULL,
    tariff_rate                   DECIMAL(8, 4)  NOT NULL,
    annual_custom_cost            DECIMAL(15, 2) NOT NULL,
    air_freight_share_max         DECIMAL(8, 4)  NOT NULL,
    air_freight_share             DECIMAL(8, 4)  NOT NULL,
    air_freight_volumetric_weight DECIMAL(15, 2) NOT NULL,
    air_freight_weight            DECIMAL(15, 2) NOT NULL,
    annual_air_freight_cost       DECIMAL(15, 2) NOT NULL,
    is_d2d                        BIT        DEFAULT 0,
    rate_d2d                      DECIMAL(15, 2) DEFAULT NULL,
    container_type                VARCHAR(8),
    hu_count                      INT            NOT NULL,
    layer_structure               NVARCHAR(MAX),
    layer_count                   INT            NOT NULL,
    transport_weight_exceeded     BIT        DEFAULT 0,
    annual_transportation_cost    DECIMAL(15, 2) NOT NULL,
    container_utilization         DECIMAL(8, 4)  NOT NULL,
    transit_time_in_days          INT            NOT NULL,
    safety_stock_in_days          INT            NOT NULL,
    material_cost                 DECIMAL(15, 2) NOT NULL,
    fca_cost                      DECIMAL(15, 2) NOT NULL,
    FOREIGN KEY (calculation_job_id) REFERENCES calculation_job (id),
    FOREIGN KEY (premise_destination_id) REFERENCES premise_destination (id),
    CONSTRAINT chk_container_type CHECK (container_type IN ('TEU', 'FEU', 'HC', 'TRUCK'))
);
CREATE INDEX idx_cjd_calculation_job_id ON calculation_job_destination (calculation_job_id);
CREATE INDEX idx_cjd_premise_destination_id ON calculation_job_destination (premise_destination_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'calculation_job_route_section') AND type in (N'U'))
CREATE TABLE calculation_job_route_section
(
    id                             INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    premise_route_section_id       INT,
    calculation_job_destination_id INT            NOT NULL,
    transport_type                 VARCHAR(16) CHECK (transport_type IN ('RAIL', 'SEA', 'ROAD', 'POST_RUN', 'MATRIX', 'D2D')),
    is_unmixed_price               BIT        DEFAULT 0,
    is_cbm_price                   BIT        DEFAULT 0,
    is_weight_price                BIT        DEFAULT 0,
    is_stacked                     BIT        DEFAULT 0,
    is_pre_run                     BIT        DEFAULT 0,
    is_main_run                    BIT        DEFAULT 0,
    is_post_run                    BIT        DEFAULT 0,
    rate                           DECIMAL(15, 2) NOT NULL,
    distance                       DECIMAL(15, 2) DEFAULT NULL,
    cbm_price                      DECIMAL(15, 2) NOT NULL,
    weight_price                   DECIMAL(15, 2) NOT NULL,
    annual_cost                    DECIMAL(15, 2) NOT NULL,
    transit_time                   INT            NOT NULL,
    FOREIGN KEY (premise_route_section_id) REFERENCES premise_route_section (id),
    FOREIGN KEY (calculation_job_destination_id) REFERENCES calculation_job_destination (id),
    CONSTRAINT chk_stacked CHECK (is_unmixed_price = 1 OR is_stacked = 1)
);
CREATE INDEX idx_cjrs_premise_route_section_id ON calculation_job_route_section (premise_route_section_id);
CREATE INDEX idx_cjrs_calculation_job_destination_id ON calculation_job_route_section (calculation_job_destination_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'bulk_operation') AND type in (N'U'))
CREATE TABLE bulk_operation
(
    id                   INT       NOT NULL IDENTITY(1,1) PRIMARY KEY,
    user_id              INT       NOT NULL,
    bulk_file_type       VARCHAR(32)  NOT NULL,
    bulk_processing_type VARCHAR(32)  NOT NULL,
    state                VARCHAR(10)  NOT NULL,
    [file]               VARBINARY(MAX)     DEFAULT NULL,
    validity_period_id   INT                DEFAULT NULL,
    created_at           DATETIME2 NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (user_id) REFERENCES sys_user (id),
    FOREIGN KEY (validity_period_id) REFERENCES validity_period (id),
    CONSTRAINT chk_bulk_file_type CHECK (bulk_file_type IN ('CONTAINER_RATE', 'COUNTRY_MATRIX', 'MATERIAL', 'PACKAGING', 'NODE')),
    CONSTRAINT chk_bulk_operation_state CHECK (state IN ('SCHEDULED', 'PROCESSING', 'COMPLETED', 'EXCEPTION')),
    CONSTRAINT chk_bulk_processing_type CHECK (bulk_processing_type IN ('IMPORT', 'EXPORT'))
);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_error') AND type in (N'U'))
CREATE TABLE sys_error
(
    id                 INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
    user_id            INT                    DEFAULT NULL,
    title              NVARCHAR(255)  NOT NULL,
    code               NVARCHAR(255)  NOT NULL,
    message            NVARCHAR(1024) NOT NULL,
    request            NVARCHAR(MAX),
    pinia              NVARCHAR(MAX),
    calculation_job_id INT                    DEFAULT NULL,
    bulk_operation_id  INT                    DEFAULT NULL,
    type               VARCHAR(16)      NOT NULL DEFAULT 'BACKEND',
    created_at         DATETIME2     NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (user_id) REFERENCES sys_user (id),
    FOREIGN KEY (calculation_job_id) REFERENCES calculation_job (id),
    FOREIGN KEY (bulk_operation_id) REFERENCES bulk_operation (id),
    CONSTRAINT chk_error_type CHECK (type IN ('BACKEND', 'FRONTEND', 'BULK', 'CALCULATION'))
);
CREATE INDEX idx_se_user_id ON sys_error (user_id);
CREATE INDEX idx_se_calculation_job_id ON sys_error (calculation_job_id);

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sys_error_trace_item') AND type in (N'U'))
CREATE TABLE sys_error_trace_item
(
    id         INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
    error_id   INT           NOT NULL,
    line       INT,
    [file]     VARCHAR(255)  NOT NULL,
    method     VARCHAR(255)  NOT NULL,
    fullPath   VARCHAR(1024) NOT NULL,
    created_at DATETIME2     NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (error_id) REFERENCES sys_error (id)
);
