-- =====================================================================
--  THE QUINTILLIAN CRM  |  Update 2
--  Students register, scheduled calls with 8 am reminders,
--  staff performance tracking and social media analytics.
--
--  New project?  schema.sql already includes this. Do not run it again.
--  Already ran the first schema.sql?  Run THIS file once in SQL Editor.
-- =====================================================================

-- ---------- Follow-ups: scheduled calls and completion tracking ----------
alter table public.tasks add column if not exists kind text not null default 'task';
alter table public.tasks add column if not exists due_time time;
alter table public.tasks add column if not exists notes text;
alter table public.tasks add column if not exists completed_at timestamptz;
alter table public.tasks add column if not exists created_by uuid references public.staff(id) on delete set null;
do $$ begin
  alter table public.tasks add constraint tasks_kind_check check (kind in ('task','call'));
exception when duplicate_object then null; end $$;

create or replace function public.task_done_stamp()
returns trigger language plpgsql as $$
begin
  if new.done and (tg_op = 'INSERT' or not coalesce(old.done, false)) then
    new.completed_at := coalesce(new.completed_at, now());
  elsif not new.done then
    new.completed_at := null;
  end if;
  return new;
end $$;
drop trigger if exists tasks_done_stamp on public.tasks;
create trigger tasks_done_stamp before insert or update on public.tasks
  for each row execute function public.task_done_stamp();

-- ---------- Deals: remember when a deal was won ----------
alter table public.deals add column if not exists won_at timestamptz;
create or replace function public.deal_won_stamp()
returns trigger language plpgsql as $$
begin
  if new.stage = 'Won' and (tg_op = 'INSERT' or old.stage is distinct from 'Won') then
    new.won_at := now();
  elsif new.stage <> 'Won' then
    new.won_at := null;
  end if;
  return new;
end $$;
drop trigger if exists deals_won_stamp on public.deals;
create trigger deals_won_stamp before insert or update on public.deals
  for each row execute function public.deal_won_stamp();

-- ---------- Students register ----------
create sequence if not exists public.student_seq start 1;

create table if not exists public.students (
  id                    uuid primary key default gen_random_uuid(),
  reg_no                text not null unique default ('QS-' || to_char(now(), 'YY') || '-' || lpad(nextval('public.student_seq')::text, 4, '0')),
  contact_id            uuid references public.contacts on delete set null,
  full_name             text not null,
  gender                text check (gender in ('Male','Female')),
  date_of_birth         date,
  phone                 text,
  email                 text,
  address               text,
  state                 text,
  occupation            text,       -- job title, or school and class for children
  organisation          text,       -- employer or school
  guardian_name         text,
  guardian_phone        text,
  guardian_relationship text,
  emergency_contact     text,
  programme             text,
  cohort_id             uuid references public.cohorts on delete set null,
  office                text not null default 'Kano' check (office in ('Kano','Abuja','Online')),
  mode                  text not null default 'General class' check (mode in ('General class','VIP class','Private class','Corporate','Self-paced')),
  level                 text,
  start_date            date,
  fee                   numeric not null default 0,
  amount_paid           numeric not null default 0,
  status                text not null default 'Active' check (status in ('Active','Completed','Paused','Withdrawn')),
  how_heard             text,
  goals                 text,
  notes                 text,
  registered_by         uuid references public.staff(id) on delete set null,
  created_at            timestamptz not null default now()
);
create index if not exists students_cohort on public.students (cohort_id);

-- keep each cohort's "places filled" in step with active students
create or replace function public.students_cohort_count()
returns trigger language plpgsql security definer set search_path = public as $$
declare
  was_in boolean := false;
  now_in boolean := false;
begin
  if tg_op <> 'INSERT' then was_in := old.cohort_id is not null and old.status = 'Active'; end if;
  if tg_op <> 'DELETE' then now_in := new.cohort_id is not null and new.status = 'Active'; end if;
  if was_in and (not now_in or old.cohort_id is distinct from new.cohort_id) then
    update public.cohorts set enrolled = greatest(enrolled - 1, 0) where id = old.cohort_id;
  end if;
  if now_in and (not was_in or old.cohort_id is distinct from new.cohort_id) then
    update public.cohorts set enrolled = enrolled + 1 where id = new.cohort_id;
  end if;
  return coalesce(new, old);
end $$;
drop trigger if exists students_cohort_count on public.students;
create trigger students_cohort_count after insert or update or delete on public.students
  for each row execute function public.students_cohort_count();

-- ---------- Social media analytics ----------
create table if not exists public.social_posts (
  id           text primary key,          -- Facebook or Instagram post id
  platform     text not null check (platform in ('fb','ig')),
  caption      text,
  media_type   text,
  permalink    text,
  image_url    text,
  posted_at    timestamptz,
  reach        int,
  views        int,
  likes        int,
  comments     int,
  shares       int,
  saves        int,
  clicks       int,
  engagement   int,
  fetched_at   timestamptz not null default now()
);
create index if not exists social_posts_time on public.social_posts (posted_at desc);

create table if not exists public.social_snapshots (
  platform   text not null check (platform in ('fb','ig')),
  taken_on   date not null,
  followers  int,
  primary key (platform, taken_on)
);

-- ---------- Phone notifications (8 am call reminders) ----------
create table if not exists public.push_subscriptions (
  endpoint    text primary key,
  staff_id    uuid not null references public.staff(id) on delete cascade,
  p256dh      text not null,
  auth        text not null,
  device      text,
  created_at  timestamptz not null default now()
);

-- ---------- Security for the new tables ----------
do $$
declare t text;
begin
  foreach t in array array['students','social_posts','social_snapshots'] loop
    execute format('alter table public.%I enable row level security', t);
    execute format('drop policy if exists "staff can read %1$s" on public.%1$I', t);
    execute format('drop policy if exists "staff can add %1$s" on public.%1$I', t);
    execute format('drop policy if exists "staff can edit %1$s" on public.%1$I', t);
    execute format('create policy "staff can read %1$s" on public.%1$I for select to authenticated using (true)', t);
    execute format('create policy "staff can add %1$s" on public.%1$I for insert to authenticated with check (true)', t);
    execute format('create policy "staff can edit %1$s" on public.%1$I for update to authenticated using (true) with check (true)', t);
  end loop;
end $$;
drop policy if exists "admins can delete students" on public.students;
create policy "admins can delete students" on public.students for delete to authenticated using (public.is_admin());

alter table public.push_subscriptions enable row level security;
drop policy if exists "own devices" on public.push_subscriptions;
create policy "own devices" on public.push_subscriptions for all to authenticated
  using (staff_id = auth.uid()) with check (staff_id = auth.uid());

insert into public.settings (key, value) values
  ('vapid_public_key', ''),     -- filled in by the "Set up notifications" button in CRM Settings
  ('reminder_hour', '8')
on conflict (key) do nothing;

-- when an invoice for a student is paid, add it to the student's "amount paid"
create or replace function public.invoice_paid_student()
returns trigger language plpgsql security definer set search_path = public as $$
declare reg text;
begin
  if new.status = 'Paid' and old.status is distinct from 'Paid' then
    reg := substring(new.item from 'QS-[0-9]{2}-[0-9]{4}');
    if reg is not null then
      update public.students set amount_paid = amount_paid + new.amount where reg_no = reg;
    end if;
  end if;
  return new;
end $$;
drop trigger if exists invoices_paid_student on public.invoices;
create trigger invoices_paid_student after update on public.invoices
  for each row execute function public.invoice_paid_student();