USE rescape_press_db;

-- Add donation settings columns introduced in today's updates.
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS page_heading_fr TEXT;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS page_description_fr TEXT;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS one_time_enabled BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS monthly_enabled BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS annual_enabled BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS one_time_suggested_amounts JSON;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS monthly_suggested_amounts JSON;
ALTER TABLE donation_settings ADD COLUMN IF NOT EXISTS annual_suggested_amounts JSON;

-- Backfill cycle-specific suggested amounts when old format is a JSON array.
UPDATE donation_settings
SET
  one_time_suggested_amounts = COALESCE(one_time_suggested_amounts, suggested_amounts),
  monthly_suggested_amounts = COALESCE(monthly_suggested_amounts, suggested_amounts),
  annual_suggested_amounts = COALESCE(annual_suggested_amounts, suggested_amounts)
WHERE JSON_VALID(suggested_amounts) = 1
  AND JSON_TYPE(suggested_amounts) = 'ARRAY';

-- Backfill cycle-specific suggested amounts when old fallback format stores an object:
-- {"one_time":[...], "monthly":[...], "annual":[...]}
UPDATE donation_settings
SET
  one_time_suggested_amounts = COALESCE(one_time_suggested_amounts, JSON_EXTRACT(suggested_amounts, '$.one_time')),
  monthly_suggested_amounts = COALESCE(monthly_suggested_amounts, JSON_EXTRACT(suggested_amounts, '$.monthly')),
  annual_suggested_amounts = COALESCE(annual_suggested_amounts, JSON_EXTRACT(suggested_amounts, '$.annual'))
WHERE JSON_VALID(suggested_amounts) = 1
  AND JSON_TYPE(suggested_amounts) = 'OBJECT';

-- Ensure each row has usable defaults if any cycle values are still missing.
UPDATE donation_settings
SET
  one_time_suggested_amounts = COALESCE(one_time_suggested_amounts, JSON_ARRAY(5, 10, 25, 50)),
  monthly_suggested_amounts = COALESCE(monthly_suggested_amounts, JSON_ARRAY(2, 5, 10, 20)),
  annual_suggested_amounts = COALESCE(annual_suggested_amounts, JSON_ARRAY(25, 50, 100, 250)),
  one_time_enabled = COALESCE(one_time_enabled, true),
  monthly_enabled = COALESCE(monthly_enabled, true),
  annual_enabled = COALESCE(annual_enabled, true),
  allow_recurring = COALESCE(allow_recurring, true),
  is_active = COALESCE(is_active, true);