Bentornato!

-- 1. Create User Quotas Table
create table if not exists public.user_quotas (
    user_id uuid references auth.users not null primary key,
    daily_limit int default 500,
    used_count int default 0,
    extra_quota int default 0,
    last_reset_date date default current_date,
    created_at timestamptz default now()
);

-- 2. Create Transactions Table
create table if not exists public.transactions (
    id uuid default gen_random_uuid() primary key,
    user_id uuid references auth.users not null,
    amount decimal(10, 2) not null,
    quota_added int not null,
    status text default 'completed',
    created_at timestamptz default now()
);

-- 3. Enable RLS (Security)
alter table public.user_quotas enable row level security;
alter table public.transactions enable row level security;

-- 4. Create Security Policies
create policy "Users can view their own quota"
    on public.user_quotas for select using (auth.uid() = user_id);

create policy "Users can update their own quota"
    on public.user_quotas for update using (auth.uid() = user_id);

create policy "Users can insert their own quota"
    on public.user_quotas for insert with check (auth.uid() = user_id);

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

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

-- 5. Create Helper Function (RPC)
create or replace function get_or_init_quota(check_user_id uuid)
returns json
language plpgsql
security definer
as $$
declare
    quota_record record;
    current_day date;
begin
    current_day := current_date;
    
    select * into quota_record from public.user_quotas where user_id = check_user_id;
    
    if not found then
        insert into public.user_quotas (user_id, last_reset_date)
        values (check_user_id, current_day)
        returning * into quota_record;
    else
        if quota_record.last_reset_date < current_day then
            update public.user_quotas
            set used_count = 0, last_reset_date = current_day
            where user_id = check_user_id
            returning * into quota_record;
        end if;
    end if;

    return row_to_json(quota_record);
end;
$$;

-- 6. Create Account Deletion Function
create or replace function delete_user_account()
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
    delete from public.user_quotas where user_id = auth.uid();
    delete from public.transactions where user_id = auth.uid();
    delete from auth.users where id = auth.uid();
end;
$$;