-- Supabase Migrations for Video Processing Queue
-- Run these in the Supabase SQL Editor or via psql

-- ============================================================================
-- STEP 1: Add chapter columns (if not exist)
-- ============================================================================
ALTER TABLE videos ADD COLUMN IF NOT EXISTS has_chapters BOOLEAN DEFAULT FALSE;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chapter_count INTEGER DEFAULT 0;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chapters JSONB;

-- ============================================================================
-- STEP 2: Add extra_meta column for full segment data (if not exist)
-- ============================================================================
ALTER TABLE videos ADD COLUMN IF NOT EXISTS extra_meta JSONB;

-- ============================================================================
-- STEP 3: Create atomic claim function
-- This uses FOR UPDATE SKIP LOCKED for safe concurrent access
-- ============================================================================
CREATE OR REPLACE FUNCTION claim_next_video(
    p_worker_id TEXT,
    p_lease_duration_sec INT DEFAULT 600
) RETURNS TABLE(
    youtube_id TEXT,
    youtube_url TEXT,
    title TEXT,
    source_duration_min DOUBLE PRECISION,
    language TEXT,
    channel TEXT
) AS $$
BEGIN
    RETURN QUERY
    WITH next_video AS (
        SELECT v.id
        FROM videos v
        WHERE v.status = 'PENDING'
          AND (v.lease_expires_at IS NULL OR v.lease_expires_at < NOW())
          AND COALESCE(v.attempt_count, 0) < COALESCE(v.max_attempts, 3)
        ORDER BY v.created_at ASC
        LIMIT 1
        FOR UPDATE SKIP LOCKED
    )
    UPDATE videos v
    SET
        status = 'CLAIMED',
        claimed_by = p_worker_id,
        claimed_at = NOW(),
        lease_expires_at = NOW() + (p_lease_duration_sec || ' seconds')::INTERVAL,
        attempt_count = COALESCE(v.attempt_count, 0) + 1,
        updated_at = NOW()
    FROM next_video
    WHERE v.id = next_video.id
    RETURNING v.youtube_id, v.youtube_url, v.title, v.source_duration_min, v.language, v.channel;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 4: Create helper function to reclaim expired leases
-- ============================================================================
CREATE OR REPLACE FUNCTION reclaim_expired_leases()
RETURNS INTEGER AS $$
DECLARE
    reclaimed_count INTEGER;
BEGIN
    WITH expired AS (
        UPDATE videos
        SET
            status = 'PENDING',
            claimed_by = NULL,
            lease_expires_at = NULL,
            updated_at = NOW()
        WHERE status = 'CLAIMED'
          AND lease_expires_at < NOW()
        RETURNING id
    )
    SELECT COUNT(*) INTO reclaimed_count FROM expired;

    RETURN reclaimed_count;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 5: Create queue stats function
-- ============================================================================
CREATE OR REPLACE FUNCTION get_queue_stats()
RETURNS TABLE(
    status TEXT,
    count BIGINT,
    avg_usable_pct DOUBLE PRECISION
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        v.status::TEXT,
        COUNT(*)::BIGINT,
        AVG(v.usable_percentage)::DOUBLE PRECISION
    FROM videos v
    GROUP BY v.status
    ORDER BY v.status;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 6: Create indexes for efficient queries
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_videos_status ON videos(status);
CREATE INDEX IF NOT EXISTS idx_videos_status_created ON videos(status, created_at);
CREATE INDEX IF NOT EXISTS idx_videos_lease_expires ON videos(lease_expires_at) WHERE status = 'CLAIMED';
CREATE INDEX IF NOT EXISTS idx_videos_language ON videos(language);

-- ============================================================================
-- STEP 7: Grant execute permissions for RPC functions
-- ============================================================================
GRANT EXECUTE ON FUNCTION claim_next_video(TEXT, INT) TO anon, authenticated, service_role;
GRANT EXECUTE ON FUNCTION reclaim_expired_leases() TO anon, authenticated, service_role;
GRANT EXECUTE ON FUNCTION get_queue_stats() TO anon, authenticated, service_role;

-- ============================================================================
-- ANALYTICS TABLES (for distributed worker monitoring)
-- ============================================================================

-- ============================================================================
-- STEP 8: Worker heartbeats (real-time status, 1 row per worker)
-- ============================================================================
CREATE TABLE IF NOT EXISTS worker_heartbeats (
    worker_id TEXT PRIMARY KEY,
    machine_id TEXT NOT NULL,
    gpu_id INT NOT NULL,
    status TEXT DEFAULT 'idle',
    current_video_id TEXT,
    current_stage INT,
    current_stage_name TEXT,
    last_heartbeat TIMESTAMPTZ DEFAULT NOW(),
    session_start TIMESTAMPTZ DEFAULT NOW(),
    session_videos_done INT DEFAULT 0,
    session_videos_failed INT DEFAULT 0,
    session_audio_minutes FLOAT DEFAULT 0,
    session_usable_minutes FLOAT DEFAULT 0
);

-- ============================================================================
-- STEP 9: Worker lifetime metrics (aggregated stats)
-- ============================================================================
CREATE TABLE IF NOT EXISTS worker_metrics (
    worker_id TEXT PRIMARY KEY,
    machine_id TEXT NOT NULL,
    gpu_id INT NOT NULL,
    total_videos_processed INT DEFAULT 0,
    total_videos_failed INT DEFAULT 0,
    total_audio_hours FLOAT DEFAULT 0,
    total_usable_hours FLOAT DEFAULT 0,
    total_processing_hours FLOAT DEFAULT 0,
    avg_rtf FLOAT DEFAULT 0,
    first_seen TIMESTAMPTZ DEFAULT NOW(),
    last_active TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================================
-- STEP 10: Processing events (done/fail only, for analysis)
-- ============================================================================
CREATE TABLE IF NOT EXISTS processing_events (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    worker_id TEXT NOT NULL,
    video_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    stage_name TEXT,
    duration_seconds FLOAT,
    audio_minutes FLOAT,
    speakers INT,
    usable_pct FLOAT,
    error_type TEXT,
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_worker ON processing_events(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_video ON processing_events(video_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON processing_events(event_type, created_at DESC);

-- ============================================================================
-- STEP 11: Error logs (detailed error tracking)
-- ============================================================================
CREATE TABLE IF NOT EXISTS error_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    worker_id TEXT NOT NULL,
    video_id TEXT NOT NULL,
    stage TEXT,
    error_type TEXT,
    error_message TEXT,
    stack_trace TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_errors_worker ON error_logs(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_errors_recent ON error_logs(created_at DESC);

-- ============================================================================
-- STEP 12: Cluster overview view (aggregated dashboard)
-- ============================================================================
CREATE OR REPLACE VIEW cluster_overview AS
SELECT
    COUNT(DISTINCT machine_id) as total_machines,
    COUNT(*) as total_workers,
    COUNT(*) FILTER (WHERE status = 'processing') as active_workers,
    COUNT(*) FILTER (WHERE status = 'idle') as idle_workers,
    COUNT(*) FILTER (WHERE last_heartbeat < NOW() - INTERVAL '2 minutes') as stale_workers,
    COALESCE(SUM(session_videos_done), 0) as session_total_videos,
    COALESCE(SUM(session_audio_minutes), 0) as session_total_audio_min,
    COALESCE(SUM(session_usable_minutes), 0) as session_total_usable_min
FROM worker_heartbeats;

-- ============================================================================
-- STEP 13: Function to get cluster stats with RTF calculation
-- ============================================================================
CREATE OR REPLACE FUNCTION get_cluster_stats()
RETURNS TABLE(
    total_machines BIGINT,
    total_workers BIGINT,
    active_workers BIGINT,
    idle_workers BIGINT,
    stale_workers BIGINT,
    session_videos_done BIGINT,
    session_audio_hours FLOAT,
    session_usable_hours FLOAT,
    hourly_throughput_videos BIGINT,
    hourly_throughput_audio_hours FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(DISTINCT wh.machine_id)::BIGINT,
        COUNT(*)::BIGINT,
        COUNT(*) FILTER (WHERE wh.status = 'processing')::BIGINT,
        COUNT(*) FILTER (WHERE wh.status = 'idle')::BIGINT,
        COUNT(*) FILTER (WHERE wh.last_heartbeat < NOW() - INTERVAL '2 minutes')::BIGINT,
        COALESCE(SUM(wh.session_videos_done), 0)::BIGINT,
        COALESCE(SUM(wh.session_audio_minutes) / 60.0, 0)::FLOAT,
        COALESCE(SUM(wh.session_usable_minutes) / 60.0, 0)::FLOAT,
        (SELECT COUNT(*) FROM processing_events pe
         WHERE pe.event_type = 'done'
         AND pe.created_at > NOW() - INTERVAL '1 hour')::BIGINT,
        (SELECT COALESCE(SUM(pe.audio_minutes) / 60.0, 0) FROM processing_events pe
         WHERE pe.event_type = 'done'
         AND pe.created_at > NOW() - INTERVAL '1 hour')::FLOAT
    FROM worker_heartbeats wh;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 14: Function to update worker lifetime metrics (called on done/fail)
-- ============================================================================
CREATE OR REPLACE FUNCTION update_worker_metrics()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.event_type = 'done' THEN
        INSERT INTO worker_metrics (worker_id, machine_id, gpu_id, total_videos_processed,
                                    total_audio_hours, total_usable_hours, total_processing_hours, last_active)
        VALUES (
            NEW.worker_id,
            split_part(NEW.worker_id, '_gpu', 1),
            split_part(NEW.worker_id, '_gpu', 2)::INT,
            1,
            COALESCE(NEW.audio_minutes, 0) / 60.0,
            COALESCE(NEW.audio_minutes * NEW.usable_pct / 100.0, 0) / 60.0,
            COALESCE(NEW.duration_seconds, 0) / 3600.0,
            NOW()
        )
        ON CONFLICT (worker_id) DO UPDATE SET
            total_videos_processed = worker_metrics.total_videos_processed + 1,
            total_audio_hours = worker_metrics.total_audio_hours + COALESCE(NEW.audio_minutes, 0) / 60.0,
            total_usable_hours = worker_metrics.total_usable_hours + COALESCE(NEW.audio_minutes * NEW.usable_pct / 100.0, 0) / 60.0,
            total_processing_hours = worker_metrics.total_processing_hours + COALESCE(NEW.duration_seconds, 0) / 3600.0,
            avg_rtf = CASE
                WHEN worker_metrics.total_processing_hours + COALESCE(NEW.duration_seconds, 0) / 3600.0 > 0
                THEN (worker_metrics.total_audio_hours + COALESCE(NEW.audio_minutes, 0) / 60.0) /
                     (worker_metrics.total_processing_hours + COALESCE(NEW.duration_seconds, 0) / 3600.0)
                ELSE 0
            END,
            last_active = NOW();
    ELSIF NEW.event_type = 'fail' THEN
        INSERT INTO worker_metrics (worker_id, machine_id, gpu_id, total_videos_failed, last_active)
        VALUES (
            NEW.worker_id,
            split_part(NEW.worker_id, '_gpu', 1),
            split_part(NEW.worker_id, '_gpu', 2)::INT,
            1,
            NOW()
        )
        ON CONFLICT (worker_id) DO UPDATE SET
            total_videos_failed = worker_metrics.total_videos_failed + 1,
            last_active = NOW();
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to auto-update metrics
DROP TRIGGER IF EXISTS trigger_update_worker_metrics ON processing_events;
CREATE TRIGGER trigger_update_worker_metrics
    AFTER INSERT ON processing_events
    FOR EACH ROW
    EXECUTE FUNCTION update_worker_metrics();

-- ============================================================================
-- STEP 15: Grant permissions for analytics tables
-- ============================================================================
GRANT ALL ON worker_heartbeats TO anon, authenticated, service_role;
GRANT ALL ON worker_metrics TO anon, authenticated, service_role;
GRANT ALL ON processing_events TO anon, authenticated, service_role;
GRANT ALL ON error_logs TO anon, authenticated, service_role;
GRANT SELECT ON cluster_overview TO anon, authenticated, service_role;
GRANT EXECUTE ON FUNCTION get_cluster_stats() TO anon, authenticated, service_role;

-- ============================================================================
-- Verification: Check that everything was created
-- ============================================================================
-- SELECT
--     routine_name,
--     routine_type
-- FROM information_schema.routines
-- WHERE routine_schema = 'public'
--   AND routine_name IN ('claim_next_video', 'reclaim_expired_leases', 'get_queue_stats', 'get_cluster_stats');
