-- Add JSONB storage for feature rules without breaking existing TEXT usage

DO $$
BEGIN
    -- Add JSONB column if missing
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_schema = 'public' 
          AND table_name = 'feature_rules' 
          AND column_name = 'rule_json'
    ) THEN
        ALTER TABLE feature_rules
        ADD COLUMN rule_json JSONB;
    END IF;

    -- Best-effort backfill: if rule_text looks like JSON, cast it
    -- This avoids errors casting arbitrary text
    UPDATE feature_rules
    SET rule_json = rule_text::jsonb
    WHERE rule_json IS NULL
      AND (
        (rule_text IS NOT NULL AND LEFT(TRIM(rule_text), 1) = '{') OR
        (rule_text IS NOT NULL AND LEFT(TRIM(rule_text), 1) = '[')
      );

    -- Helpful GIN index for future querying by JSON keys/values
    CREATE INDEX IF NOT EXISTS idx_feature_rules_rule_json_gin
      ON feature_rules USING GIN (rule_json);
END $$;


