SET search_path TO atlas, public;

-- Fixed platform-capability schema used by collection sources and the ERP header.
UPDATE platforms
SET capabilities = capabilities || jsonb_build_object(
    'can_be_source', true, 'can_be_target', true, 'can_be_header_context', true
), updated_at = now()
WHERE code = 'OZON';

INSERT INTO platforms (code, name, capabilities)
VALUES
    ('1688', '1688', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb),
    ('TAOBAO', '淘宝', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb),
    ('TMALL', '天猫', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb)
ON CONFLICT (code) DO UPDATE SET capabilities = platforms.capabilities || EXCLUDED.capabilities, updated_at = now();

CREATE TYPE collection_source_kind AS ENUM ('platform', 'manual');
CREATE TYPE collection_item_status AS ENUM ('received', 'materializing', 'ready_for_edit', 'needs_review', 'failed', 'archived', 'deleted');
CREATE TYPE processing_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');
CREATE TYPE media_candidate_role AS ENUM ('source_main', 'source_sku', 'source_detail');

ALTER TABLE workspaces ADD COLUMN collection_item_limit INTEGER NOT NULL DEFAULT 1000 CHECK (collection_item_limit >= 0);

CREATE TABLE collection_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
    source_kind collection_source_kind NOT NULL, source_platform_id UUID REFERENCES platforms(id) ON DELETE RESTRICT,
    source_item_key TEXT NOT NULL, source_url TEXT, source_title TEXT, source_currency_code CHAR(3) REFERENCES currencies(code),
    source_min_price NUMERIC(18,4) CHECK (source_min_price IS NULL OR source_min_price >= 0),
    source_max_price NUMERIC(18,4) CHECK (source_max_price IS NULL OR source_max_price >= source_min_price),
    note TEXT, status collection_item_status NOT NULL DEFAULT 'received', raw_evidence JSONB NOT NULL DEFAULT '{}'::jsonb,
    normalized_payload JSONB NOT NULL DEFAULT '{}'::jsonb, parser_version TEXT, collected_at TIMESTAMPTZ,
    last_error JSONB, deleted_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    revision BIGINT NOT NULL DEFAULT 1 CHECK (revision > 0), UNIQUE (workspace_id, id),
    CHECK ((source_kind = 'platform' AND source_platform_id IS NOT NULL) OR (source_kind = 'manual' AND source_platform_id IS NULL)),
    CHECK ((status = 'deleted') = (deleted_at IS NOT NULL))
);
CREATE UNIQUE INDEX ux_collection_items_active_source_identity ON collection_items (workspace_id, source_platform_id, source_item_key) WHERE source_platform_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX ix_collection_items_workspace_list ON collection_items (workspace_id, updated_at DESC, id DESC) WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION enforce_collection_item_limit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE configured_limit INTEGER; active_count INTEGER;
BEGIN
  IF (TG_OP = 'INSERT' AND NEW.deleted_at IS NULL) OR (TG_OP = 'UPDATE' AND OLD.deleted_at IS NOT NULL AND NEW.deleted_at IS NULL) THEN
    SELECT collection_item_limit INTO configured_limit FROM workspaces WHERE id = NEW.workspace_id FOR UPDATE;
    SELECT count(*) INTO active_count FROM collection_items WHERE workspace_id = NEW.workspace_id AND deleted_at IS NULL;
    IF active_count >= configured_limit THEN RAISE EXCEPTION 'collection item limit reached' USING ERRCODE = 'check_violation'; END IF;
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER enforce_collection_item_limit_before_write BEFORE INSERT OR UPDATE OF deleted_at ON collection_items FOR EACH ROW EXECUTE FUNCTION enforce_collection_item_limit();

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, collection_item_id UUID NOT NULL,
    source_title TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), version BIGINT NOT NULL DEFAULT 1,
    FOREIGN KEY (workspace_id, collection_item_id) REFERENCES collection_items(workspace_id, id) ON DELETE RESTRICT,
    UNIQUE (workspace_id, id), UNIQUE (workspace_id, collection_item_id)
);
CREATE TABLE product_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, product_id UUID NOT NULL,
    source_variant_key TEXT, display_name TEXT, source_sku TEXT, sort_order INTEGER NOT NULL DEFAULT 0, deleted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (workspace_id, product_id) REFERENCES products(workspace_id, id) ON DELETE RESTRICT, UNIQUE (workspace_id, id)
);
CREATE INDEX ix_product_variants_product ON product_variants (workspace_id, product_id, sort_order) WHERE deleted_at IS NULL;
CREATE TABLE collection_asset_transfers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, collection_item_id UUID NOT NULL, product_id UUID NOT NULL,
    source_variant_id UUID, role media_candidate_role NOT NULL, source_url TEXT NOT NULL, status processing_status NOT NULL DEFAULT 'queued',
    asset_id UUID, error_payload JSONB, task_key UUID NOT NULL DEFAULT gen_random_uuid(), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), completed_at TIMESTAMPTZ,
    FOREIGN KEY (workspace_id, collection_item_id) REFERENCES collection_items(workspace_id, id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, product_id) REFERENCES products(workspace_id, id) ON DELETE CASCADE,
    FOREIGN KEY (workspace_id, source_variant_id) REFERENCES product_variants(workspace_id, id) ON DELETE SET NULL,
    FOREIGN KEY (workspace_id, asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT, UNIQUE (workspace_id, task_key)
);

DO $$ DECLARE table_name TEXT; BEGIN
  FOREACH table_name IN ARRAY ARRAY['collection_items', 'products', 'product_variants', 'collection_asset_transfers'] 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 $$;