Land your first Data Analytics role, faster.

-- 1. Jobs Table (Auto-populated every 12 hours)
CREATE TABLE public.jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    company TEXT NOT NULL,
    location TEXT NOT NULL,
    salary_range TEXT,
    skills TEXT[] DEFAULT '{}',
    jd_summary TEXT,
    apply_url TEXT NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Student Profiles & 7-Day Batch Trial Tracking
CREATE TABLE public.profiles (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id),
    plan TEXT DEFAULT 'free' CHECK (plan IN ('free', 'pro')),
    trial_expires_at TIMESTAMPTZ,
    batch_code_used TEXT
);

-- 3. Secure View: Masks apply_url for <24h jobs on Free Plan
CREATE VIEW public.jobs_feed AS
SELECT
    j.id, j.title, j.company, j.location, j.salary_range, j.skills, j.jd_summary, j.posted_at,
    CASE
        WHEN NOW() - j.posted_at >= INTERVAL '24 hours' THEN j.apply_url
        WHEN EXISTS (
            SELECT 1 FROM public.profiles p
            WHERE p.user_id = auth.uid()
            AND (p.plan = 'pro' OR p.trial_expires_at > NOW())
        ) THEN j.apply_url
        ELSE NULL -- Locked for Free users during first 24 hours
    END AS direct_apply_url
FROM public.jobs j;

// Schedule via GitHub Actions or Supabase Cron: "0 */12 * * *" (Every 12 Hours)
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_KEY);

export async function syncFreshAnalyticsJobs() {
  // Fetch last 12h Data Analyst & AI jobs from Adzuna / JSearch API
  const res = await fetch('https://jsearch.p.rapidapi.com/search?query=Data%20Analyst%20India&date_posted=today', {
    headers: { 'X-RapidAPI-Key': process.env.RAPIDAPI_KEY }
  });
  const { data } = await res.json();

  const formattedJobs = data.map(item => ({
    title: item.job_title,
    company: item.employer_name,
    location: item.job_city || 'Remote / India',
    salary_range: '₹5.5 – ₹9.5 LPA',
    skills: ['SQL', 'Power BI', 'Python', 'Excel'],
    jd_summary: item.job_description?.slice(0, 400),
    apply_url: item.job_apply_link,
    posted_at: new Date().toISOString()
  }));

  await supabase.from('jobs').upsert(formattedJobs, { onConflict: 'title,company' });
  console.log(`Synced ${formattedJobs.length} fresh <12h openings.`);
}