import { repoDb } from "./scoped-db";
import { pullBaseRepoIds } from "./pull-scope";
import { ZERO_SHA } from "./git/store";
import type { RepoRow } from "~/lib/types";

const RECENT_WINDOW_MS = 2 * 60 * 60 * 1000;
const MAX_SUGGESTIONS = 3;
const HEADS_PREFIX = "refs/heads/";

export type RecentBranchPush = { branch: string; pushedAt: number };

type PushRow = { ref: string; after_sha: string; created_at: number };

/**
 * Branches the viewer pushed recently that have no open pull request yet, newest first. Only the latest
 * push per ref counts, so a branch that was pushed and then deleted does not get suggested.
 */
export async function recentBranchPushesFor(
  repo: Pick<RepoRow, "id" | "default_branch">,
  viewerId: string,
  now = Date.now(),
) {
  const rows = await repoDb(repo.id).all<PushRow>(
    `SELECT p.ref, p.after_sha, p.created_at FROM push_events p
     WHERE p.repo_id = ?1 AND p.pusher_id = ?2 AND p.created_at >= ?3 AND p.ref LIKE 'refs/heads/%'
       AND p.created_at = (
         SELECT MAX(q.created_at) FROM push_events q WHERE q.repo_id = ?1 AND q.ref = p.ref
       )
     ORDER BY p.created_at DESC`,
    repo.id,
    viewerId,
    now - RECENT_WINDOW_MS,
  );
  if (rows.length === 0) return [];
  // Open pull requests headed here live here, or in the parent's object when this repo is a fork.
  const openHeads = new Set(
    (
      await Promise.all(
        (await pullBaseRepoIds(repo.id)).map((baseId) =>
          repoDb(baseId).all<{ head_ref: string }>(
            `SELECT pr.head_ref FROM pull_requests pr JOIN issues i ON i.id = pr.issue_id
             WHERE pr.head_repo_id = ? AND i.state = 'open'`,
            repo.id,
          ),
        ),
      )
    )
      .flat()
      .map((row) => row.head_ref),
  );
  const suggestions: RecentBranchPush[] = [];
  for (const row of rows) {
    if (row.after_sha === ZERO_SHA) continue;
    const branch = row.ref.slice(HEADS_PREFIX.length);
    if (!branch || branch === repo.default_branch || openHeads.has(branch)) continue;
    if (suggestions.some((item) => item.branch === branch)) continue;
    suggestions.push({ branch, pushedAt: row.created_at });
    if (suggestions.length === MAX_SUGGESTIONS) break;
  }
  return suggestions;
}