atlas-seller-kit/packages/database/migrations/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 TYPE exchange_rate_source AS ENUM ('cbr', 'exchange_rate_api_open', 'frankfurter', 'manual');
CREATE TYPE exchange_rate_kind AS ENUM ('official_reference', 'market_reference', 'manual');
CREATE TYPE fx_rate_origin AS ENUM ('system_rate', 'policy_fixed', 'quote_override');
CREATE TYPE operation_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');
CREATE TYPE media_upload_status AS ENUM ('pending', 'uploaded', 'validating', 'ready', 'failed', 'expired');
CREATE TYPE idempotency_request_status AS ENUM ('in_progress', 'completed');
CREATE TYPE outbox_event_status AS ENUM ('pending', 'dispatching', 'dispatched', 'dead_letter');

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email CITEXT NOT NULL UNIQUE CHECK (length(email) <= 254 AND position('@' IN email) > 1),
    password_hash TEXT NOT NULL,
    display_name TEXT,
    status account_status NOT NULL DEFAULT 'active',
    email_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)
);

-- Global immutable rate history. rate always means one unit of base_currency_code equals rate units of quote_currency_code.
CREATE TABLE exchange_rates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    base_currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    quote_currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    rate NUMERIC(24,10) NOT NULL CHECK (rate > 0),
    kind exchange_rate_kind NOT NULL,
    source exchange_rate_source NOT NULL,
    source_reference TEXT,
    effective_on DATE NOT NULL,
    observed_at TIMESTAMPTZ,
    fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (base_currency_code <> quote_currency_code),
    UNIQUE (base_currency_code, quote_currency_code, kind, source, effective_on)
);
CREATE INDEX ix_exchange_rates_current_lookup
    ON exchange_rates (base_currency_code, quote_currency_code, kind, effective_on DESC, fetched_at DESC);

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,
    last_error_code TEXT,
    last_error_message TEXT,
    last_error_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(),
    revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
    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)
);

-- Durable API-visible operation state. Queue job ids are auxiliary and never the only record.
CREATE TABLE operations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    kind TEXT NOT NULL,
    status operation_status NOT NULL DEFAULT 'queued',
    resource_type TEXT,
    resource_id UUID,
    result JSONB NOT NULL DEFAULT '{}'::jsonb,
    error_code TEXT,
    error_message TEXT,
    attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    CHECK (completed_at IS NULL OR completed_at >= created_at),
    UNIQUE (workspace_id, id)
);
CREATE INDEX ix_operations_workspace_status ON operations (workspace_id, status, created_at DESC);

-- Durable replay record for authenticated, retryable business writes.
-- request_fingerprint is an HMAC of canonical request content, never a raw request body.
CREATE TABLE idempotency_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    http_method TEXT NOT NULL CHECK (http_method IN ('POST', 'PUT', 'PATCH', 'DELETE')),
    request_path TEXT NOT NULL CHECK (request_path ~ '^/api/'),
    idempotency_key UUID NOT NULL,
    request_fingerprint TEXT NOT NULL,
    status idempotency_request_status NOT NULL DEFAULT 'in_progress',
    response_status SMALLINT,
    response_body JSONB,
    resource_type TEXT,
    resource_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL DEFAULT now() + INTERVAL '48 hours',
    CHECK (expires_at > created_at),
    CHECK (
        (status = 'in_progress' AND completed_at IS NULL AND response_status IS NULL)
        OR (status = 'completed' AND completed_at IS NOT NULL AND response_status BETWEEN 200 AND 599)
    ),
    UNIQUE (workspace_id, user_id, http_method, request_path, idempotency_key),
    UNIQUE (workspace_id, id)
);
CREATE INDEX ix_idempotency_requests_expiry
    ON idempotency_requests (expires_at);

-- Transactional Outbox. A dispatcher makes these events available to BullMQ only after their business transaction commits.
-- event_payload contains identifiers and immutable snapshots only; it must not contain credentials or access URLs.
CREATE TABLE outbox_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    aggregate_type TEXT NOT NULL,
    aggregate_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    event_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    deduplication_key TEXT,
    status outbox_event_status NOT NULL DEFAULT 'pending',
    attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
    next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    locked_by TEXT,
    locked_until TIMESTAMPTZ,
    dispatched_at TIMESTAMPTZ,
    last_error_code TEXT,
    last_error_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (
        (status = 'dispatching' AND locked_by IS NOT NULL AND locked_until IS NOT NULL)
        OR (status <> 'dispatching' AND locked_by IS NULL AND locked_until IS NULL)
    ),
    CHECK (dispatched_at IS NULL OR status = 'dispatched'),
    UNIQUE (workspace_id, id)
);
CREATE INDEX ix_outbox_events_dispatch
    ON outbox_events (workspace_id, status, next_attempt_at, created_at)
    WHERE status IN ('pending', 'dispatching');
CREATE INDEX ix_outbox_events_aggregate
    ON outbox_events (workspace_id, aggregate_type, aggregate_id, created_at DESC);

CREATE TABLE media_assets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    storage_provider TEXT NOT NULL, -- local_minio / qiniu_kodo; provider configuration is server-side only.
    bucket TEXT NOT NULL,
    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, storage_provider, bucket, object_key),
    UNIQUE (workspace_id, checksum_sha256)
);

-- A short-lived, durable record for browser direct uploads. object_key is never returned as public file identity.
CREATE TABLE media_upload_intents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    purpose TEXT NOT NULL,
    storage_provider TEXT NOT NULL,
    bucket TEXT NOT NULL,
    object_key TEXT NOT NULL,
    expected_content_type TEXT NOT NULL,
    expected_byte_size BIGINT NOT NULL CHECK (expected_byte_size > 0),
    expected_checksum_sha256 TEXT NOT NULL,
    status media_upload_status NOT NULL DEFAULT 'pending',
    asset_id UUID,
    error_code TEXT,
    error_message TEXT,
    expires_at TIMESTAMPTZ NOT NULL,
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ,
    FOREIGN KEY (workspace_id, asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT,
    CHECK (expires_at > created_at),
    CHECK (completed_at IS NULL OR completed_at >= created_at),
    CHECK ((status = 'ready') = (asset_id IS NOT NULL)),
    UNIQUE (workspace_id, id),
    UNIQUE (storage_provider, bucket, object_key)
);
CREATE INDEX ix_media_upload_intents_expiry ON media_upload_intents (status, expires_at);

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(),
    revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
    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, id),
    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),
    allow_commission_override BOOLEAN NOT NULL DEFAULT false,
    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(),
    revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
    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),
    fx_rate_origin fx_rate_origin NOT NULL DEFAULT 'system_rate',
    exchange_rate_id UUID REFERENCES exchange_rates(id) ON DELETE SET NULL,
    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', 'operations', 'idempotency_requests', 'outbox_events',
        'media_assets', 'media_upload_intents',
        '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('ALTER TABLE %I FORCE 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 $$;