CREATE TABLE blc_application (id VARCHAR(36) NOT NULL, allowed_currencies VARCHAR(10485760), allowed_locales VARCHAR(10485760), deactivated CHAR(1) DEFAULT 'N' NOT NULL, default_currency VARCHAR(255), default_locale VARCHAR(255), identifier_type VARCHAR(60), identifier_value VARCHAR(255), is_marketplace CHAR(1) DEFAULT 'N' NOT NULL, name VARCHAR(255), updated_timestamp TIMESTAMP WITHOUT TIME ZONE, trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_tenant_id VARCHAR(36), CONSTRAINT blc_application_pkey PRIMARY KEY (id));
CREATE TABLE blc_application_catalog (application_id VARCHAR(36) NOT NULL, catalog_status VARCHAR(60), context_id VARCHAR(36), exclude_from_add CHAR(1) DEFAULT 'N' NOT NULL, implicit VARCHAR(36), mutability_type VARCHAR(60), visible_as_assigned CHAR(1) DEFAULT 'Y' NOT NULL, isolated_catalogs_order INTEGER NOT NULL, CONSTRAINT blc_application_catalog_pkey PRIMARY KEY (application_id, isolated_catalogs_order));
CREATE TABLE blc_notification_state (id VARCHAR(36) NOT NULL, acked CHAR(1) DEFAULT 'N' NOT NULL, attempts INTEGER, change_timestamp TIMESTAMP WITHOUT TIME ZONE, change_timestamp_ack TIMESTAMP WITHOUT TIME ZONE, container VARCHAR(36), entity_type VARCHAR(255) NOT NULL, message_type VARCHAR(255), message_value VARCHAR(10485760), notification_name VARCHAR(255), next_attempt TIMESTAMP WITHOUT TIME ZONE, stopped CHAR(1) DEFAULT 'N' NOT NULL, CONSTRAINT blc_notification_state_pkey PRIMARY KEY (id));
CREATE TABLE blc_order (id VARCHAR(36) NOT NULL, adjustments VARCHAR(10485760), adjustments_total numeric(19, 5), attribute_choices VARCHAR(10485760), attributes VARCHAR(10485760), campaigns VARCHAR(10485760), cart_create_date TIMESTAMP WITHOUT TIME ZONE, cart_id VARCHAR(36), cart_type VARCHAR(60), currency VARCHAR(255), customer_id VARCHAR(36), customer_name VARCHAR(255), email_address VARCHAR(255), total_shipping numeric(19, 5), internal_attributes VARCHAR(10485760), locale VARCHAR(255), name VARCHAR(255), offer_codes VARCHAR(65535), order_number VARCHAR(255), status VARCHAR(60), submit_date TIMESTAMP WITHOUT TIME ZONE, subtotal numeric(19, 5), total numeric(19, 5), total_tax numeric(19, 5), trk_application_context_id VARCHAR(36), trk_application_default_locale VARCHAR(255), trk_application_name VARCHAR(255), trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_creating_application_id VARCHAR(36), trk_current_message VARCHAR(255), trk_level INTEGER, trk_max_field_versions VARCHAR(10485760), trk_sandbox_archived CHAR(1) DEFAULT 'N' NOT NULL, trk_sub_container_catalog VARCHAR(36), trk_change_container_id VARCHAR(36), trk_change_container_name VARCHAR(255), trk_change_container_route_key VARCHAR(60), trk_sub_container_name VARCHAR(255), trk_sandbox_change_type VARCHAR(60), trk_sandbox_context_id VARCHAR(36), trk_sandbox_name VARCHAR(255), trk_sandbox_owner VARCHAR(60), trk_sandbox_stage VARCHAR(60), trk_tenant_id VARCHAR(36), vouchers VARCHAR(10485760), CONSTRAINT blc_order_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_fulfill_item_tax_detail (id VARCHAR(36) NOT NULL, attributes VARCHAR(10485760), country VARCHAR(255), is_vat CHAR(1) DEFAULT 'N' NOT NULL, juris_name VARCHAR(255), juris_type VARCHAR(255), rate numeric(19, 5), region VARCHAR(255), tax_calculated numeric(19, 5), tax_name VARCHAR(255), taxable_amount numeric(19, 5), transaction_id VARCHAR(255), fulfillment_item_id VARCHAR(36), CONSTRAINT blc_order_fulfill_item_tax_detail_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_fulfillment (id VARCHAR(36) NOT NULL, addl_shipments VARCHAR(10485760), address VARCHAR(10485760), adjustments VARCHAR(10485760), attributes VARCHAR(10485760), currency VARCHAR(255), external_id VARCHAR(255), adjustments_total numeric(19, 5), fulfillment_charge numeric(19, 5), option_description VARCHAR(255), option_name VARCHAR(255), fulfillment_total numeric(19, 5), grand_total numeric(19, 5), group_reference_number VARCHAR(255), inv_loc_reference VARCHAR(255), option_taxable CHAR(1) DEFAULT 'N', merchandise_total numeric(19, 5), order_fulfillment_number VARCHAR(255), order_id VARCHAR(36), override_details VARCHAR(10485760), override_flflmt_price_flag CHAR(1) DEFAULT 'N', prorated_order_ffm_adj numeric(19, 5), shipment VARCHAR(10485760), status VARCHAR(60), option_tax_code VARCHAR(255), tax_total numeric(19, 5), trk_application_context_id VARCHAR(36), trk_application_default_locale VARCHAR(255), trk_application_name VARCHAR(255), trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_creating_application_id VARCHAR(36), trk_current_message VARCHAR(255), trk_level INTEGER, trk_max_field_versions VARCHAR(10485760), trk_sandbox_archived CHAR(1) DEFAULT 'N' NOT NULL, trk_sub_container_catalog VARCHAR(36), trk_change_container_id VARCHAR(36), trk_change_container_name VARCHAR(255), trk_change_container_route_key VARCHAR(60), trk_sub_container_name VARCHAR(255), trk_sandbox_change_type VARCHAR(60), trk_sandbox_context_id VARCHAR(36), trk_sandbox_name VARCHAR(255), trk_sandbox_owner VARCHAR(60), trk_sandbox_stage VARCHAR(60), trk_tenant_id VARCHAR(36), type VARCHAR(60), option_use_flat_rates CHAR(1) DEFAULT 'N', vendor_ref VARCHAR(255), CONSTRAINT blc_order_fulfillment_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_fulfillment_item (id VARCHAR(36) NOT NULL, attributes VARCHAR(10485760), available_online CHAR(1) DEFAULT 'Y' NOT NULL, fulfillment_adjustments VARCHAR(10485760), fulfillment_adj_total numeric(19, 5), fulfillment_refund_amount numeric(19, 5), fulfillment_total numeric(19, 5), internal_attributes VARCHAR(10485760), inv_check_strategy VARCHAR(60), inv_reservation_strategy VARCHAR(60), merchandise_adj_total numeric(19, 5), merchandise_refund_amount numeric(19, 5), merchandise_subtotal numeric(19, 5), merchandise_tax_total numeric(19, 5), merchandise_total numeric(19, 5), order_item_id VARCHAR(36), prorated_order_adjustments numeric(19, 5), prorated_order_ffm_adj numeric(19, 5), quantity INTEGER, reference_number VARCHAR(255), unit_fulfillment_charge numeric(19, 5), unit_merchandise_price numeric(19, 5), order_fulfillment_id VARCHAR(36), CONSTRAINT blc_order_fulfillment_item_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_fulfillment_note (id VARCHAR(36) NOT NULL, author VARCHAR(255), created TIMESTAMP WITHOUT TIME ZONE NOT NULL, note VARCHAR(65535), order_fulfillment_context_id VARCHAR(36), trk_application_context_id VARCHAR(36), trk_application_default_locale VARCHAR(255), trk_application_name VARCHAR(255), trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_creating_application_id VARCHAR(36), trk_current_message VARCHAR(255), trk_level INTEGER, trk_max_field_versions VARCHAR(10485760), trk_sandbox_archived CHAR(1) DEFAULT 'N' NOT NULL, trk_sub_container_catalog VARCHAR(36), trk_change_container_id VARCHAR(36), trk_change_container_name VARCHAR(255), trk_change_container_route_key VARCHAR(60), trk_sub_container_name VARCHAR(255), trk_sandbox_change_type VARCHAR(60), trk_sandbox_context_id VARCHAR(36), trk_sandbox_name VARCHAR(255), trk_sandbox_owner VARCHAR(60), trk_sandbox_stage VARCHAR(60), trk_tenant_id VARCHAR(36), CONSTRAINT blc_order_fulfillment_note_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_item (id VARCHAR(36) NOT NULL, adjustments_total numeric(19, 5), attribute_choices VARCHAR(10485760), attributes VARCHAR(10485760), brand_id VARCHAR(36), category_ids VARCHAR(65535), dependent_items VARCHAR(10485760), discountable CHAR(1) DEFAULT 'Y' NOT NULL, image_asset VARCHAR(10485760), internal_attributes VARCHAR(10485760), item_adjustments VARCHAR(10485760), merchandising_context VARCHAR(255), merchandising_type_id VARCHAR(36), name VARCHAR(255), override_details VARCHAR(10485760), override_price_flag CHAR(1) DEFAULT 'N' NOT NULL, price_list_id VARCHAR(36), pricing_strategy VARCHAR(255), product_id VARCHAR(36), product_tags VARCHAR(65535), quantity INTEGER NOT NULL, refund_amount numeric(19, 5), sku_code VARCHAR(255), subtotal numeric(19, 5), target_demographic_id VARCHAR(36), total numeric(19, 5), unit_price numeric(19, 5), unit_price_type VARCHAR(60), uri VARCHAR(255), variant_id VARCHAR(36), vendor_ref VARCHAR(255), order_id VARCHAR(36), CONSTRAINT blc_order_item_pkey PRIMARY KEY (id));
CREATE TABLE blc_order_note (id VARCHAR(36) NOT NULL, author VARCHAR(255), created TIMESTAMP WITHOUT TIME ZONE NOT NULL, note VARCHAR(65535), order_context_id VARCHAR(36), trk_application_context_id VARCHAR(36), trk_application_default_locale VARCHAR(255), trk_application_name VARCHAR(255), trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_creating_application_id VARCHAR(36), trk_current_message VARCHAR(255), trk_level INTEGER, trk_max_field_versions VARCHAR(10485760), trk_sandbox_archived CHAR(1) DEFAULT 'N' NOT NULL, trk_sub_container_catalog VARCHAR(36), trk_change_container_id VARCHAR(36), trk_change_container_name VARCHAR(255), trk_change_container_route_key VARCHAR(60), trk_sub_container_name VARCHAR(255), trk_sandbox_change_type VARCHAR(60), trk_sandbox_context_id VARCHAR(36), trk_sandbox_name VARCHAR(255), trk_sandbox_owner VARCHAR(60), trk_sandbox_stage VARCHAR(60), trk_tenant_id VARCHAR(36), CONSTRAINT blc_order_note_pkey PRIMARY KEY (id));
CREATE TABLE blc_resource_lock (id VARCHAR(36) NOT NULL, concept_key VARCHAR(255), context_id VARCHAR(36), lock_expiration_timestamp TIMESTAMP WITHOUT TIME ZONE, locked CHAR(1) DEFAULT 'N', sandbox_id VARCHAR(36), lock_timestamp TIMESTAMP WITHOUT TIME ZONE, token VARCHAR(255), type_alias VARCHAR(255), CONSTRAINT blc_resource_lock_pkey PRIMARY KEY (id));
CREATE TABLE blc_return_auth (id VARCHAR(36) NOT NULL, currency VARCHAR(255), est_fulfill_total_to_refund numeric(19, 5), est_merch_total_to_refund numeric(19, 5), est_return_shipping_charge numeric(19, 5), est_tax_total_to_refund numeric(19, 5), est_total_to_refund numeric(19, 5), fulfill_total_refunded numeric(19, 5), merch_total_refunded numeric(19, 5), order_id VARCHAR(255), return_shipping_charged numeric(19, 5), status VARCHAR(255), tax_total_refunded numeric(19, 5), total_failed_to_refund numeric(19, 5), total_refund_awaiting_result numeric(19, 5), total_refunded numeric(19, 5), trk_application_context_id VARCHAR(36), trk_application_default_locale VARCHAR(255), trk_application_name VARCHAR(255), trk_archived CHAR(1) DEFAULT 'N' NOT NULL, audit_creation_time TIMESTAMP WITHOUT TIME ZONE, audit_creator VARCHAR(60), audit_update_time TIMESTAMP WITHOUT TIME ZONE, audit_updater VARCHAR(60), trk_change_details VARCHAR(10485760), trk_creating_application_id VARCHAR(36), trk_current_message VARCHAR(255), trk_level INTEGER, trk_max_field_versions VARCHAR(10485760), trk_sandbox_archived CHAR(1) DEFAULT 'N' NOT NULL, trk_sub_container_catalog VARCHAR(36), trk_change_container_id VARCHAR(36), trk_change_container_name VARCHAR(255), trk_change_container_route_key VARCHAR(60), trk_sub_container_name VARCHAR(255), trk_sandbox_change_type VARCHAR(60), trk_sandbox_context_id VARCHAR(36), trk_sandbox_name VARCHAR(255), trk_sandbox_owner VARCHAR(60), trk_sandbox_stage VARCHAR(60), trk_tenant_id VARCHAR(36), tracking_number VARCHAR(255), CONSTRAINT blc_return_auth_pkey PRIMARY KEY (id));
CREATE TABLE blc_return_confirmation (id VARCHAR(36) NOT NULL, actual_condition VARCHAR(60), fulfill_total_to_refund numeric(19, 5), merch_total_to_refund numeric(19, 5), note VARCHAR(65535), override_total_to_refund numeric(19, 5), quantity INTEGER NOT NULL, refund_fulfillment_charge CHAR(1) DEFAULT 'N' NOT NULL, status VARCHAR(60), tax_total_to_refund numeric(19, 5), total_failed_to_refund numeric(19, 5), total_refund_awaiting_result numeric(19, 5), total_refunded numeric(19, 5), total_to_refund numeric(19, 5), return_auth_item_id VARCHAR(36) NOT NULL, CONSTRAINT blc_return_confirmation_pkey PRIMARY KEY (id));
CREATE TABLE blc_return_auth_item (id VARCHAR(36) NOT NULL, est_fulfill_total_to_refund numeric(19, 5), est_merch_total_to_refund numeric(19, 5), est_tax_total_to_refund numeric(19, 5), est_total_to_refund numeric(19, 5), expected_condition VARCHAR(60), note VARCHAR(65535), order_fulfillment_item_id VARCHAR(36), primary_return_reason VARCHAR(500), quantity INTEGER NOT NULL, return_type VARCHAR(60), secondary_return_reason VARCHAR(500), return_auth_id VARCHAR(36) NOT NULL, CONSTRAINT blc_return_auth_item_pkey PRIMARY KEY (id));
ALTER TABLE blc_order ADD CONSTRAINT ukenh5h5jkisotuuqfjvdh3mt9u UNIQUE (cart_id);
ALTER TABLE blc_resource_lock ADD CONSTRAINT uk7iegjt4gojv24q9bw8k0i4j0c UNIQUE (context_id, type_alias, sandbox_id, concept_key);
ALTER TABLE blc_order_fulfill_item_tax_detail ADD CONSTRAINT fk1ewfxupk0ol1fjrlrdvvucmfy FOREIGN KEY (fulfillment_item_id) REFERENCES blc_order_fulfillment_item (id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE blc_order_fulfillment_item ADD CONSTRAINT fk7580e30p8bigbse3w2cgeqprs FOREIGN KEY (order_fulfillment_id) REFERENCES blc_order_fulfillment (id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE blc_application_catalog ADD CONSTRAINT fkdaeorqo87oh0rdu1lfkpv3qow FOREIGN KEY (application_id) REFERENCES blc_application (id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE blc_return_confirmation ADD CONSTRAINT fken1a07dpla99icvgws8bgfdid FOREIGN KEY (return_auth_item_id) REFERENCES blc_return_auth_item (id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE blc_order_item ADD CONSTRAINT fkgi0gu1f14t21cda3qyop5mobv FOREIGN KEY (order_id) REFERENCES blc_order (id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE blc_return_auth_item ADD CONSTRAINT fksxk3jvexnqxdtgq1xk334xakg FOREIGN KEY (return_auth_id) REFERENCES blc_return_auth (id) ON UPDATE NO ACTION ON DELETE NO ACTION;