-- Frontend Quality Tables for 0.1% Quality Audit
-- Stores Core Web Vitals and frontend error tracking

-- Web Vitals table - stores LCP, INP, CLS, FCP, TTFB metrics
CREATE TABLE IF NOT EXISTS web_vitals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  metric_name TEXT NOT NULL,
  metric_value NUMERIC NOT NULL,
  rating TEXT, -- 'good', 'needs-improvement', 'poor'
  page_path TEXT,
  delta NUMERIC, -- change from previous measurement
  navigation_type TEXT, -- 'navigate', 'reload', 'back_forward', 'prerender'
  user_id UUID REFERENCES auth.users(id),
  session_id TEXT,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for efficient querying by metric and time
CREATE INDEX IF NOT EXISTS idx_web_vitals_metric_time
  ON web_vitals(metric_name, recorded_at DESC);

-- Index for page-specific analysis
CREATE INDEX IF NOT EXISTS idx_web_vitals_page
  ON web_vitals(page_path, recorded_at DESC);

-- Frontend Errors table - stores client-side errors
CREATE TABLE IF NOT EXISTS frontend_errors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message TEXT NOT NULL,
  stack TEXT,
  page_path TEXT,
  component_stack TEXT,
  user_id UUID REFERENCES auth.users(id),
  session_id TEXT,
  user_agent TEXT,
  error_type TEXT, -- 'runtime', 'network', 'chunk_load', etc.
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for error analysis
CREATE INDEX IF NOT EXISTS idx_frontend_errors_time
  ON frontend_errors(created_at DESC);

-- Index for grouping similar errors
CREATE INDEX IF NOT EXISTS idx_frontend_errors_message
  ON frontend_errors(message, created_at DESC);

-- RLS Policies
ALTER TABLE web_vitals ENABLE ROW LEVEL SECURITY;
ALTER TABLE frontend_errors ENABLE ROW LEVEL SECURITY;

-- Allow service role full access (for backend API)
CREATE POLICY "web_vitals_service_all" ON web_vitals
  FOR ALL USING (true) WITH CHECK (true);

CREATE POLICY "frontend_errors_service_all" ON frontend_errors
  FOR ALL USING (true) WITH CHECK (true);

-- Audit history table - stores quality audit results
CREATE TABLE IF NOT EXISTS audit_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  audit_type TEXT NOT NULL DEFAULT 'full', -- 'full', 'critical', 'external'
  score INTEGER NOT NULL,
  passed INTEGER NOT NULL,
  failed INTEGER NOT NULL,
  warnings INTEGER DEFAULT 0,
  duration_ms INTEGER,
  categories JSONB NOT NULL, -- Full category breakdown
  failed_tests JSONB DEFAULT '[]', -- List of failed test names for quick reference
  triggered_alerts JSONB DEFAULT '[]', -- Alerts created from this audit
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for audit history queries
CREATE INDEX IF NOT EXISTS idx_audit_history_time
  ON audit_history(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_history_type_time
  ON audit_history(audit_type, created_at DESC);

-- RLS for audit history
ALTER TABLE audit_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY "audit_history_service_all" ON audit_history
  FOR ALL USING (true) WITH CHECK (true);

-- Add retention cleanup function for web_vitals (keep 30 days)
CREATE OR REPLACE FUNCTION cleanup_old_web_vitals()
RETURNS void AS $$
BEGIN
  DELETE FROM web_vitals WHERE recorded_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;

-- Add retention cleanup function for frontend_errors (keep 14 days)
CREATE OR REPLACE FUNCTION cleanup_old_frontend_errors()
RETURNS void AS $$
BEGIN
  DELETE FROM frontend_errors WHERE created_at < NOW() - INTERVAL '14 days';
END;
$$ LANGUAGE plpgsql;
