import type { Database } from "bun:sqlite";

export type ClearLocalMappingsScope = "all" | "non-cloud";

export interface ClearLocalMappingsOptions {
  scope: ClearLocalMappingsScope;
  cloudGroupKeys?: Iterable<string>;
}

export interface ClearLocalMappingsResult {
  unmappedRoots: number;
  deletedGroups: number;
  protectedInvoiceGroups: number;
}

interface MappedGroupRow {
  id: string;
  name: string;
}

interface CountRow {
  count: number;
}

export function getLocalMappingGroupKey(name: string): string {
  return name.trim().toLowerCase();
}

export function clearLocalMappings(
  sqlite: Database,
  options: ClearLocalMappingsOptions,
): ClearLocalMappingsResult {
  const cloudGroupKeys = new Set(
    Array.from(options.cloudGroupKeys ?? [], getLocalMappingGroupKey),
  );

  if (options.scope === "non-cloud" && options.cloudGroupKeys === undefined) {
    throw new Error("cloudGroupKeys is required for non-cloud mapping cleanup");
  }

  return sqlite.transaction(() => {
    const mappedGroups = sqlite
      .query<MappedGroupRow, []>(
        `SELECT DISTINCT projects.id, projects.name
         FROM projects
         INNER JOIN project_roots ON project_roots.project_id = projects.id`,
      )
      .all();
    const groupsToClear = mappedGroups.filter(
      (group) =>
        options.scope === "all" ||
        !cloudGroupKeys.has(getLocalMappingGroupKey(group.name)),
    );

    let unmappedRoots = 0;
    let deletedGroups = 0;
    let protectedInvoiceGroups = 0;
    const clearRoots = sqlite.prepare(
      "UPDATE project_roots SET project_id = NULL WHERE project_id = ?",
    );
    const countInvoiceLines = sqlite.prepare(
      "SELECT COUNT(*) AS count FROM invoice_line_items WHERE project_id = ?",
    );
    const deleteEmptyGroup = sqlite.prepare(
      `DELETE FROM projects
       WHERE id = ?
         AND NOT EXISTS (
           SELECT 1 FROM project_roots WHERE project_roots.project_id = projects.id
         )
         AND NOT EXISTS (
           SELECT 1 FROM invoice_line_items WHERE invoice_line_items.project_id = projects.id
         )`,
    );

    for (const group of groupsToClear) {
      unmappedRoots += clearRoots.run(group.id).changes;

      const invoiceLineCount = (countInvoiceLines.get(group.id) as CountRow)
        .count;
      if (invoiceLineCount > 0) {
        protectedInvoiceGroups++;
        continue;
      }

      deletedGroups += deleteEmptyGroup.run(group.id).changes;
    }

    if (options.scope === "all") {
      // Roots can point at a group missing from the local projects table when a
      // prior operation was interrupted; "all" still means every local root.
      unmappedRoots += sqlite
        .prepare(
          "UPDATE project_roots SET project_id = NULL WHERE project_id IS NOT NULL",
        )
        .run().changes;
    } else {
      unmappedRoots += sqlite
        .prepare(
          `UPDATE project_roots
           SET project_id = NULL
           WHERE project_id IS NOT NULL
             AND NOT EXISTS (
               SELECT 1 FROM projects WHERE projects.id = project_roots.project_id
             )`,
        )
        .run().changes;
    }

    return { unmappedRoots, deletedGroups, protectedInvoiceGroups };
  })();
}