SET search_path TO atlas, public;

CREATE TYPE platform_listing_source AS ENUM ('atlas_published', 'external_sync');
CREATE TYPE platform_sync_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');

CREATE TABLE platform_product_sync_runs (
    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,
    status platform_sync_status NOT NULL DEFAULT 'queued',
    trigger_source TEXT NOT NULL CHECK (trigger_source IN ('manual', 'scheduled', 'recovery')),
    cursor_started TEXT,
    cursor_finished TEXT,
    listed_count INTEGER NOT NULL DEFAULT 0 CHECK (listed_count >= 0),
    upserted_count INTEGER NOT NULL DEFAULT 0 CHECK (upserted_count >= 0),
    failed_count INTEGER NOT NULL DEFAULT 0 CHECK (failed_count >= 0),
    error_payload JSONB,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, store_id, platform_id) REFERENCES stores(workspace_id, id, platform_id) ON DELETE RESTRICT,
    UNIQUE (workspace_id, id)
);
CREATE INDEX ix_platform_product_sync_runs_store ON platform_product_sync_runs (workspace_id, store_id, created_at DESC, id DESC);
CREATE UNIQUE INDEX ux_platform_product_sync_runs_active ON platform_product_sync_runs (workspace_id, store_id) WHERE status IN ('queued', 'running');

CREATE TABLE platform_listings (
    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,
    remote_product_id TEXT NOT NULL,
    offer_id TEXT NOT NULL,
    remote_sku TEXT,
    name TEXT,
    currency_code CHAR(3) REFERENCES currencies(code),
    selling_price NUMERIC(18,4) CHECK (selling_price IS NULL OR selling_price >= 0),
    list_price NUMERIC(18,4) CHECK (list_price IS NULL OR list_price >= 0),
    visible BOOLEAN,
    remote_state TEXT,
    moderation_status TEXT,
    validation_status TEXT,
    stock_summary JSONB NOT NULL DEFAULT '{}'::jsonb,
    image_summary JSONB NOT NULL DEFAULT '{}'::jsonb,
    error_payload JSONB NOT NULL DEFAULT '[]'::jsonb,
    visibility_details JSONB NOT NULL DEFAULT '{}'::jsonb,
    source platform_listing_source NOT NULL DEFAULT 'external_sync',
    atlas_variant_listing_id UUID,
    last_sync_run_id UUID,
    last_synced_at TIMESTAMPTZ NOT NULL,
    missing_since TIMESTAMPTZ,
    archived_at TIMESTAMPTZ,
    raw_payload JSONB NOT 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 RESTRICT,
    FOREIGN KEY (workspace_id, atlas_variant_listing_id) REFERENCES product_variant_platform_listings(workspace_id, id) ON DELETE SET NULL,
    FOREIGN KEY (workspace_id, last_sync_run_id) REFERENCES platform_product_sync_runs(workspace_id, id) ON DELETE SET NULL,
    UNIQUE (workspace_id, id),
    UNIQUE (workspace_id, store_id, platform_id, remote_product_id),
    UNIQUE (workspace_id, store_id, platform_id, offer_id)
);
CREATE INDEX ix_platform_listings_store_list ON platform_listings (workspace_id, store_id, updated_at DESC, id DESC);
CREATE INDEX ix_platform_listings_store_visibility ON platform_listings (workspace_id, store_id, visible, updated_at DESC);
CREATE INDEX ix_platform_listings_atlas_mapping ON platform_listings (workspace_id, atlas_variant_listing_id) WHERE atlas_variant_listing_id IS NOT NULL;

CREATE TABLE platform_listing_sync_snapshots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    platform_listing_id UUID NOT NULL,
    sync_run_id UUID NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, platform_listing_id) REFERENCES platform_listings(workspace_id, id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, sync_run_id) REFERENCES platform_product_sync_runs(workspace_id, id) ON DELETE CASCADE,
    UNIQUE (workspace_id, platform_listing_id, sync_run_id)
);

-- 商品镜像、同步运行和响应快照均强制租户隔离，Worker 也必须在租户事务中访问。
DO $$
DECLARE table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY['platform_product_sync_runs', 'platform_listings', 'platform_listing_sync_snapshots'] 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 $$;