-- Enable RLS
alter table profiles enable row level security;
alter table applications enable row level security;

-- Create profiles table
create table profiles (
  id uuid references auth.users on delete cascade primary key,
  full_name text,
  email text unique,
  avatar_url text,
  phone text,
  education text,
  bio text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Create applications table
create table applications (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references auth.users on delete cascade not null,
  school_id text not null,
  program_name text not null,
  status text check (status in ('draft', 'submitted', 'in_review', 'accepted', 'rejected')) default 'draft',
  submitted_at timestamp with time zone,
  deadline timestamp with time zone not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Create RLS policies
create policy "Users can view own profile"
  on profiles for select
  using ( auth.uid() = id );

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

create policy "Users can view own applications"
  on applications for select
  using ( auth.uid() = user_id );

create policy "Users can insert own applications"
  on applications for insert
  with check ( auth.uid() = user_id );

create policy "Users can update own applications"
  on applications for update
  using ( auth.uid() = user_id );

create policy "Users can delete own applications"
  on applications for delete
  using ( auth.uid() = user_id );

-- Create storage bucket for avatars
insert into storage.buckets (id, name)
values ('avatars', 'avatars');

-- Enable RLS for storage
create policy "Avatar images are publicly accessible"
  on storage.objects for select
  using ( bucket_id = 'avatars' );

create policy "Users can upload their own avatar"
  on storage.objects for insert
  with check (
    bucket_id = 'avatars' AND
    auth.uid() = owner
  );

create policy "Users can update their own avatar"
  on storage.objects for update
  using (
    bucket_id = 'avatars' AND
    auth.uid() = owner
  );

create policy "Users can delete their own avatar"
  on storage.objects for delete
  using (
    bucket_id = 'avatars' AND
    auth.uid() = owner
  );

-- Create function to handle user creation
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name, email)
  values (new.id, new.raw_user_meta_data->>'full_name', new.email);
  return new;
end;
$$ language plpgsql security definer;

-- Create trigger for new user creation
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();