-- Feature Flags & Experiments Table
-- Supports: simple on/off flags, percentage rollouts, and A/B experiments

CREATE TABLE IF NOT EXISTS feature_flags (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  key TEXT NOT NULL UNIQUE,                          -- e.g. 'landing_v2', 'match_proximity_weight'
  name TEXT NOT NULL,                                -- Human-readable name
  description TEXT,
  
  -- Flag type: 'boolean' (on/off), 'percentage' (0-100 rollout), 'experiment' (A/B variants)
  flag_type TEXT NOT NULL DEFAULT 'boolean' CHECK (flag_type IN ('boolean', 'percentage', 'experiment')),
  
  -- For boolean flags
  enabled BOOLEAN NOT NULL DEFAULT false,
  
  -- For percentage rollouts (0-100)
  rollout_percentage INTEGER DEFAULT 0 CHECK (rollout_percentage >= 0 AND rollout_percentage <= 100),
  
  -- For experiments: JSON array of variants with weights
  -- e.g. [{"key": "control", "weight": 50}, {"key": "variant_a", "weight": 50}]
  variants JSONB DEFAULT '[]',
  
  -- Targeting
  target_user_types TEXT[] DEFAULT '{}',              -- empty = all, or ['worker'], ['contractor'], etc
  
  -- Lifecycle
  active BOOLEAN NOT NULL DEFAULT false,             -- Master kill switch
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_flags_key ON feature_flags(key);
CREATE INDEX IF NOT EXISTS idx_flags_active ON feature_flags(active);

-- RLS — public read for active flags (needed client-side), admin write
ALTER TABLE feature_flags ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read active flags"
  ON feature_flags FOR SELECT
  USING (true);

-- Experiment assignments — tracks which variant a user got
CREATE TABLE IF NOT EXISTS experiment_assignments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  flag_key TEXT NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  variant TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(flag_key, user_id)
);

CREATE INDEX IF NOT EXISTS idx_assignments_user ON experiment_assignments(user_id);
CREATE INDEX IF NOT EXISTS idx_assignments_flag ON experiment_assignments(flag_key);

ALTER TABLE experiment_assignments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own assignments"
  ON experiment_assignments FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

-- Experiment events — lightweight analytics
CREATE TABLE IF NOT EXISTS experiment_events (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  flag_key TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  variant TEXT NOT NULL,
  event_type TEXT NOT NULL,                          -- 'exposure', 'conversion', 'click', etc
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_flag ON experiment_events(flag_key);
CREATE INDEX IF NOT EXISTS idx_events_type ON experiment_events(flag_key, event_type);
CREATE INDEX IF NOT EXISTS idx_events_created ON experiment_events(created_at DESC);

ALTER TABLE experiment_events ENABLE ROW LEVEL SECURITY;

-- Only service role writes events
-- Users can read their own
CREATE POLICY "Users can read own events"
  ON experiment_events FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

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

CREATE TRIGGER feature_flags_updated_at
  BEFORE UPDATE ON feature_flags
  FOR EACH ROW
  EXECUTE FUNCTION update_feature_flags_updated_at();
