-- =====================================================================
--  THE QUINTILLIAN CRM  |  Database setup
--  Paste this whole file into Supabase > SQL Editor > New query > Run.
--  Run it ONCE on a new, empty project. (Includes Updates 2 and 3.)
-- =====================================================================

create extension if not exists pgcrypto;

-- ---------- Staff (one row per login) ----------
create table public.staff (
  id          uuid primary key references auth.users on delete cascade,
  full_name   text not null default '',
  email       text,
  office      text not null default 'Kano' check (office in ('Kano','Abuja','Online')),
  phone       text,                       -- staff mobile, rings first for click-to-call (+234...)
  role        text not null default 'staff' check (role in ('admin','staff')),
  created_at  timestamptz not null default now()
);

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into public.staff (id, full_name, email)
  values (new.id,
          coalesce(new.raw_user_meta_data->>'full_name', initcap(split_part(new.email,'@',1))),
          new.email)
  on conflict (id) do nothing;
  return new;
end $$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ---------- Contacts ----------
create table public.contacts (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  type        text not null default 'Individual' check (type in ('Individual','School','Corporate')),
  office      text not null default 'Kano' check (office in ('Kano','Abuja','Online')),
  phone       text,
  email       text,
  wa_id       text unique,                -- WhatsApp number, digits only (e.g. 2348035550142)
  fb_psid     text unique,                -- Facebook Messenger sender id
  ig_id       text unique,                -- Instagram sender id
  source      text not null default 'web' check (source in ('wa','ig','fb','web','call')),
  interest    text,
  owner_id    uuid references public.staff(id) on delete set null,
  created_at  timestamptz not null default now()
);

-- ---------- Conversations & messages ----------
create table public.conversations (
  id               uuid primary key default gen_random_uuid(),
  contact_id       uuid not null references public.contacts on delete cascade,
  channel          text not null check (channel in ('wa','ig','fb')),
  unread           int  not null default 0,
  last_message     text,
  last_message_at  timestamptz not null default now(),
  last_inbound_at  timestamptz,            -- used for WhatsApp's 24-hour reply window
  unique (contact_id, channel)
);

create table public.messages (
  id               uuid primary key default gen_random_uuid(),
  conversation_id  uuid not null references public.conversations on delete cascade,
  direction        text not null check (direction in ('in','out','auto','system')),
  body             text not null default '',
  media_type       text,
  external_id      text unique,            -- id from Meta, prevents duplicates
  status           text not null default 'sent',
  sent_by          uuid references public.staff(id) on delete set null,
  created_at       timestamptz not null default now()
);
create index on public.messages (conversation_id, created_at);

-- keep the conversation preview, unread count and 24-hour window up to date
create or replace function public.touch_conversation()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  update public.conversations set
    last_message    = left(new.body, 200),
    last_message_at = new.created_at,
    unread          = case when new.direction = 'in' then unread + 1 else unread end,
    last_inbound_at = case when new.direction = 'in' then new.created_at else last_inbound_at end
  where id = new.conversation_id;
  return new;
end $$;

create trigger messages_touch after insert on public.messages
  for each row execute function public.touch_conversation();

-- ---------- Deals, follow-ups, activity ----------
create table public.deals (
  id          uuid primary key default gen_random_uuid(),
  contact_id  uuid not null references public.contacts on delete cascade,
  title       text not null,
  value       numeric not null default 0,
  stage       text not null default 'New enquiry'
              check (stage in ('New enquiry','Contacted','Proposal sent','Negotiating','Won','Lost')),
  owner_id    uuid references public.staff(id) on delete set null,
  created_at  timestamptz not null default now()
);

create table public.tasks (
  id          uuid primary key default gen_random_uuid(),
  contact_id  uuid references public.contacts on delete cascade,
  title       text not null,
  due_date    date not null default current_date,
  owner_id    uuid references public.staff(id) on delete set null,
  done        boolean not null default false,
  created_at  timestamptz not null default now()
);

create table public.activities (
  id          uuid primary key default gen_random_uuid(),
  contact_id  uuid references public.contacts on delete cascade,
  kind        text not null check (kind in ('wa','ig','fb','call','note','invoice','deal')),
  body        text not null,
  created_by  uuid references public.staff(id) on delete set null,
  created_at  timestamptz not null default now()
);
create index on public.activities (contact_id, created_at desc);

-- ---------- Calls ----------
create table public.calls (
  id                uuid primary key default gen_random_uuid(),
  contact_id        uuid references public.contacts on delete set null,
  staff_id          uuid references public.staff(id) on delete set null,
  direction         text not null default 'outbound',
  to_number         text,
  at_session_id     text unique,           -- Africa's Talking session id
  status            text not null default 'initiated',
  duration_seconds  int,
  recording_url     text,
  outcome           text,
  notes             text,
  created_at        timestamptz not null default now()
);

-- ---------- Invoices ----------
create sequence if not exists public.invoice_seq start 1001;

create table public.invoices (
  id                  uuid primary key default gen_random_uuid(),
  number              text not null unique default ('QL-' || nextval('public.invoice_seq')),
  contact_id          uuid not null references public.contacts on delete cascade,
  item                text not null,
  quantity            int not null default 1,
  amount              numeric not null,
  status              text not null default 'Unpaid' check (status in ('Unpaid','Paid','Cancelled')),
  due_date            date not null default (current_date + 7),
  payment_url         text,
  paystack_reference  text unique,
  paid_at             timestamptz,
  created_by          uuid references public.staff(id) on delete set null,
  created_at          timestamptz not null default now()
);

-- ---------- Price list, cohorts, quick replies, settings ----------
create table public.catalog (
  id      serial primary key,
  name    text not null,
  price   numeric not null default 0,
  sort    int not null default 0,
  active  boolean not null default true
);

create table public.cohorts (
  id           uuid primary key default gen_random_uuid(),
  name         text not null,
  office       text not null check (office in ('Kano','Abuja','Online')),
  schedule     text,
  price_label  text,
  enrolled     int not null default 0,
  capacity     int not null default 20,
  starts       text,
  active       boolean not null default true
);

create table public.quick_replies (
  id     serial primary key,
  label  text not null,
  body   text not null,
  sort   int not null default 0
);

create table public.settings (
  key    text primary key,
  value  text
);

-- =====================================================================
--  Security: only signed-in staff can read or change anything.
--  (Also turn OFF public sign-ups in Authentication settings.)
-- =====================================================================
do $$
declare t text;
begin
  foreach t in array array['staff','contacts','conversations','messages','deals','tasks',
                           'activities','calls','invoices','catalog','cohorts','quick_replies','settings']
  loop
    execute format('alter table public.%I enable row level security', 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 $$;

-- deleting records is limited to admins
create or replace function public.is_admin() returns boolean
language sql stable security definer set search_path = public as $$
  select exists (select 1 from public.staff where id = auth.uid() and role = 'admin');
$$;

do $$
declare t text;
begin
  foreach t in array array['contacts','deals','tasks','activities','invoices','catalog','cohorts','quick_replies']
  loop
    execute format('create policy "admins can delete %1$s" on public.%1$I for delete to authenticated using (public.is_admin())', t);
  end loop;
end $$;

-- live updates in the app
alter publication supabase_realtime add table public.messages, public.conversations, public.invoices, public.calls;

-- =====================================================================
--  Starting data (edit any of it later in Table Editor)
-- =====================================================================
insert into public.catalog (name, price, sort) values
 ('Kano general class, Intermediate (6 weeks)',        115000, 1),
 ('Kano general class, Advanced/Fellowship (6 weeks)', 115000, 2),
 ('Abuja general class, Intermediate',                 250000, 3),
 ('Online general class (6 weeks)',                    65000,  4),
 ('Private class, Kano',                               870000, 5),
 ('Private class, Abuja',                              1100000,6),
 ('Private class, Online',                             870000, 7),
 ('VIP class, physical (per person)',                  450000, 8),
 ('VIP class, online (per person)',                    300000, 9),
 ('Executive Program, Abuja (monthly)',                1500000,10),
 ('Polish Kids, Kano (per child)',                     65000,  11),
 ('Polish Kids, Online (per child)',                   50000,  12),
 ('English fluency, private (monthly)',                150000, 13),
 ('Self-paced tutorial videos (per level)',            30000,  14),
 ('Corporate training (custom quote)',                 0,      15);

insert into public.cohorts (name, office, schedule, price_label, enrolled, capacity, starts) values
 ('Intermediate',                        'Kano',   'Sat & Sun, 3:30–6pm, 6 weeks', '₦115,000',             0, 20, 'Next cohort'),
 ('Advanced & Fellowship',               'Kano',   'Sat & Sun, 3:30–6pm, 6 weeks', '₦115,000',             0, 15, 'Next cohort'),
 ('Polish Kids Communication',           'Kano',   'Mon–Thu, 9:30am–12:30pm',      '₦65,000 per child',    0, 15, 'Ongoing'),
 ('Intermediate',                        'Abuja',  'Fri–Sun, 4–6pm',               '₦250,000 per month',   0, 12, 'Ongoing'),
 ('Executive Program',                   'Abuja',  '3 times a week, cohort of 10', '₦1,500,000 per month', 0, 10, 'Next cohort'),
 ('Intermediate',                        'Online', '6 weeks, live online',         '₦65,000',              0, 30, 'Next cohort'),
 ('Polish Kids Communication',           'Online', 'Mon–Thu, 9–10:30am',           '₦50,000 per child',    0, 20, 'Ongoing');

insert into public.quick_replies (label, body, sort) values
 ('Kano prices',  E'Kano prices:\n• General class: ₦115,000 per level (6 weeks, Sat & Sun 3:30–6pm)\n• VIP class (3 people): ₦450,000 each\n• Private class: ₦870,000', 1),
 ('Abuja prices', E'Abuja prices:\n• General class (Intermediate/Advanced): ₦250,000\n• VIP class (3 people): ₦450,000 each\n• Private class: ₦1,100,000\n• Executive Program: ₦1,500,000 per month', 2),
 ('Online prices',E'Online prices:\n• General class: ₦65,000 per level (6 weeks)\n• VIP class (3 people): ₦300,000 each\n• Private class: ₦870,000\n• Self-paced videos: ₦30,000 per level', 3),
 ('How to pay',   'I will send you an invoice with a secure Paystack link, so you can pay by card, transfer or USSD. Your receipt comes automatically once payment is confirmed.', 4),
 ('Our offices',  E'Kano: Opposite Saudat Plaza, Sabon Bakin Zuwo Road, Tarauni.\nAbuja: Suite 305C, 3rd Floor, Nawa Complex, Jahi, Kado.', 5);

insert into public.settings (key, value) values
 ('auto_reply_enabled',      'true'),
 ('auto_reply_text',         E'Thank you for contacting The Quintillian Limited. Audi Me Loqui!\nA member of our team will reply shortly.'),
 ('wa_template_name',        'quintillian_follow_up'),     -- approved WhatsApp template for messages after 24 hours
 ('wa_template_lang',        'en'),
 ('default_owner_email',     ''),                          -- staff email that new enquiries are assigned to
 ('inbound_forward_numbers', ''),                          -- staff numbers that ring for incoming calls, comma separated
 ('billing_email',           'helloquintilian@gmail.com'); -- used on Paystack links when a client has no email

-- ===== UPDATE 2 (included) =====
-- ---------- 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();

-- ===== UPDATE 3 (included) =====
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;