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 crypto from 'crypto';
import dns from 'dns/promises';

export async function listWebhooks(restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM webhooks WHERE restaurant_id = $1 ORDER BY created_at DESC */
  const { rows } = await db.execute(sql`SELECT * FROM webhooks WHERE restaurant_id = ${restaurantId} ORDER BY 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)} = ${val as string | null}`);
  }
  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;
}

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;
  }
}

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) {
    /* raw: INSERT INTO webhook_delivery_logs (...) VALUES (...) */
    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, ${'Blocked: endpoint URL failed safety check (private/localhost/invalid)'})
    `).catch(() => {});
    /* raw: UPDATE webhooks SET total_deliveries = total_deliveries + 1, success_rate = (...), updated_at = NOW() WHERE id = $1 */
    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(() => {});
    return;
  }

  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 = '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';
  }

  const durationMs = Date.now() - startMs;

  try {
    /* raw: INSERT INTO webhook_delivery_logs (...) VALUES (...) */
    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})
    `);
    /* raw: UPDATE webhooks SET total_deliveries = total_deliveries + 1, success_rate = (...), updated_at = NOW() WHERE id = $1 */
    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 {
  }
}

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 => {
          console.warn(`[webhook] dispatch failed webhook=${wh.id} event=${event}:`, err instanceof Error ? err.message : err);
        });
      }
    } catch (err) {
      console.warn(`[webhook] dispatchWebhook setup error event=${event}:`, err instanceof Error ? err.message : err);
    }
  });
}
