supabase_schema.sql

-- =============================================
-- Full Schema for Job Scraper App
-- Paste this into Supabase SQL Editor and run it
-- =============================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================
-- 1. USERS
-- =============================================
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT,
  full_name TEXT,
  google_id TEXT,
  auth_provider TEXT DEFAULT 'local',
  email_verified BOOLEAN DEFAULT FALSE,
  role TEXT DEFAULT 'candidate',
  subscription_plan TEXT DEFAULT 'free',
  analysis_credits INTEGER DEFAULT 1,
  reset_token TEXT,
  reset_token_expires TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 2. COMPANIES
-- =============================================
CREATE TABLE IF NOT EXISTS companies (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  website TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 3. JOB SOURCES
-- =============================================
CREATE TABLE IF NOT EXISTS job_sources (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 4. JOB CATEGORIES
-- =============================================
CREATE TABLE IF NOT EXISTS job_categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 5. JOBS
-- =============================================
CREATE TABLE IF NOT EXISTS jobs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  description TEXT,
  company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL,
  source_id INTEGER REFERENCES job_sources(id) ON DELETE SET NULL,
  category_id INTEGER REFERENCES job_categories(id) ON DELETE SET NULL,
  location TEXT,
  employment_type TEXT,
  remote_type TEXT DEFAULT 'remote',
  apply_url TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 6. JOB PROFILES (parsed job data)
-- =============================================
CREATE TABLE IF NOT EXISTS job_profiles (
  id SERIAL PRIMARY KEY,
  job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
  required_skills TEXT[],
  min_experience INTEGER,
  job_level TEXT,
  responsibilities TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 7. JOB CLICKS (analytics)
-- =============================================
CREATE TABLE IF NOT EXISTS job_clicks (
  id SERIAL PRIMARY KEY,
  job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  source INTEGER,
  ip TEXT,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 8. RESUMES
-- =============================================
CREATE TABLE IF NOT EXISTS resumes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  original_filename TEXT,
  file_type TEXT,
  file_path TEXT,
  extracted_text TEXT,
  is_active BOOLEAN DEFAULT FALSE,
  structured_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 9. RESUME PROFILES (parsed resume data)
-- =============================================
CREATE TABLE IF NOT EXISTS resume_profiles (
  id SERIAL PRIMARY KEY,
  resume_id UUID REFERENCES resumes(id) ON DELETE CASCADE,
  skills TEXT[],
  years_experience INTEGER,
  education TEXT,
  job_titles TEXT[],
  summary TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 10. RESUME JOB ANALYSES
-- =============================================
CREATE TABLE IF NOT EXISTS resume_job_analyses (
  id SERIAL PRIMARY KEY,
  resume_id UUID REFERENCES resumes(id) ON DELETE CASCADE,
  job_id UUID REFERENCES jobs(id) ON DELETE SET NULL,
  job_description_manual TEXT,
  score INTEGER,
  analysis_json JSONB,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 11. SAVED JOBS
-- =============================================
CREATE TABLE IF NOT EXISTS saved_jobs (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
  saved_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, job_id)
);

-- =============================================
-- 12. JOB APPLICATIONS
-- =============================================
CREATE TABLE IF NOT EXISTS job_applications (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  job_id UUID REFERENCES jobs(id) ON DELETE CASCADE,
  resume_id UUID REFERENCES resumes(id) ON DELETE SET NULL,
  cover_letter TEXT,
  status TEXT DEFAULT 'applied',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- 13. SUBSCRIPTION PLANS
-- =============================================
CREATE TABLE IF NOT EXISTS subscription_plans (
  id SERIAL PRIMARY KEY,
  bundle_id TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  credits INTEGER NOT NULL,
  price_ngn INTEGER NOT NULL,
  price_usd NUMERIC(10,2) NOT NULL,
  description TEXT,
  recommended BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- =============================================
-- SEED: Default Subscription Plans
-- =============================================
INSERT INTO subscription_plans (bundle_id, name, credits, price_ngn, price_usd, description, recommended)
VALUES
  ('basic', 'Starter Pack', 10, 5000, 5.00, 'Perfect for a quick job search boost.', false),
  ('pro', 'Career Pro', 30, 15000, 12.00, 'The most popular choice for serious job seekers.', true),
  ('elite', 'Elite Hunter', 100, 40000, 30.00, 'Unlimited potential for high-volume applications.', false)
ON CONFLICT (bundle_id) DO NOTHING;

-- =============================================
-- DISABLE Row Level Security on all tables
-- (since your app uses its own JWT auth)
-- =============================================
ALTER TABLE users DISABLE ROW LEVEL SECURITY;
ALTER TABLE companies DISABLE ROW LEVEL SECURITY;
ALTER TABLE job_sources DISABLE ROW LEVEL SECURITY;
ALTER TABLE job_categories DISABLE ROW LEVEL SECURITY;
ALTER TABLE jobs DISABLE ROW LEVEL SECURITY;
ALTER TABLE job_profiles DISABLE ROW LEVEL SECURITY;
ALTER TABLE job_clicks DISABLE ROW LEVEL SECURITY;
ALTER TABLE resumes DISABLE ROW LEVEL SECURITY;
ALTER TABLE resume_profiles DISABLE ROW LEVEL SECURITY;
ALTER TABLE resume_job_analyses DISABLE ROW LEVEL SECURITY;
ALTER TABLE saved_jobs DISABLE ROW LEVEL SECURITY;
ALTER TABLE job_applications DISABLE ROW LEVEL SECURITY;
ALTER TABLE subscription_plans DISABLE ROW LEVEL SECURITY;