-- Quality Audit Upgrade Schema
-- Adds tables for web vitals, frontend errors, and enhanced audit tracking
-- Run this in Supabase SQL Editor

-- ============================================
-- Web Vitals Table
-- Stores Core Web Vitals metrics from frontend
-- ============================================

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,
  page_path TEXT,
  delta NUMERIC,
  metric_id TEXT,
  navigation_type TEXT,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Index for time-based queries
CREATE INDEX IF NOT EXISTS idx_web_vitals_recorded_at
ON web_vitals(recorded_at DESC);

COMMENT ON TABLE web_vitals IS 'Core Web Vitals metrics collected from frontend for quality audit';
COMMENT ON COLUMN web_vitals.metric_name IS 'LCP, INP, CLS, FCP, TTFB';
COMMENT ON COLUMN web_vitals.rating IS 'good, needs-improvement, poor';


-- ============================================
-- Frontend Errors Table
-- Stores JavaScript errors from frontend
-- ============================================

CREATE TABLE IF NOT EXISTS frontend_errors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message TEXT NOT NULL,
  stack TEXT,
  page_path TEXT,
  url TEXT,
  user_agent TEXT,
  error_type TEXT DEFAULT 'unknown',
  component_stack TEXT,
  filename TEXT,
  lineno INTEGER,
  colno INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for time-based queries
CREATE INDEX IF NOT EXISTS idx_frontend_errors_created_at
ON frontend_errors(created_at DESC);

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

COMMENT ON TABLE frontend_errors IS 'JavaScript errors captured from frontend for quality audit';
COMMENT ON COLUMN frontend_errors.error_type IS 'caught, uncaught, boundary, unhandled_rejection';


-- ============================================
-- Quality Audits Table Enhancement
-- Add audit_type column for tracking different audit frequencies
-- ============================================

DO $$
BEGIN
  -- Add audit_type column if it doesn't exist
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'quality_audits' AND column_name = 'audit_type'
  ) THEN
    ALTER TABLE quality_audits ADD COLUMN audit_type TEXT DEFAULT 'full_daily';
  END IF;
END $$;

-- Index for filtering by audit type
CREATE INDEX IF NOT EXISTS idx_quality_audits_type_time
ON quality_audits(audit_type, created_at DESC);

COMMENT ON COLUMN quality_audits.audit_type IS 'full_daily, critical_15min, external_hourly';


-- ============================================
-- RLS Policies
-- ============================================

-- Web vitals - public insert, authenticated read
ALTER TABLE web_vitals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow insert for everyone" ON web_vitals;
CREATE POLICY "Allow insert for everyone" ON web_vitals
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Allow read for authenticated" ON web_vitals;
CREATE POLICY "Allow read for authenticated" ON web_vitals
  FOR SELECT USING (true);


-- Frontend errors - public insert, authenticated read
ALTER TABLE frontend_errors ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow insert for everyone" ON frontend_errors;
CREATE POLICY "Allow insert for everyone" ON frontend_errors
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Allow read for authenticated" ON frontend_errors;
CREATE POLICY "Allow read for authenticated" ON frontend_errors
  FOR SELECT USING (true);


-- ============================================
-- Data Retention (optional)
-- Auto-delete old records to keep table sizes manageable
-- Uncomment to enable 30-day retention
-- ============================================

-- CREATE OR REPLACE FUNCTION cleanup_old_vitals()
-- RETURNS void AS $$
-- BEGIN
--   DELETE FROM web_vitals WHERE recorded_at < NOW() - INTERVAL '30 days';
--   DELETE FROM frontend_errors WHERE created_at < NOW() - INTERVAL '30 days';
-- END;
-- $$ LANGUAGE plpgsql;

-- To run cleanup periodically, you can call:
-- SELECT cron.schedule('cleanup-vitals', '0 2 * * *', 'SELECT cleanup_old_vitals()');


-- ============================================
-- Verification Queries
-- ============================================

-- Check tables exist
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name IN ('web_vitals', 'frontend_errors', 'quality_audits');
