04_views.sql

-- Analytics Views
-- Optimized views for dashboard queries
-- Note: Render repos are identified by language='render' (no separate uses_render flag)

-- View: analytics_trending_repos_current
-- Purpose: Current top trending repositories across all languages
CREATE OR REPLACE VIEW analytics_trending_repos_current AS
SELECT
  dr.repo_full_name,
  dr.repo_url,
  dr.language,
  dr.description,
  dr.readme_content,
  dr.render_category,
  fs.stars,
  fs.star_velocity,
  fs.activity_score,
  fs.momentum_score,
  fs.rank_overall,
  fs.rank_in_language,
  fs.snapshot_date,
  fs.snapshot_date as last_updated
FROM dim_repositories dr
JOIN fact_repo_snapshots fs ON dr.repo_key = fs.repo_key
WHERE dr.is_current = TRUE
  AND fs.snapshot_date = (SELECT MAX(snapshot_date) FROM fact_repo_snapshots)
  AND dr.repo_full_name !~ '^vercel'
ORDER BY fs.momentum_score DESC;

-- View: analytics_render_showcase
-- Purpose: Render ecosystem showcase
CREATE OR REPLACE VIEW analytics_render_showcase AS
SELECT
  dr.repo_full_name,
  dr.repo_url,
  dr.language,
  dr.description,
  dr.readme_content,
  dr.render_category,
  fs.stars,
  fs.momentum_score,
  fs.star_velocity,
  fs.activity_score,
  fs.snapshot_date
FROM dim_repositories dr
JOIN fact_repo_snapshots fs ON dr.repo_key = fs.repo_key
WHERE dr.is_current = TRUE
  AND dr.language = 'render'
  AND fs.snapshot_date = (SELECT MAX(snapshot_date) FROM fact_repo_snapshots)
  AND dr.repo_full_name !~ '^vercel'
ORDER BY fs.momentum_score DESC;

-- View: analytics_language_rankings
-- Purpose: Per-language top performers with Render adoption stats
CREATE OR REPLACE VIEW analytics_language_rankings AS
SELECT
  dl.language_name,
  dr.repo_full_name,
  dr.repo_url,
  dr.description,
  dr.readme_content,
  dr.render_category,
  fs.stars,
  fs.momentum_score,
  fs.star_velocity,
  fs.rank_in_language,
  fs.snapshot_date,
  COUNT(CASE WHEN dr.language = 'render' THEN 1 END) OVER (PARTITION BY dl.language_name) as render_adoption_count
FROM dim_languages dl
JOIN dim_repositories dr ON dl.language_name = dr.language
JOIN fact_repo_snapshots fs ON dr.repo_key = fs.repo_key
WHERE dr.is_current = TRUE
  AND fs.snapshot_date = (SELECT MAX(snapshot_date) FROM fact_repo_snapshots)
  AND fs.rank_in_language <= 50
  AND dr.repo_full_name !~ '^vercel'
ORDER BY dl.language_name, fs.rank_in_language;


-- View: analytics_language_trends
-- Purpose: Language-level aggregated statistics
CREATE OR REPLACE VIEW analytics_language_trends AS
SELECT
  dl.language_name,
  dl.language_category,
  COUNT(DISTINCT dr.repo_key) as total_repos,
  SUM(fs.stars) as total_stars,
  AVG(fs.stars) as avg_stars,
  AVG(fs.momentum_score) as avg_momentum,
  COUNT(CASE WHEN dr.language = 'render' THEN 1 END) as render_projects,
  ROUND((COUNT(CASE WHEN dr.language = 'render' THEN 1 END)::DECIMAL / NULLIF(COUNT(DISTINCT dr.repo_key), 0)) * 100, 2) as render_adoption_percentage
FROM dim_languages dl
LEFT JOIN dim_repositories dr ON dl.language_name = dr.language AND dr.is_current = TRUE AND dr.repo_full_name !~ '^vercel'
LEFT JOIN fact_repo_snapshots fs ON dr.repo_key = fs.repo_key
WHERE fs.snapshot_date = (SELECT MAX(snapshot_date) FROM fact_repo_snapshots)
   OR fs.snapshot_date IS NULL
GROUP BY dl.language_key, dl.language_name, dl.language_category
ORDER BY total_repos DESC;

-- View: analytics_repo_history
-- Purpose: Historical trends for individual repositories (for charting)
CREATE OR REPLACE VIEW analytics_repo_history AS
SELECT
  dr.repo_full_name,
  dr.language,
  fs.snapshot_date,
  fs.stars,
  fs.star_velocity,
  fs.momentum_score,
  fs.activity_score,
  fs.rank_overall,
  fs.rank_in_language
FROM dim_repositories dr
JOIN fact_repo_snapshots fs ON dr.repo_key = fs.repo_key
WHERE dr.is_current = TRUE
  AND dr.repo_full_name !~ '^vercel'
ORDER BY dr.repo_full_name, fs.snapshot_date DESC;