-- Migration: Create decomposition_templates table and add metadata fields
-- Date: 2026-03-02
-- Issue: [AC-IDSMETA-16, AC-IDSMETA-22] Add metadata fields and decomposition templates

-- Create decomposition_templates table
CREATE TABLE IF NOT EXISTS decomposition_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    description TEXT,
    version INTEGER NOT NULL DEFAULT 1,
    status VARCHAR NOT NULL DEFAULT 'draft',
    template_schema JSONB NOT NULL DEFAULT '{}',
    extraction_hints JSONB,
    example_input TEXT,
    example_output JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create indexes for decomposition_templates
CREATE INDEX IF NOT EXISTS ix_decomposition_templates_tenant 
ON decomposition_templates (tenant_id);

CREATE INDEX IF NOT EXISTS ix_decomposition_templates_tenant_status 
ON decomposition_templates (tenant_id, status);

-- Add metadata column to intent_rules if not exists
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'intent_rules' AND column_name = 'metadata') THEN
        ALTER TABLE intent_rules ADD COLUMN metadata JSONB;
    END IF;
END $$;

-- Add metadata column to script_flows if not exists
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'script_flows' AND column_name = 'metadata') THEN
        ALTER TABLE script_flows ADD COLUMN metadata JSONB;
    END IF;
END $$;

-- Add metadata column to prompt_templates if not exists
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'prompt_templates' AND column_name = 'metadata') THEN
        ALTER TABLE prompt_templates ADD COLUMN metadata JSONB;
    END IF;
END $$;

-- Add comments
COMMENT ON TABLE decomposition_templates IS '[AC-IDSMETA-22] 拆解模板表';
COMMENT ON COLUMN decomposition_templates.status IS '模板状态: draft/published/archived';
COMMENT ON COLUMN decomposition_templates.template_schema IS '输出模板结构定义';
COMMENT ON COLUMN intent_rules.metadata IS '[AC-IDSMETA-16] 结构化元数据';
COMMENT ON COLUMN script_flows.metadata IS '[AC-IDSMETA-16] 结构化元数据';
COMMENT ON COLUMN prompt_templates.metadata IS '[AC-IDSMETA-16] 结构化元数据';
