-- Preferred day, redesigned per a new admin-panel mockup: a real date
-- range (two actual calendar dates) instead of free text. Confirmed
-- decision: "Easter" (Rubyrose's current value, a floating holiday
-- with no fixed date a picker can represent) converts to this year's
-- real Easter date (2026-04-05) rather than being preserved as a label.
--
-- The old `preferred_date` text column is kept, not dropped -- same
-- "don't destructively remove, just stop actively using it" pattern as
-- 0034 (rate_history.hourly_usd) -- in case the exact original wording
-- ever needs to be referenced again.
--
-- 7 of the 8 non-null legacy values were cleanly parseable into real
-- 2026 dates (a single day or day-range within a named month, no
-- ambiguity) and are backfilled directly; only Rubyrose's "Easter"
-- needed the explicit conversion decision above.

alter table public.time_off_balances
  add column if not exists preferred_date_start date,
  add column if not exists preferred_date_end date;

update public.time_off_balances set preferred_date_start = '2026-10-14', preferred_date_end = '2026-10-15' where agent_id = 'mon' and year = 2026;
update public.time_off_balances set preferred_date_start = '2026-12-30', preferred_date_end = '2026-12-30' where agent_id = 'jurina' and year = 2026;
update public.time_off_balances set preferred_date_start = '2026-09-16', preferred_date_end = '2026-09-16' where agent_id = 'mayvel' and year = 2026;
update public.time_off_balances set preferred_date_start = '2026-07-20', preferred_date_end = '2026-07-20' where agent_id = 'kate' and year = 2026;
update public.time_off_balances set preferred_date_start = '2026-04-05', preferred_date_end = '2026-04-05' where agent_id = 'rubyrose' and year = 2026; -- Easter 2026, converted per confirmed decision
update public.time_off_balances set preferred_date_start = '2026-01-20', preferred_date_end = '2026-01-20' where agent_id = 'andrew' and year = 2026;
update public.time_off_balances set preferred_date_start = '2026-11-07', preferred_date_end = '2026-11-07' where agent_id = 'dominic' and year = 2026;
-- agent_042 and berry had no preferred_date set -- stay null, unchanged.

-- ============================================================
-- Self-verification guard
-- ============================================================

do $migration_guard$
declare
  col_count int;
  backfilled_count int;
  ruby_start date;
begin
  select count(*) into col_count from information_schema.columns
    where table_schema = 'public' and table_name = 'time_off_balances' and column_name in ('preferred_date_start', 'preferred_date_end');
  if col_count <> 2 then
    raise exception 'Aborting 0041: expected 2 new columns, found %', col_count;
  end if;

  select count(*) into backfilled_count from public.time_off_balances
    where year = 2026 and preferred_date_start is not null;
  if backfilled_count <> 7 then
    raise exception 'Aborting 0041: expected 7 rows backfilled with a preferred date range, found %', backfilled_count;
  end if;

  select preferred_date_start into ruby_start from public.time_off_balances where agent_id = 'rubyrose' and year = 2026;
  if ruby_start <> '2026-04-05' then
    raise exception 'Aborting 0041: expected Rubyrose preferred_date_start = 2026-04-05 (Easter), found %', ruby_start;
  end if;
end $migration_guard$;