74 lines
2.9 KiB
SQL
74 lines
2.9 KiB
SQL
|
-- +goose Up
|
||
|
|
CREATE TABLE woo_orders (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
external_id BIGINT NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT '',
|
||
|
|
currency TEXT NOT NULL DEFAULT '',
|
||
|
|
total NUMERIC(14, 2),
|
||
|
|
customer_id BIGINT,
|
||
|
|
customer_email TEXT,
|
||
|
|
customer_name TEXT,
|
||
|
|
ordered_at TIMESTAMPTZ,
|
||
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE (company_id, external_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX woo_orders_company_email_idx ON woo_orders (company_id, lower(customer_email));
|
||
|
|
CREATE INDEX woo_orders_company_status_idx ON woo_orders (company_id, status);
|
||
|
|
CREATE INDEX woo_orders_company_ordered_idx ON woo_orders (company_id, ordered_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE woo_order_items (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
order_id UUID NOT NULL REFERENCES woo_orders(id) ON DELETE CASCADE,
|
||
|
|
external_id BIGINT NOT NULL,
|
||
|
|
product_id BIGINT,
|
||
|
|
variation_id BIGINT,
|
||
|
|
sku TEXT NOT NULL DEFAULT '',
|
||
|
|
name TEXT NOT NULL DEFAULT '',
|
||
|
|
quantity INT NOT NULL DEFAULT 1,
|
||
|
|
total NUMERIC(14, 2),
|
||
|
|
categories JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE (company_id, order_id, external_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX woo_order_items_company_product_idx ON woo_order_items (company_id, product_id);
|
||
|
|
CREATE INDEX woo_order_items_company_sku_idx ON woo_order_items (company_id, sku) WHERE sku <> '';
|
||
|
|
CREATE INDEX woo_order_items_categories_gin ON woo_order_items USING GIN (categories);
|
||
|
|
|
||
|
|
CREATE TABLE product_reviews (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
external_id BIGINT NOT NULL,
|
||
|
|
product_id BIGINT,
|
||
|
|
product_name TEXT NOT NULL DEFAULT '',
|
||
|
|
status TEXT NOT NULL DEFAULT '',
|
||
|
|
reviewer TEXT NOT NULL DEFAULT '',
|
||
|
|
reviewer_email TEXT NOT NULL DEFAULT '',
|
||
|
|
rating INT,
|
||
|
|
review TEXT NOT NULL DEFAULT '',
|
||
|
|
reviewed_at TIMESTAMPTZ,
|
||
|
|
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE (company_id, external_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX product_reviews_company_product_idx ON product_reviews (company_id, product_id);
|
||
|
|
CREATE INDEX product_reviews_company_rating_idx ON product_reviews (company_id, rating);
|
||
|
|
CREATE INDEX product_reviews_company_status_idx ON product_reviews (company_id, status);
|
||
|
|
CREATE INDEX product_reviews_company_reviewed_idx ON product_reviews (company_id, reviewed_at DESC);
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
DROP TABLE IF EXISTS woo_order_items;
|
||
|
|
DROP TABLE IF EXISTS product_reviews;
|
||
|
|
DROP TABLE IF EXISTS woo_orders;
|