-- Migration: 00006_create_support_tables
-- Description: Create support tables (reports, verification_records, subscriptions, analytics)
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- REPORTS TABLE
-- ============================================
-- User-submitted reports about reviews or responses.
-- Prevents duplicate reports from same user.

CREATE TABLE reports (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- Reporter
  reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- What's being reported
  item_type TEXT NOT NULL CHECK (item_type IN ('review', 'response')),
  item_id UUID NOT NULL,
  
  -- Report details
  reason report_reason NOT NULL,
  description TEXT CHECK (char_length(description) <= 1000),
  
  -- Status
  is_resolved BOOLEAN DEFAULT FALSE,
  resolution_note TEXT,
  resolved_by UUID REFERENCES users(id),
  resolved_at TIMESTAMPTZ,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Prevent duplicate reports from same user
  UNIQUE(reporter_id, item_type, item_id)
);

-- Indexes for reports
CREATE INDEX idx_reports_item ON reports(item_type, item_id);
CREATE INDEX idx_reports_unresolved ON reports(is_resolved) WHERE is_resolved = FALSE;
CREATE INDEX idx_reports_reporter ON reports(reporter_id);

-- RLS for reports
ALTER TABLE reports ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own reports"
  ON reports FOR SELECT
  USING (reporter_id = auth.uid());

CREATE POLICY "Authenticated users can create reports"
  ON reports FOR INSERT
  WITH CHECK (auth.uid() = reporter_id);

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


-- ============================================
-- VERIFICATION RECORDS TABLE
-- ============================================
-- Stores ID verification session data.
-- Actual ID documents stay with Veriff - we only store metadata.

CREATE TABLE verification_records (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- User being verified
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Veriff session info
  veriff_session_id TEXT NOT NULL UNIQUE,
  veriff_session_url TEXT,
  
  -- Results (no PII stored)
  status verification_status DEFAULT 'pending',
  verified_at TIMESTAMPTZ,
  
  -- Verification details (anonymized)
  age_confirmed BOOLEAN,  -- Confirms 18+
  name_match_score DECIMAL(3,2),  -- How closely name matches profile (0.00-1.00)
  
  -- Failure tracking
  failure_reason TEXT,
  attempt_count INTEGER DEFAULT 1,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ  -- Verification sessions expire
);

-- Indexes for verification_records
CREATE INDEX idx_verification_user ON verification_records(user_id);
CREATE INDEX idx_verification_session ON verification_records(veriff_session_id);
CREATE INDEX idx_verification_status ON verification_records(status);

-- RLS for verification_records
ALTER TABLE verification_records ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own verification records"
  ON verification_records FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY "Service role can manage verification records"
  ON verification_records FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');


-- ============================================
-- SUBSCRIPTIONS TABLE
-- ============================================
-- Tracks subscription history and status.
-- Synced with RevenueCat/App Store via webhooks.

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- User
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Subscription details
  tier subscription_tier NOT NULL,
  
  -- RevenueCat / App Store info
  revenuecat_id TEXT,
  product_id TEXT,  -- App Store product identifier
  
  -- Status
  is_active BOOLEAN DEFAULT TRUE,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  
  -- Billing
  price_cents INTEGER,
  currency TEXT DEFAULT 'USD',
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for subscriptions
CREATE INDEX idx_subscriptions_user ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_active ON subscriptions(is_active, expires_at);
CREATE INDEX idx_subscriptions_revenuecat ON subscriptions(revenuecat_id) WHERE revenuecat_id IS NOT NULL;

-- RLS for subscriptions
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

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

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


-- ============================================
-- ANALYTICS EVENTS TABLE
-- ============================================
-- Tracks user behavior for product analytics.
-- Consider partitioning by month for scale.

CREATE TABLE analytics_events (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- User (nullable for anonymous events)
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  
  -- Event info
  event_name TEXT NOT NULL,
  event_properties JSONB DEFAULT '{}',
  
  -- Context
  device_id TEXT,
  platform TEXT DEFAULT 'ios',
  app_version TEXT,
  
  -- Location (anonymized - no precise location)
  country TEXT,
  region TEXT,
  
  -- Source tracking
  source TEXT,  -- 'organic', 'google', 'referral', 'app_store'
  referrer TEXT,
  
  -- Timestamp
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for analytics (optimized for time-series queries)
CREATE INDEX idx_analytics_user ON analytics_events(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_analytics_event ON analytics_events(event_name, created_at DESC);
CREATE INDEX idx_analytics_created ON analytics_events(created_at DESC);

-- RLS for analytics_events
ALTER TABLE analytics_events ENABLE ROW LEVEL SECURITY;

-- Only service role can insert/read analytics
CREATE POLICY "Service role can manage analytics"
  ON analytics_events FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');


-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE reports IS 'User-submitted reports about inappropriate content';
COMMENT ON TABLE verification_records IS 'ID verification session tracking (no PII stored)';
COMMENT ON TABLE subscriptions IS 'Subscription history synced with RevenueCat';
COMMENT ON TABLE analytics_events IS 'Product analytics events for user behavior tracking';

COMMENT ON COLUMN verification_records.name_match_score IS 'Veriff name match confidence 0.00-1.00';
COMMENT ON COLUMN subscriptions.product_id IS 'App Store Connect product identifier';
COMMENT ON COLUMN analytics_events.event_properties IS 'JSON payload with event-specific data';
