| 1 | -- ?1 the repositories' ids as a JSON array; ?2 and ?3 the period's bounds. |
| 2 | -- A first review is the first approval or request for changes from a person |
| 3 | -- other than the pull request's owner: g1t's review agent gives verdicts |
| 4 | -- too, and workspace agents keep theirs in agent_verdict, so neither counts. |
| 5 | WITH selected AS ( |
| 6 | SELECT p.*, |
| 7 | (SELECT MIN(c.created_at) FROM comments c |
| 8 | WHERE c.repo_id = p.repo_id AND c.number = p.number |
| 9 | AND c.verdict IN ('approve', 'request_changes') |
| 10 | AND c.author_id != COALESCE(p.requested_by_id, p.author_id) |
| 11 | AND c.author_id != 'usr_g1t_agent' AND c.agent_id IS NULL) AS reviewed_at |
| 12 | FROM pulls p WHERE p.repo_id IN (SELECT value FROM json_each(?1)) |
| 13 | ) |
| 14 | SELECT repo_id, |
| 15 | SUM(created_at >= ?2 AND created_at < ?3) AS opened, |
| 16 | SUM(status = 'merged' AND merged_at >= ?2 AND merged_at < ?3) AS merged, |
| 17 | SUM(status IN ('draft', 'open')) AS active, |
| 18 | SUM(status = 'open' AND reviewed_at IS NULL) AS unreviewed, |
| 19 | SUM(status IN ('draft', 'open') AND EXISTS ( |
| 20 | SELECT 1 FROM queue_entries q WHERE q.pull_id = selected.id AND q.state IN ('waiting', 'testing', 'passed') |
| 21 | )) AS queued, |
| 22 | SUM(CASE WHEN status = 'merged' AND merged_at >= ?2 AND merged_at < ?3 |
| 23 | THEN MAX(0, (julianday(merged_at) - julianday(created_at)) * 86400) ELSE 0 END) AS lead_seconds, |
| 24 | SUM(CASE WHEN reviewed_at >= ?2 AND reviewed_at < ?3 |
| 25 | THEN MAX(0, (julianday(reviewed_at) - julianday(created_at)) * 86400) ELSE 0 END) AS review_seconds, |
| 26 | SUM(COALESCE(reviewed_at >= ?2 AND reviewed_at < ?3, 0)) AS reviewed |
| 27 | FROM selected GROUP BY repo_id |