import type { Database } from 'bun:sqlite';
import { Metrics } from '../services/metrics';
import { GroupSyncService } from '../services/group-sync';

// ---------------------------------------------------------------------------
// Metric collectors
// ---------------------------------------------------------------------------

function collectAllowedGroupMetrics(db: Database): void {
  try {
    const rows = db
      .prepare(`SELECT status, COUNT(*) AS c FROM allowed_groups GROUP BY status`)
      .all() as Array<{ status: string; c: number }>;
    let pending = 0, allowed = 0, blocked = 0;
    for (const r of rows) {
      const s = String(r?.status || '');
      const c = Number(r?.c || 0);
      if (s === 'pending') pending = c;
      else if (s === 'allowed') allowed = c;
      else if (s === 'blocked') blocked = c;
    }
    Metrics.set('allowed_groups_total_pending', pending);
    Metrics.set('allowed_groups_total_allowed', allowed);
    Metrics.set('allowed_groups_total_blocked', blocked);
  } catch {}
}

function collectGroupAndUserMetrics(db: Database): void {
  try {
    const groupRow = db
      .prepare(`
        SELECT
          COUNT(*) AS total,
          SUM(
            CASE
              WHEN COALESCE(active, 0) = 1
               AND COALESCE(archived, 0) = 0
              THEN 1 ELSE 0
            END
          ) AS active,
          SUM(
            CASE
              WHEN COALESCE(archived, 0) = 1
              THEN 1 ELSE 0
            END
          ) AS archived
        FROM groups
        WHERE COALESCE(is_community, 0) = 0;
      `)
      .get() as any;

    if (groupRow) {
      Metrics.set('groups_total', Number(groupRow.total ?? 0));
      Metrics.set('groups_active_total', Number(groupRow.active ?? 0));
      Metrics.set('groups_archived_total', Number(groupRow.archived ?? 0));
    }

    const gmRow = db
      .prepare(`
        SELECT COUNT(*) AS total
        FROM group_members gm
        JOIN groups g ON g.id = gm.group_id
        WHERE gm.is_active = 1
          AND COALESCE(g.active, 0) = 1
          AND COALESCE(g.is_community, 0) = 0
          AND COALESCE(g.archived, 0) = 0;
      `)
      .get() as any;

    if (gmRow) {
      Metrics.set('group_members_total', Number(gmRow.total ?? 0));
    }

    const usersRow = db.prepare(`SELECT COUNT(*) AS total FROM users;`).get() as any;
    if (usersRow) {
      Metrics.set('users_total', Number(usersRow.total ?? 0));
    }
  } catch {}
}

function collectTaskMetrics(db: Database): void {
  try {
    const row = db
      .prepare(`
        SELECT
          COUNT(*) AS total,
          SUM(CASE WHEN COALESCE(completed, 0) = 1 THEN 1 ELSE 0 END) AS completed,
          SUM(CASE WHEN COALESCE(completed, 0) = 0 THEN 1 ELSE 0 END) AS active,
          SUM(
            CASE
              WHEN COALESCE(completed, 0) = 0
               AND due_date IS NOT NULL
               AND TRIM(due_date) <> ''
               AND due_date < strftime('%Y-%m-%d','now')
            THEN 1 ELSE 0
            END
          ) AS overdue
        FROM tasks;
      `)
      .get() as any;

    if (row) {
      Metrics.set('tasks_created_total', Number(row.total ?? 0));
      Metrics.set('tasks_completed_total', Number(row.completed ?? 0));
      Metrics.set('tasks_active', Number(row.active ?? 0));
      Metrics.set('tasks_overdue', Number(row.overdue ?? 0));
    }
  } catch {}
}

function collectResponseQueueMetrics(db: Database): void {
  try {
    const row = db
      .prepare(`
        SELECT
          COUNT(*) AS pending,
          MIN(strftime('%s', created_at)) AS oldest_ts,
          strftime('%s','now') AS now_ts
        FROM response_queue
        WHERE status = 'queued';
      `)
      .get() as any;

    if (row) {
      const pending = Number(row.pending ?? 0);
      Metrics.set('response_queue_pending', pending);

      let ageSeconds = -1;
      const oldestTs = row.oldest_ts != null ? Number(row.oldest_ts) : null;
      const nowTs = row.now_ts != null ? Number(row.now_ts) : null;

      if (
        pending > 0 &&
        oldestTs != null &&
        Number.isFinite(oldestTs) &&
        nowTs != null &&
        Number.isFinite(nowTs)
      ) {
        ageSeconds = Math.max(0, nowTs - oldestTs);
      }

      Metrics.set('response_queue_oldest_age_seconds', ageSeconds);
    }
  } catch {}
}

function collectGroupSyncMetric(): void {
  try {
    const secs = GroupSyncService.getSecondsUntilNextGroupSync();
    Metrics.set('group_sync_seconds_until_next', (secs == null || !Number.isFinite(secs)) ? -1 : secs);
  } catch {}
}

// ---------------------------------------------------------------------------
// Handler
// ---------------------------------------------------------------------------

export async function handleMetricsRequest(request: Request, db: Database): Promise<Response> {
  if (request.method !== 'GET') {
    return new Response('🚫 Method not allowed', { status: 405 });
  }
  if (!Metrics.enabled()) {
    return new Response('Metrics disabled', { status: 404 });
  }

  collectAllowedGroupMetrics(db);
  collectGroupAndUserMetrics(db);
  collectTaskMetrics(db);
  collectResponseQueueMetrics(db);
  collectGroupSyncMetric();

  const format = (process.env.METRICS_FORMAT || 'prom').toLowerCase() === 'json' ? 'json' : 'prom';
  const body = Metrics.render(format as any);
  return new Response(body, {
    status: 200,
    headers: { 'Content-Type': format === 'json' ? 'application/json' : 'text/plain; version=0.0.4' }
  });
}
