66 lines
2.4 KiB
SQL
66 lines
2.4 KiB
SQL
CREATE TYPE product_status AS ENUM ('draft', 'published', 'unpublished');
|
|
|
|
-- rate_to_base: units of this currency per 1 unit of the base currency
|
|
-- (USD base: CNY 7.2 means 1 USD = 7.2 CNY).
|
|
CREATE TABLE currencies (
|
|
code CHAR(3) PRIMARY KEY,
|
|
name JSONB NOT NULL,
|
|
symbol TEXT NOT NULL,
|
|
exponent SMALLINT NOT NULL,
|
|
is_base BOOLEAN NOT NULL DEFAULT FALSE,
|
|
rate_to_base NUMERIC(20, 8) NOT NULL CHECK (rate_to_base > 0),
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
INSERT INTO currencies (code, name, symbol, exponent, is_base, rate_to_base)
|
|
VALUES
|
|
('USD', '{"en": "US Dollar", "zh": "美元"}', '$', 2, TRUE, 1),
|
|
('CNY', '{"en": "Chinese Yuan", "zh": "人民币"}', '¥', 2, FALSE, 7.2),
|
|
('EUR', '{"en": "Euro", "zh": "欧元"}', '€', 2, FALSE, 0.92),
|
|
('JPY', '{"en": "Japanese Yen", "zh": "日元"}', '¥', 0, FALSE, 150);
|
|
|
|
CREATE TABLE categories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
parent_id UUID REFERENCES categories (id),
|
|
name JSONB NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
position INT NOT NULL DEFAULT 0
|
|
);
|
|
|
|
INSERT INTO categories (name, slug, position)
|
|
VALUES
|
|
('{"en": "Electronics", "zh": "电子产品"}', 'electronics', 1),
|
|
('{"en": "Fashion", "zh": "时尚服饰"}', 'fashion', 2),
|
|
('{"en": "Home & Living", "zh": "家居生活"}', 'home-living', 3);
|
|
|
|
CREATE TABLE products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
shop_id UUID NOT NULL REFERENCES shops (id),
|
|
category_id UUID REFERENCES categories (id),
|
|
slug TEXT NOT NULL,
|
|
name JSONB NOT NULL,
|
|
description JSONB NOT NULL DEFAULT '{}',
|
|
images JSONB NOT NULL DEFAULT '[]',
|
|
status product_status NOT NULL DEFAULT 'draft',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (shop_id, slug)
|
|
);
|
|
|
|
CREATE INDEX idx_products_status ON products (status);
|
|
CREATE INDEX idx_products_category ON products (category_id);
|
|
|
|
CREATE TABLE skus (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
product_id UUID NOT NULL REFERENCES products (id) ON DELETE CASCADE,
|
|
sku_code TEXT NOT NULL,
|
|
attributes JSONB NOT NULL DEFAULT '{}',
|
|
price_minor BIGINT NOT NULL CHECK (price_minor >= 0),
|
|
currency CHAR(3) NOT NULL REFERENCES currencies (code),
|
|
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
UNIQUE (product_id, sku_code)
|
|
);
|
|
|
|
CREATE INDEX idx_skus_product ON skus (product_id);
|