-- Get the 20 most recent published episodes
SELECT 
    episode_id,
    title,
    description,
    published_live_at,
    video_url,
    church_center_url
FROM planning_center.publishing_episodes
WHERE published_live_at IS NOT NULL
ORDER BY published_live_at DESC
LIMIT 20;

-- Get all episodes from a series (replace series name)
SELECT 
    e.title as episode_title,
    e.published_live_at,
    e.description,
    s.title as series_title,
    s.started_at as series_start,
    s.ended_at as series_end
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE s.title LIKE '%Christmas%'  -- Change series name here
ORDER BY e.published_live_at;

-- Episodes published this year
SELECT 
    title,
    published_live_at,
    video_url,
    DATE_PART('month', published_live_at) as month,
    DATE_PART('week', published_live_at) as week
FROM planning_center.publishing_episodes
WHERE published_live_at >= DATE_TRUNC('year', CURRENT_DATE)
    AND published_live_at IS NOT NULL
ORDER BY published_live_at DESC;

-- List all series with episode counts
SELECT 
    s.series_id,
    s.title,
    s.description,
    s.started_at,
    s.ended_at,
    s.episodes_count,
    s.published,
    COUNT(e.episode_id) as actual_episode_count
FROM planning_center.publishing_series s
LEFT JOIN planning_center.publishing_episodes e 
    ON s.series_id = e.series_id
GROUP BY s.series_id, s.title, s.description, 
         s.started_at, s.ended_at, s.episodes_count, s.published
ORDER BY s.started_at DESC;

-- Current and recent series (last 6 months)
SELECT 
    title,
    description,
    started_at,
    ended_at,
    episodes_count,
    CASE 
        WHEN ended_at IS NULL THEN 'Ongoing'
        WHEN ended_at > CURRENT_DATE THEN 'Upcoming'
        ELSE 'Completed'
    END as status
FROM planning_center.publishing_series
WHERE started_at >= CURRENT_DATE - INTERVAL '6 months'
   OR ended_at IS NULL
   OR ended_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY started_at DESC;

-- Average series length and episode count
SELECT 
    COUNT(*) as total_series,
    AVG(episodes_count) as avg_episodes_per_series,
    AVG(
        CASE 
            WHEN ended_at IS NOT NULL 
            THEN EXTRACT(DAY FROM (ended_at - started_at))
            ELSE NULL
        END
    ) as avg_series_duration_days,
    MAX(episodes_count) as max_episodes_in_series,
    MIN(episodes_count) as min_episodes_in_series
FROM planning_center.publishing_series
WHERE published = true
    AND started_at IS NOT NULL;

-- Speaker frequency in the last year
SELECT 
    sp.speaker_id,
    sp.name as speaker_name,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode,
    COUNT(DISTINCT e.series_id) as series_count
FROM planning_center.publishing_speakers sp
JOIN planning_center.publishing_speakerships ship 
    ON sp.speaker_id = ship.speaker_id
JOIN planning_center.publishing_episodes e 
    ON ship.episode_id = e.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY sp.speaker_id, sp.name
ORDER BY episode_count DESC;

-- Speakers in the last 3 months
SELECT DISTINCT
    sp.name as speaker_name,
    sp.description,
    COUNT(e.episode_id) as recent_episodes,
    MAX(e.published_live_at) as most_recent_episode
FROM planning_center.publishing_speakers sp
JOIN planning_center.publishing_speakerships ship 
    ON sp.speaker_id = ship.speaker_id
JOIN planning_center.publishing_episodes e 
    ON ship.episode_id = e.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY sp.speaker_id, sp.name, sp.description
ORDER BY most_recent_episode DESC;

-- Speakers by series
SELECT 
    s.title as series_title,
    sp.name as speaker_name,
    COUNT(e.episode_id) as episodes_in_series,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as last_episode
FROM planning_center.publishing_series s
JOIN planning_center.publishing_episodes e 
    ON s.series_id = e.series_id
JOIN planning_center.publishing_speakerships ship 
    ON e.episode_id = ship.episode_id
JOIN planning_center.publishing_speakers sp 
    ON ship.speaker_id = sp.speaker_id
GROUP BY s.series_id, s.title, sp.speaker_id, sp.name
ORDER BY s.started_at DESC, episodes_in_series DESC;

-- Episode performance metrics
SELECT 
    e.title as episode_title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    est.average_watch_time / 60 as avg_watch_minutes,
    s.title as series_title
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
LEFT JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
ORDER BY est.view_count DESC NULLS LAST
LIMIT 25;

-- Top 10 most viewed episodes of all time
SELECT 
    e.title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    s.title as series_title,
    EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_published
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
LEFT JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE est.view_count IS NOT NULL
ORDER BY est.view_count DESC
LIMIT 10;

-- Weekly engagement summary
SELECT 
    DATE_TRUNC('week', e.published_live_at) as week,
    COUNT(DISTINCT e.episode_id) as episodes_published,
    SUM(est.view_count) as total_views,
    SUM(est.download_count) as total_downloads,
    AVG(est.view_count) as avg_views_per_episode,
    SUM(est.unique_viewers) as total_unique_viewers
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY week
ORDER BY week DESC;

-- List all channels with settings
SELECT 
    channel_id,
    name,
    kind,
    description,
    streaming_service,
    church_center_enabled,
    default_publish_time,
    time_zone
FROM planning_center.publishing_channels
ORDER BY name;

-- Episode count by channel
SELECT 
    c.name as channel_name,
    c.kind as channel_type,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode
FROM planning_center.publishing_channels c
LEFT JOIN planning_center.publishing_episodes e 
    ON c.channel_id = e.channel_id
GROUP BY c.channel_id, c.name, c.kind
ORDER BY episode_count DESC;

-- Resources for recent episodes
SELECT 
    e.title as episode_title,
    e.published_live_at,
    er.name as resource_name,
    er.kind as resource_type,
    er.url,
    er.file_size / 1048576 as size_mb
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_resources er 
    ON e.episode_id = er.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY e.published_live_at DESC, er.name;

-- Count resources by type
SELECT 
    kind as resource_type,
    COUNT(*) as resource_count,
    COUNT(DISTINCT episode_id) as episodes_with_type,
    SUM(file_size) / 1073741824 as total_size_gb
FROM planning_center.publishing_episode_resources
GROUP BY kind
ORDER BY resource_count DESC;

-- Next scheduled publishing times by channel
SELECT 
    c.name as channel_name,
    cnt.next_publish_at,
    EXTRACT(DAY FROM (cnt.next_publish_at - CURRENT_TIMESTAMP)) as days_until_publish,
    c.default_publish_time
FROM planning_center.publishing_channel_next_times cnt
JOIN planning_center.publishing_channels c 
    ON cnt.channel_id = c.channel_id
WHERE cnt.next_publish_at > CURRENT_TIMESTAMP
ORDER BY cnt.next_publish_at;

-- Episode publishing schedule
SELECT 
    e.title as episode_title,
    et.published_at as scheduled_time,
    c.name as channel_name,
    et.status as publish_status,
    e.published_live_at as actual_publish_time
FROM planning_center.publishing_episode_times et
JOIN planning_center.publishing_episodes e 
    ON et.episode_id = e.episode_id
JOIN planning_center.publishing_channels c 
    ON et.channel_id = c.channel_id
WHERE et.published_at >= CURRENT_DATE
ORDER BY et.published_at;

-- Publishing dashboard metrics
SELECT 
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at IS NOT NULL) as total_episodes,
    
    (SELECT COUNT(*) FROM planning_center.publishing_series 
     WHERE published = true) as total_series,
    
    (SELECT COUNT(*) FROM planning_center.publishing_speakers) as total_speakers,
    
    (SELECT COUNT(*) FROM planning_center.publishing_channels) as total_channels,
    
    (SELECT SUM(view_count) FROM planning_center.publishing_episode_statistics) as total_views,
    
    (SELECT SUM(download_count) FROM planning_center.publishing_episode_statistics) as total_downloads,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at >= CURRENT_DATE - INTERVAL '30 days') as episodes_last_30_days,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episode_resources) as total_resources;

-- Monthly publishing statistics
SELECT 
    DATE_TRUNC('month', published_live_at) as month,
    COUNT(*) as episodes_published,
    COUNT(DISTINCT series_id) as active_series,
    COUNT(DISTINCT DATE_TRUNC('week', published_live_at)) as weeks_with_content,
    ARRAY_AGG(DISTINCT EXTRACT(DOW FROM published_live_at)) as publishing_days
FROM planning_center.publishing_episodes
WHERE published_live_at >= CURRENT_DATE - INTERVAL '12 months'
    AND published_live_at IS NOT NULL
GROUP BY month
ORDER BY month DESC;