import type { CommitSummary } from "~/lib/types";

const WEEK_MS = 7 * 24 * 60 * 60 * 1000;
const MAX_EMAIL_LENGTH = 254;
const MAX_LOOKUP_EMAILS = 40;

export type IndexedCommit = Pick<CommitSummary, "sha" | "authorName" | "authorEmail" | "authoredAt"> & {
  coAuthors: string[];
};
export type EmailDay = { email: string; day: string; commits: number };
export type ContributorRow = { email: string; name: string; commits: number; first: number; last: number };

function normalizeEmail(email: string): string {
  return email.trim().toLowerCase().slice(0, MAX_EMAIL_LENGTH);
}

export function utcDay(timestamp: number): string {
  return new Date(timestamp).toISOString().slice(0, 10);
}

/**
 * The set of commits reachable from one repo's default branch head, in Durable Object SQLite. The set is
 * kept closed under ancestry, which is what lets a fast-forward be indexed by walking only new commits.
 * `gen` tags rows written by a rebuild so a finished rebuild can drop everything no longer reachable.
 */
export class CommitIndex {
  constructor(private readonly sql: SqlStorage) {
    sql.exec(`CREATE TABLE IF NOT EXISTS commits (
      sha TEXT PRIMARY KEY,
      email TEXT NOT NULL,
      name TEXT NOT NULL,
      authored_at INTEGER NOT NULL,
      day TEXT NOT NULL,
      gen INTEGER NOT NULL
    )`);
    sql.exec("CREATE INDEX IF NOT EXISTS commits_email_day ON commits(email, day)");
    sql.exec("CREATE INDEX IF NOT EXISTS commits_authored ON commits(authored_at)");
    sql.exec("CREATE INDEX IF NOT EXISTS commits_gen ON commits(gen)");
    sql.exec(`CREATE TABLE IF NOT EXISTS coauthors (
      sha TEXT NOT NULL,
      email TEXT NOT NULL,
      day TEXT NOT NULL,
      PRIMARY KEY (sha, email)
    )`);
    sql.exec("CREATE INDEX IF NOT EXISTS coauthors_email_day ON coauthors(email, day)");
    sql.exec(`CREATE TABLE IF NOT EXISTS published (
      email TEXT NOT NULL,
      day TEXT NOT NULL,
      commits INTEGER NOT NULL,
      PRIMARY KEY (email, day)
    )`);
  }

  /**
   * Up to `limit` (email, day) counts that differ from what was last published to D1 (a count of 0 means
   * delete). Diffing against a local mirror keeps D1 writes proportional to what changed, so a force-push
   * that drops one commit costs one row, not a full republish.
   */
  pendingPublish(limit: number): EmailDay[] {
    return this.sql
      .exec<EmailDay>(
        `WITH credits AS (
           SELECT email, day FROM commits WHERE email != ''
           UNION ALL SELECT email, day FROM coauthors
         ), cur AS (SELECT email, day, COUNT(*) AS commits FROM credits GROUP BY email, day)
         SELECT email, day, commits FROM cur
         WHERE NOT EXISTS (SELECT 1 FROM published p WHERE p.email = cur.email AND p.day = cur.day AND p.commits = cur.commits)
         UNION ALL
         SELECT p.email, p.day, 0 AS commits FROM published p
         WHERE NOT EXISTS (SELECT 1 FROM cur WHERE cur.email = p.email AND cur.day = p.day)
         LIMIT ?`,
        limit,
      )
      .toArray();
  }

  markPublished(rows: EmailDay[]): void {
    for (const row of rows) {
      if (row.commits === 0) this.sql.exec("DELETE FROM published WHERE email = ? AND day = ?", row.email, row.day);
      else
        this.sql.exec(
          `INSERT INTO published (email, day, commits) VALUES (?, ?, ?)
           ON CONFLICT (email, day) DO UPDATE SET commits = excluded.commits`,
          row.email,
          row.day,
          row.commits,
        );
    }
  }

  clearPublished(): void {
    this.sql.exec("DELETE FROM published");
  }

  has(sha: string): boolean {
    return this.sql.exec("SELECT 1 FROM commits WHERE sha = ?", sha).toArray().length > 0;
  }

  /** Inserts commits, or re-tags already indexed ones for `gen`. */
  upsert(commits: IndexedCommit[], gen: number): void {
    for (const commit of commits) {
      const email = normalizeEmail(commit.authorEmail);
      const day = utcDay(commit.authoredAt);
      this.sql.exec(
        `INSERT INTO commits (sha, email, name, authored_at, day, gen) VALUES (?, ?, ?, ?, ?, ?)
         ON CONFLICT (sha) DO UPDATE SET gen = excluded.gen`,
        commit.sha,
        email,
        commit.authorName.slice(0, MAX_EMAIL_LENGTH),
        commit.authoredAt,
        day,
        gen,
      );
      for (const coAuthor of commit.coAuthors) {
        this.sql.exec(
          "INSERT OR IGNORE INTO coauthors (sha, email, day) VALUES (?, ?, ?)",
          commit.sha,
          normalizeEmail(coAuthor),
          day,
        );
      }
    }
  }

  dropOtherGenerations(gen: number): void {
    this.sql.exec("DELETE FROM commits WHERE gen != ?", gen);
    this.sql.exec("DELETE FROM coauthors WHERE sha NOT IN (SELECT sha FROM commits)");
  }

  clear(): void {
    this.sql.exec("DELETE FROM commits");
    this.sql.exec("DELETE FROM coauthors");
  }

  count(): number {
    return this.sql.exec<{ count: number }>("SELECT COUNT(*) AS count FROM commits").one().count;
  }

  /** Up to `limit` commits authored (or co-authored) with one of `emails` on a UTC day, newest first. */
  commitsOn(emails: string[], day: string, limit: number): Array<{ sha: string; authored_at: number }> {
    const normalized = [...new Set(emails.map(normalizeEmail))].slice(0, MAX_LOOKUP_EMAILS);
    if (normalized.length === 0) return [];
    const marks = normalized.map(() => "?").join(", ");
    return this.sql
      .exec<{ sha: string; authored_at: number }>(
        `SELECT sha, authored_at FROM commits WHERE day = ? AND email IN (${marks})
         UNION SELECT c.sha, c.authored_at FROM coauthors x JOIN commits c ON c.sha = x.sha
         WHERE x.day = ? AND x.email IN (${marks})
         ORDER BY authored_at DESC LIMIT ?`,
        day,
        ...normalized,
        day,
        ...normalized,
        limit,
      )
      .toArray();
  }

  /** Commit counts per week for the `weeks` weeks ending at `now`, oldest first. */
  weekly(now: number, weeks: number): number[] {
    const start = now - weeks * WEEK_MS;
    const out = Array.from({ length: weeks }, () => 0);
    const rows = this.sql
      .exec<{ bucket: number; count: number }>(
        "SELECT CAST((authored_at - ?) / ? AS INTEGER) AS bucket, COUNT(*) AS count FROM commits WHERE authored_at >= ? GROUP BY bucket",
        start,
        WEEK_MS,
        start,
      )
      .toArray();
    for (const row of rows) if (row.bucket >= 0 && row.bucket < weeks) out[row.bucket] = row.count;
    return out;
  }

  contributors(since: number, limit: number): ContributorRow[] {
    return this.sql
      .exec<ContributorRow>(
        `SELECT email, MAX(name) AS name, COUNT(*) AS commits, MIN(authored_at) AS first, MAX(authored_at) AS last
         FROM commits WHERE authored_at >= ? AND email != '' GROUP BY email ORDER BY commits DESC LIMIT ?`,
        since,
        limit,
      )
      .toArray();
  }

  contributorWeekly(email: string, now: number, weeks: number): number[] {
    const start = now - weeks * WEEK_MS;
    const out = Array.from({ length: weeks }, () => 0);
    const rows = this.sql
      .exec<{ bucket: number; count: number }>(
        `SELECT CAST((authored_at - ?) / ? AS INTEGER) AS bucket, COUNT(*) AS count FROM commits
         WHERE email = ? AND authored_at >= ? GROUP BY bucket`,
        start,
        WEEK_MS,
        email,
        start,
      )
      .toArray();
    for (const row of rows) if (row.bucket >= 0 && row.bucket < weeks) out[row.bucket] = row.count;
    return out;
  }
}