| 1 | -- ?1 the repositories' ids as a JSON array. Every read of workspace_signals |
| 2 | -- about pull requests starts with this, and goes on with its own CTEs. |
| 3 | -- |
| 4 | -- tied: the harness sessions tied to each pull request, as PULL_SESSIONS |
| 5 | -- (coordination/links.rs) ties them: a link, the pull request the session |
| 6 | -- said when it started, or a push to its branch (not a fork's). |
| 7 | WITH tied(pull_id, session_id) AS ( |
| 8 | SELECT p.id, l.session_id FROM session_links l |
| 9 | JOIN pulls p ON p.repo_id = l.repo_id AND p.number = l.number |
| 10 | WHERE l.repo_id IN (SELECT value FROM json_each(?1)) AND l.kind = 'pull' |
| 11 | UNION SELECT p.id, s.id FROM sessions s |
| 12 | JOIN pulls p ON p.repo_id = s.repo_id AND p.number = s.pull |
| 13 | WHERE s.repo_id IN (SELECT value FROM json_each(?1)) |
| 14 | UNION SELECT p.id, t.session_id FROM session_touches t |
| 15 | JOIN pulls p ON p.repo_id = t.repo_id AND p.source_branch = t.value |
| 16 | AND p.fork_repo_id IS NULL AND p.head_repo_id IS NULL |
| 17 | WHERE t.repo_id IN (SELECT value FROM json_each(?1)) AND t.kind = 'branch' |
| 18 | ), |
| 19 | -- When each tied pull request's first session started, as a Julian day. |
| 20 | first_session(pull_id, started) AS ( |
| 21 | SELECT tied.pull_id, MIN(julianday(s.started_at)) FROM tied JOIN sessions s ON s.id = tied.session_id |
| 22 | GROUP BY tied.pull_id |
| 23 | ), |
| 24 | -- The pull requests, with whose they are (the person who asked for it, |
| 25 | -- else its author), whether an agent's (g1t's agent opened it, it was |
| 26 | -- opened as an agent's, or a session is tied to it), and, when its first |
| 27 | -- session started before it opened, the seconds in between. |
| 28 | pr AS ( |
| 29 | SELECT p.id, p.repo_id, p.number, p.title, p.status, p.item_id, p.milestone, |
| 30 | p.created_at, p.updated_at, p.merged_at, |
| 31 | COALESCE(p.requested_by_id, p.author_id) AS owner_id, |
| 32 | CASE WHEN p.requested_by_id IS NULL THEN p.author_name ELSE p.requested_by_name END AS owner_name, |
| 33 | (p.agent = 'agent' OR p.author_id = 'usr_g1t_agent' OR f.pull_id IS NOT NULL) AS agent, |
| 34 | CASE WHEN f.started < julianday(p.created_at) |
| 35 | THEN MAX(0, (julianday(p.created_at) - f.started) * 86400) END AS draft_seconds |
| 36 | FROM pulls p LEFT JOIN first_session f ON f.pull_id = p.id |
| 37 | WHERE p.repo_id IN (SELECT value FROM json_each(?1)) |
| 38 | ) |