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 { dispatchWebhook } from './webhooks.service';
import * as loyalty from './loyalty.service';
import { applyCoupon, enforceStackingPolicy, recordRedemption, type ApplyCouponItem } from './coupons.service';
import { composeOrderPricing, computeGiftCardOffset, TAX_RATE } from './pricing.service';
import { recomputeCustomerAggregates } from './customers.service';
import { decrementStockForOrder, type StockDecrementAlert } from './menu.service';
import { notifyLowStock } from './email/notify';

import { childLogger } from '@server/logger';
const log = childLogger('svc.orders');

const ORDER_SORT_COLS: Record<string, string> = {
  placed_at: 'o.created_at',
  created_at: 'o.created_at',
  total: 'o.total',
  status: 'o.status',
  customer_name: 'o.customer_name',
};

export interface OrderFilters {
  restaurantId: string;
  branchId?: string;
  customerId?: string;
  status?: string;
  channel?: string;
  page?: number;
  limit?: number;
  search?: string;
  startDate?: string;
  endDate?: string;
  sortField?: string;
  sortDir?: string;
}

export async function listOrders(filters: OrderFilters) {
  await initDatabase();
  const { restaurantId, branchId, customerId, status, channel, search, page = 1, limit = 20, startDate, endDate, sortField, sortDir } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`o.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`o.branch_id = ${branchId}`);
  if (customerId) conditions.push(sql`o.customer_id = ${customerId}`);
  if (status) conditions.push(sql`o.status = ${status}`);
  if (channel) conditions.push(sql`o.channel = ${channel}`);
  if (startDate) conditions.push(sql`o.created_at >= ${startDate}`);
  if (endDate) conditions.push(sql`o.created_at <= ${endDate}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(o.customer_name ILIKE ${pattern} OR o.id::text ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM orders o WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM orders o WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  const orderCol = ORDER_SORT_COLS[sortField ?? ''] ?? 'o.created_at';
  const orderDir = sortDir === 'asc' ? 'ASC' : 'DESC';

  /* raw: SELECT o.*, c.name as customer_full_name FROM orders o LEFT JOIN customers c ... WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT o.*, c.name as customer_full_name
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.id
    WHERE ${where}
    ORDER BY ${sql.raw(orderCol)} ${sql.raw(orderDir)}
    LIMIT ${limit} OFFSET ${offset}
  `);

  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getOrder(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT o.*, c.name as customer_full_name, c.email as customer_email FROM orders o LEFT JOIN customers c ... WHERE o.id = $1 AND o.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT o.*, c.name as customer_full_name, c.email as customer_email
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.id
    WHERE o.id = ${id} AND o.restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Order');
  return rows[0];
}

export async function createOrder(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  // Pull out fields shared by both loyalty and coupon flows.
  const branchId = (data.branch_id as string) ?? null;
  const couponCode = (data.coupon_code as string | undefined)?.trim() || null;
  const customerId = (data.customer_id as string) ?? null;
  const requestedPoints = Math.max(0, Math.floor(Number(data.points_to_redeem ?? 0)));
  const channel = ((data.channel as string) ?? 'chat') as string;
  const deliveryType = ((data.delivery_type as string) ?? 'dine-in') as string;
  const rawSubtotal = Number(data.subtotal ?? 0);
  const orderItems = (data.items as Array<{ price?: unknown; quantity?: unknown }>) ?? [];

  const giftCardCode = (data.gift_card_code as string | null) ?? null;

  /* raw: BEGIN; pg_advisory_xact_lock; SELECT MAX(order_number)+1; INSERT INTO orders ... RETURNING *; COMMIT */
  // Task #295: low-stock alerts collected by decrementStockForOrder are
  // dispatched AFTER the tx commits. Declared in the outer scope so the
  // tx body can populate it without escaping via a stash field.
  let stockAlerts: StockDecrementAlert[] = [];
  const row = await db.transaction(async (tx) => {
    await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtext(${restaurantId})::bigint)`);

    // -------------------------------------------------------------------
    // Pricing pipeline: subtotal → coupon → loyalty → gift card → tax → total
    // Each stage's DB step (applyCoupon / previewRedeem / lockCardByCodeInTx)
    // runs IN ORDER so caps reflect the running post-discount subtotal.
    // The final {subtotal, tax, total} are produced by composeOrderPricing
    // so persisted numbers always agree with the line items the dashboard
    // and customer email render.
    // -------------------------------------------------------------------

    // 1) Coupon
    let couponId: string | null = null;
    let resolvedCode: string | null = null;
    let couponDiscount = 0;
    let appliedCoupon: { code: string; stack_with_loyalty: boolean; stack_with_gift_card: boolean } | null = null;
    if (couponCode && branchId) {
      const items = (Array.isArray(data.items) ? data.items : []) as ApplyCouponItem[];
      const result = await applyCoupon({
        branchId, restaurantId, items, code: couponCode,
        orderType: deliveryType as 'dine-in' | 'takeaway' | 'delivery',
        channel,
        customerId,
        tx,
      });
      if (!result.ok) throw new ValidationError(result.message);
      couponId = result.coupon.id;
      resolvedCode = result.coupon.code;
      couponDiscount = result.discount;
      appliedCoupon = {
        code: result.coupon.code,
        stack_with_loyalty: result.coupon.stack_with_loyalty,
        stack_with_gift_card: result.coupon.stack_with_gift_card,
      };
    }
    const subtotalAfterCoupon = Math.max(0, Math.round((rawSubtotal - couponDiscount) * 100) / 100);

    // 2) Loyalty — preview against the POST-COUPON subtotal so point caps
    //    are enforced against the actual taxable base, not the raw cart.
    let loyaltyDiscount = 0;
    let loyaltyPointsRedeemed = 0;
    if (customerId && requestedPoints > 0) {
      const preview = await loyalty.previewRedeem({
        restaurantId,
        customerId,
        pointsRequested: requestedPoints,
        subtotal: subtotalAfterCoupon,
      });
      loyaltyDiscount = preview.discount_amount;
      loyaltyPointsRedeemed = preview.allowed_points;
    }

    // Resolve gift card settings before the policy gate so we can fail fast
    // (no balance debit) when stacking is disallowed by either the coupon
    // OR the gift-card-side flags.
    let giftCardSettings: Awaited<ReturnType<typeof import('./gift-cards.service').getSettings>> | null = null;
    if (giftCardCode) {
      const { getSettings } = await import('./gift-cards.service');
      giftCardSettings = await getSettings(restaurantId);
    }
    enforceStackingPolicy({
      coupon: appliedCoupon,
      loyaltyApplied: loyaltyDiscount > 0,
      giftCardApplied: !!giftCardCode,
      giftCardSettings,
    });

    // 3) Gift card — cap against the post-loyalty subtotal (gift cards now
    //    reduce taxable base). The lock + ledger write happen here inside
    //    the same tx, so balances roll back if the order insert fails.
    const subtotalAfterLoyalty = Math.max(0, Math.round((subtotalAfterCoupon - loyaltyDiscount) * 100) / 100);
    let giftCardId: string | null = null;
    let giftCardApplied = 0;
    let giftCardRemaining = 0;
    let giftCardBalance = 0;
    if (giftCardCode) {
      const { lockCardByCodeInTx } = await import('./gift-cards.service');
      const settings = giftCardSettings!;
      const card = await lockCardByCodeInTx(tx, restaurantId, giftCardCode);
      giftCardBalance = Number(card.balance);
      giftCardApplied = computeGiftCardOffset({
        subtotalAfterLoyalty,
        cardBalance: giftCardBalance,
        redemptionRule: settings.redemption_rule,
        taxRate: TAX_RATE,
      });
      giftCardId = card.id as string;
    }

    // 4) Compose authoritative subtotal/tax/total from the three stages.
    const pricing = composeOrderPricing({
      subtotal: rawSubtotal,
      couponDiscount,
      loyaltyDiscount,
      giftCardApplied,
    });
    // Persist the PRE-discount subtotal (the cart line-item sum). Each
    // discount lives in its own column (discount_amount / loyalty_discount /
    // gift_card_applied) so the dashboard + email breakdown sums cleanly:
    //   subtotal − coupon − loyalty − gift_card + tax === total.
    const persistedSubtotal = pricing.subtotal;
    const tax = pricing.tax;
    const finalTotal = pricing.total;
    // Take the composer's clamped giftCardApplied as authoritative so the
    // gift_cards ledger (debit) and the orders row (gift_card_applied)
    // never disagree by a cent of rounding.
    giftCardApplied = pricing.giftCardApplied;
    if (giftCardId) {
      giftCardRemaining = Math.round((giftCardBalance - giftCardApplied) * 100) / 100;
    }

    const { rows: [numRow] } = await tx.execute(sql`SELECT COALESCE(MAX(order_number), 0) + 1 AS next_num FROM orders WHERE restaurant_id = ${restaurantId}`);
    const nextOrderNumber = parseInt((numRow as { next_num: string })?.next_num ?? '1', 10);
    const { rows: [inserted] } = await tx.execute(sql`
      INSERT INTO orders (restaurant_id, branch_id, customer_id, customer_name, customer_phone,
         customer_email, items, status, channel, delivery_type, table_number, subtotal, tax, total,
         special_instructions, agent_id, conversation_id, ai_confidence, delivery_address, order_number,
         loyalty_points_redeemed, loyalty_discount,
         gift_card_applied, gift_card_id,
         coupon_id, coupon_code, discount_amount)
      VALUES (${restaurantId}, ${branchId}, ${customerId},
              ${(data.customer_name as string) ?? null}, ${(data.customer_phone as string) ?? null},
              ${(data.customer_email as string) ?? null},
              ${JSON.stringify(orderItems)}::jsonb, COALESCE(${(data.status as string) ?? null}::order_status,'pending'::order_status),
              ${channel}::channel_type, COALESCE(${deliveryType},'dine-in'),
              ${(data.table_number as string) ?? null},
              ${persistedSubtotal}, ${tax}, ${finalTotal},
              ${(data.special_instructions as string) ?? null}, ${(data.agent_id as string) ?? null},
              ${(data.conversation_id as string) ?? null}, ${(data.ai_confidence as number) ?? null},
              ${(data.delivery_address as string) ?? null}, ${nextOrderNumber},
              ${loyaltyPointsRedeemed}, ${loyaltyDiscount},
              ${giftCardApplied}, ${giftCardId},
              ${couponId}, ${resolvedCode}, ${couponDiscount})
      RETURNING *
    `);
    if (loyaltyPointsRedeemed > 0 && customerId && inserted) {
      await loyalty.commitRedeem({
        restaurantId,
        customerId,
        orderId: (inserted as { id: string }).id,
        points: loyaltyPointsRedeemed,
        discount: loyaltyDiscount,
      }, tx);
    }

    if (giftCardId && giftCardApplied > 0 && inserted) {
      const newStatus = giftCardRemaining <= 0 ? 'redeemed' : 'active';
      await tx.execute(sql`
        UPDATE gift_cards
        SET balance = ${giftCardRemaining}, status = ${newStatus}, last_used_at = NOW(), updated_at = NOW()
        WHERE id = ${giftCardId}::uuid
      `);
      await tx.execute(sql`
        INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, branch_id, order_id, entry_type, amount, balance_after, note)
        VALUES (${giftCardId}::uuid, ${restaurantId}, ${branchId},
                ${(inserted as Record<string, unknown>).id as string}::uuid,
                'redemption', ${giftCardApplied}, ${giftCardRemaining}, 'Chat agent order redemption')
      `);
    }

    if (inserted && giftCardId) {
      (inserted as Record<string, unknown>).gift_card_remaining_balance = giftCardRemaining;
      (inserted as Record<string, unknown>).__gift_card_id = giftCardId;
      (inserted as Record<string, unknown>).__gift_card_applied = giftCardApplied;
      (inserted as Record<string, unknown>).__gift_card_remaining = giftCardRemaining;
    }

    if (couponId && inserted && branchId) {
      await recordRedemption({
        couponId, orderId: (inserted as Record<string, unknown>).id as string,
        restaurantId, branchId, customerId, channel, discountAmount: couponDiscount, tx,
      });
    }

    // Atomic stock reservation. Items in this code path may carry either a
    // menu_item_id (from richer clients) or just a name (chat / generic POST
    // /api/orders). The helper resolves both. Throws ValidationError on
    // sold-out — rolls back the entire order insert above.
    const stockItems = (Array.isArray(data.items) ? data.items : []) as Array<{
      menu_item_id?: string | null;
      name?: string | null;
      quantity?: number;
    }>;
    stockAlerts = await decrementStockForOrder(
      tx,
      restaurantId,
      branchId ?? null,
      stockItems.map(i => ({
        menu_item_id: i.menu_item_id ?? null,
        name: i.name ?? null,
        quantity: Number(i.quantity ?? 1),
      }))
    );

    return inserted ?? null;
  });
  if (row) dispatchWebhook(restaurantId, 'order.created', row as Record<string, unknown>);

  // Task #295: fire low-stock alerts AFTER the order tx has committed so a
  // rolled-back order never produces a phantom "running low" email/notif.
  // Failures inside notifyLowStock are logged but never bubble.
  for (const a of stockAlerts) {
    void notifyLowStock({
      restaurantId,
      branchId: branchId ?? null,
      itemId: a.itemId,
      itemName: a.itemName,
      stockRemaining: a.stockRemaining,
      threshold: a.threshold,
      kind: a.kind,
    });
  }

  // Send the redemption receipt email (with remaining balance) to the
  // recipient on file. Failure must never break the order flow.
  const r = row as Record<string, unknown> | null;
  if (r && r.__gift_card_id && Number(r.__gift_card_applied) > 0) {
    try {
      const { enqueueGiftCardRedeemedEmail } = await import('./gift-cards-email.service');
      await enqueueGiftCardRedeemedEmail({
        restaurantId,
        branchId: ((r.branch_id as string | undefined) ?? null),
        cardId: r.__gift_card_id as string,
        appliedAmount: Number(r.__gift_card_applied),
        remainingBalance: Number(r.__gift_card_remaining),
        orderRef: r.order_number ? `#${r.order_number}` : String(r.id ?? '').slice(0, 8).toUpperCase(),
      });
    } catch (err) {
      log.warn({ err }, 'orders gift card redeem email failed');
    } finally {
      delete r.__gift_card_id;
      delete r.__gift_card_applied;
      delete r.__gift_card_remaining;
    }
  }
  return row;
}

/**
 * Internal: dispatch loyalty earn/reverse for a status transition. Runs
 * inside the same transaction as the status update so a failure rolls back
 * the order status — the order is never marked completed without points
 * being credited.
 *
 * Returns the email-notification payload (if any) so the caller can dispatch
 * customer-facing emails AFTER the transaction commits — emails must never
 * be queued for a status change that later rolled back.
 */
async function _runLoyaltyForStatusChange(
  restaurantId: string,
  order: Record<string, unknown>,
  newStatus: string,
  tx: Parameters<Parameters<typeof db.transaction>[0]>[0],
): Promise<loyalty.EarnNotification | null> {
  const customerId = (order.customer_id as string) ?? null;
  if (!customerId) return null;
  let earnResult: loyalty.CommitEarnResult | null = null;
  if (newStatus === 'completed' || newStatus === 'delivered') {
    const items = Array.isArray(order.items) ? (order.items as Array<{ quantity?: unknown }>) : [];
    const itemCount = items.reduce((s, i) => s + Number(i.quantity ?? 1), 0);
    earnResult = await loyalty.commitEarn({
      restaurantId,
      customerId,
      orderId: order.id as string,
      channel: ((order.channel as string) ?? null),
      deliveryType: ((order.delivery_type as string) ?? null),
      subtotal: Number(order.subtotal ?? 0),
      itemCount,
      total: Number(order.total ?? 0),
    }, tx);
  } else if (newStatus === 'cancelled') {
    await loyalty.reverseEarnForOrder(restaurantId, order.id as string, tx);
    await loyalty.reverseRedeemForOrder(restaurantId, order.id as string, tx);
  }
  // Always re-derive customer lifetime aggregates from the orders table so
  // marketing audience filters (min visits / min spend / recency) see fresh
  // values without the operator having to manually edit the customer row.
  // Recompute on EVERY status change — completion adds, cancellation
  // subtracts — keeping the snapshot honest.
  await recomputeCustomerAggregates(tx, customerId, restaurantId);
  return earnResult?.notifications ?? null;
}

export async function updateOrderStatus(id: string, restaurantId: string, status: string, note?: string) {
  await initDatabase();
  // Status transition + loyalty earn/reversal must be atomic. If the loyalty
  // write fails, the status change rolls back so kitchen-side state stays in
  // sync with points-side accounting.
  let pendingLoyaltyNotifications: loyalty.EarnNotification | null = null;
  const updated = await db.transaction(async (tx) => {
    const { rows } = await tx.execute(sql`
      UPDATE orders SET status = ${status}, modification_note = COALESCE(${note ?? null}, modification_note),
        is_modified = true, updated_at = NOW()
      WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
    `);
    if (!rows[0]) throw new NotFoundError('Order');
    pendingLoyaltyNotifications = await _runLoyaltyForStatusChange(restaurantId, rows[0] as Record<string, unknown>, status, tx);
    return rows[0];
  });
  // Post-commit only: enqueue customer-facing loyalty emails. Doing this
  // outside the transaction means a rollback never produces phantom emails.
  if (pendingLoyaltyNotifications) {
    void loyalty.dispatchEarnNotifications(pendingLoyaltyNotifications);
  }
  const event = status === 'cancelled' ? 'order.cancelled' : status === 'completed' ? 'order.completed' : 'order.updated';
  dispatchWebhook(restaurantId, event, updated as Record<string, unknown>);
  return updated;
}

export async function updateOrderDetails(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const sets: SQL[] = [sql`updated_at = NOW()`, sql`is_modified = true`];

  if (data.items !== undefined) {
    const items = data.items as Array<{ price?: unknown; quantity?: unknown }>;
    const subtotal = items.reduce((sum, i) => sum + Number(i.price ?? 0) * Number(i.quantity ?? 1), 0);
    const tax = Math.round(subtotal * 0.08 * 100) / 100;
    const total = Math.round((subtotal + tax) * 100) / 100;
    sets.push(sql`items = ${JSON.stringify(items)}::jsonb`);
    sets.push(sql`subtotal = ${subtotal}`);
    sets.push(sql`tax = ${tax}`);
    sets.push(sql`total = ${total}`);
  }
  if (data.customer_name !== undefined) sets.push(sql`customer_name = ${bindValue((data.customer_name as string) ?? null)}`);
  if (data.customer_phone !== undefined) sets.push(sql`customer_phone = ${bindValue((data.customer_phone as string) ?? null)}`);
  if (data.delivery_type !== undefined) sets.push(sql`delivery_type = ${bindValue((data.delivery_type as string) ?? null)}`);
  if (data.delivery_address !== undefined) sets.push(sql`delivery_address = ${bindValue((data.delivery_address as string) ?? null)}`);
  if (data.table_number !== undefined) sets.push(sql`table_number = ${bindValue((data.table_number as string) ?? null)}`);
  if (data.special_instructions !== undefined) sets.push(sql`special_instructions = ${bindValue((data.special_instructions as string) ?? null)}`);
  if (data.modification_note !== undefined) sets.push(sql`modification_note = ${bindValue((data.modification_note as string) ?? null)}`);
  if (data.status !== undefined) sets.push(sql`status = ${bindValue(data.status as string)}::order_status`);

  /* raw: UPDATE orders SET ... WHERE id = $1 AND restaurant_id = $2 RETURNING * */
  const { rows } = await db.execute(sql`UPDATE orders SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Order');
  dispatchWebhook(restaurantId, 'order.updated', rows[0] as Record<string, unknown>);
  return rows[0];
}

export async function cancelOrder(id: string, restaurantId: string, reason?: string) {
  return updateOrderStatus(id, restaurantId, 'cancelled', reason);
}

export async function getOrderStats(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FILTER (...) as pending, ... FROM orders WHERE ... */
  const { rows } = await db.execute(sql`
    SELECT
      COUNT(*) FILTER (WHERE status = 'pending') as pending,
      COUNT(*) FILTER (WHERE status IN ('confirmed','kitchen','ready')) as active,
      COUNT(*) FILTER (WHERE status = 'completed') as completed,
      COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
      COUNT(*) as total,
      COALESCE(SUM(total) FILTER (WHERE status = 'completed'), 0) as revenue
    FROM orders WHERE ${where}
  `);
  return rows[0];
}

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