R524809
52 lines
4.4 KiB
PL/PgSQL
52 lines
4.4 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
ALTER TABLE logistics_product_types ADD COLUMN min_actual_weight_inclusive BOOLEAN NOT NULL DEFAULT true;
|
|
ALTER TABLE pricing_quotes ADD COLUMN product_id UUID, ADD COLUMN profile_id UUID, ADD COLUMN input_hash TEXT;
|
|
ALTER TABLE pricing_quotes ADD CONSTRAINT pricing_quote_variant_product_fk
|
|
FOREIGN KEY (workspace_id, product_variant_id, product_id) REFERENCES product_variants(workspace_id,id,product_id);
|
|
ALTER TABLE pricing_quotes ADD CONSTRAINT pricing_quote_profile_product_fk
|
|
FOREIGN KEY (workspace_id,profile_id,product_id,platform_id) REFERENCES product_platform_profiles(workspace_id,id,product_id,platform_id);
|
|
ALTER TABLE pricing_quotes ADD CONSTRAINT pricing_quote_binding_complete CHECK (
|
|
(product_variant_id IS NULL AND product_id IS NULL AND profile_id IS NULL) OR
|
|
(product_variant_id IS NOT NULL AND product_id IS NOT NULL AND profile_id IS NOT NULL));
|
|
ALTER TABLE pricing_quotes ADD CONSTRAINT pricing_quote_listing_identity UNIQUE(workspace_id,id,profile_id,product_variant_id,store_id,platform_id);
|
|
ALTER TABLE pricing_quote_options ADD CONSTRAINT pricing_option_identity UNIQUE(workspace_id,quote_id,id);
|
|
ALTER TABLE pricing_quotes DROP CONSTRAINT fk_pricing_quotes_selected_option;
|
|
ALTER TABLE pricing_quotes ADD CONSTRAINT pricing_quote_selected_option_fk
|
|
FOREIGN KEY(workspace_id,id,selected_option_id) REFERENCES pricing_quote_options(workspace_id,quote_id,id) DEFERRABLE INITIALLY DEFERRED;
|
|
ALTER TABLE product_variant_platform_listings
|
|
ADD COLUMN pricing_quote_id UUID,
|
|
ADD COLUMN pricing_draft JSONB,
|
|
ADD COLUMN pricing_valid BOOLEAN NOT NULL DEFAULT false;
|
|
ALTER TABLE product_variant_platform_listings ADD CONSTRAINT listing_pricing_quote_fk
|
|
FOREIGN KEY(workspace_id,pricing_quote_id,profile_id,product_variant_id,store_id,platform_id)
|
|
REFERENCES pricing_quotes(workspace_id,id,profile_id,product_variant_id,store_id,platform_id) DEFERRABLE INITIALLY DEFERRED;
|
|
|
|
CREATE TABLE pricing_previews (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL REFERENCES workspaces(id),
|
|
user_id UUID NOT NULL REFERENCES users(id), store_id UUID NOT NULL, platform_id UUID NOT NULL,
|
|
input JSONB NOT NULL, input_hash TEXT NOT NULL, rules_hash TEXT NOT NULL, result JSONB NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT now()+interval '15 minutes', created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
FOREIGN KEY(workspace_id,store_id,platform_id) REFERENCES stores(workspace_id,id,platform_id),
|
|
UNIQUE(workspace_id,id)
|
|
);
|
|
CREATE INDEX pricing_previews_expiry ON pricing_previews(expires_at);
|
|
ALTER TABLE pricing_previews ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE pricing_previews FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY workspace_isolation ON pricing_previews USING(workspace_id=app_current_workspace_id()) WITH CHECK(workspace_id=app_current_workspace_id());
|
|
|
|
-- 通用商品输入影响所有平台报价，其他平台必须同步失效；当前保存可在复核后恢复有效状态。
|
|
CREATE FUNCTION invalidate_variant_pricing() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF TG_TABLE_NAME='product_variants' THEN
|
|
UPDATE product_variant_platform_listings SET pricing_valid=false WHERE product_variant_id=NEW.id;
|
|
ELSE
|
|
UPDATE product_variant_platform_listings SET pricing_valid=false WHERE product_id=NEW.id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER variant_pricing_changed AFTER UPDATE OF purchase_cost_cny,package_length_mm,package_width_mm,package_height_mm,shipping_weight_g,package_spec_status ON product_variants
|
|
FOR EACH ROW WHEN (ROW(OLD.purchase_cost_cny,OLD.package_length_mm,OLD.package_width_mm,OLD.package_height_mm,OLD.shipping_weight_g,OLD.package_spec_status) IS DISTINCT FROM ROW(NEW.purchase_cost_cny,NEW.package_length_mm,NEW.package_width_mm,NEW.package_height_mm,NEW.shipping_weight_g,NEW.package_spec_status)) EXECUTE FUNCTION invalidate_variant_pricing();
|
|
CREATE TRIGGER product_pricing_changed AFTER UPDATE OF default_package_length_mm,default_package_width_mm,default_package_height_mm,default_shipping_weight_g,package_spec_status ON products
|
|
FOR EACH ROW WHEN (ROW(OLD.default_package_length_mm,OLD.default_package_width_mm,OLD.default_package_height_mm,OLD.default_shipping_weight_g,OLD.package_spec_status) IS DISTINCT FROM ROW(NEW.default_package_length_mm,NEW.default_package_width_mm,NEW.default_package_height_mm,NEW.default_shipping_weight_g,NEW.package_spec_status)) EXECUTE FUNCTION invalidate_variant_pricing();
|