174 lines
8.4 KiB
SQL
174 lines
8.4 KiB
SQL
|
CREATE TABLE IF NOT EXISTS supplier_media_resources (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
supplier VARCHAR(32) NOT NULL,
|
||
|
|
supplier_resource_id VARCHAR(64) NOT NULL,
|
||
|
|
model_id INT NOT NULL,
|
||
|
|
name VARCHAR(500) NOT NULL,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'unknown',
|
||
|
|
cost_price_cents BIGINT NOT NULL DEFAULT 0,
|
||
|
|
cost_prices_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
sale_price_label VARCHAR(64),
|
||
|
|
resource_url TEXT,
|
||
|
|
baidu_weight INT,
|
||
|
|
resource_remark TEXT,
|
||
|
|
customer_visible BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
channel_type VARCHAR(128),
|
||
|
|
region VARCHAR(128),
|
||
|
|
inclusion_effect VARCHAR(128),
|
||
|
|
link_type VARCHAR(128),
|
||
|
|
publish_rate VARCHAR(64),
|
||
|
|
delivery_speed VARCHAR(128),
|
||
|
|
supplier_updated_at TIMESTAMPTZ,
|
||
|
|
last_synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
raw_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
deleted_at TIMESTAMPTZ,
|
||
|
|
CONSTRAINT uk_supplier_media_resources_supplier_id UNIQUE (supplier, supplier_resource_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_media_resources_model_status
|
||
|
|
ON supplier_media_resources (supplier, model_id, status, updated_at DESC);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_media_resources_name_trgm_fallback
|
||
|
|
ON supplier_media_resources (supplier, name);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS supplier_media_price_overrides (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
resource_id BIGINT NOT NULL REFERENCES supplier_media_resources(id) ON DELETE CASCADE,
|
||
|
|
price_type VARCHAR(64) NOT NULL DEFAULT 'price',
|
||
|
|
sell_price_cents BIGINT NOT NULL,
|
||
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
updated_by BIGINT REFERENCES users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT chk_supplier_media_price_override_non_negative CHECK (sell_price_cents >= 0),
|
||
|
|
CONSTRAINT uk_supplier_media_price_overrides_resource_type UNIQUE (resource_id, price_type)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_media_price_overrides_resource
|
||
|
|
ON supplier_media_price_overrides (resource_id, enabled);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS media_supply_user_wallets (
|
||
|
|
tenant_id BIGINT NOT NULL REFERENCES tenants(id),
|
||
|
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
||
|
|
balance_cents BIGINT NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
PRIMARY KEY (tenant_id, user_id),
|
||
|
|
CONSTRAINT chk_media_supply_user_wallet_balance_non_negative CHECK (balance_cents >= 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS media_supply_orders (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
tenant_id BIGINT NOT NULL REFERENCES tenants(id),
|
||
|
|
workspace_id BIGINT NOT NULL REFERENCES workspaces(id),
|
||
|
|
brand_id BIGINT NOT NULL REFERENCES brands(id),
|
||
|
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
||
|
|
article_id BIGINT REFERENCES articles(id),
|
||
|
|
supplier VARCHAR(32) NOT NULL,
|
||
|
|
model_id INT NOT NULL,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'queued',
|
||
|
|
title VARCHAR(500) NOT NULL,
|
||
|
|
content_snapshot TEXT NOT NULL,
|
||
|
|
remark TEXT,
|
||
|
|
order_brand VARCHAR(255),
|
||
|
|
external_order_id VARCHAR(128),
|
||
|
|
external_order_code VARCHAR(128),
|
||
|
|
supplier_status VARCHAR(64),
|
||
|
|
cost_total_cents BIGINT NOT NULL DEFAULT 0,
|
||
|
|
sell_total_cents BIGINT NOT NULL DEFAULT 0,
|
||
|
|
wallet_debit_cents BIGINT NOT NULL DEFAULT 0,
|
||
|
|
wallet_debited_at TIMESTAMPTZ,
|
||
|
|
wallet_refunded_at TIMESTAMPTZ,
|
||
|
|
request_payload_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
response_payload_json JSONB,
|
||
|
|
error_message TEXT,
|
||
|
|
attempt_count INT NOT NULL DEFAULT 0,
|
||
|
|
next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
queued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
submitted_at TIMESTAMPTZ,
|
||
|
|
completed_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
deleted_at TIMESTAMPTZ,
|
||
|
|
CONSTRAINT chk_media_supply_order_totals_non_negative CHECK (cost_total_cents >= 0 AND sell_total_cents >= 0),
|
||
|
|
CONSTRAINT chk_media_supply_order_no_loss CHECK (sell_total_cents >= cost_total_cents),
|
||
|
|
CONSTRAINT chk_media_supply_order_wallet_debit_non_negative CHECK (wallet_debit_cents >= 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_orders_user_created
|
||
|
|
ON media_supply_orders (tenant_id, user_id, created_at DESC)
|
||
|
|
WHERE deleted_at IS NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_orders_status_next
|
||
|
|
ON media_supply_orders (supplier, status, next_attempt_at, created_at)
|
||
|
|
WHERE deleted_at IS NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_orders_article
|
||
|
|
ON media_supply_orders (tenant_id, article_id, created_at DESC)
|
||
|
|
WHERE article_id IS NOT NULL AND deleted_at IS NULL;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS media_supply_order_items (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
order_id BIGINT NOT NULL REFERENCES media_supply_orders(id) ON DELETE CASCADE,
|
||
|
|
resource_id BIGINT NOT NULL REFERENCES supplier_media_resources(id),
|
||
|
|
supplier_resource_id VARCHAR(64) NOT NULL,
|
||
|
|
price_type VARCHAR(64) NOT NULL DEFAULT 'price',
|
||
|
|
resource_name_snapshot VARCHAR(500) NOT NULL,
|
||
|
|
locked_cost_price_cents BIGINT NOT NULL,
|
||
|
|
locked_sell_price_cents BIGINT NOT NULL,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'queued',
|
||
|
|
external_article_url TEXT,
|
||
|
|
raw_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT chk_media_supply_order_item_prices_non_negative CHECK (locked_cost_price_cents >= 0 AND locked_sell_price_cents >= 0),
|
||
|
|
CONSTRAINT chk_media_supply_order_item_no_loss CHECK (locked_sell_price_cents >= locked_cost_price_cents)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_order_items_order
|
||
|
|
ON media_supply_order_items (order_id, id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS media_supply_wallet_ledgers (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
tenant_id BIGINT NOT NULL REFERENCES tenants(id),
|
||
|
|
user_id BIGINT NOT NULL REFERENCES users(id),
|
||
|
|
order_id BIGINT REFERENCES media_supply_orders(id) ON DELETE SET NULL,
|
||
|
|
delta_cents BIGINT NOT NULL,
|
||
|
|
balance_after_cents BIGINT NOT NULL,
|
||
|
|
reason VARCHAR(128) NOT NULL,
|
||
|
|
note TEXT,
|
||
|
|
created_by BIGINT REFERENCES users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT chk_media_supply_wallet_ledger_balance_non_negative CHECK (balance_after_cents >= 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_wallet_ledgers_user_created
|
||
|
|
ON media_supply_wallet_ledgers (tenant_id, user_id, created_at DESC);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_wallet_ledgers_user_recent
|
||
|
|
ON media_supply_wallet_ledgers (tenant_id, user_id, created_at DESC, id DESC);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_wallet_ledgers_order
|
||
|
|
ON media_supply_wallet_ledgers (order_id)
|
||
|
|
WHERE order_id IS NOT NULL;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS media_supply_sync_jobs (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
supplier VARCHAR(32) NOT NULL,
|
||
|
|
model_id INT,
|
||
|
|
status VARCHAR(32) NOT NULL DEFAULT 'queued',
|
||
|
|
requested_by BIGINT REFERENCES users(id),
|
||
|
|
started_at TIMESTAMPTZ,
|
||
|
|
finished_at TIMESTAMPTZ,
|
||
|
|
error_message TEXT,
|
||
|
|
attempt_count INT NOT NULL DEFAULT 0,
|
||
|
|
stats_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_supply_sync_jobs_status_created
|
||
|
|
ON media_supply_sync_jobs (supplier, status, created_at);
|