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();