import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import crypto from 'crypto';
import dns from 'dns/promises';

import { childLogger } from '@server/logger';
import { notifyWebhookExhausted } from '@server/services/email/notify';
const log = childLogger('svc.webhooks');

export async function listWebhooks(restaurantId: string) {
  await initDatabase();
  /*
   * Join aggregate delivery-attempt counts (pending + exhausted only) so the
   * frontend can render per-card health indicators without an extra round-trip.
   */
  const { rows } = await db.execute(sql`
    SELECT
      w.*,
      COALESCE(agg.pending_attempts,  0)::int AS pending_attempts,
      COALESCE(agg.exhausted_attempts, 0)::int AS exhausted_attempts
    FROM webhooks w
    LEFT JOIN (
      SELECT
        webhook_id,
        COUNT(*) FILTER (WHERE status = 'pending')  AS pending_attempts,
        COUNT(*) FILTER (WHERE status = 'exhausted') AS exhausted_attempts
      FROM webhook_delivery_attempts
      WHERE restaurant_id = ${restaurantId}
        AND status IN ('pending', 'exhausted')
      GROUP BY webhook_id
    ) agg ON agg.webhook_id = w.id
    WHERE w.restaurant_id = ${restaurantId}
    ORDER BY w.created_at DESC
  `);
  return rows;
}

export async function getWebhook(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM webhooks WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM webhooks WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Webhook');
  return rows[0];
}

export async function createWebhook(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const secret = data.secret ?? crypto.randomBytes(24).toString('hex');
  /* raw: INSERT INTO webhooks (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO webhooks (restaurant_id, endpoint_url, events, secret, status)
    VALUES (${restaurantId}, ${data.endpoint_url as string}, ${JSON.stringify(data.events ?? [])}::jsonb, ${secret as string}, ${(data.status as string) ?? 'active'})
    RETURNING *
  `);
  return rows[0];
}

export async function updateWebhook(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['endpoint_url','events','secret','status'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = k === 'events' ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE webhooks SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE webhooks SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Webhook');
  return rows[0];
}

export async function deleteWebhook(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM webhooks WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM webhooks WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Webhook');
}

export async function listDeliveryLogs(restaurantId: string, webhookId?: string) {
  await initDatabase();
  if (webhookId) {
    /* raw: SELECT wdl.*, w.endpoint_url FROM webhook_delivery_logs wdl JOIN webhooks w ... WHERE wdl.restaurant_id = $1 AND wdl.webhook_id = $2 ORDER BY wdl.triggered_at DESC LIMIT 100 */
    const { rows } = await db.execute(sql`
      SELECT wdl.*, w.endpoint_url FROM webhook_delivery_logs wdl
      JOIN webhooks w ON w.id = wdl.webhook_id
      WHERE wdl.restaurant_id = ${restaurantId} AND wdl.webhook_id = ${webhookId}
      ORDER BY wdl.triggered_at DESC LIMIT 100
    `);
    return rows;
  }
  /* raw: SELECT wdl.*, w.endpoint_url FROM webhook_delivery_logs wdl JOIN webhooks w ... WHERE wdl.restaurant_id = $1 ORDER BY wdl.triggered_at DESC LIMIT 100 */
  const { rows } = await db.execute(sql`
    SELECT wdl.*, w.endpoint_url FROM webhook_delivery_logs wdl
    JOIN webhooks w ON w.id = wdl.webhook_id
    WHERE wdl.restaurant_id = ${restaurantId}
    ORDER BY wdl.triggered_at DESC LIMIT 100
  `);
  return rows;
}

export async function listDeliveryAttempts(restaurantId: string, webhookId?: string) {
  await initDatabase();
  if (webhookId) {
    const { rows } = await db.execute(sql`
      SELECT wda.* FROM webhook_delivery_attempts wda
      WHERE wda.restaurant_id = ${restaurantId} AND wda.webhook_id = ${webhookId}
      ORDER BY wda.created_at DESC LIMIT 200
    `);
    return rows;
  }
  const { rows } = await db.execute(sql`
    SELECT wda.* FROM webhook_delivery_attempts wda
    WHERE wda.restaurant_id = ${restaurantId}
    ORDER BY wda.created_at DESC LIMIT 200
  `);
  return rows;
}

export async function resendDeliveryAttempt(id: string, restaurantId: string) {
  await initDatabase();
  /*
   * Reset the attempt so the retry worker picks it up immediately.
   * Crucially, reset attempt_count to 0 so exhausted rows (attempt_count >= max_attempts)
   * are also eligible for the worker's attempt_count < max_attempts guard.
   */
  const { rows } = await db.execute(sql`
    UPDATE webhook_delivery_attempts
    SET status = 'pending',
        attempt_count = 0,
        next_retry_at = NOW(),
        updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId}
    RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('DeliveryAttempt');
  return rows[0];
}

function isPrivateIp(ip: string): boolean {
  if (ip === '::1') return true;
  if (ip.startsWith('::ffff:')) return isPrivateIp(ip.slice(7));
  if (ip.includes(':')) return false;
  const parts = ip.split('.').map(Number);
  if (parts.length !== 4 || parts.some(p => isNaN(p) || p < 0 || p > 255)) return true;
  const [a, b] = parts;
  return (
    a === 127 ||
    a === 10 ||
    a === 0 ||
    (a === 169 && b === 254) ||
    (a === 172 && b >= 16 && b <= 31) ||
    (a === 192 && b === 168) ||
    (a === 100 && b >= 64 && b <= 127)
  );
}

async function isSafeWebhookUrl(rawUrl: string): Promise<boolean> {
  try {
    const parsed = new URL(rawUrl);
    if (!['http:', 'https:'].includes(parsed.protocol)) return false;
    const hostname = parsed.hostname;
    if (!hostname || hostname === 'localhost') return false;
    const { address } = await dns.lookup(hostname, { family: 4 });
    return !isPrivateIp(address);
  } catch {
    return false;
  }
}

/** Compute exponential back-off delay in minutes for a given attempt count. */
function backoffMinutes(attemptCount: number): number {
  // Attempt 1→2min, 2→4min, 3→8min, 4→16min (capped at 60)
  return Math.min(Math.pow(2, attemptCount), 60);
}

/**
 * Core delivery function. Returns { status, responseCode, errorMessage }.
 * Does NOT write to the DB — callers handle persistence.
 */
async function _makeDelivery(
  endpointUrl: string,
  event: string,
  payload: Record<string, unknown>,
  secret: string
): Promise<{ status: 'success' | 'failed'; responseCode: number | null; responseBody: string | null; errorMessage: string | null; durationMs: number }> {
  const body = JSON.stringify({ event, payload, timestamp: new Date().toISOString() });
  const signature = 'sha256=' + crypto.createHmac('sha256', secret).update(body).digest('hex');
  const startMs = Date.now();
  let status: 'success' | 'failed' = 'failed';
  let responseCode: number | null = null;
  let responseBody: string | null = null;
  let errorMessage: string | null = null;

  try {
    const res = await fetch(endpointUrl, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'X-RestroAgent-Signature': signature,
        'X-RestroAgent-Event': event,
      },
      body,
      signal: AbortSignal.timeout(10000),
    });
    responseCode = res.status;
    const rawBody = await res.text().catch(() => '');
    responseBody = rawBody.slice(0, 500);
    status = res.ok ? 'success' : 'failed';
    if (!res.ok) errorMessage = `HTTP ${res.status}`;
  } catch (err) {
    errorMessage = err instanceof Error ? err.message.slice(0, 200) : 'Unknown error';
  }

  return { status, responseCode, responseBody, errorMessage, durationMs: Date.now() - startMs };
}

async function _dispatchToWebhook(
  webhookId: string,
  restaurantId: string,
  event: string,
  payload: Record<string, unknown>,
  endpointUrl: string,
  secret: string
) {
  const safe = await isSafeWebhookUrl(endpointUrl).catch(() => false);
  if (!safe) {
    const safeCheckError = 'Blocked: endpoint URL failed safety check (private/localhost/invalid)';
    await db.execute(sql`
      INSERT INTO webhook_delivery_logs
        (webhook_id, restaurant_id, event, status, response_code, duration_ms, error_message)
      VALUES (${webhookId}, ${restaurantId}, ${event}, 'failed', ${null}, 0, ${safeCheckError})
    `).catch(() => {});
    await db.execute(sql`
      UPDATE webhooks SET
        total_deliveries = total_deliveries + 1,
        success_rate = (
          SELECT ROUND(
            COUNT(*) FILTER (WHERE status = 'success') * 100.0 / NULLIF(COUNT(*), 0)
          , 2) FROM webhook_delivery_logs WHERE webhook_id = ${webhookId}
        ),
        updated_at = NOW()
      WHERE id = ${webhookId}
    `).catch(() => {});
    /* Record as exhausted (retrying a blocked URL is pointless; operator must fix endpoint first then use Resend) */
    await db.execute(sql`
      INSERT INTO webhook_delivery_attempts
        (webhook_id, restaurant_id, event, payload, endpoint_url, status, attempt_count, max_attempts, last_error_message, last_attempted_at)
      VALUES (
        ${webhookId}, ${restaurantId}, ${event}, ${JSON.stringify(payload)}::jsonb,
        ${endpointUrl}, 'exhausted', 1, 5, ${safeCheckError}, NOW()
      )
    `).catch(() => {});
    notifyWebhookExhausted({ restaurantId, eventType: event, endpointUrl }).catch(() => {});
    return;
  }

  const { status, responseCode, responseBody, errorMessage, durationMs } = await _makeDelivery(endpointUrl, event, payload, secret);

  try {
    await db.execute(sql`
      INSERT INTO webhook_delivery_logs
        (webhook_id, restaurant_id, event, status, response_code, response_body, duration_ms, error_message)
      VALUES (${webhookId}, ${restaurantId}, ${event}, ${status}, ${responseCode}, ${responseBody}, ${durationMs}, ${errorMessage})
    `);
    await db.execute(sql`
      UPDATE webhooks SET
        total_deliveries = total_deliveries + 1,
        success_rate = (
          SELECT ROUND(
            COUNT(*) FILTER (WHERE status = 'success') * 100.0 / NULLIF(COUNT(*), 0)
          , 2) FROM webhook_delivery_logs WHERE webhook_id = ${webhookId}
        ),
        updated_at = NOW()
      WHERE id = ${webhookId}
    `);
  } catch {
  }

  /* If delivery failed, always create a new retry attempt record for this specific delivery */
  if (status === 'failed') {
    try {
      await db.execute(sql`
        INSERT INTO webhook_delivery_attempts
          (webhook_id, restaurant_id, event, payload, endpoint_url, status, attempt_count, last_response_code, last_error_message, next_retry_at, last_attempted_at)
        VALUES (
          ${webhookId}, ${restaurantId}, ${event}, ${JSON.stringify(payload)}::jsonb,
          ${endpointUrl}, 'pending', 1, ${responseCode}, ${errorMessage},
          NOW() + (${backoffMinutes(1)} * INTERVAL '1 minute'),
          NOW()
        )
      `);
      log.info({ webhookId, event }, 'webhook delivery failed — queued for retry');
    } catch (err) {
      log.warn({ err, webhookId, event }, 'failed to queue webhook retry attempt');
    }
  }
}

export function dispatchWebhook(
  restaurantId: string,
  event: string,
  payload: Record<string, unknown>
): void {
  Promise.resolve().then(async () => {
    try {
      await initDatabase();
      /* raw: SELECT id, endpoint_url, secret FROM webhooks WHERE restaurant_id = $1 AND status = 'active' AND events @> $2::jsonb */
      const { rows: webhooks } = await db.execute(sql`
        SELECT id, endpoint_url, secret FROM webhooks
        WHERE restaurant_id = ${restaurantId} AND status = 'active' AND events @> ${JSON.stringify([event])}::jsonb
      `);
      for (const wh of webhooks as { id: string; endpoint_url: string; secret: string }[]) {
        _dispatchToWebhook(wh.id, restaurantId, event, payload, wh.endpoint_url, wh.secret ?? '').catch(err => {
          log.warn({ err, webhookId: wh.id, event }, 'webhook dispatch failed');
        });
      }
    } catch (err) {
      log.warn({ err, event }, 'webhook dispatchWebhook setup error');
    }
  });
}

/** Background retry worker — runs every 60 seconds. */
let retryWorkerStarted = false;
export function startWebhookRetryWorker(): void {
  if (retryWorkerStarted) return;
  retryWorkerStarted = true;
  log.info('webhook retry worker started');
  const run = async () => {
    try {
      /*
       * Atomically claim up to 50 pending rows by flipping them to 'processing'
       * using FOR UPDATE SKIP LOCKED, which prevents concurrent worker runs (or
       * multiple app instances) from picking up the same row and sending duplicate
       * webhook deliveries.
       */
      const { rows: attempts } = await db.execute(sql`
        UPDATE webhook_delivery_attempts wda
        SET status = 'processing', updated_at = NOW()
        FROM webhooks w
        WHERE wda.webhook_id = w.id
          AND wda.status = 'pending'
          AND wda.next_retry_at <= NOW()
          AND wda.attempt_count < wda.max_attempts
          AND w.status = 'active'
          AND wda.id IN (
            SELECT a.id
            FROM webhook_delivery_attempts a
            JOIN webhooks wh ON wh.id = a.webhook_id
            WHERE a.status = 'pending'
              AND a.next_retry_at <= NOW()
              AND a.attempt_count < a.max_attempts
              AND wh.status = 'active'
            ORDER BY a.next_retry_at ASC
            LIMIT 50
            FOR UPDATE OF a SKIP LOCKED
          )
        RETURNING wda.*, w.secret
      `);

      for (const attempt of attempts as {
        id: string; webhook_id: string; restaurant_id: string; event: string;
        payload: Record<string, unknown>; endpoint_url: string; secret: string;
        attempt_count: number; max_attempts: number;
      }[]) {
        try {
          const safe = await isSafeWebhookUrl(attempt.endpoint_url).catch(() => false);
          const newCount = attempt.attempt_count + 1;

          if (!safe) {
            await db.execute(sql`
              UPDATE webhook_delivery_attempts
              SET status = 'exhausted',
                  attempt_count = ${newCount},
                  last_error_message = ${'Blocked: endpoint URL failed safety check'},
                  last_attempted_at = NOW(),
                  updated_at = NOW()
              WHERE id = ${attempt.id}
            `).catch(() => {});
            notifyWebhookExhausted({ restaurantId: attempt.restaurant_id, eventType: attempt.event, endpointUrl: attempt.endpoint_url }).catch(() => {});
            continue;
          }

          const payload = typeof attempt.payload === 'string'
            ? JSON.parse(attempt.payload)
            : attempt.payload;

          const { status, responseCode, responseBody, errorMessage, durationMs } = await _makeDelivery(
            attempt.endpoint_url,
            attempt.event,
            payload,
            attempt.secret ?? ''
          );

          /* Log the retry attempt in delivery_logs and update aggregate stats */
          await db.execute(sql`
            INSERT INTO webhook_delivery_logs
              (webhook_id, restaurant_id, event, status, response_code, response_body, duration_ms, error_message)
            VALUES (${attempt.webhook_id}, ${attempt.restaurant_id}, ${attempt.event}, ${status}, ${responseCode}, ${responseBody}, ${durationMs}, ${errorMessage})
          `).catch(() => {});
          await db.execute(sql`
            UPDATE webhooks SET
              total_deliveries = total_deliveries + 1,
              success_rate = (
                SELECT ROUND(
                  COUNT(*) FILTER (WHERE status = 'success') * 100.0 / NULLIF(COUNT(*), 0)
                , 2) FROM webhook_delivery_logs WHERE webhook_id = ${attempt.webhook_id}
              ),
              updated_at = NOW()
            WHERE id = ${attempt.webhook_id}
          `).catch(() => {});

          if (status === 'success') {
            await db.execute(sql`
              UPDATE webhook_delivery_attempts
              SET status = 'succeeded',
                  attempt_count = ${newCount},
                  last_response_code = ${responseCode},
                  last_error_message = ${null},
                  last_attempted_at = NOW(),
                  updated_at = NOW()
              WHERE id = ${attempt.id}
            `);
            log.info({ attemptId: attempt.id, webhookId: attempt.webhook_id, attempt: newCount }, 'webhook retry succeeded');
          } else {
            const isExhausted = newCount >= attempt.max_attempts;
            await db.execute(sql`
              UPDATE webhook_delivery_attempts
              SET status = ${isExhausted ? 'exhausted' : 'pending'},
                  attempt_count = ${newCount},
                  last_response_code = ${responseCode},
                  last_error_message = ${errorMessage},
                  last_attempted_at = NOW(),
                  next_retry_at = CASE WHEN ${!isExhausted} THEN NOW() + (${backoffMinutes(newCount)} * INTERVAL '1 minute') ELSE next_retry_at END,
                  updated_at = NOW()
              WHERE id = ${attempt.id}
            `);
            if (isExhausted) {
              notifyWebhookExhausted({ restaurantId: attempt.restaurant_id, eventType: attempt.event, endpointUrl: attempt.endpoint_url }).catch(() => {});
            }
            log.warn({ attemptId: attempt.id, webhookId: attempt.webhook_id, attempt: newCount, isExhausted }, 'webhook retry failed');
          }
        } catch (err) {
          /* Unexpected error mid-processing — reset to pending (or exhausted if max reached) so the row is not stuck */
          const newCount = attempt.attempt_count + 1;
          const isExhausted = newCount >= attempt.max_attempts;
          await db.execute(sql`
            UPDATE webhook_delivery_attempts
            SET status = ${isExhausted ? 'exhausted' : 'pending'},
                attempt_count = ${newCount},
                last_error_message = ${err instanceof Error ? err.message.slice(0, 200) : 'Internal processing error'},
                last_attempted_at = NOW(),
                next_retry_at = CASE WHEN ${!isExhausted} THEN NOW() + (${backoffMinutes(newCount)} * INTERVAL '1 minute') ELSE next_retry_at END,
                updated_at = NOW()
            WHERE id = ${attempt.id}
          `).catch(() => {});
          if (isExhausted) {
            notifyWebhookExhausted({ restaurantId: attempt.restaurant_id, eventType: attempt.event, endpointUrl: attempt.endpoint_url }).catch(() => {});
          }
          log.warn({ err, attemptId: attempt.id, isExhausted }, 'error processing webhook retry attempt — status reset');
        }
      }
    } catch (err) {
      log.warn({ err }, 'webhook retry worker run error');
    }
  };

  /*
   * Stale-processing reaper: on startup reclaim any rows stuck in 'processing'
   * for more than 15 minutes (e.g., crash mid-run or killed process).
   */
  const reclaimStale = async () => {
    try {
      const { rowCount } = await db.execute(sql`
        UPDATE webhook_delivery_attempts
        SET status = 'pending',
            next_retry_at = NOW(),
            last_error_message = COALESCE(last_error_message, '') || ' [reclaimed from stale processing state]',
            updated_at = NOW()
        WHERE status = 'processing'
          AND updated_at < NOW() - INTERVAL '15 minutes'
      `);
      if ((rowCount ?? 0) > 0) {
        log.info({ reclaimed: rowCount }, 'reclaimed stale processing webhook attempts');
      }
    } catch (err) {
      log.warn({ err }, 'webhook retry: stale-processing reaper error');
    }
  };

  /*
   * Terminal-state cleanup: delete 'succeeded' and 'exhausted' rows older than the
   * configured retention window (default 30 days). The webhook_delivery_logs table
   * already captures the full delivery history, so pruning attempts does not lose
   * audit data. Runs on startup and every 6 hours thereafter.
   */
  const _rawRetention = Number(process.env.WEBHOOK_ATTEMPT_RETENTION_DAYS);
  const RETENTION_DAYS = Number.isFinite(_rawRetention) && _rawRetention >= 1
    ? Math.floor(_rawRetention)
    : 30;
  const cleanupTerminalAttempts = async () => {
    try {
      const { rowCount } = await db.execute(sql`
        DELETE FROM webhook_delivery_attempts
        WHERE status IN ('succeeded', 'exhausted')
          AND updated_at < NOW() - (${RETENTION_DAYS} * INTERVAL '1 day')
      `);
      if ((rowCount ?? 0) > 0) {
        log.info({ deleted: rowCount, retentionDays: RETENTION_DAYS }, 'pruned old terminal webhook delivery attempts');
      }
    } catch (err) {
      log.warn({ err }, 'webhook cleanup: terminal-attempt pruning error');
    }
  };

  /* Run immediately then on a 60-second interval */
  reclaimStale().catch(() => {});
  cleanupTerminalAttempts().catch(() => {});
  run().catch(() => {});
  setInterval(() => { run().catch(() => {}); }, 60_000).unref();
  /* Reaper runs every 15 minutes to catch any future stragglers */
  setInterval(() => { reclaimStale().catch(() => {}); }, 15 * 60_000).unref();
  /* Cleanup runs every 6 hours */
  setInterval(() => { cleanupTerminalAttempts().catch(() => {}); }, 6 * 60 * 60_000).unref();
}