import { all, first } from "./db";
import { withStore } from "./git/store";
import { repoInsights } from "./insights/repo-insights-do";
import { COUNTED_ACTIVITY, VERIFIED_EMAILS } from "./contributions";
import type { PrivateMode } from "./contributions";
import type { JsonValue } from "~/lib/types";

const DAY_MS = 24 * 60 * 60 * 1000;
export const DAY_REPOS_PAGE = 10;
export const DAY_ACTIVITY_PAGE = 20;
/** Commit subjects shown per repository; the rest are one click away in the repository's history. */
const SAMPLE_COMMITS = 3;

export type DayCommitRepo = {
  owner: string;
  name: string;
  commits: number;
  sample: Array<{ sha: string; subject: string }>;
};

export type DayActivity = {
  id: string;
  kind: string;
  data: { [key: string]: JsonValue };
  repo: { owner: string; name: string };
  createdAt: number;
};

export type ContributionDayDetail = {
  day: string;
  commitRepos: { items: DayCommitRepo[]; total: number; page: number; hasMore: boolean };
  activity: { items: DayActivity[]; total: number; page: number; hasMore: boolean };
  /** Contributions to private repositories shown only as a number (`counts` mode). */
  privateCount: number;
};

type RepoDayRow = {
  id: string;
  owner: string;
  name: string;
  artifacts_name: string;
  private: number;
  commits: number;
};

async function sampleCommits(row: RepoDayRow, emails: string[], day: string): Promise<DayCommitRepo["sample"]> {
  try {
    const shas = await repoInsights(row.id).commitsOn(emails, day, SAMPLE_COMMITS);
    if (shas.length === 0) return [];
    return await withStore(row.artifacts_name, async (store) => {
      const commits = await Promise.all(shas.map((entry) => store.commit(entry.sha)));
      return commits.flatMap((commit) => (commit ? [{ sha: commit.sha, subject: commit.subject }] : []));
    });
  } catch (error) {
    console.warn("Contribution day sample failed", row.id, error);
    return [];
  }
}

/**
 * One day of a user's contributions, for the panel under the graph: commits grouped by repository (most
 * first) and the day's other activity in order, each paginated so a prolific day never renders unbounded.
 * In `counts` mode private repositories contribute only to `privateCount`; in `none` mode they are absent.
 */
export async function contributionDayDetail(
  accountId: string,
  day: string,
  mode: PrivateMode,
  pages: { repos: number; activity: number },
): Promise<ContributionDayDetail> {
  const start = Date.parse(`${day}T00:00:00Z`);
  const end = start + DAY_MS;
  const visible = mode === "full" ? "1 = 1" : "r.visibility = 'public'";
  const counted = mode === "none" ? "r.visibility = 'public'" : "1 = 1";
  const repoOffset = (pages.repos - 1) * DAY_REPOS_PAGE;
  const activityOffset = (pages.activity - 1) * DAY_ACTIVITY_PAGE;
  const [repoRows, repoTotals, activityRows, activityTotals, emails] = await Promise.all([
    all<RepoDayRow>(
      `SELECT r.id, a.name AS owner, r.name, r.artifacts_name, r.visibility != 'public' AS private,
         SUM(d.commits) AS commits
       FROM commit_email_days d JOIN repos r ON r.id = d.repo_id JOIN accounts a ON a.id = r.owner_id
       WHERE d.email IN (${VERIFIED_EMAILS}) AND d.day = ?2 AND r.fork_of_id IS NULL AND r.status = 'ready'
         AND ${visible}
       GROUP BY r.id ORDER BY commits DESC, owner, r.name LIMIT ?3 OFFSET ?4`,
      accountId,
      day,
      DAY_REPOS_PAGE + 1,
      repoOffset,
    ),
    first<{ repos: number; private_commits: number | null }>(
      `SELECT COUNT(DISTINCT CASE WHEN ${visible} THEN r.id END) AS repos,
         SUM(CASE WHEN r.visibility != 'public' THEN d.commits ELSE 0 END) AS private_commits
       FROM commit_email_days d JOIN repos r ON r.id = d.repo_id
       WHERE d.email IN (${VERIFIED_EMAILS}) AND d.day = ?2 AND r.fork_of_id IS NULL AND r.status = 'ready'
         AND ${counted}`,
      accountId,
      day,
    ),
    all<{ id: string; kind: string; data: string; owner: string; name: string; created_at: number }>(
      `SELECT ac.id, ac.kind, ac.data, a.name AS owner, r.name, ac.created_at
       FROM activity ac JOIN repos r ON r.id = ac.repo_id JOIN accounts a ON a.id = r.owner_id
       WHERE ac.actor_id = ?1 AND ac.created_at >= ?2 AND ac.created_at < ?3 AND ${COUNTED_ACTIVITY} AND ${visible}
       ORDER BY ac.created_at, ac.id LIMIT ?4 OFFSET ?5`,
      accountId,
      start,
      end,
      DAY_ACTIVITY_PAGE + 1,
      activityOffset,
    ),
    first<{ visible: number; private: number | null }>(
      `SELECT COUNT(CASE WHEN ${visible} THEN 1 END) AS visible,
         SUM(CASE WHEN r.visibility != 'public' THEN 1 ELSE 0 END) AS private
       FROM activity ac JOIN repos r ON r.id = ac.repo_id
       WHERE ac.actor_id = ?1 AND ac.created_at >= ?2 AND ac.created_at < ?3 AND ${COUNTED_ACTIVITY} AND ${counted}`,
      accountId,
      start,
      end,
    ),
    all<{ email: string }>(VERIFIED_EMAILS, accountId),
  ]);
  const shownRepos = repoRows.slice(0, DAY_REPOS_PAGE);
  const addresses = emails.map((row) => row.email);
  const commitRepos = await Promise.all(
    shownRepos.map(async (row) => ({
      owner: row.owner,
      name: row.name,
      commits: row.commits,
      sample: await sampleCommits(row, addresses, day),
    })),
  );
  const privateCount = mode === "counts" ? (repoTotals?.private_commits ?? 0) + (activityTotals?.private ?? 0) : 0;
  return {
    day,
    commitRepos: {
      items: commitRepos,
      total: repoTotals?.repos ?? 0,
      page: pages.repos,
      hasMore: repoRows.length > DAY_REPOS_PAGE,
    },
    activity: {
      items: activityRows.slice(0, DAY_ACTIVITY_PAGE).map((row) => ({
        id: row.id,
        kind: row.kind,
        data: JSON.parse(row.data),
        repo: { owner: row.owner, name: row.name },
        createdAt: row.created_at,
      })),
      total: activityTotals?.visible ?? 0,
      page: pages.activity,
      hasMore: activityRows.length > DAY_ACTIVITY_PAGE,
    },
    privateCount,
  };
}