supabase/schema.sql

-- Supabase Database Schema
-- Run this SQL in your Supabase SQL Editor

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- User profiles table
create table if not exists profiles (
  id uuid references auth.users on delete cascade primary key,
  email text unique,
  full_name text,
  avatar_url text,
  is_premium boolean default true, -- Beta: all users premium
  premium_expires_at timestamp with time zone,
  tokens_remaining integer default -1, -- Beta: unlimited
  tokens_reset_at timestamp with time zone default now() + interval '24 hours',
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Projects table (user saved code)
create table if not exists projects (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  name text default 'Untitled Project',
  description text,
  html_code text default '',
  css_code text default '',
  js_code text default '',
  preview_url text,
  thumbnail_url text,
  is_public boolean default false,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- AI chat messages history
create table if not exists chat_messages (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  project_id uuid references projects(id) on delete cascade,
  role text not null check (role in ('user', 'assistant')),
  content text not null,
  tokens_used integer default 0,
  created_at timestamp with time zone default now()
);

-- Token transactions ledger
create table if not exists token_transactions (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  transaction_type text not null check (transaction_type in ('ai_usage', 'ad_reward', 'daily_reset', 'purchase', 'premium_bonus')),
  amount integer not null,
  description text,
  created_at timestamp with time zone default now()
);

-- Subscriptions table (kept for future Stripe integration)
create table if not exists subscriptions (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade unique not null,
  status text not null check (status in ('active', 'canceled', 'past_due', 'trialing', 'beta')),
  current_period_end timestamp with time zone,
  cancel_at_period_end boolean default false,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Ad engagement tracking
create table if not exists ad_engagements (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  ad_type text not null check (ad_type in ('banner', 'rewarded')),
  ad_slot text,
  reward_amount integer default 0,
  completed boolean default false,
  created_at timestamp with time zone default now()
);

-- Enable Row Level Security
alter table profiles enable row level security;
alter table projects enable row level security;
alter table chat_messages enable row level security;
alter table token_transactions enable row level security;
alter table subscriptions enable row level security;
alter table ad_engagements enable row level security;

-- RLS Policies for profiles
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);

-- RLS Policies for projects
create policy "Users can view own projects"
  on projects for select
  using (auth.uid() = user_id);

create policy "Users can view public projects"
  on projects for select
  using (is_public = true);

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

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

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

-- RLS Policies for chat_messages
create policy "Users can view own messages"
  on chat_messages for select
  using (auth.uid() = user_id);

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

-- RLS Policies for token_transactions
create policy "Users can view own transactions"
  on token_transactions for select
  using (auth.uid() = user_id);

create policy "System can create transactions"
  on token_transactions for insert
  with check (true);

-- RLS Policies for subscriptions
create policy "Users can view own subscription"
  on subscriptions for select
  using (auth.uid() = user_id);

create policy "Users can manage own subscriptions"
  on subscriptions for all
  using (auth.uid() = user_id);

-- RLS Policies for ad_engagements
create policy "Users can view own engagements"
  on ad_engagements for select
  using (auth.uid() = user_id);

create policy "System can create engagements"
  on ad_engagements for insert
  with check (true);

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

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

-- Function to reset tokens daily
create or replace function reset_daily_tokens()
returns void as $$
begin
  update profiles
  set tokens_remaining = 50,
      tokens_reset_at = now() + interval '24 hours'
  where tokens_reset_at <= now()
  and is_premium = false;
end;
$$ language plpgsql security definer;

-- Indexes for performance
create index if not exists idx_projects_user_id on projects(user_id);
create index if not exists idx_projects_updated on projects(user_id, updated_at desc);
create index if not exists idx_chat_messages_user_project on chat_messages(user_id, project_id);
create index if not exists idx_token_transactions_user on token_transactions(user_id, created_at desc);
create index if not exists idx_ad_engagements_user on ad_engagements(user_id, created_at desc);