-- =====================================================================
-- 01_schema.sql  —  REFERENCE ONLY
-- This is the schema you have ALREADY applied to your Supabase project.
-- Kept here so the repo is self-contained. You do NOT need to run it again.
-- =====================================================================

create type user_role          as enum ('admin', 'volunteer');
create type program_status     as enum ('open', 'closed', 'completed');
create type application_status as enum ('pending', 'approved', 'rejected');
create type notif_channel      as enum ('in_app', 'whatsapp', 'calendar');

create table profiles (
  id           uuid primary key references auth.users (id) on delete cascade,
  full_name    text not null,
  matric_no    text unique,
  email        text unique not null,
  phone        text,
  role         user_role not null default 'volunteer',
  total_merit  int not null default 0,
  created_at   timestamptz not null default now()
);

create table skills (
  id    uuid primary key default gen_random_uuid(),
  name  text unique not null
);

create table volunteer_skills (
  volunteer_id uuid not null references profiles (id) on delete cascade,
  skill_id     uuid not null references skills (id)   on delete cascade,
  primary key (volunteer_id, skill_id)
);

create table programs (
  id           uuid primary key default gen_random_uuid(),
  title        text not null,
  description  text not null,
  venue        text,
  start_at     timestamptz not null,
  end_at       timestamptz,
  slots        int not null default 0 check (slots >= 0),
  merit_value  int not null default 0 check (merit_value >= 0),
  status       program_status not null default 'open',
  created_by   uuid references profiles (id) on delete set null,
  created_at   timestamptz not null default now()
);

create table program_skills (
  program_id uuid not null references programs (id) on delete cascade,
  skill_id   uuid not null references skills (id)   on delete cascade,
  primary key (program_id, skill_id)
);

create table applications (
  id           uuid primary key default gen_random_uuid(),
  program_id   uuid not null references programs (id) on delete cascade,
  volunteer_id uuid not null references profiles (id) on delete cascade,
  status       application_status not null default 'pending',
  applied_at   timestamptz not null default now(),
  reviewed_by  uuid references profiles (id) on delete set null,
  reviewed_at  timestamptz,
  unique (program_id, volunteer_id)
);

create table merit_transactions (
  id           uuid primary key default gen_random_uuid(),
  volunteer_id uuid not null references profiles (id) on delete cascade,
  program_id   uuid references programs (id) on delete set null,
  points       int not null,
  reason       text,
  awarded_by   uuid references profiles (id) on delete set null,
  created_at   timestamptz not null default now()
);

create table rewards (
  id              uuid primary key default gen_random_uuid(),
  name            text not null,
  description     text,
  points_required int not null default 0 check (points_required >= 0)
);

create table volunteer_rewards (
  volunteer_id uuid not null references profiles (id) on delete cascade,
  reward_id    uuid not null references rewards (id)  on delete cascade,
  earned_at    timestamptz not null default now(),
  primary key (volunteer_id, reward_id)
);

create table certificates (
  id           uuid primary key default gen_random_uuid(),
  volunteer_id uuid not null references profiles (id) on delete cascade,
  program_id   uuid references programs (id) on delete set null,
  file_url     text,
  issued_at    timestamptz not null default now(),
  issued_by    uuid references profiles (id) on delete set null
);

create table notifications (
  id           uuid primary key default gen_random_uuid(),
  volunteer_id uuid not null references profiles (id) on delete cascade,
  program_id   uuid references programs (id) on delete set null,
  title        text not null,
  message      text,
  channel      notif_channel not null default 'in_app',
  is_read      boolean not null default false,
  sent_at      timestamptz not null default now()
);

create table feedback (
  id           uuid primary key default gen_random_uuid(),
  program_id   uuid not null references programs (id) on delete cascade,
  volunteer_id uuid not null references profiles (id) on delete cascade,
  rating       int check (rating between 1 and 5),
  comment      text,
  created_at   timestamptz not null default now()
);

-- indexes, handle_new_user() trigger, is_admin(), and the STARTER RLS
-- policies are also part of your applied schema (see your original file).