create table profiles (
    user_id uuid primary key references auth.users(id) on delete cascade,
    user_type text not null check (user_type in ('brand', 'creator', 'admin'))
);
alter table profiles enable row level security;
create policy profiles_everyone_select
    on profiles for select
    to authenticated, anon
    using (true);
create policy profiles_user_update
    on profiles for update
    using ( (select auth.uid()) = user_id );

create table brands (
    user_id uuid primary key references profiles(user_id) on delete cascade,
    company_name text not null,
    website text not null
);
alter table brands enable row level security;
create policy brands_everyone_select
    on brands for select
    to authenticated, anon
    using (true);
create policy brands_user_update
    on brands for update
    using ( (select auth.uid()) = user_id );

create table creators (
    user_id uuid primary key references profiles(user_id) on delete cascade,
    nickname text not null,
    bio text,
    social_links jsonb
);
alter table creators enable row level security;
create policy creators_everyone_select
    on creators for select
    to authenticated, anon
    using (true);
create policy creators_user_update
    on creators for update
    using ( (select auth.uid()) = user_id );


create table admins (
    user_id uuid primary key references profiles(user_id) on delete cascade
);
alter table admins enable row level security;
create policy admins_everyone_select
    on admins for select
    to authenticated, anon
    using (true);
create policy admins_user_update
    on admins for update
    using ( (select auth.uid()) = user_id );


-- based on raw_user_meta_data, create a profile and subprofile for new users
create function public.handle_new_user()
    returns trigger
    language plpgsql
    security definer set search_path = ''
as $$
declare
    user_type text;
begin
    user_type := new.raw_user_meta_data ->> 'user_type';
    if user_type is null or trim(user_type) = '' then
        raise exception 'Missing required user_type in raw_user_meta_data';
    end if;
    insert into profiles (user_id, user_type) values (new.id, new.raw_user_meta_data ->> 'user_type');
    if user_type = 'brand' then
        insert into brands (user_id, company_name, website)
        values (new.id,
                new.raw_user_meta_data ->> 'company_name',
                new.raw_user_meta_data ->> 'website');
    elsif user_type = 'creator' then
        insert into creators (user_id, nickname, bio, social_links)
        values (new.id,
                new.raw_user_meta_data ->> 'nickname',
                new.raw_user_meta_data ->> 'bio',
                new.raw_user_meta_data ->> 'social_links');
    elsif user_type = 'admin' then
        insert into admins (user_id) values (new.id);
    end if;
    return new;
end;
$$;

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

-- keep validation on subprofiles
create or replace function public.enforce_profile_type(user_id uuid, expected_type text)
    returns void
    language plpgsql
    security definer set search_path = ''
as $$
declare
    actual_type text;
begin
    select user_type into actual_type
    from profiles where user_id = user_id;

    if actual_type is null then
        raise exception 'profile not found for id: %', user_id;
    end if;

    if actual_type <> expected_type then
        raise exception 'user_type mismatch: expected %, got %', expected_type, actual_type;
    end if;
end;
$$;

create or replace function public.check_brand_user_type()
    returns trigger
    language plpgsql
    security definer set search_path = ''
as $$ begin
    perform public.enforce_profile_type(new.user_id, 'brand');
    return new;
end; $$;

create or replace function public.check_creator_user_type()
    returns trigger
    language plpgsql
    security definer set search_path = ''
as $$ begin
    perform public.enforce_profile_type(new.user_id, 'creator');
    return new;
end; $$;

create or replace function public.check_admin_user_type()
    returns trigger
    language plpgsql
    security definer set search_path = ''
as $$ begin
    perform public.enforce_profile_type(new.user_id, 'admin');
    return new;
end; $$;

create trigger check_brands_user_type
    before insert or update
    on brands
    for each row
execute function public.check_brand_user_type();

create trigger check_creators_user_type
    before insert or update
    on creators
    for each row
execute function public.check_creator_user_type();

create trigger check_admins_user_type
    before insert or update
    on admins
    for each row
execute function public.check_admin_user_type();