import { tool } from '@openai/agents';
import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { searchKnowledgeBase, formatSearchResults } from '@server/services/kb-embeddings.service';
import { transferCall } from '@server/services/telephone/twilio-phone.service';
import { sendOrderConfirmationEmail, sendBookingConfirmationEmail } from '@server/services/email.service';

export interface ToolSessionContext {
  session_id: string;
  restaurant_id: string;
  agent_id: string | null;
  capabilities: Record<string, boolean> | null;
}

export function makeRestaurantTools(ctx: ToolSessionContext) {
  const { session_id, restaurant_id, agent_id, capabilities } = ctx;
  const caps = capabilities || {};

  let resolvedBranchId: string | null | undefined = undefined;

  const resolveBranchId = async (): Promise<string | null> => {
    if (resolvedBranchId !== undefined) return resolvedBranchId;
    try {
      /* raw: SELECT pn.branch_id FROM sip_call_sessions s JOIN phone_numbers pn ON pn.id = s.phone_number_id WHERE s.id = $1 AND pn.branch_id IS NOT NULL LIMIT 1 */
      const row = await db.execute(sql`SELECT pn.branch_id
         FROM sip_call_sessions s
         JOIN phone_numbers pn ON pn.id = s.phone_number_id
         WHERE s.id = ${session_id} AND pn.branch_id IS NOT NULL
         LIMIT 1`);
      resolvedBranchId = (row.rows[0]?.branch_id as string) || null;
    } catch (err) {
      console.error(`[Engine/Twilio] Failed to resolve branch_id for session ${session_id}:`, err);
      resolvedBranchId = null;
    }
    return resolvedBranchId;
  };

  const kbSearchTool = tool({
    name: 'search_knowledge_base',
    description:
      'Search the restaurant knowledge base for information about opening hours, policies, menu details, FAQs, and other restaurant-specific information. Use this whenever callers ask about specific restaurant details.',
    parameters: {
      type: 'object' as const,
      properties: {
        query: {
          type: 'string',
          description: 'The search query to find relevant knowledge base entries.',
        },
      },
      required: ['query'] as string[],
      additionalProperties: false as const,
    },
    execute: async (args: unknown) => {
      const { query } = args as { query: string };
      try {
        const results = await searchKnowledgeBase(restaurant_id, query);
        return formatSearchResults(results);
      } catch (err) {
        console.error('[Engine/Twilio] KB search error:', err);
        return 'Knowledge base search is temporarily unavailable.';
      }
    },
  });

  const placeOrderTool = tool({
    name: 'place_order',
    description:
      'Save a food order to the system after confirming all details with the caller. Call this tool once you have the full order: items, customer name, phone, and delivery type. The order will be sent to the kitchen staff.',
    parameters: {
      type: 'object' as const,
      properties: {
        items: {
          type: 'array',
          description: 'List of items being ordered.',
          items: {
            type: 'object',
            properties: {
              name: { type: 'string', description: 'Name of the menu item.' },
              quantity: { type: 'number', description: 'Quantity ordered.' },
            },
            required: ['name', 'quantity'],
          },
        },
        customer_name: { type: 'string', description: 'Full name of the customer.' },
        customer_phone: { type: 'string', description: 'Phone number of the customer.' },
        customer_email: { type: 'string', description: 'Email address of the customer for order confirmation (optional).' },
        delivery_type: {
          type: 'string',
          enum: ['dine-in', 'takeaway', 'delivery'],
          description: 'How the customer wants to receive their order.',
        },
        special_instructions: {
          type: 'string',
          description: 'Any special instructions or dietary requirements.',
        },
      },
      required: ['items', 'customer_name', 'delivery_type'] as string[],
      additionalProperties: false as const,
    },
    execute: async (args: unknown) => {
      const { items, customer_name, customer_phone, customer_email, delivery_type, special_instructions } =
        args as {
          items: { name: string; quantity: number }[];
          customer_name: string;
          customer_phone?: string;
          customer_email?: string;
          delivery_type: string;
          special_instructions?: string;
        };
      try {
        const branchId = await resolveBranchId();

        const menuNames = items.map(i => i.name.toLowerCase());
        /* raw: SELECT name, price FROM menu_items WHERE restaurant_id = $1 AND LOWER(name) IN (...) */
        const menuRows = await db.execute(sql`SELECT name, price FROM menu_items
           WHERE restaurant_id = ${restaurant_id}
             AND LOWER(name) IN (${sql.join(menuNames.map(n => sql`${n}`), sql`, `)})`);
        const priceMap: Record<string, number> = {};
        for (const row of menuRows.rows as { name: string; price: string }[]) {
          priceMap[row.name.toLowerCase()] = parseFloat(row.price);
        }

        const enrichedItems = items.map(i => {
          const price = priceMap[i.name.toLowerCase()] ?? 0;
          return { name: i.name, quantity: i.quantity, price };
        });

        const subtotal = enrichedItems.reduce((sum, i) => sum + i.price * i.quantity, 0);
        const taxRate = 0.08;
        const tax = parseFloat((subtotal * taxRate).toFixed(2));
        const total = parseFloat((subtotal + tax).toFixed(2));

        const result = await db.transaction(async (tx) => {
          /* raw: SELECT pg_advisory_xact_lock(hashtext($1)::bigint) */
          await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtext(${restaurant_id})::bigint)`);
          /* raw: SELECT COALESCE(MAX(order_number), 0) + 1 AS next_num FROM orders WHERE restaurant_id = $1 */
          const { rows: [numRow] } = await tx.execute(sql`SELECT COALESCE(MAX(order_number), 0) + 1 AS next_num FROM orders WHERE restaurant_id = ${restaurant_id}`);
          const nextOrderNumber = parseInt((numRow as Record<string, unknown>)?.next_num as string ?? '1', 10);
          /* raw: INSERT INTO orders (...) VALUES (...) RETURNING id, order_number */
          const insertResult = await tx.execute(sql`INSERT INTO orders
              (restaurant_id, branch_id, customer_name, customer_phone, customer_email, items, delivery_type,
               special_instructions, channel, status, conversation_id, agent_id,
               subtotal, tax, total, order_number)
             VALUES (${restaurant_id}, ${branchId}, ${customer_name}, ${customer_phone || null}, ${customer_email || null},
               ${JSON.stringify(enrichedItems)}::jsonb, ${delivery_type}, ${special_instructions || null},
               'voice', 'pending', ${session_id}, ${agent_id || null},
               ${subtotal}, ${tax}, ${total}, ${nextOrderNumber})
             RETURNING id, order_number`);
          return insertResult;
        });

        const orderId = result.rows[0]?.id as string;
        const orderNumber = result.rows[0]?.order_number as number | null;
        const itemList = enrichedItems.map(i => `${i.quantity}x ${i.name}`).join(', ');
        const orderRef = orderNumber ? `#${orderNumber}` : orderId.slice(0, 8).toUpperCase();
        console.log(`[Engine/Twilio] Order placed: ${orderId} (${itemList}) total=$${total}`);

        if (customer_email) {
          try {
            /* raw: SELECT name FROM restaurants WHERE id = $1 LIMIT 1 */
            const restRow = await db.execute(sql`SELECT name FROM restaurants WHERE id = ${restaurant_id} LIMIT 1`);
            const restName = (restRow.rows[0]?.name as string | undefined) ?? 'The Restaurant';
            await sendOrderConfirmationEmail({
              to: customer_email,
              customerName: customer_name,
              orderNumber: orderRef,
              items: enrichedItems,
              total,
              deliveryType: delivery_type,
              restaurantName: restName,
            }, branchId ?? undefined, restaurant_id);
          } catch { /* Email failure must never break the order */ }
        }

        return `Order confirmed! Your order (${itemList}) has been placed. Order ${orderRef}. Our team will prepare it shortly.`;
      } catch (err) {
        console.error('[Engine/Twilio] place_order error:', err);
        return 'I was unable to save the order right now. Please ask the caller to call back or visit in person.';
      }
    },
  });

  const bookTableTool = tool({
    name: 'book_table',
    description:
      'Save a table reservation after confirming all details with the caller. Call this tool once you have the guest name, date, time, and party size.',
    parameters: {
      type: 'object' as const,
      properties: {
        guest_name: { type: 'string', description: 'Full name of the guest making the booking.' },
        guest_phone: { type: 'string', description: 'Phone number of the guest.' },
        guest_email: { type: 'string', description: 'Email address of the guest for booking confirmation (optional).' },
        party_size: { type: 'number', description: 'Number of people in the party.' },
        booking_date: {
          type: 'string',
          description: 'Date of the reservation in YYYY-MM-DD format.',
        },
        booking_time: {
          type: 'string',
          description: 'Time of the reservation in HH:MM (24-hour) format.',
        },
        special_requests: {
          type: 'string',
          description: 'Any special requests such as occasion, dietary needs, or seating preference.',
        },
      },
      required: ['guest_name', 'party_size', 'booking_date', 'booking_time'] as string[],
      additionalProperties: false as const,
    },
    execute: async (args: unknown) => {
      const { guest_name, guest_phone, guest_email, party_size, booking_date, booking_time, special_requests } =
        args as {
          guest_name: string;
          guest_phone?: string;
          guest_email?: string;
          party_size: number;
          booking_date: string;
          booking_time: string;
          special_requests?: string;
        };
      try {
        const branchId = await resolveBranchId();
        /* raw: INSERT INTO bookings (...) VALUES (...) RETURNING id */
        const result = await db.execute(sql`INSERT INTO bookings
            (restaurant_id, branch_id, guest_name, guest_phone, guest_email, party_size, booking_date, booking_time,
             special_requests, channel, status, conversation_id, agent_id)
           VALUES (${restaurant_id}, ${branchId}, ${guest_name}, ${guest_phone || null}, ${guest_email || null},
             ${party_size}, ${booking_date}, ${booking_time}, ${special_requests || null},
             'voice', 'pending', ${session_id}, ${agent_id || null})
           RETURNING id`);
        const bookingId = result.rows[0]?.id as string;
        const bookingRef = bookingId.slice(0, 8).toUpperCase();
        console.log(`[Engine/Twilio] Booking created: ${bookingId} for ${guest_name}`);

        if (guest_email) {
          try {
            /* raw: SELECT name FROM restaurants WHERE id = $1 LIMIT 1 */
            const restRow = await db.execute(sql`SELECT name FROM restaurants WHERE id = ${restaurant_id} LIMIT 1`);
            const restName = (restRow.rows[0]?.name as string | undefined) ?? 'The Restaurant';
            await sendBookingConfirmationEmail({
              to: guest_email,
              guestName: guest_name,
              bookingRef,
              date: booking_date,
              time: booking_time,
              partySize: party_size,
              restaurantName: restName,
            }, branchId ?? undefined, restaurant_id);
          } catch { /* Email failure must never break the booking */ }
        }

        return `Booking confirmed! A table for ${party_size} has been reserved for ${guest_name} on ${booking_date} at ${booking_time}. Booking reference: ${bookingRef}.`;
      } catch (err) {
        console.error('[Engine/Twilio] book_table error:', err);
        return 'I was unable to save the reservation right now. Please ask the caller to call back or visit in person.';
      }
    },
  });

  const escalateCallTool = tool({
    name: 'escalate_call',
    description:
      'Transfer the call to a human staff member when the caller requests it or when you cannot handle their query. Call this immediately when a caller asks to speak to a person.',
    parameters: {
      type: 'object' as const,
      properties: {
        reason: {
          type: 'string',
          description: 'Brief reason for escalation (e.g. "caller requested human", "complaint handling").',
        },
      },
      required: ['reason'] as string[],
      additionalProperties: false as const,
    },
    execute: async (args: unknown) => {
      const { reason } = args as { reason: string };
      try {
        /* raw: SELECT pn.fallback_number FROM sip_call_sessions s JOIN phone_numbers pn ON pn.id = s.phone_number_id WHERE s.id = $1 AND pn.fallback_number IS NOT NULL LIMIT 1 */
        const fallbackRow = await db.execute(sql`SELECT pn.fallback_number
           FROM sip_call_sessions s
           JOIN phone_numbers pn ON pn.id = s.phone_number_id
           WHERE s.id = ${session_id} AND pn.fallback_number IS NOT NULL
           LIMIT 1`);
        const fallbackNumber = fallbackRow.rows[0]?.fallback_number as string | undefined;

        if (fallbackNumber) {
          console.log(`[Engine/Twilio] Escalating call ${session_id} to ${fallbackNumber} (reason: ${reason})`);
          try {
            await transferCall(session_id, restaurant_id, fallbackNumber);
            return 'Please hold for a moment while I connect you to a staff member.';
          } catch (transferErr) {
            console.error('[Engine/Twilio] Transfer failed:', transferErr);
            return 'I apologise — I was unable to connect the transfer. Please let the caller know a staff member will call them back shortly.';
          }
        } else {
          console.log(`[Engine/Twilio] Escalation requested (${reason}) but no fallback number configured`);
          return 'No transfer number is configured. Please tell the caller: "I\'ll have one of our staff members call you back shortly. Thank you for your patience."';
        }
      } catch (err) {
        console.error('[Engine/Twilio] escalate_call error:', err);
        return 'Please tell the caller a staff member will call them back shortly.';
      }
    },
  });

  const tools = [kbSearchTool];

  if (caps.order_taking) tools.push(placeOrderTool);
  if (caps.reservation_booking) tools.push(bookTableTool);
  if (caps.escalation) tools.push(escalateCallTool);

  return tools;
}
