1-- ============================================================
-- 快捷提问标签表 v2 迁移
-- 1. 补充 updated_at / created_by / updated_by 字段
-- 2. 新建审计日志表
-- 执行: psql -h <host> -U <user> -d pharmacopoeia -f database/migrate_quick_asks_v2.sql
-- ============================================================
BEGIN;

-- 补充字段（幂等）
ALTER TABLE public.quick_asks ADD COLUMN IF NOT EXISTS updated_at timestamptz DEFAULT now();
ALTER TABLE public.quick_asks ADD COLUMN IF NOT EXISTS created_by varchar(255);
ALTER TABLE public.quick_asks ADD COLUMN IF NOT EXISTS updated_by varchar(255);

-- 审计日志表
CREATE TABLE IF NOT EXISTS public.quick_ask_audit_log (
    id SERIAL PRIMARY KEY,
    quick_ask_id integer NOT NULL,
    action varchar(16) NOT NULL,
    label varchar(64),
    question varchar(256),
    sort_order integer DEFAULT 0,
    is_active boolean DEFAULT true,
    operator varchar(255) NOT NULL,
    created_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_qa_audit_action ON public.quick_ask_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_qa_audit_created_at ON public.quick_ask_audit_log(created_at DESC);

COMMIT;