-- -- Insert example data into runs table
-- INSERT INTO runs (name,created_at) VALUES
--     ('Test Suite Run 1', (now() - interval '1 min')),
--     ('Test Suite Run 2', (now() - interval '1.5 min')),
--     ('Integration Tests', (now() - interval '1 days 2 min'));


-- -- Insert example data into test_runs table
-- INSERT INTO test_runs (run_id, test_name, path, run_type, created_at, success, pending ) VALUES
--     (1, 'Login Test', '/tests/auth/login.test.ts', 'start', (now()), true, false),
--     (1, 'Login Test', '/tests/auth/login.test.ts', 'end', (now() + interval '8 second'), true, false),
  
--     (1, 'Registration Test', '/tests/auth/register.test.ts', 'start', (now() + interval '10 second'), true, false),
--     (1, 'Registration Test', '/tests/auth/register.test.ts', 'end', (now() + interval '25 second'), true, false),
  
--     (1, 'Password Reset Test', '/tests/auth/password-reset.test.ts', 'start', (now() + interval '30 second'), true, false),
--     (1, 'Password Reset Test', '/tests/auth/password-reset.test.ts', 'end', (now() + interval '42 second'), true, false),
    
--     (2, 'Login Test', '/tests/auth/login.test.ts', 'start', (now() + interval '1 minute'), true, false),
--     (2, 'Login Test', '/tests/auth/login.test.ts', 'end', (now() + interval '1 minute 12 second'), false, false),
  
--     (2, 'Registration Test', '/tests/auth/register.test.ts', 'start', (now() + interval '2 minute'), true, false),
--     (2, 'Registration Test', '/tests/auth/register.test.ts', 'end', (now() + interval '2 minute 18 second'), true, false),
  
--     (2, 'Password Reset Test', '/tests/auth/password-reset.test.ts', 'start', (now() + interval '3 minute'), true, false),
--     (2, 'Password Reset Test', '/tests/auth/password-reset.test.ts', 'end', (now() + interval '5 minute 15.5 second'), false, false),
    
--     (3, 'API Integration', '/tests/api/integration.test.ts', 'start', (now() + interval '1 hours'), true, false),
--     (3, 'API Integration', '/tests/api/integration.test.ts', 'end', (now() + interval '1 hours 8 minutes'), true, false),
  
--     (3, 'Database Integration', '/tests/api/database.test.ts', 'start', (now() + interval '1 hours 10 minutes' - interval '1 days'), true, false),
--     (3, 'Database Integration', '/tests/api/database.test.ts', 'end', (now() + interval '1 hours 25 minutes' - interval '1 days'), false, false);


--     -- Create a view that shows test durations
-- CREATE VIEW test_durations AS
-- SELECT 
--         tr_end.run_id,
--         tr_end.test_name,
--         tr_end.path,
--         EXTRACT(EPOCH FROM age(tr_end.created_at, tr_start.created_at)) as totalSeconds,
--         tr_start.created_at as started_at,
--         tr_end.created_at as ended_at
--     FROM test_runs tr_end
--     JOIN test_runs tr_start 
--         ON tr_start.run_id = tr_end.run_id 
--         AND tr_start.test_name = tr_end.test_name
--         AND tr_start.path = tr_end.path
--         AND tr_start.run_type = 'start'
--         AND tr_end.run_type = 'end';



--         -- Create a view that shows total run durations
-- CREATE VIEW run_durations AS
--             SELECT
--                 r.id as run_id,
--                 r.name as run_name,
--                 r.created_at as run_date,
--                 SUM(td.totalSeconds) as totalSeconds
--             FROM runs r
-- 				LEFT JOIN test_durations td ON td.run_id = r.id
--             GROUP BY r.id, r.name;

-- -- Create a view that returns JSON-structured test results
-- CREATE VIEW formatted_test_results AS
-- WITH test_results AS (
--     SELECT tr.run_id, tr.test_name, tr.path, tr.success, td.totalSeconds
--     FROM test_runs tr
--     JOIN test_durations td ON td.run_id = tr.run_id 
--         AND td.test_name = tr.test_name
--         AND td.path = tr.path
--     WHERE tr.run_type = 'end'
-- ),
-- test_runs_json AS (
--     SELECT 
--         rd.run_id, rd.run_name, rd.totalSeconds, rd.run_date, SUM(CASE WHEN tr.success THEN 1 ELSE 0 END) as success_count,
--         SUM(CASE WHEN NOT tr.success THEN 1 ELSE 0 END) as failure_count,
--         json_agg(
--             json_build_object(
--                 'test_name', tr.test_name,
--                 'path', tr.path,
--                 'duration', tr.totalSeconds,
--                 -- 'duration_seconds', ROUND(EXTRACT(EPOCH FROM tr.duration)::numeric, 2),
--                 'status', CASE WHEN tr.success THEN 'success' ELSE 'failure' END
--             ) ORDER BY tr.test_name
--         ) as children
--     FROM run_durations rd
--     JOIN test_results tr ON tr.run_id = rd.run_id
--     GROUP BY rd.run_id, rd.run_name, rd.totalSeconds, rd.run_date
-- )
-- SELECT json_build_object(
--     'run_id', trj.run_id,
--     'run_name', trj.run_name,
--     'date', trj.run_date,
--     'total_duration', trj.totalSeconds,
--     'success_count', trj.success_count,
--     'failure_count', trj.failure_count,
--     'children', trj.children
-- ) as result
-- FROM test_runs_json trj
-- ORDER BY trj.run_id;