import { all, chunks } from "./db";
import { accountDb, repoDb, stmt } from "./scoped-db";
import type { ScopedStatement } from "./scoped-db";
import { backfillDone, backfillRepos } from "./backfill";

/**
 * Activity rows live in each repository's RepoState; the contribution graph needs "this account's activity in a
 * time range". Each actor's AccountState keeps `activity_repos` (repository, first and last counted activity) so
 * the graph visits only those repositories. RepoState feeds it from every activity insert it runs.
 */
export const ACTIVITY_INDEX = "activity-repos";

/** Kinds that never count as contributions (contributions.ts COUNTED_ACTIVITY) and so are not indexed. */
const UNCOUNTED = new Set(["view", "push"]);
const ACTIVITY_INSERT = /^\s*INSERT\s+INTO\s+activity\s*\(\s*id\s*,\s*actor_id\s*,\s*repo_id\s*,\s*kind\s*,/i;

export type ActivityTouch = { actorId: string; repoId: string; at: number; counted: boolean };

/** The activity inserts among `statements` (repoActivityStatement's shape), for the actor index. */
export function activityTouches(statements: ScopedStatement[]): ActivityTouch[] {
  const out: ActivityTouch[] = [];
  for (const { sql, params } of statements) {
    if (!ACTIVITY_INSERT.test(sql)) continue;
    const [, actorId, repoId, kind, , createdAt] = params;
    if (typeof actorId !== "string" || typeof repoId !== "string") continue;
    out.push({ actorId, repoId, at: Number(createdAt) || Date.now(), counted: !UNCOUNTED.has(String(kind)) });
  }
  return out;
}

const UPSERT = `INSERT INTO activity_repos (repo_id, first_at, last_at, touched_at) VALUES (?1, ?2, ?3, ?4)
  ON CONFLICT (repo_id) DO UPDATE SET
    first_at = CASE WHEN excluded.first_at IS NULL THEN first_at ELSE MIN(COALESCE(first_at, excluded.first_at), excluded.first_at) END,
    last_at = CASE WHEN excluded.last_at IS NULL THEN last_at ELSE MAX(COALESCE(last_at, excluded.last_at), excluded.last_at) END,
    touched_at = MAX(touched_at, excluded.touched_at)`;

export async function indexActivity(touches: ActivityTouch[]): Promise<void> {
  await Promise.all(
    touches.map((touch) =>
      accountDb(touch.actorId).run(
        UPSERT,
        touch.repoId,
        touch.counted ? touch.at : null,
        touch.counted ? touch.at : null,
        touch.at,
      ),
    ),
  );
}

/** Every repository the account has activity rows in (pushes and views included), for account deletion. */
export async function touchedRepoIds(accountId: string): Promise<string[]> {
  const rows = await accountDb(accountId).all<{ repo_id: string }>("SELECT repo_id FROM activity_repos");
  return rows.map((row) => row.repo_id);
}

/**
 * Repositories `accountId` has counted activity in that overlaps `[start, end)` (all of them when no range),
 * from the index once it is complete; until then every repository, as before.
 */
export async function activityRepoIds(
  accountId: string,
  range: { start: number; end: number } | null,
): Promise<string[]> {
  if (!(await backfillDone(ACTIVITY_INDEX))) {
    return (await all<{ id: string }>("SELECT id FROM repos")).map((row) => row.id);
  }
  const rows = range
    ? await accountDb(accountId).all<{ repo_id: string }>(
        "SELECT repo_id FROM activity_repos WHERE first_at IS NOT NULL AND last_at >= ? AND first_at < ?",
        range.start,
        range.end,
      )
    : await accountDb(accountId).all<{ repo_id: string }>(
        "SELECT repo_id FROM activity_repos WHERE first_at IS NOT NULL",
      );
  return rows.map((row) => row.repo_id);
}

/** The ids among `repoIds` that pass `filter` (SQL over `repos r`, may use ?1 for the account). */
export async function filterRepoIds(
  repoIds: string[],
  filter: string,
  accountId: string,
): Promise<Array<{ id: string; owner: string; name: string; private: number }>> {
  const out: Array<{ id: string; owner: string; name: string; private: number }> = [];
  for (const part of chunks(repoIds, 80)) {
    out.push(
      ...(await all<{ id: string; owner: string; name: string; private: number }>(
        `SELECT r.id, a.name AS owner, r.name, r.visibility != 'public' AS private
         FROM repos r JOIN accounts a ON a.id = r.owner_id
         WHERE r.id IN (${part.map((_, index) => `?${index + 2}`).join(", ")}) AND ${filter}`,
        accountId,
        ...part,
      )),
    );
  }
  return out;
}

/** Cron step: indexes activity recorded before the index existed. */
export async function backfillActivityIndex(): Promise<void> {
  await backfillRepos(ACTIVITY_INDEX, async (repoId) => {
    const rows = await repoDb(repoId).all<{
      actor_id: string;
      first_at: number | null;
      last_at: number | null;
      touched_at: number;
    }>(
      `SELECT actor_id,
         MIN(CASE WHEN kind NOT IN ('view', 'push') THEN created_at END) AS first_at,
         MAX(CASE WHEN kind NOT IN ('view', 'push') THEN created_at END) AS last_at,
         MAX(created_at) AS touched_at
       FROM activity WHERE actor_id IS NOT NULL GROUP BY actor_id`,
    );
    for (const row of rows) {
      await accountDb(row.actor_id).batch([stmt(UPSERT, repoId, row.first_at, row.last_at, row.touched_at)]);
    }
  });
}