-- ============================================
-- SELF-IMPROVING INTELLIGENCE SYSTEM - MVP SCHEMA
-- Created: January 2026
-- ============================================

-- 1. Usage Analytics (Raw Events)
-- PARTITIONED BY MONTH for performance at scale
-- Retention: 90 days raw events, aggregated metrics kept forever
CREATE TABLE IF NOT EXISTS usage_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type VARCHAR(50) NOT NULL, -- 'page_view', 'button_click', 'flow_complete', 'flow_abandon', 'error', 'rage_click'
  event_name VARCHAR(100) NOT NULL, -- 'call_list_view', 'mark_hot_click', etc.
  user_id UUID REFERENCES auth.users(id),
  page VARCHAR(100),
  element VARCHAR(100),
  context JSONB DEFAULT '{}', -- { lead_id, position, previous_page, etc. }
  duration_ms INTEGER,
  session_id UUID,
  device_type VARCHAR(20), -- 'mobile', 'desktop', 'tablet'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for fast querying
CREATE INDEX IF NOT EXISTS idx_usage_events_created_at ON usage_events(created_at);
CREATE INDEX IF NOT EXISTS idx_usage_events_type ON usage_events(event_type);
CREATE INDEX IF NOT EXISTS idx_usage_events_user ON usage_events(user_id);
CREATE INDEX IF NOT EXISTS idx_usage_events_page ON usage_events(page);
CREATE INDEX IF NOT EXISTS idx_usage_events_session ON usage_events(session_id);

-- 2. Aggregated Metrics (Computed Daily) - Intelligence System
-- Named intelligence_daily_metrics to avoid conflict with existing daily_metrics table
-- These are kept forever for trend analysis
CREATE TABLE IF NOT EXISTS intelligence_daily_metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  date DATE NOT NULL,
  metric_name VARCHAR(100) NOT NULL, -- 'call_prep_abandonment_rate', 'avg_page_load_time', etc.
  metric_value DECIMAL(12,4) NOT NULL,
  metric_context JSONB DEFAULT '{}', -- { by_user: {...}, by_page: {...}, by_device: {...} }
  sample_size INTEGER, -- how many data points
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, metric_name)
);

CREATE INDEX IF NOT EXISTS idx_intelligence_daily_metrics_date ON intelligence_daily_metrics(date);
CREATE INDEX IF NOT EXISTS idx_intelligence_daily_metrics_name ON intelligence_daily_metrics(metric_name);

-- 3. Intelligence Suggestions
CREATE TABLE IF NOT EXISTS intelligence_suggestions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  suggestion_type VARCHAR(50) NOT NULL, -- 'quick_win', 'friction_fix', 'pattern_opportunity', 'unused_feature'
  title VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  evidence JSONB DEFAULT '{}', -- supporting metrics and data

  -- Scoring
  predicted_impact VARCHAR(100), -- human readable: "Save 60 taps/week"
  predicted_effort VARCHAR(20), -- 'low', 'medium', 'high'
  priority_score DECIMAL(5,2), -- calculated: impact / effort (higher = do first)

  -- Source
  source VARCHAR(50) NOT NULL, -- 'weekly_review', 'monthly_deep_dive', 'real_time'
  source_report_id UUID, -- link to weekly/monthly report

  -- Status tracking
  status VARCHAR(20) DEFAULT 'suggested', -- 'suggested', 'planned', 'in_progress', 'implemented', 'rejected', 'deferred'
  status_changed_at TIMESTAMPTZ,
  status_changed_by UUID,
  rejection_reason TEXT,

  -- Implementation tracking
  implemented_at TIMESTAMPTZ,
  implemented_by UUID,
  implementation_notes TEXT,

  -- Impact measurement
  baseline_metrics JSONB, -- metrics before implementation
  post_implementation_metrics JSONB, -- metrics after (filled in later)
  actual_impact VARCHAR(100), -- human readable result
  impact_measured_at TIMESTAMPTZ,
  success_verdict VARCHAR(20), -- 'success', 'partial', 'failed', 'inconclusive'

  -- Learning
  learnings TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_suggestions_status ON intelligence_suggestions(status);
CREATE INDEX IF NOT EXISTS idx_suggestions_type ON intelligence_suggestions(suggestion_type);
CREATE INDEX IF NOT EXISTS idx_suggestions_created ON intelligence_suggestions(created_at);

-- 4. Weekly Reports Archive
CREATE TABLE IF NOT EXISTS weekly_excellence_reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  week_start DATE NOT NULL,
  week_end DATE NOT NULL,

  -- Analysis results
  friction_points JSONB DEFAULT '[]', -- array of detected friction
  quick_wins JSONB DEFAULT '[]', -- array of quick win suggestions
  patterns_detected JSONB DEFAULT '[]', -- array of patterns found
  unused_features JSONB DEFAULT '[]', -- array of declining features
  ai_quality JSONB DEFAULT '{}', -- AI acceptance rates etc.

  -- Summary scores
  overall_health_score DECIMAL(5,2), -- 0-100
  friction_score DECIMAL(5,2), -- 0-100 (lower = more friction)
  improvement_opportunities INTEGER, -- count

  -- Full report for reference
  full_report_json JSONB,

  -- Slack threading
  slack_channel_id VARCHAR(20),
  slack_message_ts VARCHAR(50),
  slack_thread_reply_count INTEGER DEFAULT 0,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(week_start)
);

CREATE INDEX IF NOT EXISTS idx_weekly_reports_week ON weekly_excellence_reports(week_start DESC);

-- 5. Detected Friction Points
CREATE TABLE IF NOT EXISTS detected_friction (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  detection_date DATE NOT NULL,
  friction_type VARCHAR(50) NOT NULL, -- 'high_abandonment', 'slow_load', 'rage_clicks', 'error_prone', 'confusing_flow'
  location VARCHAR(100) NOT NULL, -- page or feature name

  -- Severity
  severity_score DECIMAL(5,2) NOT NULL, -- 0-100
  affected_users INTEGER,
  occurrences_per_week INTEGER,

  -- Evidence
  evidence JSONB DEFAULT '{}', -- { metrics, examples, user_paths }

  -- Analysis
  root_cause_hypothesis TEXT,
  suggested_fix TEXT,
  estimated_effort VARCHAR(20), -- 'low', 'medium', 'high'

  -- Status
  status VARCHAR(20) DEFAULT 'detected', -- 'detected', 'acknowledged', 'fixing', 'resolved', 'wont_fix'
  linked_suggestion_id UUID REFERENCES intelligence_suggestions(id),
  resolved_at TIMESTAMPTZ,
  resolution_notes TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_friction_status ON detected_friction(status);
CREATE INDEX IF NOT EXISTS idx_friction_severity ON detected_friction(severity_score DESC);
CREATE INDEX IF NOT EXISTS idx_friction_location ON detected_friction(location);

-- ============================================
-- HELPER FUNCTIONS
-- ============================================

-- Function to clean up old usage events (run daily)
CREATE OR REPLACE FUNCTION cleanup_old_usage_events()
RETURNS INTEGER AS $$
DECLARE
  deleted_count INTEGER;
BEGIN
  DELETE FROM usage_events
  WHERE created_at < NOW() - INTERVAL '90 days';

  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Function to get page abandonment rate
CREATE OR REPLACE FUNCTION get_page_abandonment_rate(
  p_page VARCHAR,
  p_start_date TIMESTAMPTZ,
  p_end_date TIMESTAMPTZ
)
RETURNS DECIMAL AS $$
DECLARE
  total_views INTEGER;
  completed_flows INTEGER;
  abandonment_rate DECIMAL;
BEGIN
  -- Count page views
  SELECT COUNT(*) INTO total_views
  FROM usage_events
  WHERE page = p_page
    AND event_type = 'page_view'
    AND created_at BETWEEN p_start_date AND p_end_date;

  -- Count flow completions from that page
  SELECT COUNT(*) INTO completed_flows
  FROM usage_events
  WHERE page = p_page
    AND event_type = 'flow_complete'
    AND created_at BETWEEN p_start_date AND p_end_date;

  IF total_views = 0 THEN
    RETURN 0;
  END IF;

  abandonment_rate := 1 - (completed_flows::DECIMAL / total_views::DECIMAL);
  RETURN ROUND(abandonment_rate * 100, 2);
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- VIEWS
-- ============================================

-- Active friction needing attention
CREATE OR REPLACE VIEW v_active_friction AS
SELECT
  id,
  friction_type,
  location,
  severity_score,
  affected_users,
  suggested_fix,
  estimated_effort,
  status,
  created_at
FROM detected_friction
WHERE status IN ('detected', 'acknowledged')
ORDER BY severity_score DESC;

-- Pending suggestions
CREATE OR REPLACE VIEW v_pending_suggestions AS
SELECT
  id,
  suggestion_type,
  title,
  predicted_impact,
  predicted_effort,
  priority_score,
  status,
  created_at
FROM intelligence_suggestions
WHERE status IN ('suggested', 'planned')
ORDER BY priority_score DESC;

-- Page metrics summary (last 7 days)
CREATE OR REPLACE VIEW v_page_metrics_7d AS
SELECT
  page,
  COUNT(*) FILTER (WHERE event_type = 'page_view') as views,
  COUNT(*) FILTER (WHERE event_type = 'flow_complete') as completions,
  COUNT(*) FILTER (WHERE event_type = 'flow_abandon') as abandons,
  COUNT(*) FILTER (WHERE event_type = 'rage_click') as rage_clicks,
  COUNT(*) FILTER (WHERE event_type = 'error') as errors,
  AVG(duration_ms) FILTER (WHERE duration_ms IS NOT NULL) as avg_duration_ms
FROM usage_events
WHERE created_at > NOW() - INTERVAL '7 days'
  AND page IS NOT NULL
GROUP BY page
ORDER BY views DESC;
