export const BETA_OPERATIONS_WINDOW_DAYS = 30;

export const BETA_ACCOUNTS_SQL = `
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN created_at >= ? THEN 1 ELSE 0 END) AS new_accounts,
  SUM(CASE WHEN EXISTS (
    SELECT 1 FROM hosts
    WHERE hosts.account_id = accounts.id AND hosts.lifecycle = 'active'
  ) THEN 1 ELSE 0 END) AS with_active_host
FROM accounts`;

export const BETA_PAIRINGS_SQL = `
SELECT
  COUNT(*) AS created,
  SUM(CASE WHEN status = 'claimed' THEN 1 ELSE 0 END) AS claimed,
  SUM(CASE WHEN status = 'waiting' AND expires_at > ? THEN 1 ELSE 0 END) AS waiting,
  SUM(CASE WHEN status = 'waiting' AND expires_at <= ? THEN 1 ELSE 0 END) AS expired,
  SUM(CASE
    WHEN status = 'waiting' AND (expires_at <= ? OR locked_at IS NOT NULL)
    THEN 1 ELSE 0
  END) AS attention_required,
  SUM(CASE WHEN locked_at IS NOT NULL THEN 1 ELSE 0 END) AS locked,
  AVG(CASE
    WHEN status = 'claimed' AND claimed_at IS NOT NULL
    THEN (julianday(claimed_at) - julianday(created_at)) * 86400
  END) AS average_claim_seconds
FROM pairing_requests
WHERE created_at >= ?`;

export const BETA_CLAIM_ATTEMPTS_SQL = `
SELECT
  SUM(CASE WHEN outcome = 'rejected' THEN 1 ELSE 0 END) AS rejected,
  SUM(CASE WHEN outcome = 'rate_limited' THEN 1 ELSE 0 END) AS rate_limited
FROM pairing_claim_attempts
WHERE created_at >= ?`;

export const BETA_PROVISIONING_SQL = `
SELECT
  COUNT(*) AS created,
  SUM(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS succeeded,
  SUM(CASE WHEN state = 'failed' THEN 1 ELSE 0 END) AS failed,
  SUM(CASE WHEN state IN ('provisioning', 'quiescing', 'copying', 'switching', 'draining') THEN 1 ELSE 0 END) AS in_progress,
  AVG(CASE
    WHEN state = 'active'
    THEN (julianday(updated_at) - julianday(created_at)) * 86400
  END) AS average_completion_seconds
FROM tenant_placements
WHERE created_at >= ?`;

export const BETA_SUPPORT_SQL = `
SELECT
  COUNT(*) AS created,
  SUM(CASE WHEN category = 'connection_issue' THEN 1 ELSE 0 END) AS connection_issues,
  SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open,
  SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved,
  AVG(CASE
    WHEN status = 'resolved' AND resolved_at IS NOT NULL
    THEN (julianday(resolved_at) - julianday(created_at)) * 86400
  END) AS average_resolution_seconds
FROM beta_feedback
WHERE created_at >= ?`;

export const BETA_ACCESS_REQUESTS_SQL = `
SELECT
  COUNT(*) AS submitted,
  SUM(requested_slots) AS requested_slot_demand,
  AVG(requested_slots) AS average_requested_slots,
  SUM(CASE WHEN preferred_os = 'windows' THEN 1 ELSE 0 END) AS windows,
  SUM(CASE WHEN preferred_os = 'linux' THEN 1 ELSE 0 END) AS linux,
  SUM(CASE WHEN preferred_os = 'both' THEN 1 ELSE 0 END) AS both,
  SUM(CASE WHEN status = 'requested' THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved,
  SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS declined,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE
    WHEN status = 'approved' AND resolved_at IS NOT NULL AND EXISTS (
      SELECT 1 FROM hosts
      WHERE hosts.account_id = beta_access_requests.account_id
        AND hosts.claimed_at >= beta_access_requests.resolved_at
    ) THEN 1 ELSE 0
  END) AS approved_with_post_approval_claim,
  AVG(CASE
    WHEN status IN ('approved', 'declined') AND resolved_at IS NOT NULL
    THEN (julianday(resolved_at) - julianday(requested_at)) * 86400
  END) AS average_review_seconds
FROM beta_access_requests
WHERE requested_at >= ?`;

type AggregateRow = Record<string, number | null>;

export type BetaOperationsSnapshot = {
  generatedAt: string;
  windowDays: number;
  accounts: {
    total: number;
    newAccounts: number;
    withActiveHost: number;
  };
  pairings: {
    created: number;
    claimed: number;
    waiting: number;
    expired: number;
    attentionRequired: number;
    locked: number;
    rejectedAttempts: number;
    rateLimitedAttempts: number;
    claimRatePercent: number | null;
    averageClaimSeconds: number | null;
  };
  provisioning: {
    created: number;
    succeeded: number;
    failed: number;
    inProgress: number;
    successRatePercent: number | null;
    averageCompletionSeconds: number | null;
  };
  support: {
    created: number;
    connectionIssues: number;
    open: number;
    resolved: number;
    resolutionRatePercent: number | null;
    averageResolutionSeconds: number | null;
  };
  accessRequests: {
    submitted: number;
    requestedSlotDemand: number;
    averageRequestedSlots: number | null;
    windows: number;
    linux: number;
    both: number;
    pending: number;
    approved: number;
    declined: number;
    cancelled: number;
    decided: number;
    approvalRatePercent: number | null;
    averageReviewSeconds: number | null;
    approvedWithPostApprovalClaim: number;
    postApprovalClaimRatePercent: number | null;
  };
  unavailable: readonly [
    "relay_reconnect_rate",
    "relay_latency",
    "runtime_resource_cost",
    "support_effort",
  ];
};

function count(row: AggregateRow, key: string): number {
  const value = Number(row[key] ?? 0);
  return Number.isFinite(value) && value > 0 ? Math.floor(value) : 0;
}

function duration(row: AggregateRow, key: string): number | null {
  const value = Number(row[key]);
  return Number.isFinite(value) && value >= 0 ? Math.round(value) : null;
}

function average(row: AggregateRow, key: string): number | null {
  const value = Number(row[key]);
  return Number.isFinite(value) && value >= 0
    ? Math.round(value * 10) / 10
    : null;
}

function percent(numerator: number, denominator: number): number | null {
  if (denominator <= 0) return null;
  return Math.round((numerator / denominator) * 1000) / 10;
}

export function deriveBetaOperationsSnapshot(input: {
  generatedAt: string;
  accounts: AggregateRow;
  pairings: AggregateRow;
  claimAttempts: AggregateRow;
  provisioning: AggregateRow;
  support: AggregateRow;
  accessRequests: AggregateRow;
}): BetaOperationsSnapshot {
  const pairingCreated = count(input.pairings, "created");
  const pairingClaimed = count(input.pairings, "claimed");
  const provisioningCreated = count(input.provisioning, "created");
  const provisioningSucceeded = count(input.provisioning, "succeeded");
  const supportCreated = count(input.support, "created");
  const supportResolved = count(input.support, "resolved");
  const accessSubmitted = count(input.accessRequests, "submitted");
  const accessApproved = count(input.accessRequests, "approved");
  const accessDeclined = count(input.accessRequests, "declined");
  const accessDecided = accessApproved + accessDeclined;
  const approvedWithPostApprovalClaim = count(
    input.accessRequests,
    "approved_with_post_approval_claim",
  );

  return {
    generatedAt: input.generatedAt,
    windowDays: BETA_OPERATIONS_WINDOW_DAYS,
    accounts: {
      total: count(input.accounts, "total"),
      newAccounts: count(input.accounts, "new_accounts"),
      withActiveHost: count(input.accounts, "with_active_host"),
    },
    pairings: {
      created: pairingCreated,
      claimed: pairingClaimed,
      waiting: count(input.pairings, "waiting"),
      expired: count(input.pairings, "expired"),
      attentionRequired: count(input.pairings, "attention_required"),
      locked: count(input.pairings, "locked"),
      rejectedAttempts: count(input.claimAttempts, "rejected"),
      rateLimitedAttempts: count(input.claimAttempts, "rate_limited"),
      claimRatePercent: percent(pairingClaimed, pairingCreated),
      averageClaimSeconds: duration(input.pairings, "average_claim_seconds"),
    },
    provisioning: {
      created: provisioningCreated,
      succeeded: provisioningSucceeded,
      failed: count(input.provisioning, "failed"),
      inProgress: count(input.provisioning, "in_progress"),
      successRatePercent: percent(provisioningSucceeded, provisioningCreated),
      averageCompletionSeconds: duration(
        input.provisioning,
        "average_completion_seconds",
      ),
    },
    support: {
      created: supportCreated,
      connectionIssues: count(input.support, "connection_issues"),
      open: count(input.support, "open"),
      resolved: supportResolved,
      resolutionRatePercent: percent(supportResolved, supportCreated),
      averageResolutionSeconds: duration(
        input.support,
        "average_resolution_seconds",
      ),
    },
    accessRequests: {
      submitted: accessSubmitted,
      requestedSlotDemand: count(input.accessRequests, "requested_slot_demand"),
      averageRequestedSlots: average(input.accessRequests, "average_requested_slots"),
      windows: count(input.accessRequests, "windows"),
      linux: count(input.accessRequests, "linux"),
      both: count(input.accessRequests, "both"),
      pending: count(input.accessRequests, "pending"),
      approved: accessApproved,
      declined: accessDeclined,
      cancelled: count(input.accessRequests, "cancelled"),
      decided: accessDecided,
      approvalRatePercent: percent(accessApproved, accessDecided),
      averageReviewSeconds: duration(input.accessRequests, "average_review_seconds"),
      approvedWithPostApprovalClaim,
      postApprovalClaimRatePercent: percent(
        approvedWithPostApprovalClaim,
        accessApproved,
      ),
    },
    unavailable: [
      "relay_reconnect_rate",
      "relay_latency",
      "runtime_resource_cost",
      "support_effort",
    ],
  };
}