-- ============================================
-- STEP 1: Install pg_background extension
-- ============================================
CREATE EXTENSION IF NOT EXISTS pg_background;

-- ============================================
-- STEP 2: Create the results table
-- ============================================
DROP TABLE IF EXISTS task_results;

CREATE TABLE task_results (
    id SERIAL PRIMARY KEY,
    task_name TEXT NOT NULL,
    task_data JSONB,
    status TEXT DEFAULT 'pending',
    result JSONB,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

-- ============================================
-- STEP 3: Create the long-running function
-- ============================================
CREATE OR REPLACE FUNCTION long_running_process(p_task_id INTEGER)
RETURNS VOID AS $$
DECLARE
    v_data JSONB;
    v_sleep_seconds INTEGER;
BEGIN
    -- Update status to running
    UPDATE task_results
    SET status = 'running', started_at = NOW()
    WHERE id = p_task_id;

    -- Get task data
    SELECT task_data INTO v_data
    FROM task_results
    WHERE id = p_task_id;

    -- Get sleep time from data (default 10 seconds)
    v_sleep_seconds := COALESCE((v_data->>'sleep_seconds')::INTEGER, 10);

    -- Simulate long work
    RAISE NOTICE 'Task % starting, will sleep for % seconds', p_task_id, v_sleep_seconds;
    PERFORM pg_sleep(v_sleep_seconds);
    RAISE NOTICE 'Task % completed after % seconds', p_task_id, v_sleep_seconds;

    -- Mark as completed
    UPDATE task_results
    SET status = 'completed',
        result = jsonb_build_object(
            'success', true,
            'slept_for', v_sleep_seconds,
            'message', 'Task completed successfully'
        ),
        completed_at = NOW()
    WHERE id = p_task_id;

EXCEPTION WHEN OTHERS THEN
    -- Handle errors
    UPDATE task_results
    SET status = 'failed',
        error_message = SQLERRM,
        completed_at = NOW()
    WHERE id = p_task_id;

    RAISE NOTICE 'Task % failed: %', p_task_id, SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- STEP 4: Create the async launcher
-- ============================================
CREATE OR REPLACE FUNCTION start_async_task(
    p_task_name TEXT,
    p_data JSONB DEFAULT '{}'::JSONB
)
RETURNS INTEGER AS $$
DECLARE
    v_task_id INTEGER;
BEGIN
    -- Insert task record
    INSERT INTO task_results (task_name, task_data, status)
    VALUES (p_task_name, p_data, 'pending')
    RETURNING id INTO v_task_id;

    RAISE NOTICE 'Created task % with name: %', v_task_id, p_task_name;

    -- Launch in background (this returns immediately!)
    PERFORM pg_background_launch(
        format('SELECT long_running_process(%s)', v_task_id)
    );

    RAISE NOTICE 'Task % launched in background', v_task_id;

    RETURN v_task_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- STEP 5: Create helper polling function
-- ============================================
CREATE OR REPLACE FUNCTION get_task_info(p_task_id INTEGER)
RETURNS TABLE (
    id INTEGER,
    task_name TEXT,
    status TEXT,
    result JSONB,
    error_message TEXT,
    created_at TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    duration_seconds NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        t.id,
        t.task_name,
        t.status,
        t.result,
        t.error_message,
        t.created_at,
        t.started_at,
        t.completed_at,
        EXTRACT(EPOCH FROM (COALESCE(t.completed_at, NOW()) - t.created_at))::NUMERIC
    FROM task_results t
    WHERE t.id = p_task_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TESTING SECTION
-- ============================================

-- TEST 1: Start a simple task (10 seconds)
SELECT start_async_task('quick_test', '{"sleep_seconds": 10}'::JSONB);
-- Output: Returns task_id (e.g., 1)
-- This returns IMMEDIATELY

-- TEST 2: Check status immediately (should be 'pending' or 'running')
SELECT id, task_name, status, created_at FROM task_results WHERE id = 1;

-- TEST 3: Wait a few seconds and check again
SELECT pg_sleep(3);
SELECT id, status, started_at FROM task_results WHERE id = 1;
-- Status should now be 'running'

-- TEST 4: Wait for completion and check
SELECT pg_sleep(8);
SELECT * FROM get_task_info(1);
-- Status should now be 'completed'

-- TEST 5: Start multiple tasks at once
SELECT start_async_task('task_1', '{"sleep_seconds": 5}'::JSONB);
SELECT start_async_task('task_2', '{"sleep_seconds": 8}'::JSONB);
SELECT start_async_task('task_3', '{"sleep_seconds": 3}'::JSONB);
-- All three return immediately with different task_ids

-- TEST 6: Monitor all tasks
SELECT
    id,
    task_name,
    status,
    EXTRACT(EPOCH FROM (NOW() - created_at))::INTEGER as elapsed_seconds,
    CASE
        WHEN completed_at IS NOT NULL THEN
            EXTRACT(EPOCH FROM (completed_at - created_at))::INTEGER
        ELSE NULL
    END as total_duration
FROM task_results
ORDER BY id;

-- TEST 7: Watch task progress in real-time (run this in a loop)
DO $$
DECLARE
    v_status TEXT;
    v_count INTEGER := 0;
BEGIN
    LOOP
        SELECT status INTO v_status FROM task_results WHERE id = 1;
        v_count := v_count + 1;

        RAISE NOTICE 'Check %: Status = %', v_count, v_status;

        EXIT WHEN v_status IN ('completed', 'failed') OR v_count > 20;

        PERFORM pg_sleep(1);
    END LOOP;
END $$;

-- TEST 8: Start a long task and disconnect
-- Run this in one session:
SELECT start_async_task('long_task', '{"sleep_seconds": 30}'::JSONB);
-- Now close this connection/session

-- Run this in a NEW session to poll:
SELECT * FROM get_task_info(currval('task_results_id_seq'));

-- TEST 9: Test error handling
CREATE OR REPLACE FUNCTION failing_task(p_task_id INTEGER)
RETURNS VOID AS $$
BEGIN
    UPDATE task_results SET status = 'running' WHERE id = p_task_id;
    PERFORM pg_sleep(2);
    RAISE EXCEPTION 'Intentional error for testing';
END;
$$ LANGUAGE plpgsql;

-- Start a task that will fail
SELECT start_async_task('error_test', '{}'::JSONB);
-- Wait and check
SELECT pg_sleep(3);
SELECT id, status, error_message FROM task_results ORDER BY id DESC LIMIT 1;

-- TEST 10: View all tasks summary
SELECT
    status,
    COUNT(*) as count,
    AVG(EXTRACT(EPOCH FROM (completed_at - created_at))) as avg_duration_seconds
FROM task_results
WHERE completed_at IS NOT NULL
GROUP BY status;

-- TEST 11: Clean up test data
-- TRUNCATE task_results;
-- Or delete specific tests:
-- DELETE FROM task_results WHERE id IN (1,2,3);