Database Guide

-- Users table for profile information
CREATE TABLE public.users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE,
  provider TEXT DEFAULT 'email' NOT NULL,
  role TEXT DEFAULT 'user' NOT NULL,
  billing_address JSONB,
  payment_method JSONB,
  terms_accepted BOOLEAN DEFAULT FALSE,
  terms_accepted_at TIMESTAMP WITH TIME ZONE,
  terms_version TEXT
);

-- Customers table for subscription management
CREATE TABLE public.customers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  stripe_customer_id TEXT UNIQUE,
  subscription_status TEXT,
  subscription_tier TEXT DEFAULT 'free',
  subscription_updated_at TIMESTAMP WITH TIME ZONE,
  trial_end TIMESTAMP WITH TIME ZONE,
  is_lifetime BOOLEAN DEFAULT FALSE,
  current_period_end TIMESTAMP WITH TIME ZONE
);

-- Products table for subscription tiers
CREATE TABLE public.products (
  id TEXT PRIMARY KEY,
  active BOOLEAN DEFAULT TRUE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  image TEXT,
  metadata JSONB
);

-- Prices table for product pricing
CREATE TABLE public.prices (
  id TEXT PRIMARY KEY,
  product_id TEXT REFERENCES public.products(id) NOT NULL,
  active BOOLEAN DEFAULT TRUE NOT NULL,
  description TEXT,
  unit_amount BIGINT NOT NULL,
  currency TEXT NOT NULL,
  type TEXT NOT NULL,
  interval TEXT,
  interval_count INTEGER,
  trial_period_days INTEGER,
  metadata JSONB
);

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.prices ENABLE ROW LEVEL SECURITY;

-- Users table policies
CREATE POLICY "Users can read own data" ON public.users 
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own data" ON public.users 
  FOR UPDATE USING (auth.uid() = id);

-- Customers table policies
CREATE POLICY "Authenticated users can read customer data" ON public.customers 
  FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "Users can read own customer data" ON public.customers 
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own customer data" ON public.customers 
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own customer data" ON public.customers 
  FOR UPDATE USING (auth.uid() = user_id);

-- Products and Prices policies
CREATE POLICY "Anyone can read active products" ON public.products 
  FOR SELECT USING (active = true);

CREATE POLICY "Anyone can read active prices" ON public.prices 
  FOR SELECT USING (active = true);

-- Available products
INSERT INTO public.products (id, active, name, description, metadata) VALUES
  ('prod_free', true, 'Free Plan', 'Basic features for individuals', 
   '{"features": ["Basic dashboard", "Limited API calls", "Email support"]}'),
  ('prod_basic', true, 'Basic Plan', 'Essential features for small teams', 
   '{"features": ["Everything in Free", "Advanced analytics", "Team collaboration"]}'),
  ('prod_pro', true, 'Pro Plan', 'Advanced features for growing businesses', 
   '{"features": ["Everything in Basic", "Premium support", "Custom integrations", "Advanced security"]}');

-- Product pricing
INSERT INTO public.prices (id, product_id, active, description, unit_amount, currency, type, interval) VALUES
  ('price_free_monthly', 'prod_free', true, 'Free Plan', 0, 'usd', 'recurring', 'month'),
  ('price_basic_monthly', 'prod_basic', true, 'Basic Plan Monthly', 1000, 'usd', 'recurring', 'month'),
  ('price_pro_monthly', 'prod_pro', true, 'Pro Plan Monthly', 2500, 'usd', 'recurring', 'month');