Your Growth,
Our Mission.

-- 1. Create Tables
create table if not exists public."Job Applications" (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  name text,
  email text,
  phone text,
  job_category text,
  experience text,
  resume_url text,
  status text default 'New'
);

create table if not exists public.profiles (
  id uuid references auth.users on delete cascade not null primary key,
  email text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 2. Enable Security (RLS)
alter table public."Job Applications" enable row level security;
alter table public.profiles enable row level security;

-- 3. Policies (Public Access for this Demo)
create policy "Public Insert Apps" on public."Job Applications" for insert with check (true);
create policy "Public Read Apps" on public."Job Applications" for select using (true);

create policy "Public Read Profiles" on profiles for select using (true);
-- Note: Insert policy for profiles is handled by the trigger (security definer), so client doesn't need insert permission on profiles if using trigger.

-- 4. AUTO-SYNC TRIGGER (The Fix for User Sync)
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

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();

-- 5. BACKFILL EXISTING USERS (Fix for missing 3 users)
insert into public.profiles (id, email)
select id, email from auth.users
on conflict (id) do nothing;

-- 6. STORAGE SETUP
insert into storage.buckets (id, name, public) values ('resumes', 'resumes', true) 
on conflict (id) do nothing;

create policy "Public Upload Resumes" on storage.objects for insert with check ( bucket_id = 'resumes' );
create policy "Public View Resumes" on storage.objects for select using ( bucket_id = 'resumes' );