-- FAQ Management Table
-- Run via Supabase SQL Editor or Management API

CREATE TABLE IF NOT EXISTS faq_entries (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  category TEXT NOT NULL CHECK (category IN ('worker', 'contractor', 'general')),
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  published BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_faq_category ON faq_entries(category);
CREATE INDEX IF NOT EXISTS idx_faq_sort ON faq_entries(category, sort_order);
CREATE INDEX IF NOT EXISTS idx_faq_published ON faq_entries(published);

-- RLS — public read for published entries, service role for admin writes
ALTER TABLE faq_entries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read published FAQs"
  ON faq_entries FOR SELECT
  USING (published = true);

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

CREATE TRIGGER faq_updated_at
  BEFORE UPDATE ON faq_entries
  FOR EACH ROW
  EXECUTE FUNCTION update_faq_updated_at();
