import { backfillDone, backfillOnce } from "../backfill";
import { all, batch, first, run, stmt } from "../db";
import { repoDb } from "../scoped-db";
import type { ScopedValue } from "../scoped-db/types";
import { locatedScopes } from "../scoped-db/locators";

const INDEX = "site-directory";

/** What the directory keeps of each site, read from the scope's own `sites` table. */
export const SITE_DIRECTORY_SELECT = `SELECT id, owner_id, scratch, expires_at, paused_at,
  CASE WHEN disabled_reason IS NULL AND active_deployment_id IS NOT NULL THEN 1 ELSE 0 END AS active,
  CASE WHEN disabled_reason IS NULL THEN 0 ELSE 1 END AS disabled FROM sites`;

type DirectoryRow = {
  id: string;
  owner_id: string | null;
  scratch: number;
  expires_at: number | null;
  paused_at: number | null;
  active: number;
  disabled: number;
};

type Sql = { exec<T extends Record<string, ScopedValue>>(sql: string, ...params: ScopedValue[]): { toArray(): T[] } };

async function write(scopeId: string, rows: DirectoryRow[], now: number): Promise<void> {
  await batch([
    stmt("DELETE FROM site_directory WHERE scope_id = ?", scopeId),
    ...rows.map((row) =>
      stmt(
        `INSERT INTO site_directory (site_id, scope_id, owner_id, active, scratch, expires_at, paused_at, disabled, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON CONFLICT (site_id) DO UPDATE SET scope_id = excluded.scope_id, owner_id = excluded.owner_id,
           active = excluded.active, scratch = excluded.scratch, expires_at = excluded.expires_at,
           paused_at = excluded.paused_at, disabled = excluded.disabled, updated_at = excluded.updated_at`,
        row.id,
        scopeId,
        row.owner_id,
        row.active,
        row.scratch,
        row.expires_at,
        row.paused_at,
        row.disabled,
        now,
      ),
    ),
  ]);
}

/** RepoState alarm, a little after the scope's sites change: replaces the scope's directory rows. */
export async function publishSiteDirectory(scopeId: string, sql: Sql, now: number): Promise<void> {
  await write(scopeId, sql.exec<DirectoryRow>(SITE_DIRECTORY_SELECT).toArray(), now);
}

/** A deleted site leaves the directory at once (its scope may be going away with it). */
export async function forgetSiteDirectory(siteId: string): Promise<void> {
  await run("DELETE FROM site_directory WHERE site_id = ?", siteId);
}

/** Cron step until done: fills the directory for sites that existed before it. */
export async function backfillSiteDirectory(): Promise<void> {
  await backfillOnce(INDEX, async () => {
    const now = Date.now();
    for (const scope of await locatedScopes("site")) {
      await write(scope, await repoDb(scope).all<DirectoryRow>(SITE_DIRECTORY_SELECT), now);
    }
  });
}

export function siteDirectoryReady(): Promise<boolean> {
  return backfillDone(INDEX);
}

export type SiteTotals = {
  total: number;
  active: number;
  paused: number;
  disabled: number;
  scratch: number;
  autoPausedWeek: number;
};
export type SiteStats = { totals: SiteTotals; sitesByOwner: Map<string, number>; reportsByOwner: Map<string, number> };

function tally(rows: { owner_id: string | null; count: number }[]): Map<string, number> {
  return new Map(rows.flatMap((row) => (row.owner_id ? [[row.owner_id, row.count] as const] : [])));
}

/** Site counts from the directory copy each site scope keeps up to date (`site_directory`). */
export async function siteDirectoryStats(now: number, week: number): Promise<SiteStats> {
  const [totals, byOwner, reports] = await Promise.all([
    first<SiteTotals>(
      `SELECT COUNT(*) AS total, COALESCE(SUM(active), 0) AS active,
              COALESCE(SUM(paused_at IS NOT NULL), 0) AS paused,
              COALESCE(SUM(disabled = 1 AND paused_at IS NULL), 0) AS disabled,
              COALESCE(SUM(scratch = 1 AND (expires_at IS NULL OR expires_at > ?1)), 0) AS scratch,
              COALESCE(SUM(paused_at > ?2), 0) AS autoPausedWeek
       FROM site_directory`,
      now,
      week,
    ),
    all<{ owner_id: string | null; count: number }>(
      "SELECT owner_id, COUNT(*) AS count FROM site_directory WHERE owner_id IS NOT NULL GROUP BY owner_id",
    ),
    all<{ owner_id: string | null; count: number }>(
      `SELECT d.owner_id, COUNT(*) AS count FROM abuse_reports r JOIN site_directory d ON d.site_id = r.site_id
       WHERE r.status = 'open' AND d.owner_id IS NOT NULL GROUP BY d.owner_id`,
    ),
  ]);
  return {
    totals: totals ?? { total: 0, active: 0, paused: 0, disabled: 0, scratch: 0, autoPausedWeek: 0 },
    sitesByOwner: tally(byOwner),
    reportsByOwner: tally(reports),
  };
}