BEGIN;

ALTER TABLE rm_forecast_picks
  ADD COLUMN IF NOT EXISTS grading_category varchar(32),
  ADD COLUMN IF NOT EXISTS signal_tier varchar(16),
  ADD COLUMN IF NOT EXISTS market_implied_probability double precision,
  ADD COLUMN IF NOT EXISTS projected_probability double precision,
  ADD COLUMN IF NOT EXISTS projected_outcome double precision,
  ADD COLUMN IF NOT EXISTS result_outcome varchar(16),
  ADD COLUMN IF NOT EXISTS closing_line_value double precision,
  ADD COLUMN IF NOT EXISTS closing_odds_snapshot integer;

COMMIT;

-- Pre-check:
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_name = 'rm_forecast_picks'
--   AND column_name IN (
--     'grading_category',
--     'signal_tier',
--     'market_implied_probability',
--     'projected_probability',
--     'projected_outcome',
--     'result_outcome',
--     'closing_line_value',
--     'closing_odds_snapshot'
--   )
-- ORDER BY column_name;

-- Post-check:
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_name = 'rm_forecast_picks'
--   AND column_name IN (
--     'grading_category',
--     'signal_tier',
--     'market_implied_probability',
--     'projected_probability',
--     'projected_outcome',
--     'result_outcome',
--     'closing_line_value',
--     'closing_odds_snapshot'
--   )
-- ORDER BY column_name;

-- Rollback:
-- BEGIN;
-- ALTER TABLE rm_forecast_picks
--   DROP COLUMN IF EXISTS closing_odds_snapshot,
--   DROP COLUMN IF EXISTS closing_line_value,
--   DROP COLUMN IF EXISTS result_outcome,
--   DROP COLUMN IF EXISTS projected_outcome,
--   DROP COLUMN IF EXISTS projected_probability,
--   DROP COLUMN IF EXISTS market_implied_probability,
--   DROP COLUMN IF EXISTS signal_tier,
--   DROP COLUMN IF EXISTS grading_category;
-- COMMIT;
