-- Supabase Schema for Interiors Express Talent Hub

-- 1. Candidate Profiles
CREATE TABLE IF NOT EXISTS public.candidate_profiles (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
    first_name TEXT,
    last_name TEXT,
    user_type TEXT DEFAULT 'candidate',
    role TEXT,
    city TEXT,
    experience TEXT,
    experience_band TEXT,
    headline TEXT,
    bio TEXT,
    skills JSONB,
    specialisations JSONB,
    open_to JSONB,
    cv_url TEXT,
    portfolio_url TEXT,
    public_profile BOOLEAN DEFAULT TRUE,
    availability TEXT,
    view_count INTEGER DEFAULT 0,
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 2. Employer Profiles
CREATE TABLE IF NOT EXISTS public.employer_profiles (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
    company_name TEXT,
    first_name TEXT,
    company_type TEXT,
    city TEXT,
    about TEXT,
    website TEXT,
    logo_url TEXT,
    user_type TEXT DEFAULT 'employer',
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 3. Job Listings
CREATE TABLE IF NOT EXISTS public.job_listings (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    employer_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    company TEXT,
    description TEXT,
    type TEXT,
    discipline TEXT,
    experience TEXT,
    work_mode TEXT,
    location TEXT,
    salary_display TEXT,
    deadline TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    featured_at TIMESTAMP WITH TIME ZONE,
    featured_payment_id TEXT,
    applications_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()),
    updated_at TIMESTAMP WITH TIME ZONE,
    closed_at TIMESTAMP WITH TIME ZONE
);

-- 4. Job Applications
CREATE TABLE IF NOT EXISTS public.job_applications (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    job_id UUID REFERENCES public.job_listings(id) ON DELETE CASCADE,
    candidate_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    cover_note TEXT,
    status TEXT DEFAULT 'pending',
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()),
    updated_at TIMESTAMP WITH TIME ZONE,
    UNIQUE(job_id, candidate_id)
);

-- 5. Saved Jobs
CREATE TABLE IF NOT EXISTS public.saved_jobs (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    job_id UUID REFERENCES public.job_listings(id) ON DELETE CASCADE,
    saved_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()),
    UNIQUE(user_id, job_id)
);

-- 6. Job Alerts
CREATE TABLE IF NOT EXISTS public.job_alerts (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
    criteria JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

-- 7. Notifications
CREATE TABLE IF NOT EXISTS public.notifications (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    type TEXT,
    title TEXT,
    body TEXT,
    read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

-- 8. Payments
CREATE TABLE IF NOT EXISTS public.payments (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    razorpay_payment_id TEXT,
    razorpay_order_id TEXT,
    amount NUMERIC,
    description TEXT,
    paid_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

-- 9. Messages
CREATE TABLE IF NOT EXISTS public.messages (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    from_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    to_user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    sender_name TEXT,
    sender_email TEXT,
    sender_firm TEXT,
    message TEXT,
    read BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

-- 10. RPC: Increment Applications Count
CREATE OR REPLACE FUNCTION public.increment_applications_count(job_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE public.job_listings
  SET applications_count = applications_count + 1
  WHERE id = job_id;
END;
$$ LANGUAGE plpgsql;

-- 11. Storage Buckets Setup (Run these manually in Supabase Dashboard Storage settings or using pgSQL)
INSERT INTO storage.buckets (id, name, public) VALUES 
  ('resumes', 'resumes', true),
  ('portfolios', 'portfolios', true),
  ('profile-photos', 'profile-photos', true)
ON CONFLICT (id) DO NOTHING;