atlas-seller-kit/docs/architecture/sql/001_foundation.sql

-- Atlas Seller Kit: cloud foundation schema
-- PostgreSQL 15+ recommended. Run through a migration tool, not from the browser.

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS atlas;
SET search_path TO atlas, public;

CREATE TYPE membership_role AS ENUM ('owner', 'admin', 'editor', 'viewer');
CREATE TYPE account_status AS ENUM ('active', 'disabled', 'pending_review');
CREATE TYPE platform_status AS ENUM ('active', 'disabled');
CREATE TYPE store_status AS ENUM ('verifying', 'active', 'credential_invalid', 'sync_error', 'disabled', 'deleted');
CREATE TYPE credential_status AS ENUM ('unknown', 'valid', 'invalid', 'expired', 'rotation_required');
CREATE TYPE secret_kind AS ENUM ('seller_api_key', 'browser_cookie');
CREATE TYPE watermark_kind AS ENUM ('text', 'image');
CREATE TYPE watermark_position AS ENUM ('top_left', 'top_center', 'top_right', 'middle_left', 'center', 'middle_right', 'bottom_left', 'bottom_center', 'bottom_right');
CREATE TYPE record_status AS ENUM ('draft', 'active', 'archived', 'superseded');
CREATE TYPE rule_source AS ENUM ('manual_rule', 'competitor_capture', 'platform_sync', 'product_observed');
CREATE TYPE fulfillment_mode AS ENUM ('rfbs', 'fbs', 'fbo', 'fbp', 'unknown');
CREATE TYPE billing_weight_basis AS ENUM ('actual_only', 'max_actual_volume', 'conditional_volume');
CREATE TYPE quote_status AS ENUM ('draft', 'calculated', 'confirmed', 'invalid', 'superseded');

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone_e164 TEXT NOT NULL UNIQUE CHECK (phone_e164 ~ '^\\+[1-9][0-9]{5,14}$'),
    password_hash TEXT NOT NULL,
    display_name TEXT,
    status account_status NOT NULL DEFAULT 'active',
    phone_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE workspaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug CITEXT NOT NULL UNIQUE,
    status record_status NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE workspace_memberships (
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    role membership_role NOT NULL DEFAULT 'editor',
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (workspace_id, user_id)
);

CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    refresh_token_hash TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    ip_hash TEXT,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (expires_at > created_at)
);

CREATE TABLE platforms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code CITEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    status platform_status NOT NULL DEFAULT 'active',
    capabilities JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE currencies (
    code CHAR(3) PRIMARY KEY,
    name TEXT NOT NULL,
    symbol TEXT NOT NULL,
    decimal_places SMALLINT NOT NULL DEFAULT 2 CHECK (decimal_places BETWEEN 0 AND 6),
    is_enabled BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE platform_currency_support (
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE CASCADE,
    currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    is_enabled BOOLEAN NOT NULL DEFAULT true,
    PRIMARY KEY (platform_id, currency_code)
);

CREATE TABLE stores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE RESTRICT,
    name TEXT NOT NULL,
    platform_client_id TEXT NOT NULL,
    currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    is_default BOOLEAN NOT NULL DEFAULT false,
    note TEXT,
    status store_status NOT NULL DEFAULT 'verifying',
    credential_status credential_status NOT NULL DEFAULT 'unknown',
    credential_checked_at TIMESTAMPTZ,
    api_key_expires_at TIMESTAMPTZ,
    product_limit INTEGER CHECK (product_limit IS NULL OR product_limit >= 0),
    product_limit_checked_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, id),
    UNIQUE (workspace_id, id, platform_id),
    UNIQUE (workspace_id, platform_id, platform_client_id)
);
CREATE UNIQUE INDEX ux_stores_one_default_per_platform
    ON stores (workspace_id, platform_id)
    WHERE is_default AND deleted_at IS NULL;
CREATE INDEX ix_stores_workspace_platform ON stores (workspace_id, platform_id, created_at DESC);

CREATE TABLE store_secrets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    store_id UUID NOT NULL,
    kind secret_kind NOT NULL,
    secret_ciphertext BYTEA NOT NULL,
    secret_nonce BYTEA NOT NULL,
    encryption_key_version TEXT NOT NULL,
    fingerprint TEXT NOT NULL,
    status credential_status NOT NULL DEFAULT 'unknown',
    expires_at TIMESTAMPTZ,
    last_verified_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, store_id) REFERENCES stores(workspace_id, id) ON DELETE CASCADE,
    UNIQUE (workspace_id, store_id, kind, fingerprint)
);
CREATE UNIQUE INDEX ux_store_secrets_one_current_kind
    ON store_secrets (workspace_id, store_id, kind)
    WHERE revoked_at IS NULL;

CREATE TABLE store_warehouses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    store_id UUID NOT NULL,
    platform_warehouse_id TEXT NOT NULL,
    name TEXT NOT NULL,
    fulfillment fulfillment_mode NOT NULL DEFAULT 'unknown',
    is_rfbs BOOLEAN,
    status TEXT NOT NULL,
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    synced_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, store_id) REFERENCES stores(workspace_id, id) ON DELETE CASCADE,
    UNIQUE (workspace_id, store_id, platform_warehouse_id)
);

CREATE TABLE media_assets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    object_key TEXT NOT NULL,
    content_type TEXT NOT NULL,
    byte_size BIGINT NOT NULL CHECK (byte_size > 0),
    checksum_sha256 TEXT NOT NULL,
    width_px INTEGER CHECK (width_px IS NULL OR width_px > 0),
    height_px INTEGER CHECK (height_px IS NULL OR height_px > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, id),
    UNIQUE (workspace_id, object_key),
    UNIQUE (workspace_id, checksum_sha256)
);

CREATE TABLE watermark_fonts (
    code CITEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    status record_status NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE watermark_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    name TEXT NOT NULL,
    kind watermark_kind NOT NULL,
    current_version INTEGER NOT NULL DEFAULT 1 CHECK (current_version > 0),
    status record_status NOT NULL DEFAULT 'active',
    deleted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, id),
    UNIQUE (workspace_id, name)
);

CREATE TABLE watermark_template_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    template_id UUID NOT NULL,
    version INTEGER NOT NULL CHECK (version > 0),
    kind watermark_kind NOT NULL,
    text_content TEXT,
    image_asset_id UUID,
    font_code CITEXT REFERENCES watermark_fonts(code),
    font_size_px SMALLINT CHECK (font_size_px BETWEEN 12 AND 60),
    opacity NUMERIC(5,4) NOT NULL CHECK (opacity BETWEEN 0 AND 1),
    scale_ratio NUMERIC(7,4) CHECK (scale_ratio > 0),
    position watermark_position NOT NULL,
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, template_id) REFERENCES watermark_templates(workspace_id, id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, image_asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT,
    UNIQUE (workspace_id, template_id, version),
    CHECK (
        (kind = 'text' AND text_content IS NOT NULL AND font_code IS NOT NULL AND font_size_px IS NOT NULL AND image_asset_id IS NULL)
        OR (kind = 'image' AND image_asset_id IS NOT NULL AND scale_ratio IS NOT NULL AND text_content IS NULL)
    )
);

CREATE TABLE store_watermark_bindings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    store_id UUID NOT NULL,
    template_id UUID NOT NULL,
    bound_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    bound_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    unbound_at TIMESTAMPTZ,
    FOREIGN KEY (workspace_id, store_id) REFERENCES stores(workspace_id, id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, template_id) REFERENCES watermark_templates(workspace_id, id) ON DELETE RESTRICT,
    CHECK (unbound_at IS NULL OR unbound_at >= bound_at)
);
CREATE UNIQUE INDEX ux_store_watermark_one_active
    ON store_watermark_bindings (workspace_id, store_id)
    WHERE unbound_at IS NULL;

CREATE TABLE platform_product_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES platform_product_categories(id) ON DELETE RESTRICT,
    external_category_id TEXT NOT NULL,
    external_type_id TEXT,
    name TEXT NOT NULL,
    path TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    source_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    synced_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (platform_id, id),
    UNIQUE (platform_id, external_category_id, external_type_id)
);

CREATE TABLE commission_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES commission_categories(id) ON DELETE RESTRICT,
    source_category_key TEXT,
    name TEXT NOT NULL,
    path TEXT NOT NULL,
    source rule_source NOT NULL,
    status record_status NOT NULL DEFAULT 'draft',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (platform_id, id),
    UNIQUE (platform_id, path)
);

CREATE TABLE commission_category_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commission_category_id UUID NOT NULL REFERENCES commission_categories(id) ON DELETE CASCADE,
    fulfillment fulfillment_mode NOT NULL DEFAULT 'unknown',
    market_code TEXT NOT NULL DEFAULT 'RU',
    currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    min_price NUMERIC(18,4),
    max_price NUMERIC(18,4),
    min_inclusive BOOLEAN NOT NULL DEFAULT false,
    max_inclusive BOOLEAN NOT NULL DEFAULT true,
    commission_rate NUMERIC(7,6) NOT NULL CHECK (commission_rate >= 0 AND commission_rate < 1),
    source rule_source NOT NULL,
    version TEXT NOT NULL,
    status record_status NOT NULL DEFAULT 'draft',
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (min_price IS NULL OR min_price >= 0),
    CHECK (max_price IS NULL OR max_price >= 0),
    CHECK (min_price IS NULL OR max_price IS NULL OR min_price <= max_price),
    CHECK (effective_to IS NULL OR effective_to > effective_from)
);

CREATE TABLE platform_product_category_commission_mappings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE CASCADE,
    platform_product_category_id UUID NOT NULL,
    commission_category_id UUID NOT NULL,
    fulfillment fulfillment_mode NOT NULL DEFAULT 'unknown',
    market_code TEXT NOT NULL DEFAULT 'RU',
    priority SMALLINT NOT NULL DEFAULT 100 CHECK (priority >= 0),
    source rule_source NOT NULL DEFAULT 'manual_rule',
    status record_status NOT NULL DEFAULT 'draft',
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (platform_id, platform_product_category_id)
        REFERENCES platform_product_categories(platform_id, id) ON DELETE CASCADE,
    FOREIGN KEY (platform_id, commission_category_id)
        REFERENCES commission_categories(platform_id, id) ON DELETE RESTRICT,
    CHECK (effective_to IS NULL OR effective_to > effective_from)
);
CREATE INDEX ix_category_commission_mapping_lookup
    ON platform_product_category_commission_mappings (platform_product_category_id, fulfillment, market_code, status, effective_from DESC, priority);

CREATE TABLE logistics_providers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code CITEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    status record_status NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE logistics_rate_cards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_id UUID NOT NULL REFERENCES logistics_providers(id) ON DELETE RESTRICT,
    platform_id UUID NOT NULL REFERENCES platforms(id) ON DELETE RESTRICT,
    fulfillment fulfillment_mode NOT NULL,
    market_code TEXT NOT NULL DEFAULT 'RU',
    version TEXT NOT NULL,
    source_name TEXT NOT NULL,
    source_uri TEXT,
    status record_status NOT NULL DEFAULT 'draft',
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ,
    reviewed_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (platform_id, id),
    UNIQUE (provider_id, platform_id, fulfillment, market_code, version),
    CHECK (effective_to IS NULL OR effective_to > effective_from)
);

CREATE TABLE logistics_product_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rate_card_id UUID NOT NULL REFERENCES logistics_rate_cards(id) ON DELETE CASCADE,
    code CITEXT NOT NULL,
    name TEXT NOT NULL,
    merchandise_currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    min_merchandise_value NUMERIC(18,4),
    max_merchandise_value NUMERIC(18,4),
    min_value_inclusive BOOLEAN NOT NULL DEFAULT false,
    max_value_inclusive BOOLEAN NOT NULL DEFAULT true,
    min_actual_weight_kg NUMERIC(12,3) NOT NULL DEFAULT 0.001 CHECK (min_actual_weight_kg >= 0.001),
    max_actual_weight_kg NUMERIC(12,3),
    max_sum_dimensions_cm NUMERIC(12,3),
    max_longest_side_cm NUMERIC(12,3),
    max_second_longest_side_cm NUMERIC(12,3),
    billing_basis billing_weight_basis NOT NULL,
    volume_divisor NUMERIC(12,3),
    volume_condition_sum_gt_cm NUMERIC(12,3),
    volume_round_scale SMALLINT CHECK (volume_round_scale BETWEEN 0 AND 6),
    billing_round_scale SMALLINT CHECK (billing_round_scale BETWEEN 0 AND 6),
    max_volume_weight_kg NUMERIC(12,3),
    volume_limit_round_scale SMALLINT CHECK (volume_limit_round_scale BETWEEN 0 AND 6),
    return_policy JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (rate_card_id, code),
    CHECK (max_merchandise_value IS NULL OR max_merchandise_value >= 0),
    CHECK (min_merchandise_value IS NULL OR min_merchandise_value >= 0),
    CHECK (min_merchandise_value IS NULL OR max_merchandise_value IS NULL OR min_merchandise_value <= max_merchandise_value),
    CHECK (max_actual_weight_kg IS NULL OR max_actual_weight_kg >= min_actual_weight_kg),
    CHECK ((billing_basis = 'actual_only' AND volume_divisor IS NULL) OR (billing_basis <> 'actual_only' AND volume_divisor IS NOT NULL))
);

CREATE TABLE logistics_transport_methods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rate_card_id UUID NOT NULL REFERENCES logistics_rate_cards(id) ON DELETE CASCADE,
    code CITEXT NOT NULL,
    name TEXT NOT NULL,
    speed_label TEXT,
    min_transit_days SMALLINT CHECK (min_transit_days >= 0),
    max_transit_days SMALLINT CHECK (max_transit_days >= min_transit_days),
    status record_status NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (rate_card_id, code)
);

CREATE TABLE logistics_rate_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_type_id UUID NOT NULL REFERENCES logistics_product_types(id) ON DELETE CASCADE,
    transport_method_id UUID NOT NULL REFERENCES logistics_transport_methods(id) ON DELETE CASCADE,
    fixed_fee_cny NUMERIC(18,4) NOT NULL DEFAULT 0 CHECK (fixed_fee_cny >= 0),
    per_billable_kg_fee_cny NUMERIC(18,4) NOT NULL CHECK (per_billable_kg_fee_cny >= 0),
    min_charge_cny NUMERIC(18,4) CHECK (min_charge_cny IS NULL OR min_charge_cny >= 0),
    surcharge_rules JSONB NOT NULL DEFAULT '[]'::jsonb,
    status record_status NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (product_type_id, transport_method_id)
);

CREATE TABLE pricing_policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    store_id UUID NOT NULL,
    platform_id UUID NOT NULL,
    name TEXT NOT NULL,
    note TEXT,
    status record_status NOT NULL DEFAULT 'active',
    is_default BOOLEAN NOT NULL DEFAULT false,
    rate_card_id UUID NOT NULL,
    default_transport_method_id UUID REFERENCES logistics_transport_methods(id) ON DELETE RESTRICT,
    domestic_shipping_and_label_fee_cny NUMERIC(18,4) NOT NULL DEFAULT 0 CHECK (domestic_shipping_and_label_fee_cny >= 0),
    ad_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (ad_rate >= 0 AND ad_rate < 1),
    last_mile_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (last_mile_rate >= 0 AND last_mile_rate < 1),
    withdrawal_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (withdrawal_rate >= 0 AND withdrawal_rate < 1),
    other_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (other_rate >= 0 AND other_rate < 1),
    desired_markup_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (desired_markup_rate >= 0),
    list_price_discount_rate NUMERIC(7,6) NOT NULL DEFAULT 0 CHECK (list_price_discount_rate >= 0 AND list_price_discount_rate < 1),
    fx_strategy TEXT NOT NULL DEFAULT 'system' CHECK (fx_strategy IN ('system', 'fixed', 'override_allowed')),
    fixed_cny_to_rub_rate NUMERIC(18,8),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, store_id, platform_id) REFERENCES stores(workspace_id, id, platform_id) ON DELETE CASCADE,
    FOREIGN KEY (platform_id, rate_card_id) REFERENCES logistics_rate_cards(platform_id, id) ON DELETE RESTRICT,
    UNIQUE (workspace_id, id),
    UNIQUE (workspace_id, id, store_id, platform_id),
    UNIQUE (workspace_id, store_id, name),
    CHECK ((fx_strategy = 'fixed' AND fixed_cny_to_rub_rate > 0) OR (fx_strategy <> 'fixed' AND fixed_cny_to_rub_rate IS NULL))
);
CREATE UNIQUE INDEX ux_pricing_policies_one_default
    ON pricing_policies (workspace_id, store_id)
    WHERE is_default AND status = 'active';

CREATE TABLE pricing_policy_transport_methods (
    workspace_id UUID NOT NULL,
    pricing_policy_id UUID NOT NULL,
    transport_method_id UUID NOT NULL REFERENCES logistics_transport_methods(id) ON DELETE RESTRICT,
    is_enabled BOOLEAN NOT NULL DEFAULT true,
    sort_order SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (workspace_id, pricing_policy_id, transport_method_id),
    FOREIGN KEY (workspace_id, pricing_policy_id) REFERENCES pricing_policies(workspace_id, id) ON DELETE CASCADE
);

CREATE TABLE pricing_quotes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    store_id UUID NOT NULL,
    platform_id UUID NOT NULL,
    pricing_policy_id UUID,
    product_variant_id UUID, -- Logical FK; add real FK in the product/SKU migration.
    platform_product_category_id UUID,
    fulfillment fulfillment_mode NOT NULL,
    purchase_cost_cny NUMERIC(18,4) NOT NULL CHECK (purchase_cost_cny >= 0),
    package_weight_kg NUMERIC(12,3) NOT NULL CHECK (package_weight_kg >= 0.001),
    package_length_cm NUMERIC(12,3) NOT NULL CHECK (package_length_cm > 0),
    package_width_cm NUMERIC(12,3) NOT NULL CHECK (package_width_cm > 0),
    package_height_cm NUMERIC(12,3) NOT NULL CHECK (package_height_cm > 0),
    fx_cny_to_rub NUMERIC(18,8) NOT NULL CHECK (fx_cny_to_rub > 0),
    input_snapshot JSONB NOT NULL,
    status quote_status NOT NULL DEFAULT 'draft',
    selected_option_id UUID,
    calculated_at TIMESTAMPTZ,
    confirmed_at TIMESTAMPTZ,
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, store_id, platform_id) REFERENCES stores(workspace_id, id, platform_id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, pricing_policy_id, store_id, platform_id)
        REFERENCES pricing_policies(workspace_id, id, store_id, platform_id) ON DELETE RESTRICT,
    FOREIGN KEY (platform_id, platform_product_category_id)
        REFERENCES platform_product_categories(platform_id, id) ON DELETE RESTRICT,
    UNIQUE (workspace_id, id)
);

CREATE TABLE pricing_quote_options (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL,
    quote_id UUID NOT NULL,
    logistics_rate_rule_id UUID REFERENCES logistics_rate_rules(id) ON DELETE SET NULL,
    commission_rule_id UUID REFERENCES commission_category_rules(id) ON DELETE SET NULL,
    logistics_product_type_id UUID REFERENCES logistics_product_types(id) ON DELETE SET NULL,
    transport_method_id UUID REFERENCES logistics_transport_methods(id) ON DELETE SET NULL,
    merchandise_value_cny NUMERIC(18,4) NOT NULL CHECK (merchandise_value_cny >= 0),
    selling_price_rub NUMERIC(18,4) NOT NULL CHECK (selling_price_rub >= 0),
    list_price_rub NUMERIC(18,4) NOT NULL CHECK (list_price_rub >= selling_price_rub),
    billable_weight_kg NUMERIC(12,3) NOT NULL CHECK (billable_weight_kg >= 0.001),
    logistics_fee_cny NUMERIC(18,4) NOT NULL CHECK (logistics_fee_cny >= 0),
    commission_fee_rub NUMERIC(18,4) NOT NULL CHECK (commission_fee_rub >= 0),
    domestic_fee_cny NUMERIC(18,4) NOT NULL CHECK (domestic_fee_cny >= 0),
    ad_fee_rub NUMERIC(18,4) NOT NULL CHECK (ad_fee_rub >= 0),
    last_mile_fee_rub NUMERIC(18,4) NOT NULL CHECK (last_mile_fee_rub >= 0),
    withdrawal_fee_rub NUMERIC(18,4) NOT NULL CHECK (withdrawal_fee_rub >= 0),
    other_fee_rub NUMERIC(18,4) NOT NULL CHECK (other_fee_rub >= 0),
    profit_rub NUMERIC(18,4) NOT NULL,
    calculation_snapshot JSONB NOT NULL,
    is_valid BOOLEAN NOT NULL DEFAULT true,
    validation_errors JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, quote_id) REFERENCES pricing_quotes(workspace_id, id) ON DELETE CASCADE,
    UNIQUE (workspace_id, quote_id, transport_method_id, logistics_product_type_id, commission_rule_id)
);
ALTER TABLE pricing_quotes
    ADD CONSTRAINT fk_pricing_quotes_selected_option
    FOREIGN KEY (selected_option_id) REFERENCES pricing_quote_options(id) ON DELETE SET NULL;

-- Tenant isolation. The API must set app.workspace_id with SET LOCAL after authorization.
CREATE OR REPLACE FUNCTION app_current_workspace_id() RETURNS UUID
LANGUAGE SQL STABLE AS $$
    SELECT NULLIF(current_setting('app.workspace_id', true), '')::UUID
$$;

DO $$
DECLARE table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'stores', 'store_secrets', 'store_warehouses', 'media_assets',
        'watermark_templates', 'watermark_template_versions', 'store_watermark_bindings',
        'pricing_policies', 'pricing_policy_transport_methods', 'pricing_quotes', 'pricing_quote_options'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
        EXECUTE format(
            'CREATE POLICY workspace_isolation ON %I USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id())',
            table_name
        );
    END LOOP;
END $$;