import { Hono } from 'hono';
import { SyncHistoryRepo } from '@/entities/sync-history';
import { getDb, pgAll, pgGet } from '@/shared/db';

const AGGREGATE_SENTINEL = 'default';

function isAggregate(deviceId: string | undefined): boolean {
  return !deviceId || deviceId === AGGREGATE_SENTINEL;
}

type SyncStatus = 'healthy' | 'stale' | 'failing';

interface HealthEntry {
  source: string;
  lastSuccessAt: string | null;
  lastAttemptAt: string | null;
  lastError: string | null;
  status: SyncStatus;
}

export interface FailedSendItem {
  id: string;
  deviceId: string;
  toHandle: string;
  body: string;
  status: string;
  failureReason: string | null;
  createdAt: string;
  updatedAt: string;
}

const SOURCES = ['messages', 'mail', 'photos', 'calendar', 'contacts'] as const;

function deriveStatus(row: {
  last_success_at: string | null;
  last_attempt_at: string | null;
  last_status: string | null;
}): SyncStatus {
  if (row.last_status === 'error') return 'failing';
  if (!row.last_success_at) return 'stale';
  const ageMs = Date.now() - new Date(row.last_success_at).getTime();
  return ageMs > 60 * 60 * 1000 ? 'stale' : 'healthy';
}

export const syncClientRouter = new Hono()
  .get('/health', async (c) => {
    const db = getDb();
    const rows = await pgAll<{
      module: string;
      last_success_at: string | null;
      last_attempt_at: string | null;
      last_status: string | null;
      last_error: string | null;
    }>(
      db,
      `SELECT
         module,
         MAX(CASE WHEN status = 'success' THEN started_at END)::text AS last_success_at,
         MAX(started_at)::text AS last_attempt_at,
         (array_agg(status ORDER BY started_at DESC))[1] AS last_status,
         (array_agg(error_message ORDER BY started_at DESC))[1] AS last_error
       FROM macsync.sync_history
       WHERE module = ANY($1)
       GROUP BY module`,
      [[...SOURCES]],
    );

    const byModule = new Map(rows.map((r) => [r.module, r]));

    const health: HealthEntry[] = SOURCES.map((source) => {
      const row = byModule.get(source);
      if (!row) {
        return { source, lastSuccessAt: null, lastAttemptAt: null, lastError: null, status: 'stale' as SyncStatus };
      }
      return {
        source,
        lastSuccessAt: row.last_success_at,
        lastAttemptAt: row.last_attempt_at,
        lastError: row.last_error,
        status: deriveStatus(row),
      };
    });

    return c.json(health);
  })
  .get('/send-queue/summary', async (c) => {
    const db = getDb();
    const row = await pgGet<{
      queued: string;
      failed: string;
      sent_last_hour: string;
      oldest_queued_at: string | null;
    }>(
      db,
      `SELECT
         COUNT(CASE WHEN status = 'queued' THEN 1 END)::text AS queued,
         COUNT(CASE WHEN status = 'failed' THEN 1 END)::text AS failed,
         COUNT(CASE WHEN status = 'sent' AND sent_at >= now() - interval '1 hour' THEN 1 END)::text AS sent_last_hour,
         MIN(CASE WHEN status = 'queued' THEN created_at END)::text AS oldest_queued_at
       FROM macsync.send_queue`,
    );

    return c.json({
      queued: parseInt(row?.queued ?? '0', 10),
      failed: parseInt(row?.failed ?? '0', 10),
      sentLastHour: parseInt(row?.sent_last_hour ?? '0', 10),
      oldestQueuedAt: row?.oldest_queued_at ?? null,
    });
  })
  .get('/throughput', async (c) => {
    const windowParam = c.req.query('window') ?? '24h';
    const hours = windowParam === '1h' ? 1 : windowParam === '7d' ? 168 : 24;
    const db = getDb();

    const row = await pgGet<{
      imessages: string;
      mail: string;
      contacts: string;
      photos: string;
      events: string;
      imessages_total: string;
      mail_total: string;
      contacts_total: string;
      photos_total: string;
      events_total: string;
    }>(
      db,
      `SELECT
         (SELECT COUNT(*)::text FROM macsync.messages WHERE provider = 'imessage' AND created_at >= now() - make_interval(hours => $1)) AS imessages,
         (SELECT COUNT(*)::text FROM macsync.messages WHERE provider = 'icloud'   AND created_at >= now() - make_interval(hours => $1)) AS mail,
         (SELECT COUNT(*)::text FROM macsync.contacts  WHERE created_at >= now() - make_interval(hours => $1)) AS contacts,
         (SELECT COUNT(*)::text FROM macsync.photos    WHERE created_at >= now() - make_interval(hours => $1)) AS photos,
         (SELECT COUNT(*)::text FROM macsync.events    WHERE synced_at  >= now() - make_interval(hours => $1)) AS events,
         (SELECT COUNT(*)::text FROM macsync.messages WHERE provider = 'imessage') AS imessages_total,
         (SELECT COUNT(*)::text FROM macsync.messages WHERE provider = 'icloud')   AS mail_total,
         (SELECT COUNT(*)::text FROM macsync.contacts)  AS contacts_total,
         (SELECT COUNT(*)::text FROM macsync.photos)    AS photos_total,
         (SELECT COUNT(*)::text FROM macsync.events)    AS events_total`,
      [hours],
    );

    return c.json({
      imessages: parseInt(row?.imessages ?? '0', 10),
      mail: parseInt(row?.mail ?? '0', 10),
      contacts: parseInt(row?.contacts ?? '0', 10),
      photos: parseInt(row?.photos ?? '0', 10),
      events: parseInt(row?.events ?? '0', 10),
      totals: {
        imessages: parseInt(row?.imessages_total ?? '0', 10),
        mail: parseInt(row?.mail_total ?? '0', 10),
        contacts: parseInt(row?.contacts_total ?? '0', 10),
        photos: parseInt(row?.photos_total ?? '0', 10),
        events: parseInt(row?.events_total ?? '0', 10),
      },
    });
  })
  .get('/stats', async (c) => {
    const deviceId = c.req.query('device_id');
    const aggregate = isAggregate(deviceId);

    const db = getDb();

    const stats = aggregate
      ? await pgGet<{
          total_syncs: string;
          successful_syncs: string;
          failed_syncs: string;
          total_messages: string;
          total_contacts: string;
          last_sync_at: string | null;
        }>(
          db,
          `SELECT
            COUNT(*)::text as total_syncs,
            COUNT(CASE WHEN status = 'success' THEN 1 END)::text as successful_syncs,
            COUNT(CASE WHEN status = 'error' THEN 1 END)::text as failed_syncs,
            COALESCE(SUM(messages_synced), 0)::text as total_messages,
            COALESCE(SUM(contacts_synced), 0)::text as total_contacts,
            MAX(started_at)::text as last_sync_at
           FROM macsync.sync_history`,
        )
      : await SyncHistoryRepo.getStats(deviceId!);

    const systemStats = aggregate
      ? await pgAll<{ table_name: string; count: string }>(
          db,
          `SELECT 'messages' as table_name, COUNT(*)::text as count FROM macsync.messages
           UNION ALL SELECT 'contacts', COUNT(*)::text FROM macsync.contacts
           UNION ALL SELECT 'conversations', COUNT(*)::text FROM macsync.conversations
           UNION ALL SELECT 'albums', COUNT(*)::text FROM macsync.albums
           UNION ALL SELECT 'photos', COUNT(*)::text FROM macsync.photos
           UNION ALL SELECT 'calendars', COUNT(*)::text FROM macsync.calendars
           UNION ALL SELECT 'events', COUNT(*)::text FROM macsync.events
           UNION ALL SELECT 'pending_sends', COUNT(*)::text FROM macsync.send_queue WHERE status = 'queued'`,
        )
      : await pgAll<{ table_name: string; count: string }>(
          db,
          `SELECT 'messages' as table_name, COUNT(*)::text as count FROM macsync.messages WHERE device_id = $1
           UNION ALL SELECT 'contacts', COUNT(*)::text FROM macsync.contacts WHERE device_id = $1
           UNION ALL SELECT 'conversations', COUNT(*)::text FROM macsync.conversations WHERE device_id = $1
           UNION ALL SELECT 'albums', COUNT(*)::text FROM macsync.albums WHERE device_id = $1
           UNION ALL SELECT 'photos', COUNT(*)::text FROM macsync.photos WHERE device_id = $1
           UNION ALL SELECT 'calendars', COUNT(*)::text FROM macsync.calendars WHERE device_id = $1
           UNION ALL SELECT 'events', COUNT(*)::text FROM macsync.events WHERE device_id = $1
           UNION ALL SELECT 'pending_sends', COUNT(*)::text FROM macsync.send_queue WHERE device_id = $1 AND status = 'queued'`,
          [deviceId!],
        );

    return c.json({
      syncStats: stats,
      entityCounts: Object.fromEntries(
        systemStats.map((s) => [s.table_name, parseInt(s.count, 10)]),
      ),
    });
  })
  .get('/history', async (c) => {
    const deviceId = c.req.query('device_id');
    const limit = Math.min(parseInt(c.req.query('limit') ?? '20', 10), 100);
    const db = getDb();

    if (isAggregate(deviceId)) {
      const history = await pgAll(
        db,
        `SELECT * FROM macsync.sync_history ORDER BY started_at DESC LIMIT $1`,
        [limit],
      );
      return c.json({ history });
    }

    const history = await SyncHistoryRepo.getRecentByDevice(deviceId!, limit);
    return c.json({ history });
  })
  .get('/send-queue/failed', async (c) => {
    const db = getDb();
    const rows = await pgAll<{
      id: string;
      device_id: string;
      to_handle: string;
      body: string;
      status: string;
      failure_reason: string | null;
      created_at: string;
      updated_at: string;
    }>(
      db,
      `SELECT id, device_id, to_handle, body, status, failure_reason, created_at::text, updated_at::text
       FROM macsync.send_queue
       WHERE status IN ('failed', 'error')
       ORDER BY created_at DESC`,
    );

    const items: FailedSendItem[] = rows.map((r) => ({
      id: r.id,
      deviceId: r.device_id,
      toHandle: r.to_handle,
      body: r.body,
      status: r.status,
      failureReason: r.failure_reason,
      createdAt: r.created_at,
      updatedAt: r.updated_at,
    }));

    return c.json(items);
  })
  .post('/send-queue/:id/retry', async (c) => {
    const id = c.req.param('id');
    const db = getDb();

    const existing = await pgGet<{ status: string }>(
      db,
      `SELECT status FROM macsync.send_queue WHERE id = $1`,
      [id],
    );

    if (!existing) {
      return c.json({ error: 'not found' }, 404);
    }

    if (existing.status !== 'failed' && existing.status !== 'error') {
      return c.json({ error: `cannot retry: status is ${existing.status}` }, 409);
    }

    const updated = await pgGet<{
      id: string;
      device_id: string;
      to_handle: string;
      body: string;
      status: string;
      failure_reason: string | null;
      created_at: string;
      updated_at: string;
    }>(
      db,
      `UPDATE macsync.send_queue
       SET status = 'queued', failure_reason = NULL, updated_at = now()
       WHERE id = $1
       RETURNING id, device_id, to_handle, body, status, failure_reason, created_at::text, updated_at::text`,
      [id],
    );

    const item: FailedSendItem = {
      id: updated!.id,
      deviceId: updated!.device_id,
      toHandle: updated!.to_handle,
      body: updated!.body,
      status: updated!.status,
      failureReason: updated!.failure_reason,
      createdAt: updated!.created_at,
      updatedAt: updated!.updated_at,
    };

    return c.json(item);
  });
