-- RateRight v2 Database Schema
-- Run this in the Supabase SQL Editor

-- ============================================
-- ENUMS
-- ============================================

CREATE TYPE user_type AS ENUM ('worker', 'contractor');
CREATE TYPE availability_type AS ENUM ('full-time', 'casual', 'weekends', 'flexible');
CREATE TYPE rate_type AS ENUM ('hourly', 'daily');
CREATE TYPE job_status AS ENUM ('draft', 'active', 'filled', 'closed');
CREATE TYPE match_status AS ENUM ('suggested', 'applied', 'accepted', 'rejected', 'hired');
CREATE TYPE action_status AS ENUM ('pending', 'accepted', 'rejected');
CREATE TYPE payment_status AS ENUM ('pending', 'charged', 'refunded');
CREATE TYPE notification_type AS ENUM ('match', 'hire_request', 'hire_confirmed', 'rating', 'job_posted', 'system');

-- ============================================
-- HELPER FUNCTION: auto-update updated_at
-- ============================================

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TABLES
-- ============================================

-- Profiles (extends Supabase Auth)
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  type user_type NOT NULL,
  name text,
  phone text,
  email text,
  avatar_url text,
  location point,
  suburb text,
  verified boolean DEFAULT false,
  onboarding_completed boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  ai_generated boolean DEFAULT false
);

CREATE TRIGGER profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Companies (contractor-specific)
CREATE TABLE companies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL UNIQUE REFERENCES profiles ON DELETE CASCADE,
  name text NOT NULL,
  abn text,
  address text,
  industry text,
  size text,
  website text,
  logo_url text,
  ai_research jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER companies_updated_at
  BEFORE UPDATE ON companies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Worker profiles (worker-specific)
CREATE TABLE worker_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL UNIQUE REFERENCES profiles ON DELETE CASCADE,
  trades text[] DEFAULT '{}',
  experience_years integer,
  certifications text[] DEFAULT '{}',
  white_card_number text,
  white_card_verified boolean DEFAULT false,
  availability availability_type,
  languages text[] DEFAULT '{}',
  bio text,
  voice_note_url text,
  ai_extracted jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER worker_profiles_updated_at
  BEFORE UPDATE ON worker_profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Jobs
CREATE TABLE jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES companies ON DELETE CASCADE,
  title text NOT NULL,
  description text,
  trade text NOT NULL,
  location point,
  suburb text,
  site_address text,
  rate_min decimal,
  rate_max decimal,
  rate_type rate_type DEFAULT 'hourly',
  start_date date,
  duration text,
  workers_needed integer DEFAULT 1,
  certifications_required text[] DEFAULT '{}',
  status job_status DEFAULT 'draft',
  ai_generated boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER jobs_updated_at
  BEFORE UPDATE ON jobs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Matches
CREATE TABLE matches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id uuid NOT NULL REFERENCES jobs ON DELETE CASCADE,
  worker_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  status match_status DEFAULT 'suggested',
  match_score decimal,
  ai_summary text,
  contractor_action action_status DEFAULT 'pending',
  worker_action action_status DEFAULT 'pending',
  hired_at timestamptz,
  fee_charged boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  UNIQUE (job_id, worker_id)
);

CREATE TRIGGER matches_updated_at
  BEFORE UPDATE ON matches
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Ratings
CREATE TABLE ratings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches ON DELETE CASCADE,
  from_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  to_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  score integer NOT NULL CHECK (score BETWEEN 1 AND 5),
  comment text,
  created_at timestamptz DEFAULT now(),
  UNIQUE (match_id, from_id)
);

-- Payments
CREATE TABLE payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches ON DELETE CASCADE,
  company_id uuid NOT NULL REFERENCES companies ON DELETE CASCADE,
  amount decimal DEFAULT 50.00,
  status payment_status DEFAULT 'pending',
  stripe_payment_id text,
  created_at timestamptz DEFAULT now()
);

-- Notifications
CREATE TABLE notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  type notification_type NOT NULL,
  title text NOT NULL,
  body text,
  data jsonb,
  read boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX idx_profiles_type ON profiles (type);
CREATE INDEX idx_profiles_suburb ON profiles (suburb);

CREATE INDEX idx_companies_profile_id ON companies (profile_id);
CREATE INDEX idx_companies_abn ON companies (abn);

CREATE INDEX idx_worker_profiles_profile_id ON worker_profiles (profile_id);
CREATE INDEX idx_worker_profiles_trades ON worker_profiles USING gin (trades);
CREATE INDEX idx_worker_profiles_availability ON worker_profiles (availability);
CREATE INDEX idx_worker_profiles_certifications ON worker_profiles USING gin (certifications);

CREATE INDEX idx_jobs_company_id ON jobs (company_id);
CREATE INDEX idx_jobs_status ON jobs (status);
CREATE INDEX idx_jobs_trade ON jobs (trade);
CREATE INDEX idx_jobs_created_at ON jobs (created_at DESC);
CREATE INDEX idx_jobs_suburb ON jobs (suburb);

CREATE INDEX idx_matches_job_id ON matches (job_id);
CREATE INDEX idx_matches_worker_id ON matches (worker_id);
CREATE INDEX idx_matches_status ON matches (status);

CREATE INDEX idx_ratings_to_id ON ratings (to_id);
CREATE INDEX idx_ratings_from_id ON ratings (from_id);

CREATE INDEX idx_payments_match_id ON payments (match_id);
CREATE INDEX idx_payments_company_id ON payments (company_id);

CREATE INDEX idx_notifications_profile_id ON notifications (profile_id);
CREATE INDEX idx_notifications_read ON notifications (profile_id, read);

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE worker_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE matches ENABLE ROW LEVEL SECURITY;
ALTER TABLE ratings ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;

-- Profiles: users can read all profiles, update their own
CREATE POLICY "Public profiles are viewable by everyone"
  ON profiles FOR SELECT
  USING (true);

CREATE POLICY "Users can update their own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = id);

CREATE POLICY "Users can insert their own profile"
  ON profiles FOR INSERT
  WITH CHECK (auth.uid() = id);

-- Profiles: users can delete their own profile (GDPR compliance)
CREATE POLICY "Users can delete their own profile"
  ON profiles FOR DELETE
  USING (auth.uid() = id);

-- Companies: viewable by everyone, editable by owner
CREATE POLICY "Companies are viewable by everyone"
  ON companies FOR SELECT
  USING (true);

CREATE POLICY "Contractors can insert their own company"
  ON companies FOR INSERT
  WITH CHECK (profile_id = auth.uid());

CREATE POLICY "Contractors can update their own company"
  ON companies FOR UPDATE
  USING (profile_id = auth.uid());

-- Companies: owners can delete their own company (GDPR compliance)
CREATE POLICY "Contractors can delete their own company"
  ON companies FOR DELETE
  USING (profile_id = auth.uid());

-- Worker profiles: viewable by everyone, editable by owner
CREATE POLICY "Worker profiles are viewable by everyone"
  ON worker_profiles FOR SELECT
  USING (true);

CREATE POLICY "Workers can insert their own profile"
  ON worker_profiles FOR INSERT
  WITH CHECK (profile_id = auth.uid());

CREATE POLICY "Workers can update their own profile"
  ON worker_profiles FOR UPDATE
  USING (profile_id = auth.uid());

-- Worker profiles: workers can delete their own profile (GDPR compliance)
CREATE POLICY "Workers can delete their own profile"
  ON worker_profiles FOR DELETE
  USING (profile_id = auth.uid());

-- Jobs: viewable by everyone, editable by company owner
CREATE POLICY "Jobs are viewable by everyone"
  ON jobs FOR SELECT
  USING (true);

CREATE POLICY "Contractors can insert jobs for their company"
  ON jobs FOR INSERT
  WITH CHECK (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "Contractors can update their own jobs"
  ON jobs FOR UPDATE
  USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

-- Jobs: contractors can delete their own jobs (GDPR compliance)
CREATE POLICY "Contractors can delete their own jobs"
  ON jobs FOR DELETE
  USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

-- Matches: viewable by involved parties
CREATE POLICY "Users can view their own matches"
  ON matches FOR SELECT
  USING (
    worker_id = auth.uid() OR
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

CREATE POLICY "System can insert matches"
  ON matches FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Involved parties can update matches"
  ON matches FOR UPDATE
  USING (
    worker_id = auth.uid() OR
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

-- Matches: involved parties can delete matches (GDPR compliance)
CREATE POLICY "Involved parties can delete matches"
  ON matches FOR DELETE
  USING (
    worker_id = auth.uid() OR
    job_id IN (
      SELECT j.id FROM jobs j
      JOIN companies c ON j.company_id = c.id
      WHERE c.profile_id = auth.uid()
    )
  );

-- Ratings: viewable by everyone, insertable by match participants
-- Note: No DELETE policy — ratings are permanent records
CREATE POLICY "Ratings are viewable by everyone"
  ON ratings FOR SELECT
  USING (true);

CREATE POLICY "Match participants can insert ratings"
  ON ratings FOR INSERT
  WITH CHECK (from_id = auth.uid());

-- Payments: viewable by company owner
-- Note: No DELETE policy — payments are financial records
CREATE POLICY "Company owners can view their payments"
  ON payments FOR SELECT
  USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "System can insert payments"
  ON payments FOR INSERT
  WITH CHECK (true);

-- Notifications: users can only see their own
CREATE POLICY "Users can view their own notifications"
  ON notifications FOR SELECT
  USING (profile_id = auth.uid());

CREATE POLICY "System can insert notifications"
  ON notifications FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Users can update their own notifications"
  ON notifications FOR UPDATE
  USING (profile_id = auth.uid());

-- Notifications: users can delete their own notifications (GDPR compliance)
CREATE POLICY "Users can delete their own notifications"
  ON notifications FOR DELETE
  USING (profile_id = auth.uid());

-- ============================================
-- FUNCTIONS
-- ============================================

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
DECLARE
  _type user_type;
BEGIN
  -- Safely cast user type, defaulting to 'worker' if invalid
  BEGIN
    _type := (NEW.raw_user_meta_data->>'type')::user_type;
  EXCEPTION WHEN invalid_text_representation THEN
    _type := 'worker';
  END;

  INSERT INTO public.profiles (id, email, type)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(_type, 'worker')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Average rating helper
CREATE OR REPLACE FUNCTION public.get_average_rating(user_id uuid)
RETURNS decimal AS $$
  SELECT COALESCE(AVG(score)::decimal, 0)
  FROM ratings
  WHERE to_id = user_id;
$$ LANGUAGE sql STABLE;

-- Rating count helper
CREATE OR REPLACE FUNCTION public.get_rating_count(user_id uuid)
RETURNS integer AS $$
  SELECT COUNT(*)::integer
  FROM ratings
  WHERE to_id = user_id;
$$ LANGUAGE sql STABLE;
