create extension if not exists "pgcrypto";

create type public.user_role as enum ('candidate', 'employer', 'admin');
create type public.verification_tier as enum ('none', 'tier_1', 'tier_2');
create type public.application_status as enum ('saved', 'applied', 'shortlisted', 'interview', 'offer', 'rejected');

create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  role public.user_role not null,
  full_name text,
  email text not null unique,
  phone text,
  location text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.candidate_profiles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references public.profiles(id) on delete cascade,
  headline text,
  summary text,
  sectors text[] not null default '{}',
  skills text[] not null default '{}',
  resume_url text,
  profile_score int not null default 0 check (profile_score between 0 and 100),
  created_at timestamptz not null default now()
);

create table public.companies (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles(id) on delete cascade,
  name text not null,
  business_email text not null,
  address text,
  verification public.verification_tier not null default 'none',
  cac_certificate_url text,
  tin text,
  created_at timestamptz not null default now()
);

create unique index if not exists companies_owner_id_key on public.companies(owner_id);

create table public.jobs (
  id uuid primary key default gen_random_uuid(),
  company_id uuid not null references public.companies(id) on delete cascade,
  title text not null,
  description text not null,
  requirements text[] not null default '{}',
  location text,
  work_type text not null default 'Remote',
  interview_type text,
  interview_platform text,
  is_featured boolean not null default false,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table public.applications (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references public.jobs(id) on delete cascade,
  candidate_id uuid not null references public.candidate_profiles(id) on delete cascade,
  status public.application_status not null default 'saved',
  match_score int not null default 0 check (match_score between 0 and 100),
  ai_summary text,
  created_at timestamptz not null default now(),
  unique (job_id, candidate_id)
);

create table public.interviews (
  id uuid primary key default gen_random_uuid(),
  application_id uuid not null references public.applications(id) on delete cascade,
  starts_at timestamptz not null,
  mode text not null,
  meeting_url text,
  prep_questions jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now()
);

create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references public.profiles(id) on delete cascade,
  plan_name text not null,
  amount_ngn int not null default 0,
  status text not null default 'trialing',
  renews_at timestamptz,
  created_at timestamptz not null default now()
);

create table public.moderation_events (
  id uuid primary key default gen_random_uuid(),
  admin_id uuid references public.profiles(id) on delete set null,
  subject_type text not null,
  subject_id uuid,
  action text not null,
  reason text,
  severity text not null default 'medium' check (severity in ('low', 'medium', 'high')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table public.compliance_appeals (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  subject_type text not null,
  subject_id uuid,
  reason text not null,
  status text not null default 'open' check (status in ('open', 'reviewing', 'approved', 'denied')),
  resolution_note text,
  reviewed_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists applications_candidate_id_idx on public.applications(candidate_id);
create index if not exists applications_job_id_idx on public.applications(job_id);
create index if not exists jobs_company_id_idx on public.jobs(company_id);
create index if not exists interviews_application_id_idx on public.interviews(application_id);
create index if not exists subscriptions_owner_id_idx on public.subscriptions(owner_id);
create index if not exists moderation_events_admin_id_idx on public.moderation_events(admin_id);
create index if not exists compliance_appeals_user_id_idx on public.compliance_appeals(user_id);
create index if not exists compliance_appeals_reviewed_by_idx on public.compliance_appeals(reviewed_by);

alter table public.profiles enable row level security;
alter table public.candidate_profiles enable row level security;
alter table public.companies enable row level security;
alter table public.jobs enable row level security;
alter table public.applications enable row level security;
alter table public.interviews enable row level security;
alter table public.subscriptions enable row level security;
alter table public.moderation_events enable row level security;
alter table public.compliance_appeals enable row level security;

create or replace function public.touch_updated_at()
returns trigger
language plpgsql
set search_path = public
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger profiles_touch_updated_at
  before update on public.profiles
  for each row execute function public.touch_updated_at();

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
  requested_role public.user_role;
begin
  requested_role := coalesce((new.raw_user_meta_data ->> 'role')::public.user_role, 'candidate');

  if requested_role = 'candidate' and new.email not like '%@gmail.com' then
    raise exception 'Candidates must register with a Gmail address.';
  end if;

  if requested_role = 'employer' and new.email like '%@gmail.com' then
    raise exception 'Employers must register with a business email address.';
  end if;

  insert into public.profiles (id, role, email, full_name)
  values (new.id, requested_role, new.email, new.raw_user_meta_data ->> 'full_name')
  on conflict (id) do nothing;

  if requested_role = 'candidate' then
    insert into public.candidate_profiles (user_id, headline, sectors, skills, profile_score)
    values (new.id, 'New hirly.ng candidate', '{}', '{}', 20)
    on conflict (user_id) do nothing;
  end if;

  return new;
end;
$$;

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

create policy "profiles can read own profile"
  on public.profiles for select
  using (auth.uid() = id);

create policy "profiles can insert own profile"
  on public.profiles for insert
  with check (auth.uid() = id);

create policy "profiles can update own profile"
  on public.profiles for update
  using (auth.uid() = id);

create policy "candidate owns candidate profile"
  on public.candidate_profiles for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create policy "employers manage own companies"
  on public.companies for all
  using (auth.uid() = owner_id)
  with check (auth.uid() = owner_id);

create policy "public can read active jobs"
  on public.jobs for select
  using (is_active = true);

create policy "employers manage company jobs"
  on public.jobs for all
  using (
    exists (
      select 1 from public.companies
      where companies.id = jobs.company_id
      and companies.owner_id = auth.uid()
    )
  )
  with check (
    exists (
      select 1 from public.companies
      where companies.id = jobs.company_id
      and companies.owner_id = auth.uid()
    )
  );

create policy "candidates read own applications"
  on public.applications for select
  using (
    exists (
      select 1 from public.candidate_profiles
      where candidate_profiles.id = applications.candidate_id
      and candidate_profiles.user_id = auth.uid()
    )
  );

create policy "candidates create own applications"
  on public.applications for insert
  with check (
    exists (
      select 1 from public.candidate_profiles
      where candidate_profiles.id = applications.candidate_id
      and candidate_profiles.user_id = auth.uid()
    )
  );

create policy "candidates update own applications"
  on public.applications for update
  using (
    exists (
      select 1 from public.candidate_profiles
      where candidate_profiles.id = applications.candidate_id
      and candidate_profiles.user_id = auth.uid()
    )
  )
  with check (
    exists (
      select 1 from public.candidate_profiles
      where candidate_profiles.id = applications.candidate_id
      and candidate_profiles.user_id = auth.uid()
    )
  );

create policy "employers read applications for own jobs"
  on public.applications for select
  using (
    exists (
      select 1
      from public.jobs
      join public.companies on companies.id = jobs.company_id
      where jobs.id = applications.job_id
      and companies.owner_id = auth.uid()
    )
  );

create policy "interview parties can read interviews"
  on public.interviews for select
  using (
    exists (
      select 1
      from public.applications
      join public.candidate_profiles on candidate_profiles.id = applications.candidate_id
      join public.jobs on jobs.id = applications.job_id
      join public.companies on companies.id = jobs.company_id
      where applications.id = interviews.application_id
      and (candidate_profiles.user_id = auth.uid() or companies.owner_id = auth.uid())
    )
  );

create policy "users manage own subscriptions"
  on public.subscriptions for all
  using (auth.uid() = owner_id)
  with check (auth.uid() = owner_id);

create policy "admins manage moderation events"
  on public.moderation_events for all
  using (
    exists (
      select 1 from public.profiles
      where profiles.id = auth.uid()
      and profiles.role = 'admin'
    )
  )
  with check (
    exists (
      select 1 from public.profiles
      where profiles.id = auth.uid()
      and profiles.role = 'admin'
    )
  );

create policy "users create own compliance appeals"
  on public.compliance_appeals for insert
  with check (auth.uid() = user_id);

create policy "users read own compliance appeals"
  on public.compliance_appeals for select
  using (auth.uid() = user_id);

create policy "admins manage compliance appeals"
  on public.compliance_appeals for all
  using (
    exists (
      select 1 from public.profiles
      where profiles.id = auth.uid()
      and profiles.role = 'admin'
    )
  )
  with check (
    exists (
      select 1 from public.profiles
      where profiles.id = auth.uid()
      and profiles.role = 'admin'
    )
  );

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values (
  'resumes',
  'resumes',
  false,
  5242880,
  array[
    'application/pdf',
    'application/msword',
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
    'text/plain'
  ]
)
on conflict (id) do update
set
  public = excluded.public,
  file_size_limit = excluded.file_size_limit,
  allowed_mime_types = excluded.allowed_mime_types;

create policy "users can upload own resumes"
  on storage.objects for insert
  with check (
    bucket_id = 'resumes'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy "users can read own resumes"
  on storage.objects for select
  using (
    bucket_id = 'resumes'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy "users can update own resumes"
  on storage.objects for update
  using (
    bucket_id = 'resumes'
    and auth.uid()::text = (storage.foldername(name))[1]
  )
  with check (
    bucket_id = 'resumes'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy "users can delete own resumes"
  on storage.objects for delete
  using (
    bucket_id = 'resumes'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

revoke execute on function public.handle_new_user() from public;
revoke execute on function public.touch_updated_at() from public;
