Phantom SQL Script

create extension if not exists pgcrypto;

create table if not exists public.users (
  created_at timestamp with time zone not null default now(),
  settings jsonb null default '{}'::jsonb,
  id uuid not null default auth.uid(),
  theme jsonb null,
  constraint users_pkey primary key (id),
  constraint users_id_key unique (id)
) tablespace pg_default;

create table if not exists public.customers (
  id uuid not null default gen_random_uuid(),
  created_at timestamp with time zone not null default now(),
  name text null default 'User',
  user_id uuid null,
  discord_id text null,
  constraint customers_pkey primary key (id),
  constraint customers_discord_id_key unique (discord_id),
  constraint customers_user_id_fkey foreign key (user_id) references public.users (id) on update cascade on delete cascade
) tablespace pg_default;

create table if not exists public.products (
  id uuid not null default gen_random_uuid(),
  created_at timestamp with time zone not null default now(),
  name text null default 'New Product',
  image_url text null,
  version text null default 'v1',
  price double precision null default 0,
  enabled boolean null default true,
  user_id uuid null default auth.uid(),
  constraint products_pkey primary key (id),
  constraint products_user_id_fkey foreign key (user_id) references public.users (id) on update cascade on delete cascade
) tablespace pg_default;

create table if not exists public.licenses (
  id bigint generated by default as identity not null,
  customer uuid null,
  product uuid null,
  issue_date timestamp with time zone null default now(),
  expiration timestamp with time zone null,
  license_key text null,
  user_id uuid null,
  updated_at timestamp with time zone null,
  status text null default 'active',
  used_ips text[] null default '{}'::text[],
  whitelisted_ips text[] null default '{}'::text[],
  ip_whitelist_enabled boolean null default false,
  max_ips smallint null default 1,
  custom_data jsonb null default '{}'::jsonb,
  constraint licenses_pkey primary key (id),
  constraint licenses_id_key unique (id),
  constraint licenses_license_key_key unique (license_key),
  constraint licenses_customer_fkey foreign key (customer) references public.customers (id) on update cascade on delete cascade,
  constraint licenses_product_fkey foreign key (product) references public.products (id) on update cascade on delete cascade,
  constraint licenses_user_id_fkey foreign key (user_id) references public.users (id) on update cascade on delete cascade,
  constraint licenses_check check (true)
) tablespace pg_default;

create table if not exists public.logs (
  id bigint generated by default as identity not null,
  created_at timestamp with time zone not null default now(),
  license_key text null,
  ip_address text null,
  metadata jsonb null default '{}'::jsonb,
  status text null default 'Unknown',
  user_id uuid null default auth.uid(),
  constraint logs_pkey primary key (id),
  constraint logs_id_key unique (id),
  constraint logs_license_key_fkey foreign key (license_key) references public.licenses (license_key) on update cascade on delete cascade,
  constraint logs_user_id_fkey foreign key (user_id) references public.users (id) on update cascade on delete cascade
) tablespace pg_default;

alter table public.customers
  add column if not exists auth_user_id uuid unique,
  add column if not exists client_email text unique,
  add column if not exists client_password text,
  add column if not exists metadata jsonb;

alter table public.products
  add column if not exists metadata jsonb;

alter table public.licenses
  add column if not exists max_hwids int4,
  add column if not exists used_hwids text[];

create index if not exists customers_auth_user_id_idx on public.customers(auth_user_id);
create index if not exists customers_client_email_idx on public.customers(client_email);

update public.users
set settings = coalesce(settings, '{}'::jsonb) || jsonb_build_object('client_email_domain', 'buzz.dev')
where (settings->>'client_email_domain') is null;

do $$
begin
  if not exists (
    select 1 from pg_publication_tables
    where pubname = 'supabase_realtime'
    and schemaname = 'public'
    and tablename = 'logs'
  ) then
    alter publication supabase_realtime add table public.logs;
  end if;
end
$$;

alter table public.users enable row level security;
alter table public.licenses enable row level security;
alter table public.logs enable row level security;
alter table public.products enable row level security;
alter table public.customers enable row level security;

drop policy if exists "Users can access their own record" on public.users;
create policy "Users can access their own record"
on public.users
for all
using (auth.uid() = id);

drop policy if exists "Users can select their own licenses" on public.licenses;
create policy "Users can select their own licenses"
on public.licenses
for select
using (auth.uid() = customer);

drop policy if exists "Users can select their own products" on public.products;
create policy "Users can select their own products"
on public.products
for all
using (auth.uid() = user_id);

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

drop policy if exists "Users can access their own customer record" on public.customers;
create policy "Users can access their own customer record"
on public.customers
for all
using (auth.uid() = user_id);