-- Migration: 00032_create_reputation_tables
-- Description: Create tables for reputation scores and reputation events
-- Author: Auto-generated
-- Date: 2025-02-21

-- ============================================
-- REPUTATION SCORES TABLE
-- ============================================

CREATE TABLE reputation_scores (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  score INTEGER NOT NULL,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reputation_scores_user ON reputation_scores(user_id);
CREATE INDEX idx_reputation_scores_time ON reputation_scores(recorded_at DESC);

ALTER TABLE reputation_scores ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public can read reputation scores"
  ON reputation_scores FOR SELECT
  USING (true);

CREATE POLICY "Service role can manage reputation scores"
  ON reputation_scores FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- REPUTATION EVENTS TABLE
-- ============================================

CREATE TABLE reputation_events (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  delta NUMERIC(8,2) NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reputation_events_user ON reputation_events(user_id);
CREATE INDEX idx_reputation_events_time ON reputation_events(created_at DESC);

ALTER TABLE reputation_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own reputation events"
  ON reputation_events FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Service role can manage reputation events"
  ON reputation_events FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');
