AIyaodianzhushou/database/migrate_json_to_jsonb.sql

-- ============================================================
-- Migration: json → jsonb for existing databases
-- Run this on existing PostgreSQL databases to apply Phase 1 changes
-- Safe to run multiple times (uses IF NOT EXISTS / ALTER COLUMN IF)
-- ============================================================

BEGIN;

-- Convert json columns to jsonb
ALTER TABLE public.drugs ALTER COLUMN sections TYPE jsonb USING sections::jsonb;

ALTER TABLE public.knowledge_points ALTER COLUMN related_drugs TYPE jsonb USING related_drugs::jsonb;

ALTER TABLE public.questions ALTER COLUMN options TYPE jsonb USING options::jsonb;

ALTER TABLE public.questions ALTER COLUMN knowledge_point_ids TYPE jsonb USING knowledge_point_ids::jsonb;

-- Add GIN indexes for jsonb columns (performance)
CREATE INDEX IF NOT EXISTS ix_drugs_sections_gin ON public.drugs USING gin (sections);

CREATE INDEX IF NOT EXISTS ix_drugs_source_version ON public.drugs USING btree (source_version);

COMMIT;