-- Migration: Create metadata_field_definitions table
-- Date: 2026-03-02
-- Issue: [AC-IDSMETA-13] 元数据字段定义表，支持字段级状态治理

-- Create metadata_field_definitions table
CREATE TABLE IF NOT EXISTS metadata_field_definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR NOT NULL,
    field_key VARCHAR(64) NOT NULL,
    label VARCHAR(64) NOT NULL,
    type VARCHAR NOT NULL DEFAULT 'string',
    required BOOLEAN NOT NULL DEFAULT FALSE,
    options JSONB,
    default_value JSONB,
    scope JSONB NOT NULL DEFAULT '["kb_document"]',
    is_filterable BOOLEAN NOT NULL DEFAULT TRUE,
    is_rank_feature BOOLEAN NOT NULL DEFAULT FALSE,
    status VARCHAR NOT NULL DEFAULT 'draft',
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS ix_metadata_field_definitions_tenant 
ON metadata_field_definitions (tenant_id);

CREATE INDEX IF NOT EXISTS ix_metadata_field_definitions_tenant_status 
ON metadata_field_definitions (tenant_id, status);

CREATE UNIQUE INDEX IF NOT EXISTS ix_metadata_field_definitions_tenant_field_key 
ON metadata_field_definitions (tenant_id, field_key);

-- Add comments
COMMENT ON TABLE metadata_field_definitions IS '[AC-IDSMETA-13] 元数据字段定义表';
COMMENT ON COLUMN metadata_field_definitions.field_key IS '字段键名，仅允许小写字母数字下划线';
COMMENT ON COLUMN metadata_field_definitions.type IS '字段类型: string/number/boolean/enum/array_enum';
COMMENT ON COLUMN metadata_field_definitions.scope IS '适用范围: kb_document/intent_rule/script_flow/prompt_template';
COMMENT ON COLUMN metadata_field_definitions.status IS '字段状态: draft/active/deprecated';
