-- Migration: Add USDA plant data columns
-- Run after migrate_add_discovery_catalog.sql

ALTER TABLE plant_catalog
  ADD COLUMN IF NOT EXISTS family         VARCHAR(120)  NULL         AFTER scientific_name,
  ADD COLUMN IF NOT EXISTS usda_symbol    VARCHAR(20)   NULL         AFTER family,
  ADD COLUMN IF NOT EXISTS native_status  VARCHAR(120)  NULL,
  ADD COLUMN IF NOT EXISTS source         VARCHAR(20)   NOT NULL DEFAULT 'manual',
  ADD COLUMN IF NOT EXISTS zone_min_score SMALLINT      NULL,
  ADD COLUMN IF NOT EXISTS zone_max_score SMALLINT      NULL;

-- Index for fast zone-range queries
ALTER TABLE plant_catalog
  ADD INDEX IF NOT EXISTS idx_zone_min_score (zone_min_score),
  ADD INDEX IF NOT EXISTS idx_usda_symbol    (usda_symbol),
  ADD INDEX IF NOT EXISTS idx_source         (source);

-- Back-fill zone scores for existing hand-crafted entries
UPDATE plant_catalog SET
  zone_min_score = CASE usda_zone_min
    WHEN '1a'  THEN 10  WHEN '1b'  THEN 11
    WHEN '2a'  THEN 20  WHEN '2b'  THEN 21
    WHEN '3a'  THEN 30  WHEN '3b'  THEN 31
    WHEN '4a'  THEN 40  WHEN '4b'  THEN 41
    WHEN '5a'  THEN 50  WHEN '5b'  THEN 51
    WHEN '6a'  THEN 60  WHEN '6b'  THEN 61
    WHEN '7a'  THEN 70  WHEN '7b'  THEN 71
    WHEN '8a'  THEN 80  WHEN '8b'  THEN 81
    WHEN '9a'  THEN 90  WHEN '9b'  THEN 91
    WHEN '10a' THEN 100 WHEN '10b' THEN 101
    WHEN '11a' THEN 110 WHEN '11b' THEN 111
    WHEN '12a' THEN 120 WHEN '12b' THEN 121
    WHEN '13a' THEN 130 WHEN '13b' THEN 131
    ELSE NULL
  END,
  zone_max_score = CASE usda_zone_max
    WHEN '1a'  THEN 10  WHEN '1b'  THEN 11
    WHEN '2a'  THEN 20  WHEN '2b'  THEN 21
    WHEN '3a'  THEN 30  WHEN '3b'  THEN 31
    WHEN '4a'  THEN 40  WHEN '4b'  THEN 41
    WHEN '5a'  THEN 50  WHEN '5b'  THEN 51
    WHEN '6a'  THEN 60  WHEN '6b'  THEN 61
    WHEN '7a'  THEN 70  WHEN '7b'  THEN 71
    WHEN '8a'  THEN 80  WHEN '8b'  THEN 81
    WHEN '9a'  THEN 90  WHEN '9b'  THEN 91
    WHEN '10a' THEN 100 WHEN '10b' THEN 101
    WHEN '11a' THEN 110 WHEN '11b' THEN 111
    WHEN '12a' THEN 120 WHEN '12b' THEN 121
    WHEN '13a' THEN 130 WHEN '13b' THEN 131
    ELSE NULL
  END
WHERE source = 'manual';
