AIyaodianzhushou/database/migrate_brand_recommend.sql

-- ============================================================
-- 品牌药品 + 品牌推荐规则 迁移
-- 执行: psql -h <host> -U <user> -d pharmacopoeia -f database/migrate_brand_recommend.sql
-- ============================================================
BEGIN;

-- 品牌药品表
CREATE TABLE IF NOT EXISTS public.brands (
    id SERIAL PRIMARY KEY,
    name varchar(200) NOT NULL,
    function_indication text,
    usage_dosage text,
    contraindication text,
    ingredients text,
    properties text,
    specification text,
    adverse_reactions text,
    precautions text,
    execution_standard text,
    storage text,
    jump_url varchar(500),
    description varchar(500),
    sort_order integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_brand_name ON public.brands(name);

-- 品牌推荐规则表（关联品牌）
CREATE TABLE IF NOT EXISTS public.brand_recommend_rules (
    id SERIAL PRIMARY KEY,
    keyword varchar(100) NOT NULL,
    brand_id integer NOT NULL REFERENCES public.brands(id) ON DELETE CASCADE,
    tier integer NOT NULL DEFAULT 1,
    is_active boolean NOT NULL DEFAULT true,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_br_keyword ON public.brand_recommend_rules(keyword);
CREATE INDEX IF NOT EXISTS idx_br_brand ON public.brand_recommend_rules(brand_id);
CREATE INDEX IF NOT EXISTS idx_br_tier ON public.brand_recommend_rules(tier);
CREATE INDEX IF NOT EXISTS idx_br_active ON public.brand_recommend_rules(is_active);

-- messages 表加 brand_recommendations 快照字段（历史对话持久化）
ALTER TABLE public.messages ADD COLUMN IF NOT EXISTS brand_recommendations jsonb;

COMMIT;