GreenPage Guardian

-- Users Table
CREATE TABLE public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  full_name TEXT,
  avatar_url TEXT,
  billing_id TEXT -- Stripe Customer ID
);
COMMENT ON TABLE public.users IS 'Stores user profiles linked to Supabase Auth.';
COMMENT ON COLUMN public.users.billing_id IS 'Stripe Customer ID for billing purposes.';

-- Websites Table
CREATE TABLE public.websites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  url TEXT UNIQUE NOT NULL,
  name TEXT,
  last_scanned_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  scan_frequency TEXT DEFAULT 'daily', -- 'daily', 'weekly', 'monthly'
  is_active BOOLEAN DEFAULT TRUE,
  badge_slug TEXT UNIQUE DEFAULT gen_random_uuid() -- Unique identifier for public badge
);
CREATE INDEX idx_websites_user_id ON public.websites(user_id);
CREATE INDEX idx_websites_badge_slug ON public.websites(badge_slug);
COMMENT ON TABLE public.websites IS 'Stores websites added by users for monitoring.';
COMMENT ON COLUMN public.websites.url IS 'The URL of the website to monitor.';
COMMENT ON COLUMN public.websites.badge_slug IS 'Unique slug for embedding the public green badge.';

-- Scan Results Table
CREATE TABLE public.scan_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  website_id UUID NOT NULL REFERENCES public.websites(id) ON DELETE CASCADE,
  scanned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  green_score INT NOT NULL, -- 0-100 score
  page_weight_kb INT,
  num_requests INT,
  asset_optimization_score INT, -- 0-100
  hosting_green_rating TEXT, -- e.g., 'A+', 'B', 'C', 'Unknown'
  recommendations JSONB, -- JSON array of actionable recommendations
  raw_data JSONB -- Raw data from the scanning service
);
CREATE INDEX idx_scan_results_website_id ON public.scan_results(website_id);
CREATE INDEX idx_scan_results_scanned_at ON public.scan_results(scanned_at DESC);
COMMENT ON TABLE public.scan_results IS 'Stores detailed results of each website scan.';
COMMENT ON COLUMN public.scan_results.green_score IS 'Overall green score for the website (0-100).';
COMMENT ON COLUMN public.scan_results.recommendations IS 'JSON array of actionable tips for improvement.';

-- Subscriptions Table (Stripe integration)
CREATE TABLE public.subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT NOT NULL,
  stripe_subscription_id TEXT NOT NULL UNIQUE,
  stripe_price_id TEXT NOT NULL,
  status TEXT NOT NULL, -- 'active', 'canceled', 'past_due', 'unpaid'
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_subscriptions_user_id ON public.subscriptions(user_id);
CREATE INDEX idx_subscriptions_stripe_customer_id ON public.subscriptions(stripe_customer_id);
COMMENT ON TABLE public.subscriptions IS 'Manages user subscriptions linked to Stripe.';