COMPLETE_SCHEMA_ADDITIONS.sql

-- ============================================================================
-- COMPLETE SCHEMA ADDITIONS FOR OPTIVISE APP
-- ============================================================================
-- This file contains all missing tables, columns, and updates needed
-- to make the database schema complete for the Optivise application.
-- ============================================================================

-- ============================================================================
-- 1. MISSING TABLES
-- ============================================================================

-- Admin Whitelist Table (for admin authentication)
CREATE TABLE IF NOT EXISTS public.admin_whitelist (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  CONSTRAINT admin_whitelist_pkey PRIMARY KEY (id)
);

-- Serpstat Data Table (for storing Serpstat API keyword data)
CREATE TABLE IF NOT EXISTS public.serpstat_data (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  keyword text NOT NULL,
  search_volume integer,
  difficulty integer,
  cpc numeric,
  competition integer,
  position integer,
  date date NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  CONSTRAINT serpstat_data_pkey PRIMARY KEY (id)
);

-- User Locations Table (for user-location access mapping)
CREATE TABLE IF NOT EXISTS public.user_locations (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  location_id text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  CONSTRAINT user_locations_pkey PRIMARY KEY (id),
  CONSTRAINT user_locations_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE,
  CONSTRAINT user_locations_location_id_fkey FOREIGN KEY (location_id) REFERENCES public.locations(location_id) ON DELETE CASCADE,
  CONSTRAINT user_locations_user_location_unique UNIQUE (user_id, location_id)
);

-- User Roles Table (for user role management)
CREATE TYPE public.user_role AS ENUM ('admin', 'user', 'viewer');

CREATE TABLE IF NOT EXISTS public.user_roles (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL UNIQUE,
  role public.user_role NOT NULL DEFAULT 'user'::public.user_role,
  created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
  CONSTRAINT user_roles_pkey PRIMARY KEY (id),
  CONSTRAINT user_roles_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
);

-- ============================================================================
-- 2. UPDATE EXISTING TABLES - Add Missing Columns
-- ============================================================================

-- Update keyword_scans table to include all required fields
DO $$ 
BEGIN
  -- Add missing columns to keyword_scans if they don't exist
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'search_volume') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN search_volume double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'difficulty') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN difficulty double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'cpc') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN cpc double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'competition') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN competition double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'location_name') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN location_name text;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'lat') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN lat double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'lng') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN lng double precision;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'keyword_scans' 
                 AND column_name = 'inserted_at') THEN
    ALTER TABLE public.keyword_scans 
    ADD COLUMN inserted_at timestamp with time zone DEFAULT now();
  END IF;

  -- Change scan_date to timestamp if it's not already
  IF EXISTS (SELECT 1 FROM information_schema.columns 
             WHERE table_schema = 'public' 
             AND table_name = 'keyword_scans' 
             AND column_name = 'scan_date'
             AND data_type != 'timestamp with time zone') THEN
    ALTER TABLE public.keyword_scans 
    ALTER COLUMN scan_date TYPE timestamp with time zone USING scan_date::timestamp with time zone;
  END IF;
END $$;

-- Update memberships table to include id, created_at, updated_at if missing
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'memberships' 
                 AND column_name = 'id') THEN
    ALTER TABLE public.memberships 
    ADD COLUMN id uuid NOT NULL DEFAULT gen_random_uuid();
    
    -- Make it primary key if not already composite
    ALTER TABLE public.memberships 
    DROP CONSTRAINT IF EXISTS memberships_pkey;
    
    ALTER TABLE public.memberships 
    ADD CONSTRAINT memberships_pkey PRIMARY KEY (id);
    
    -- Keep unique constraint on user_id, client_id
    ALTER TABLE public.memberships 
    ADD CONSTRAINT memberships_user_client_unique UNIQUE (user_id, client_id);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'memberships' 
                 AND column_name = 'created_at') THEN
    ALTER TABLE public.memberships 
    ADD COLUMN created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now());
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'memberships' 
                 AND column_name = 'updated_at') THEN
    ALTER TABLE public.memberships 
    ADD COLUMN updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now());
  END IF;
END $$;

-- Update websites table to include name and domain_clean if missing
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'websites' 
                 AND column_name = 'name') THEN
    ALTER TABLE public.websites 
    ADD COLUMN name text;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'websites' 
                 AND column_name = 'updated_at') THEN
    ALTER TABLE public.websites 
    ADD COLUMN updated_at timestamp with time zone DEFAULT now();
  END IF;
END $$;

-- Update web_keywords table to include search_volume, difficulty, cpc if missing
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keywords' 
                 AND column_name = 'search_volume') THEN
    ALTER TABLE public.web_keywords 
    ADD COLUMN search_volume integer;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keywords' 
                 AND column_name = 'difficulty') THEN
    ALTER TABLE public.web_keywords 
    ADD COLUMN difficulty integer;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keywords' 
                 AND column_name = 'cpc') THEN
    ALTER TABLE public.web_keywords 
    ADD COLUMN cpc numeric;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keywords' 
                 AND column_name = 'created_at') THEN
    ALTER TABLE public.web_keywords 
    ADD COLUMN created_at timestamp with time zone DEFAULT now();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keywords' 
                 AND column_name = 'updated_at') THEN
    ALTER TABLE public.web_keywords 
    ADD COLUMN updated_at timestamp with time zone DEFAULT now();
  END IF;
END $$;

-- Update web_keyword_ranks table to include url, volume, est_traffic if missing
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keyword_ranks' 
                 AND column_name = 'url') THEN
    ALTER TABLE public.web_keyword_ranks 
    ADD COLUMN url text;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keyword_ranks' 
                 AND column_name = 'volume') THEN
    ALTER TABLE public.web_keyword_ranks 
    ADD COLUMN volume integer;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keyword_ranks' 
                 AND column_name = 'est_traffic') THEN
    ALTER TABLE public.web_keyword_ranks 
    ADD COLUMN est_traffic integer;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_keyword_ranks' 
                 AND column_name = 'created_at') THEN
    ALTER TABLE public.web_keyword_ranks 
    ADD COLUMN created_at timestamp with time zone DEFAULT now();
  END IF;
END $$;

-- Update web_competitor_metrics to include competitor_domain, competitor_url if missing
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_competitor_metrics' 
                 AND column_name = 'competitor_domain') THEN
    ALTER TABLE public.web_competitor_metrics 
    ADD COLUMN competitor_domain text;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                 WHERE table_schema = 'public' 
                 AND table_name = 'web_competitor_metrics' 
                 AND column_name = 'competitor_url') THEN
    ALTER TABLE public.web_competitor_metrics 
    ADD COLUMN competitor_url text;
  END IF;
END $$;

-- ============================================================================
-- 3. CREATE INDEXES FOR PERFORMANCE
-- ============================================================================

-- Admin whitelist indexes
CREATE INDEX IF NOT EXISTS idx_admin_whitelist_email ON public.admin_whitelist(email);

-- Serpstat data indexes
CREATE INDEX IF NOT EXISTS idx_serpstat_data_keyword ON public.serpstat_data(keyword);
CREATE INDEX IF NOT EXISTS idx_serpstat_data_date ON public.serpstat_data(date);

-- User locations indexes
CREATE INDEX IF NOT EXISTS idx_user_locations_user_id ON public.user_locations(user_id);
CREATE INDEX IF NOT EXISTS idx_user_locations_location_id ON public.user_locations(location_id);

-- User roles indexes
CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON public.user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON public.user_roles(role);

-- Keyword scans indexes (if not exist)
CREATE INDEX IF NOT EXISTS idx_keyword_scans_location_id ON public.keyword_scans(location_id);
CREATE INDEX IF NOT EXISTS idx_keyword_scans_scan_date ON public.keyword_scans(scan_date);
CREATE INDEX IF NOT EXISTS idx_keyword_scans_keyword ON public.keyword_scans(keyword);

-- Web SEO indexes (if not exist)
CREATE INDEX IF NOT EXISTS idx_web_keyword_ranks_keyword_id ON public.web_keyword_ranks(keyword_id);
CREATE INDEX IF NOT EXISTS idx_web_keyword_ranks_date ON public.web_keyword_ranks(date);
CREATE INDEX IF NOT EXISTS idx_web_keyword_ranks_website_id ON public.web_keyword_ranks(website_id);

CREATE INDEX IF NOT EXISTS idx_web_page_metrics_page_id ON public.web_page_metrics(page_id);
CREATE INDEX IF NOT EXISTS idx_web_page_metrics_date ON public.web_page_metrics(date);
CREATE INDEX IF NOT EXISTS idx_web_page_metrics_website_id ON public.web_page_metrics(website_id);

CREATE INDEX IF NOT EXISTS idx_web_audit_summary_website_id ON public.web_audit_summary(website_id);
CREATE INDEX IF NOT EXISTS idx_web_audit_summary_date ON public.web_audit_summary(date);

CREATE INDEX IF NOT EXISTS idx_web_backlink_summary_website_id ON public.web_backlink_summary(website_id);
CREATE INDEX IF NOT EXISTS idx_web_backlink_summary_snapshot_week ON public.web_backlink_summary(snapshot_week);

-- ============================================================================
-- 4. ADD FOREIGN KEY CONSTRAINTS (if missing)
-- ============================================================================

-- Ensure keyword_scans has proper foreign keys
DO $$ 
BEGIN
  -- Location foreign key
  IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                 WHERE constraint_name = 'keyword_scans_location_id_fkey'
                 AND table_schema = 'public') THEN
    ALTER TABLE public.keyword_scans 
    ADD CONSTRAINT keyword_scans_location_id_fkey 
    FOREIGN KEY (location_id) REFERENCES public.locations(location_id) ON DELETE CASCADE;
  END IF;
END $$;

-- ============================================================================
-- 5. ROW LEVEL SECURITY (RLS) POLICIES
-- ============================================================================

-- Enable RLS on new tables
ALTER TABLE public.admin_whitelist ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.serpstat_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_locations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

-- Admin whitelist policies (only admins can read)
CREATE POLICY "Admin whitelist is viewable by admins" ON public.admin_whitelist
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.user_roles 
      WHERE user_id = auth.uid() AND role = 'admin'
    )
  );

-- Serpstat data policies (users can read their own client's data)
CREATE POLICY "Users can view serpstat data for their clients" ON public.serpstat_data
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.clients c
      JOIN public.memberships m ON m.client_id = c.id
      WHERE m.user_id = auth.uid()
    )
  );

-- User locations policies
CREATE POLICY "Users can view their own location access" ON public.user_locations
  FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "Users can insert their own location access" ON public.user_locations
  FOR INSERT WITH CHECK (user_id = auth.uid());

-- User roles policies
CREATE POLICY "Users can view their own role" ON public.user_roles
  FOR SELECT USING (user_id = auth.uid());

-- ============================================================================
-- 6. FUNCTIONS AND TRIGGERS
-- ============================================================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = timezone('utc'::text, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at columns
CREATE TRIGGER update_memberships_updated_at
  BEFORE UPDATE ON public.memberships
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_user_roles_updated_at
  BEFORE UPDATE ON public.user_roles
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_websites_updated_at
  BEFORE UPDATE ON public.websites
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_web_keywords_updated_at
  BEFORE UPDATE ON public.web_keywords
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- ============================================================================
-- 7. HELPER FUNCTIONS
-- ============================================================================

-- Function to check if user is member of a client
CREATE OR REPLACE FUNCTION public.is_member_of(client_id_param uuid)
RETURNS boolean AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.memberships
    WHERE user_id = auth.uid() 
    AND client_id = client_id_param
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check if user is admin
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid() 
    AND role = 'admin'
  ) OR EXISTS (
    SELECT 1 FROM public.admin_whitelist
    WHERE email = (SELECT email FROM auth.users WHERE id = auth.uid())
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- COMPLETE!
-- ============================================================================
-- All missing tables, columns, indexes, constraints, and policies have been added.
-- Your database schema is now complete for the Optivise application.
-- ============================================================================