SET search_path TO atlas, public;

-- SKU 发布标题为空时继承当前平台 SPU 标题；内部展示名不是发布标题。
ALTER TABLE product_variant_platform_listings ADD COLUMN title TEXT;
ALTER TABLE product_variants ADD CONSTRAINT uq_product_variants_workspace_id_product
    UNIQUE (workspace_id, id, product_id);

-- 模型任务只保存服务端固化的资料快照；Outbox 仅携带任务 ID。
CREATE TABLE product_ai_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    product_id UUID NOT NULL,
    profile_id UUID NOT NULL,
    variant_id UUID,
    kind TEXT NOT NULL CHECK (kind IN ('autofill', 'sku_copy')),
    status TEXT NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'succeeded', 'partial', 'failed')),
    input_snapshot JSONB NOT NULL,
    result JSONB NOT NULL DEFAULT '{}'::jsonb,
    error_code TEXT,
    error_message TEXT,
    model_name TEXT,
    prompt_version TEXT NOT NULL DEFAULT 'v1',
    token_usage JSONB NOT NULL DEFAULT '{}'::jsonb,
    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,
    FOREIGN KEY (workspace_id, profile_id, product_id)
        REFERENCES product_platform_profiles(workspace_id, id, product_id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, variant_id, product_id)
        REFERENCES product_variants(workspace_id, id, product_id) ON DELETE RESTRICT,
    CHECK ((kind = 'autofill' AND variant_id IS NULL)
        OR (kind = 'sku_copy' AND variant_id IS NOT NULL)),
    UNIQUE (workspace_id, id)
);
CREATE INDEX ix_product_ai_tasks_profile_recent
    ON product_ai_tasks (workspace_id, profile_id, created_at DESC);

-- 中文只作编辑对照；发布适配器只读取俄文草稿。
CREATE TABLE product_copy_translations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    product_id UUID NOT NULL,
    profile_id UUID NOT NULL,
    variant_id UUID,
    field TEXT NOT NULL CHECK (field IN ('title', 'tags', 'description')),
    russian_value JSONB NOT NULL,
    chinese_value JSONB NOT NULL,
    source_task_id UUID,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, profile_id, product_id)
        REFERENCES product_platform_profiles(workspace_id, id, product_id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, variant_id, product_id)
        REFERENCES product_variants(workspace_id, id, product_id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, source_task_id)
        REFERENCES product_ai_tasks(workspace_id, id) ON DELETE SET NULL (source_task_id)
);
CREATE UNIQUE INDEX ux_product_copy_translations_spu
    ON product_copy_translations (workspace_id, profile_id, field)
    WHERE variant_id IS NULL;
CREATE UNIQUE INDEX ux_product_copy_translations_sku
    ON product_copy_translations (workspace_id, profile_id, variant_id, field)
    WHERE variant_id IS NOT NULL;

ALTER TABLE product_ai_tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_ai_tasks FORCE ROW LEVEL SECURITY;
CREATE POLICY workspace_isolation ON product_ai_tasks
    USING (workspace_id = app_current_workspace_id())
    WITH CHECK (workspace_id = app_current_workspace_id());
ALTER TABLE product_copy_translations ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_copy_translations FORCE ROW LEVEL SECURITY;
CREATE POLICY workspace_isolation ON product_copy_translations
    USING (workspace_id = app_current_workspace_id())
    WITH CHECK (workspace_id = app_current_workspace_id());