bazzar/migrations/20230603073510_init.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA
    COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';

CREATE TYPE payment_collection_statuses AS ENUM (
    'not_paid',
    'awaiting',
    'authorized',
    'partially_authorized',
    'canceled'
    );

CREATE TYPE payment_collection_types AS ENUM (
    'order_edit'
    );

CREATE TYPE claim_item_reasons AS ENUM (
    'missing_item',
    'wrong_item',
    'production_failure',
    'other'
    );

CREATE TYPE claim_order_fulfillment_statuses AS ENUM (
    'not_fulfilled',
    'partially_fulfilled',
    'fulfilled',
    'partially_shipped',
    'shipped',
    'partially_returned',
    'returned',
    'canceled',
    'requires_action'
    );

CREATE TYPE claim_order_payment_statuses AS ENUM (
    'na',
    'not_refunded',
    'refunded'
    );

CREATE TYPE claim_order_types AS ENUM (
    'refund',
    'replace'
    );

CREATE TYPE discount_condition_operators AS ENUM (
    'in',
    'not_in'
    );

CREATE TYPE discount_condition_types AS ENUM (
    'products',
    'product_types',
    'product_collections',
    'product_tags',
    'customer_groups'
    );

CREATE TYPE discount_rule_allocations AS ENUM (
    'total',
    'item'
    );

CREATE TYPE discount_rule_types AS ENUM (
    'fixed',
    'percentage',
    'free_shipping'
    );

CREATE TYPE draft_order_statuses AS ENUM (
    'open',
    'completed'
    );

CREATE TYPE invite_roles AS ENUM (
    'admin',
    'member',
    'developer'
    );

CREATE TYPE order_fulfillment_statuses AS ENUM (
    'not_fulfilled',
    'partially_fulfilled',
    'fulfilled',
    'partially_shipped',
    'shipped',
    'partially_returned',
    'returned',
    'canceled',
    'requires_action'
    );

CREATE TYPE order_item_change_types AS ENUM (
    'item_add',
    'item_remove',
    'item_update'
    );

CREATE TYPE order_payment_statuses AS ENUM (
    'not_paid',
    'awaiting',
    'captured',
    'partially_refunded',
    'refunded',
    'canceled',
    'requires_action'
    );

CREATE TYPE order_statuses AS ENUM (
    'pending',
    'completed',
    'archived',
    'canceled',
    'requires_action'
    );

CREATE TYPE payment_session_statuses AS ENUM (
    'authorized',
    'pending',
    'requires_more',
    'error',
    'canceled'
    );

CREATE TYPE price_list_statuses AS ENUM (
    'active',
    'draft'
    );
CREATE TYPE price_list_types AS ENUM (
    'sale',
    'override'
    );

CREATE TYPE product_statuses AS ENUM (
    'draft',
    'proposed',
    'published',
    'rejected'
    );

CREATE TYPE refund_reasons AS ENUM (
    'discount',
    'return',
    'swap',
    'claim',
    'other'
    );

CREATE TYPE return_statuses AS ENUM (
    'requested',
    'received',
    'requires_action',
    'canceled'
    );

CREATE TYPE shipping_option_price_types AS ENUM (
    'flat_rate',
    'calculated'
    );

CREATE TYPE shipping_option_requirement_types AS ENUM (
    'min_subtotal',
    'max_subtotal'
    );

CREATE TYPE shipping_profile_types AS ENUM (
    'default',
    'gift_card',
    'custom'
    );

CREATE TYPE swap_fulfillment_statuses AS ENUM (
    'not_fulfilled',
    'fulfilled',
    'shipped',
    'partially_shipped',
    'canceled',
    'requires_action'
    );

CREATE TYPE swap_payment_statuses AS ENUM (
    'not_paid',
    'awaiting',
    'captured',
    'confirmed',
    'canceled',
    'difference_refunded',
    'partially_refunded',
    'refunded',
    'requires_action'
    );

CREATE TABLE analytics_configs
(
    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,
    user_id    uuid                                   NOT NULL,
    opt_out    boolean                  DEFAULT false NOT NULL,
    anonymize  boolean                  DEFAULT false NOT NULL
);

CREATE TABLE batch_jobs
(
    id               uuid                                   NOT NULL,
    type             text                                   NOT NULL,
    created_by       character varying,
    context          jsonb,
    result           jsonb,
    dry_run          boolean                  DEFAULT false NOT NULL,
    created_at       timestamp with time zone DEFAULT now() NOT NULL,
    pre_processed_at timestamp with time zone,
    confirmed_at     timestamp with time zone,
    processing_at    timestamp with time zone,
    completed_at     timestamp with time zone,
    failed_at        timestamp with time zone,
    canceled_at      timestamp with time zone,
    updated_at       timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at       timestamp with time zone
);

CREATE TABLE claim_images
(
    id            uuid                                   NOT NULL,
    claim_item_id uuid                                   NOT NULL,
    url           character varying                      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 claim_items
(
    id             uuid                                   NOT NULL,
    claim_order_id uuid                                   NOT NULL,
    item_id        uuid                                   NOT NULL,
    variant_id     uuid                                   NOT NULL,
    reason         claim_item_reasons                     NOT NULL,
    note           character varying,
    quantity       integer                                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 claim_item_tags
(
    item_id uuid NOT NULL,
    tag_id  uuid NOT NULL
);

CREATE TABLE claim_orders
(
    id                  uuid                                                                                       NOT NULL,
    payment_status      claim_order_payment_statuses     DEFAULT 'na'::claim_order_payment_statuses                NOT NULL,
    fulfillment_status  claim_order_fulfillment_statuses DEFAULT 'not_fulfilled'::claim_order_fulfillment_statuses NOT NULL,
    type                claim_order_types                                                                          NOT NULL,
    order_id            uuid                                                                                       NOT NULL,
    shipping_address_id uuid,
    refund_amount       integer,
    canceled_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,
    idempotency_key     character varying,
    no_notification     boolean
);

CREATE TABLE claim_tags
(
    id         uuid                                   NOT NULL,
    value      character varying                      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 orders
(
    id                  uuid                                                                           NOT NULL,
    status              order_statuses             DEFAULT 'pending'::order_statuses                   NOT NULL,
    fulfillment_status  order_fulfillment_statuses DEFAULT 'not_fulfilled'::order_fulfillment_statuses NOT NULL,
    payment_status      order_payment_statuses     DEFAULT 'not_paid'::order_payment_statuses          NOT NULL,
    display_id          integer                                                                        NOT NULL,
    cart_id             uuid,
    customer_id         uuid                                                                           NOT NULL,
    email               character varying                                                              NOT NULL,
    billing_address_id  uuid,
    shipping_address_id uuid,
    region_id           uuid                                                                           NOT NULL,
    currency_code       character varying                                                              NOT NULL,
    tax_rate            real,
    canceled_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,
    metadata            jsonb,
    idempotency_key     character varying,
    draft_order_id      uuid,
    no_notification     boolean,
    external_id         uuid,
    sales_channel_id    uuid
);

CREATE TABLE order_discounts
(
    order_id    uuid NOT NULL,
    discount_id uuid NOT NULL
);

CREATE TABLE order_edits
(
    id                    uuid                                   NOT NULL,
    created_at            timestamp with time zone DEFAULT now() NOT NULL,
    updated_at            timestamp with time zone DEFAULT now() NOT NULL,
    order_id              uuid                                   NOT NULL,
    internal_note         character varying,
    created_by            character varying                      NOT NULL,
    requested_by          character varying,
    requested_at          timestamp with time zone,
    confirmed_by          character varying,
    confirmed_at          timestamp with time zone,
    declined_by           character varying,
    declined_reason       character varying,
    declined_at           timestamp with time zone,
    canceled_by           character varying,
    canceled_at           timestamp with time zone,
    payment_collection_id uuid
);

CREATE TABLE order_gift_cards
(
    order_id     uuid NOT NULL,
    gift_card_id uuid NOT NULL
);

CREATE TABLE order_item_changes
(
    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,
    type                  order_item_change_types                NOT NULL,
    order_edit_id         uuid                                   NOT NULL,
    original_line_item_id uuid,
    line_item_id          uuid
);

CREATE TABLE payments
(
    id              uuid                                   NOT NULL,
    swap_id         uuid,
    cart_id         uuid,
    order_id        uuid,
    amount          integer                                NOT NULL,
    currency_code   character varying                      NOT NULL,
    amount_refunded integer                  DEFAULT 0     NOT NULL,
    provider_id     uuid                                   NOT NULL,
    data            jsonb                                  NOT NULL,
    captured_at     timestamp with time zone,
    canceled_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,
    metadata        jsonb,
    idempotency_key character varying
);

CREATE TABLE payment_collections
(
    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,
    type              payment_collection_types               NOT NULL,
    status            payment_collection_statuses            NOT NULL,
    description       text,
    amount            integer                                NOT NULL,
    authorized_amount integer,
    region_id         uuid                                   NOT NULL,
    currency_code     character varying                      NOT NULL,
    metadata          jsonb,
    created_by        character varying                      NOT NULL
);

CREATE TABLE payment_collection_payments
(
    payment_collection_id uuid NOT NULL,
    payment_id            uuid NOT NULL
);

CREATE TABLE payment_collection_sessions
(
    payment_collection_id uuid NOT NULL,
    payment_session_id    uuid NOT NULL
);

CREATE TABLE payment_providers
(
    id           uuid                 NOT NULL,
    is_installed boolean DEFAULT true NOT NULL
);

CREATE TABLE payment_sessions
(
    id                    uuid                                   NOT NULL,
    cart_id               uuid,
    provider_id           uuid                                   NOT NULL,
    is_selected           boolean,
    status                payment_session_statuses               NOT NULL,
    data                  jsonb                                  NOT NULL,
    created_at            timestamp with time zone DEFAULT now() NOT NULL,
    updated_at            timestamp with time zone DEFAULT now() NOT NULL,
    idempotency_key       character varying,
    payment_authorized_at timestamp with time zone,
    amount                integer,
    is_initiated          boolean                  DEFAULT false NOT NULL
);

CREATE TABLE line_items
(
    id                 uuid                                   NOT NULL,
    cart_id            uuid,
    order_id           uuid,
    swap_id            uuid,
    title              character varying                      NOT NULL,
    description        character varying,
    thumbnail          character varying,
    is_giftcard        boolean                  DEFAULT false NOT NULL,
    should_merge       boolean                  DEFAULT true  NOT NULL,
    allow_discounts    boolean                  DEFAULT true  NOT NULL,
    has_shipping       boolean,
    unit_price         integer                                NOT NULL,
    variant_id         uuid,
    quantity           integer                                NOT NULL,
    fulfilled_quantity integer,
    returned_quantity  integer,
    shipped_quantity   integer,
    created_at         timestamp with time zone DEFAULT now() NOT NULL,
    updated_at         timestamp with time zone DEFAULT now() NOT NULL,
    metadata           jsonb,
    claim_order_id     uuid,
    is_return          boolean                  DEFAULT false NOT NULL,
    original_item_id   uuid,
    order_edit_id      uuid,
    CONSTRAINT "CHK_0cd85e15610d11b553d5e8fda6" CHECK ((shipped_quantity <= fulfilled_quantity)),
    CONSTRAINT "CHK_64eef00a5064887634f1680866" CHECK ((quantity > 0)),
    CONSTRAINT "CHK_91f40396d847f6ecfd9f752bf8" CHECK ((returned_quantity <= quantity)),
    CONSTRAINT "CHK_c61716c68f5ad5de2834c827d3" CHECK ((fulfilled_quantity <= quantity))
);

CREATE TABLE line_item_adjustments
(
    id          uuid              NOT NULL,
    item_id     uuid              NOT NULL,
    description character varying NOT NULL,
    discount_id uuid,
    amount      numeric           NOT NULL,
    metadata    jsonb
);

CREATE TABLE line_item_tax_lines
(
    id         uuid                                   NOT NULL,
    rate       real                                   NOT NULL,
    name       character varying                      NOT NULL,
    code       character varying,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata   jsonb,
    item_id    uuid                                   NOT NULL
);

---- ###########################################################
---- ###########################################################
---- ###########################################################
---- ###########################################################
---- ###########################################################
---- ###########################################################
---- ###########################################################
---- ###########################################################

CREATE TABLE draft_orders
(
    id                    uuid                                                          NOT NULL,
    status                draft_order_statuses     DEFAULT 'open'::draft_order_statuses NOT NULL,
    display_id            sequence                                                      NOT NULL,
    cart_id               uuid,
    order_id              uuid,
    canceled_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,
    completed_at          timestamp with time zone,
    metadata              jsonb,
    idempotency_key       character varying,
    no_notification_order boolean
);

CREATE TABLE fulfillments
(
    id               uuid                                         NOT NULL,
    swap_id          uuid,
    order_id         uuid,
    tracking_numbers jsonb                    DEFAULT '[]'::jsonb NOT NULL,
    data             jsonb                                        NOT NULL,
    shipped_at       timestamp with time zone,
    canceled_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,
    metadata         jsonb,
    idempotency_key  character varying,
    provider_id      uuid,
    claim_order_id   uuid,
    no_notification  boolean,
    location_id      uuid
);

CREATE TABLE fulfillment_items
(
    fulfillment_id uuid    NOT NULL,
    item_id        uuid    NOT NULL,
    quantity       integer NOT NULL
);

CREATE TABLE fulfillment_providers
(
    id           uuid                 NOT NULL,
    is_installed boolean DEFAULT true NOT NULL
);

CREATE TABLE images
(
    id         uuid                                   NOT NULL,
    url        character varying                      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 invites
(
    id         uuid                                   NOT NULL,
    user_email character varying                      NOT NULL,
    role       invite_roles             DEFAULT 'member'::invite_roles,
    accepted   boolean                  DEFAULT false 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,
    token      character varying                      NOT NULL,
    expires_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE notes
(
    id            uuid                                   NOT NULL,
    value         character varying                      NOT NULL,
    resource_type character varying                      NOT NULL,
    resource_id   uuid                                   NOT NULL,
    author_id     uuid,
    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 staged_jobs
(
    id         uuid                      NOT NULL,
    event_name character varying         NOT NULL,
    data       jsonb                     NOT NULL,
    options    jsonb DEFAULT '{}'::jsonb NOT NULL
);

CREATE TABLE swaps
(
    id                  uuid                                   NOT NULL,
    fulfillment_status  swap_fulfillment_statuses              NOT NULL,
    payment_status      swap_payment_statuses                  NOT NULL,
    order_id            uuid                                   NOT NULL,
    difference_due      integer,
    shipping_address_id uuid,
    cart_id             uuid,
    confirmed_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,
    idempotency_key     character varying,
    no_notification     boolean,
    canceled_at         timestamp with time zone,
    allow_backorder     boolean                  DEFAULT false NOT NULL
);