Database Schema

-- Customers table
CREATE TABLE IF NOT EXISTS 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
CREATE TABLE IF NOT EXISTS public.products (
  id TEXT PRIMARY KEY,
  active BOOLEAN DEFAULT TRUE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  image TEXT,
  metadata JSONB
);

-- Prices table
CREATE TABLE IF NOT EXISTS 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
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.prices ENABLE ROW LEVEL SECURITY;

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

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

-- Allow authenticated users to insert their own customer records
CREATE POLICY "Users can insert own customer data" ON public.customers 
  FOR INSERT WITH CHECK (auth.uid() = user_id);

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

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

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

-- Create indexes
CREATE INDEX IF NOT EXISTS customers_user_id_idx ON public.customers (user_id);
CREATE INDEX IF NOT EXISTS prices_product_id_idx ON public.prices (product_id);
CREATE INDEX IF NOT EXISTS customers_stripe_customer_idx ON public.customers (stripe_customer_id);