-- Migration: Add field_roles and slot_definitions
-- Date: 2026-03-05
-- Issue: [AC-MRS-01, AC-MRS-07] 元数据职责分层优化

-- 1. 为 metadata_field_definitions 表新增 field_roles 字段
ALTER TABLE metadata_field_definitions 
ADD COLUMN IF NOT EXISTS field_roles JSONB DEFAULT '[]'::jsonb;

-- 2. 创建 GIN 索引支持按角色查询
CREATE INDEX IF NOT EXISTS idx_metadata_field_definitions_roles 
ON metadata_field_definitions USING GIN (field_roles);

-- 3. 添加字段注释
COMMENT ON COLUMN metadata_field_definitions.field_roles IS 
'[AC-MRS-01] 字段角色列表：resource_filter, slot, prompt_var, routing_signal';

-- 4. 创建 slot_definitions 表
CREATE TABLE IF NOT EXISTS slot_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR NOT NULL,
    slot_key VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'string',
    required BOOLEAN NOT NULL DEFAULT FALSE,
    extract_strategy VARCHAR(20),
    validation_rule TEXT,
    ask_back_prompt TEXT,
    default_value JSONB,
    linked_field_id UUID REFERENCES metadata_field_definitions(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uk_slot_definitions_tenant_key UNIQUE (tenant_id, slot_key),
    CONSTRAINT chk_slot_definitions_type CHECK (type IN ('string', 'number', 'boolean', 'enum', 'array_enum')),
    CONSTRAINT chk_slot_definitions_extract_strategy CHECK (extract_strategy IS NULL OR extract_strategy IN ('rule', 'llm', 'user_input'))
);

-- 5. 创建索引
CREATE INDEX IF NOT EXISTS ix_slot_definitions_tenant 
ON slot_definitions(tenant_id);

CREATE INDEX IF NOT EXISTS ix_slot_definitions_tenant_key 
ON slot_definitions(tenant_id, slot_key);

CREATE INDEX IF NOT EXISTS ix_slot_definitions_linked_field 
ON slot_definitions(linked_field_id);

-- 6. 添加表和字段注释
COMMENT ON TABLE slot_definitions IS '[AC-MRS-07] 槽位定义表';
COMMENT ON COLUMN slot_definitions.slot_key IS '[AC-MRS-07] 槽位键名，可与元数据字段 field_key 关联';
COMMENT ON COLUMN slot_definitions.type IS '[AC-MRS-07] 槽位类型：string/number/boolean/enum/array_enum';
COMMENT ON COLUMN slot_definitions.required IS '[AC-MRS-07] 是否必填槽位';
COMMENT ON COLUMN slot_definitions.extract_strategy IS '[AC-MRS-07] 提取策略：rule/llm/user_input';
COMMENT ON COLUMN slot_definitions.validation_rule IS '[AC-MRS-07] 校验规则（正则或 JSON Schema）';
COMMENT ON COLUMN slot_definitions.ask_back_prompt IS '[AC-MRS-07] 追问提示语模板';
COMMENT ON COLUMN slot_definitions.default_value IS '[AC-MRS-07] 默认值';
COMMENT ON COLUMN slot_definitions.linked_field_id IS '[AC-MRS-08] 关联的元数据字段 ID';

-- 7. 初始化现有字段的默认角色（根据 is_filterable 推断 resource_filter 角色）
UPDATE metadata_field_definitions 
SET field_roles = '["resource_filter"]'::jsonb
WHERE is_filterable = true 
  AND status = 'active'
  AND (field_roles IS NULL OR field_roles = '[]'::jsonb);
