-- Create a table for User Subscription
 
-- Enum Type for Subscription Status
create type subscriptiontype as enum ('free', 'standard', 'premium');
create type billingcycle as enum ('month', 'year');
 
-- Table
create table
  public.subscriptions (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    type public.subscriptiontype not null default 'free'::subscriptiontype,
    subscription_id text null,
    amount int null,
    interval public.billingcycle null,
    start_date date null,
    active boolean null,
    user_email text not null,
    user_id uuid not null,
    constraint subscriptions_pkey primary key (id),
    constraint subscriptions_user_email_fkey foreign key (user_email) references users (email),
    constraint subscriptions_user_id_fkey foreign key (user_id) references users (id)
  ) tablespace pg_default;
 
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table subscriptions
  enable row level security;
 
create policy "Users can insert their own row." on subscriptions
  for insert with check (auth.uid() = id);
 
create policy "Users can read own row" on subscriptions
  for select using (auth.uid() = id);
 
-- Function to handle new subscription for users
create function public.handle_new_subscription()
returns trigger as $$
BEGIN
  -- Insert a new subscription record for the new user
  insert into public.subscriptions (user_id, user_email)
  values (new.id, new.email);
 
  return new;
END;
$$ language plpgsql security definer;
 
-- Trigger to execute the function after a new user is created
create trigger on_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_subscription();