-- Customer Success Tracking Tables
-- Run via Supabase SQL Editor or Management API

-- 1. Milestones — tracks key user events
CREATE TABLE IF NOT EXISTS success_milestones (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  user_type TEXT NOT NULL CHECK (user_type IN ('worker', 'contractor')),
  milestone_type TEXT NOT NULL CHECK (milestone_type IN (
    'signup', 'onboarding_complete', 'first_job_posted', 'first_application',
    'first_hire', 'repeat_hire', 'first_rating', 'five_star_given',
    'five_star_received', 'referral_sent', 'referral_converted',
    'profile_complete', 'first_message', 'fast_hire'
  )),
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Unique constraint: most milestones should only fire once per user
-- (repeat_hire, five_star_given, five_star_received can fire multiple times)
CREATE UNIQUE INDEX IF NOT EXISTS idx_milestones_unique_once
  ON success_milestones(user_id, milestone_type)
  WHERE milestone_type NOT IN ('repeat_hire', 'five_star_given', 'five_star_received', 'referral_converted');

CREATE INDEX IF NOT EXISTS idx_milestones_user ON success_milestones(user_id);
CREATE INDEX IF NOT EXISTS idx_milestones_type ON success_milestones(milestone_type);
CREATE INDEX IF NOT EXISTS idx_milestones_created ON success_milestones(created_at DESC);

-- RLS
ALTER TABLE success_milestones ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own milestones"
  ON success_milestones FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

-- Only service role inserts milestones (server-side tracking)
-- No INSERT policy for authenticated — milestones are recorded by the system


-- 2. Success Surveys — onboarding, post-hire, exit interviews
CREATE TABLE IF NOT EXISTS success_surveys (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  user_type TEXT NOT NULL CHECK (user_type IN ('worker', 'contractor')),
  survey_type TEXT NOT NULL CHECK (survey_type IN ('onboarding', 'post_hire', 'exit')),
  responses JSONB NOT NULL DEFAULT '{}',
  nps_score INTEGER CHECK (nps_score IS NULL OR (nps_score >= 0 AND nps_score <= 10)),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- One onboarding survey per user, but allow multiple post-hire surveys
CREATE UNIQUE INDEX IF NOT EXISTS idx_surveys_onboarding_once
  ON success_surveys(user_id, survey_type)
  WHERE survey_type = 'onboarding';

CREATE UNIQUE INDEX IF NOT EXISTS idx_surveys_exit_once
  ON success_surveys(user_id, survey_type)
  WHERE survey_type = 'exit';

CREATE INDEX IF NOT EXISTS idx_surveys_user ON success_surveys(user_id);
CREATE INDEX IF NOT EXISTS idx_surveys_type ON success_surveys(survey_type);
CREATE INDEX IF NOT EXISTS idx_surveys_nps ON success_surveys(nps_score) WHERE nps_score IS NOT NULL;

-- RLS
ALTER TABLE success_surveys ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can insert own surveys"
  ON success_surveys FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view own surveys"
  ON success_surveys FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);


-- 3. Success Candidates — computed view of who's a success story candidate
-- This is a materialized view that gets refreshed, or a regular table updated by a cron
CREATE TABLE IF NOT EXISTS success_candidates (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,
  user_type TEXT NOT NULL CHECK (user_type IN ('worker', 'contractor')),
  user_name TEXT,
  user_email TEXT,
  score INTEGER NOT NULL DEFAULT 0,         -- Computed from milestone points + NPS
  milestone_count INTEGER NOT NULL DEFAULT 0,
  latest_milestone TEXT,
  nps_score INTEGER,
  status TEXT NOT NULL DEFAULT 'candidate' CHECK (status IN (
    'candidate', 'contacted', 'agreed', 'published', 'declined'
  )),
  admin_notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_candidates_score ON success_candidates(score DESC);
CREATE INDEX IF NOT EXISTS idx_candidates_status ON success_candidates(status);
CREATE INDEX IF NOT EXISTS idx_candidates_type ON success_candidates(user_type);

-- RLS
ALTER TABLE success_candidates ENABLE ROW LEVEL SECURITY;

-- No user access — admin only (service role bypasses RLS)


-- Updated_at trigger for success_candidates
CREATE OR REPLACE FUNCTION update_success_candidates_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER success_candidates_updated_at
  BEFORE UPDATE ON success_candidates
  FOR EACH ROW
  EXECUTE FUNCTION update_success_candidates_updated_at();
