bazzar/migrations/20230603073532_marketing.sql

CREATE TABLE discounts
(
    id                 uuid                                               NOT NULL,
    code               character varying                                  NOT NULL,
    is_dynamic         boolean                                            NOT NULL,
    rule_id            uuid,
    is_disabled        boolean                                            NOT NULL,
    parent_discount_id uuid,
    starts_at          timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    ends_at            timestamp with time zone,
    created_at         timestamp with time zone DEFAULT now()             NOT NULL,
    updated_at         timestamp with time zone DEFAULT now()             NOT NULL,
    deleted_at         timestamp with time zone,
    metadata           jsonb,
    usage_limit        integer,
    usage_count        integer                  DEFAULT 0                 NOT NULL,
    valid_duration     character varying
);

CREATE TABLE discount_conditions
(
    id               uuid                                   NOT NULL,
    type             discount_condition_types               NOT NULL,
    operator         discount_condition_operators           NOT NULL,
    discount_rule_id uuid                                   NOT NULL,
    created_at       timestamp with time zone DEFAULT now() NOT NULL,
    updated_at       timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at       timestamp with time zone,
    metadata         jsonb
);

CREATE TABLE discount_condition_customer_groups
(
    customer_group_id uuid                                   NOT NULL,
    condition_id      uuid                                   NOT NULL,
    created_at        timestamp with time zone DEFAULT now() NOT NULL,
    updated_at        timestamp with time zone DEFAULT now() NOT NULL,
    metadata          jsonb
);

CREATE TABLE discount_condition_products
(
    product_id   uuid                                   NOT NULL,
    condition_id uuid                                   NOT NULL,
    created_at   timestamp with time zone DEFAULT now() NOT NULL,
    updated_at   timestamp with time zone DEFAULT now() NOT NULL,
    metadata     jsonb
);

CREATE TABLE discount_condition_product_collections
(
    product_collection_id uuid                                   NOT NULL,
    condition_id          uuid                                   NOT NULL,
    created_at            timestamp with time zone DEFAULT now() NOT NULL,
    updated_at            timestamp with time zone DEFAULT now() NOT NULL,
    metadata              jsonb
);

CREATE TABLE discount_condition_product_tags
(
    product_tag_id uuid                                   NOT NULL,
    condition_id   uuid                                   NOT NULL,
    created_at     timestamp with time zone DEFAULT now() NOT NULL,
    updated_at     timestamp with time zone DEFAULT now() NOT NULL,
    metadata       jsonb
);

CREATE TABLE discount_condition_product_types
(
    product_type_id uuid                                   NOT NULL,
    condition_id    uuid                                   NOT NULL,
    created_at      timestamp with time zone DEFAULT now() NOT NULL,
    updated_at      timestamp with time zone DEFAULT now() NOT NULL,
    metadata        jsonb
);

CREATE TABLE discount_regions
(
    discount_id uuid NOT NULL,
    region_id   uuid NOT NULL
);

CREATE TABLE discount_rules
(
    id          uuid                                   NOT NULL,
    description character varying,
    type        discount_rule_types                    NOT NULL,
    value       integer                                NOT NULL,
    allocation  discount_rule_allocations,
    created_at  timestamp with time zone DEFAULT now() NOT NULL,
    updated_at  timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at  timestamp with time zone,
    metadata    jsonb
);

CREATE TABLE discount_rule_products
(
    discount_rule_id uuid NOT NULL,
    product_id       uuid NOT NULL
);

CREATE TABLE gift_cards
(
    id          uuid                                   NOT NULL,
    code        character varying                      NOT NULL,
    value       integer                                NOT NULL,
    balance     integer                                NOT NULL,
    region_id   uuid                                   NOT NULL,
    order_id    uuid,
    is_disabled boolean                  DEFAULT false NOT NULL,
    ends_at     timestamp with time zone,
    created_at  timestamp with time zone DEFAULT now() NOT NULL,
    updated_at  timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at  timestamp with time zone,
    metadata    jsonb,
    tax_rate    real
);

CREATE TABLE gift_card_transactions
(
    id           uuid                                   NOT NULL,
    gift_card_id uuid                                   NOT NULL,
    order_id     uuid                                   NOT NULL,
    amount       integer                                NOT NULL,
    created_at   timestamp with time zone DEFAULT now() NOT NULL,
    is_taxable   boolean,
    tax_rate     real
);

CREATE TABLE sales_channels
(
    id          uuid                                   NOT NULL,
    created_at  timestamp with time zone DEFAULT now() NOT NULL,
    updated_at  timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at  timestamp with time zone,
    name        character varying                      NOT NULL,
    description character varying,
    is_disabled boolean                  DEFAULT false NOT NULL,
    metadata    jsonb
);

CREATE TABLE sales_channel_locations
(
    id               uuid                                   NOT NULL,
    created_at       timestamp with time zone DEFAULT now() NOT NULL,
    updated_at       timestamp with time zone DEFAULT now() NOT NULL,
    sales_channel_id text                                   NOT NULL,
    location_id      text                                   NOT NULL,
    deleted_at       timestamp with time zone
);