create extension if not exists pgcrypto;
create table if not exists job_roles(id uuid primary key default gen_random_uuid(),title text not null,department text,responsibilities text,working_style text,default_salary text,default_hours text,default_work_mode text,meeting_platform text,meeting_link text,meeting_schedule text,created_at timestamptz default now());
create table if not exists settings(id int primary key default 1,company_name text default 'MyProFunnels',meeting_platform text,meeting_link text,meeting_schedule text,task_sheet_url text,default_hours text,default_work_mode text,default_probation text,updated_at timestamptz default now());
create table if not exists applications(id uuid primary key default gen_random_uuid(),application_number text unique not null,full_name text not null,email text not null,whatsapp_number text not null,position text not null,experience text,qualification text,skills text,city text,state text,status text default 'pending',admin_notes text,joining_date date,salary text,department text,working_hours text,work_mode text,reporting_manager text,probation_period text,meeting_platform text,meeting_link text,meeting_schedule text,task_sheet_url text,offer_pdf_path text,created_at timestamptz default now(),updated_at timestamptz default now());
create table if not exists communication_logs(id uuid primary key default gen_random_uuid(),application_id uuid references applications(id) on delete cascade,communication_type text,recipient text,subject text,status text,provider_message_id text,error_message text,sent_at timestamptz,created_at timestamptz default now());
create table if not exists audit_logs(id uuid primary key default gen_random_uuid(),application_id uuid references applications(id) on delete cascade,action text,details jsonb,created_at timestamptz default now());
alter table applications enable row level security;
create policy "public submit" on applications for insert to anon with check(true);
alter table job_roles enable row level security;alter table settings enable row level security;
insert into settings(id) values(1) on conflict(id) do nothing;