-- =====================================================================
--  THE QUINTILLIAN CRM  |  Update 3: numbered cohorts
--  Each programme runs numbered cohorts per location:
--    Kano Cohort 1, 2, 3 ... / Abuja Cohort 1, 2 ... / Online Cohort 1, 2 ...
--  Private classes (and self-paced / corporate) have no cohorts.
--
--  New project?  schema.sql already includes this. Do not run it again.
--  Existing project?  Run this file once in SQL Editor (safe to re-run).
-- =====================================================================

alter table public.cohorts add column if not exists programme  text;
alter table public.cohorts add column if not exists cohort_no  int;
alter table public.cohorts add column if not exists status     text not null default 'Open for registration';
alter table public.cohorts add column if not exists start_date date;
alter table public.cohorts add column if not exists end_date   date;
alter table public.cohorts add column if not exists created_at timestamptz not null default now();
do $$ begin
  alter table public.cohorts add constraint cohorts_status_check
    check (status in ('Open for registration','Running','Completed'));
exception when duplicate_object then null; end $$;

-- existing cohorts: use their name as the programme and number them 1, 2, 3 ...
update public.cohorts set programme = name where programme is null;
with n as (
  select id, row_number() over (partition by office, programme order by created_at, name, id) as rn
  from public.cohorts where cohort_no is null
)
update public.cohorts c set cohort_no = n.rn from n where c.id = n.id;

-- new cohorts get the next number for that programme in that location automatically
create or replace function public.cohort_next_number()
returns trigger language plpgsql as $$
begin
  if new.cohort_no is null then
    select coalesce(max(cohort_no), 0) + 1 into new.cohort_no
    from public.cohorts
    where office = new.office and programme is not distinct from new.programme;
  end if;
  if new.name is null or new.name = '' then new.name := coalesce(new.programme, 'Cohort'); end if;
  return new;
end $$;
drop trigger if exists cohorts_next_number on public.cohorts;
create trigger cohorts_next_number before insert on public.cohorts
  for each row execute function public.cohort_next_number();

create unique index if not exists cohorts_unique_number on public.cohorts (office, programme, cohort_no);

-- which programmes run in cohorts (edit in CRM Settings); private classes are not listed
insert into public.settings (key, value) values
  ('cohort_programmes', 'Intermediate, Advanced & Fellowship, Executive Program, Polish Kids Communication, VIP class')
on conflict (key) do nothing;