import { router, publicProcedure, protectedProcedure } from '@/src/trpc/trpc-index'
import { z } from 'zod'
import dayjs from 'dayjs'
import { getAppUrl } from '@/src/lib/env-exporter'
import {
  checkVendorSnippetExists as checkVendorSnippetExistsInDb,
  getVendorSnippetById as getVendorSnippetByIdInDb,
  getVendorSnippetByCode as getVendorSnippetByCodeInDb,
  getAllVendorSnippets as getAllVendorSnippetsInDb,
  createVendorSnippet as createVendorSnippetInDb,
  updateVendorSnippet as updateVendorSnippetInDb,
  deleteVendorSnippet as deleteVendorSnippetInDb,
  getProductsByIds as getProductsByIdsInDb,
  getVendorSlotById as getVendorSlotByIdInDb,
  getVendorOrdersBySlotId as getVendorOrdersBySlotIdInDb,
  getVendorOrders as getVendorOrdersInDb,
  updateVendorOrderItemPackaging as updateVendorOrderItemPackagingInDb,
  getSlotsAfterDate as getSlotsAfterDateInDb,
} from '@/src/dbService'
import type {
  AdminVendorSnippet,
  AdminVendorSnippetWithProducts,
  AdminVendorSnippetWithSlot,
  AdminVendorSnippetDeleteResult,
  AdminVendorSnippetOrdersResult,
  AdminVendorSnippetOrdersWithSlotResult,
  AdminVendorOrderSummary,
  AdminUpcomingSlotsResult,
  AdminVendorUpdatePackagingResult,
} from '@packages/shared'

const createSnippetSchema = z.object({
  snippetCode: z.string().min(1, "Snippet code is required"),
  slotId: z.number().optional(),
  productIds: z.array(z.number().int().positive()).min(1, "At least one product is required"),
  validTill: z.string().optional(),
  isPermanent: z.boolean().default(false)
});

const updateSnippetSchema = z.object({
  id: z.number().int().positive(),
  updates: createSnippetSchema.partial().extend({
    snippetCode: z.string().min(1).optional(),
    productIds: z.array(z.number().int().positive()).optional(),
    isPermanent: z.boolean().default(false)
  }),
});

export const vendorSnippetsRouter = router({
  create: protectedProcedure
    .input(createSnippetSchema)
    .mutation(async ({ input, ctx }): Promise<AdminVendorSnippet> => {
      const { snippetCode, slotId, productIds, validTill, isPermanent } = input;

      // Get staff user ID from auth middleware
      const staffUserId = ctx.staffUser?.id;
      if (!staffUserId) {
        throw new Error("Unauthorized");
      }
      
      if(slotId) {
        const slot = await getVendorSlotByIdInDb(slotId)
        if (!slot) {
          throw new Error("Invalid slot ID")
        }
      }

      const products = await getProductsByIdsInDb(productIds)
      if (products.length !== productIds.length) {
        throw new Error("One or more invalid product IDs")
      }

      const existingSnippet = await checkVendorSnippetExistsInDb(snippetCode)
      if (existingSnippet) {
        throw new Error("Snippet code already exists")
      }

      const result = await createVendorSnippetInDb({
        snippetCode,
        slotId,
        productIds,
        isPermanent,
        validTill: validTill ? new Date(validTill) : undefined,
      })

      /*
      // Old implementation - direct DB queries:
      // Validate slot exists
      if(slotId) {
        const slot = await db.query.deliverySlotInfo.findFirst({
          where: eq(deliverySlotInfo.id, slotId),
        });
        if (!slot) {
          throw new Error("Invalid slot ID");
        }
      }

      // Validate products exist
      const products = await db.query.productInfo.findMany({
        where: inArray(productInfo.id, productIds),
      });
      if (products.length !== productIds.length) {
        throw new Error("One or more invalid product IDs");
      }

      // Check if snippet code already exists
      const existingSnippet = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.snippetCode, snippetCode),
      });
      if (existingSnippet) {
        throw new Error("Snippet code already exists");
      }

      const result = await db.insert(vendorSnippets).values({
        snippetCode,
        slotId,
        productIds,
        isPermanent,
        validTill: validTill ? new Date(validTill) : undefined,
      }).returning();

      return result[0];
      */

      return result
    }),

  getAll: protectedProcedure
    .query(async (): Promise<AdminVendorSnippetWithProducts[]> => {
      console.log('from the vendor snipptes methods')

      try {
        const result = await getAllVendorSnippetsInDb()

        const snippetsWithProducts = await Promise.all(
          result.map(async (snippet) => {
            const products = await getProductsByIdsInDb(snippet.productIds)

            return {
              ...snippet,
              accessUrl: `${getAppUrl()}/vendor-order-list?id=${snippet.snippetCode}`,
              products,
            }
          })
        )

        /*
        // Old implementation - direct DB queries:
        const result = await db.query.vendorSnippets.findMany({
          with: {
            slot: true,
          },
          orderBy: (vendorSnippets, { desc }) => [desc(vendorSnippets.createdAt)],
        });

        const snippetsWithProducts = await Promise.all(
          result.map(async (snippet) => {
            const products = await db.query.productInfo.findMany({
              where: inArray(productInfo.id, snippet.productIds),
              columns: { id: true, name: true },
            });

            return {
              ...snippet,
              accessUrl: `${getAppUrl()}/vendor-order-list?id=${snippet.snippetCode}`,
              products: products.map(p => ({ id: p.id, name: p.name })),
            };
          })
        );

        return snippetsWithProducts;
        */

        return snippetsWithProducts
      }
      catch(e) {
        console.log(e)
      }
      return []
    }),

  getById: protectedProcedure
    .input(z.object({ id: z.number().int().positive() }))
    .query(async ({ input }): Promise<AdminVendorSnippetWithSlot> => {
      const { id } = input;

      const result = await getVendorSnippetByIdInDb(id)

      /*
      // Old implementation - direct DB queries:
      const result = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.id, id),
        with: {
          slot: true,
        },
      });

      if (!result) {
        throw new Error("Vendor snippet not found");
      }

      return result;
      */

      if (!result) {
        throw new Error('Vendor snippet not found')
      }

      return result
    }),

  update: protectedProcedure
    .input(updateSnippetSchema)
    .mutation(async ({ input }): Promise<AdminVendorSnippet> => {
      const { id, updates } = input;
      
      const existingSnippet = await getVendorSnippetByIdInDb(id)
      if (!existingSnippet) {
        throw new Error('Vendor snippet not found')
      }

      if (updates.slotId) {
        const slot = await getVendorSlotByIdInDb(updates.slotId)
        if (!slot) {
          throw new Error('Invalid slot ID')
        }
      }

      if (updates.productIds) {
        const products = await getProductsByIdsInDb(updates.productIds)
        if (products.length !== updates.productIds.length) {
          throw new Error('One or more invalid product IDs')
        }
      }

      if (updates.snippetCode && updates.snippetCode !== existingSnippet.snippetCode) {
        const duplicateSnippet = await checkVendorSnippetExistsInDb(updates.snippetCode)
        if (duplicateSnippet) {
          throw new Error('Snippet code already exists')
        }
      }

      const updateData = {
        ...updates,
        validTill: updates.validTill !== undefined
          ? (updates.validTill ? new Date(updates.validTill) : null)
          : undefined,
      }

      const result = await updateVendorSnippetInDb(id, updateData)

      /*
      // Old implementation - direct DB queries:
      const existingSnippet = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.id, id),
      });
      if (!existingSnippet) {
        throw new Error("Vendor snippet not found");
      }

      // Validate slot if being updated
      if (updates.slotId) {
        const slot = await db.query.deliverySlotInfo.findFirst({
          where: eq(deliverySlotInfo.id, updates.slotId),
        });
        if (!slot) {
          throw new Error("Invalid slot ID");
        }
      }

      // Validate products if being updated
      if (updates.productIds) {
        const products = await db.query.productInfo.findMany({
          where: inArray(productInfo.id, updates.productIds),
        });
        if (products.length !== updates.productIds.length) {
          throw new Error("One or more invalid product IDs");
        }
      }

      // Check snippet code uniqueness if being updated
      if (updates.snippetCode && updates.snippetCode !== existingSnippet.snippetCode) {
        const duplicateSnippet = await db.query.vendorSnippets.findFirst({
          where: eq(vendorSnippets.snippetCode, updates.snippetCode),
        });
        if (duplicateSnippet) {
          throw new Error("Snippet code already exists");
        }
      }

      const updateData: any = { ...updates };
      if (updates.validTill !== undefined) {
        updateData.validTill = updates.validTill ? new Date(updates.validTill) : null;
      }

      const result = await db.update(vendorSnippets)
        .set(updateData)
        .where(eq(vendorSnippets.id, id))
        .returning();

      if (result.length === 0) {
        throw new Error("Failed to update vendor snippet");
      }

      return result[0];
      */

      if (!result) {
        throw new Error('Failed to update vendor snippet')
      }

      return result
    }),

  delete: protectedProcedure
    .input(z.object({ id: z.number().int().positive() }))
    .mutation(async ({ input }): Promise<AdminVendorSnippetDeleteResult> => {
      const { id } = input;

      const result = await deleteVendorSnippetInDb(id)

      /*
      // Old implementation - direct DB queries:
      const result = await db.delete(vendorSnippets)
        .where(eq(vendorSnippets.id, id))
        .returning();

      if (result.length === 0) {
        throw new Error("Vendor snippet not found");
      }

      return { message: "Vendor snippet deleted successfully" };
      */

      if (!result) {
        throw new Error('Vendor snippet not found')
      }

      return { message: 'Vendor snippet deleted successfully' }
    }),

  getOrdersBySnippet: publicProcedure
    .input(z.object({
      snippetCode: z.string().min(1, "Snippet code is required")
    }))
    .query(async ({ input }): Promise<AdminVendorSnippetOrdersResult> => {
      const { snippetCode } = input;

      const snippet = await getVendorSnippetByCodeInDb(snippetCode)

      /*
      // Old implementation - direct DB queries:
      // Find the snippet
      const snippet = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.snippetCode, snippetCode),
      });

      if (!snippet) {
        throw new Error("Vendor snippet not found");
      }

      // Check if snippet is still valid
      if (snippet.validTill && new Date(snippet.validTill) < new Date()) {
        throw new Error("Vendor snippet has expired");
      }

      // Query orders that match the snippet criteria
      const matchingOrders = await db.query.orders.findMany({
        where: eq(orders.slotId, snippet.slotId!),
        with: {
          orderItems: {
            with: {
              product: {
                with: {
                  unit: true,
                },
              },
            },
          },
          orderStatus: true,
          user: true,
          slot: true,
        },
        orderBy: (orders, { desc }) => [desc(orders.createdAt)],
      });
      */

      if (!snippet) {
        throw new Error('Vendor snippet not found')
      }

      if (snippet.validTill && new Date(snippet.validTill) < new Date()) {
        throw new Error('Vendor snippet has expired')
      }

      if (!snippet.slotId) {
        throw new Error('Vendor snippet not associated with a slot')
      }

      const matchingOrders = await getVendorOrdersBySlotIdInDb(snippet.slotId)

      // Filter orders that contain at least one of the snippet's products
      const filteredOrders = matchingOrders.filter(order => {
        const status = order.orderStatus;
        if (status[0].isCancelled) return false;
        const orderProductIds = order.orderItems.map(item => item.productId);
        return snippet.productIds.some(productId => orderProductIds.includes(productId));
      });

      // Format the response
      const formattedOrders = filteredOrders.map(order => {
        // Filter orderItems to only include products attached to the snippet
        const attachedOrderItems = order.orderItems.filter(item =>
          snippet.productIds.includes(item.productId)
        );

        const products = attachedOrderItems.map(item => ({
          orderItemId: item.id,
          productId: item.productId,
          productName: item.product.name,
          quantity: parseFloat(item.quantity),
          productSize: item.product.productQuantity,
          price: parseFloat((item.price ?? 0).toString()),
          unit: item.product.unit?.shortNotation || 'unit',
          subtotal: parseFloat((item.price ?? 0).toString()) * parseFloat(item.quantity),
          is_packaged: item.is_packaged,
          is_package_verified: item.is_package_verified,
        }));

        const orderTotal = products.reduce((sum, p) => sum + p.subtotal, 0);

         return {
           orderId: `ORD${order.id}`,
           orderDate: order.createdAt ? order.createdAt.toISOString() : new Date(0).toISOString(),
           customerName: order.user.name || '',
           totalAmount: orderTotal,
          slotInfo: order.slot ? {
            time: order.slot.deliveryTime ? order.slot.deliveryTime.toISOString() : new Date(0).toISOString(),
            sequence: order.slot.deliverySequence,
          } : null,
          products,
          matchedProducts: snippet.productIds, // All snippet products are considered matched
          snippetCode: snippet.snippetCode,
        };
      });

      return {
        success: true,
        data: formattedOrders,
        snippet: {
          id: snippet.id,
          snippetCode: snippet.snippetCode,
          slotId: snippet.slotId,
          productIds: snippet.productIds,
          validTill: snippet.validTill?.toISOString(),
          createdAt: snippet.createdAt.toISOString(),
          isPermanent: snippet.isPermanent,
        },
      }
    }),

  getVendorOrders: protectedProcedure
    .query(async (): Promise<AdminVendorOrderSummary[]> => {
      const vendorOrders = await getVendorOrdersInDb()

      /*
      // Old implementation - direct DB queries:
      const vendorOrders = await db.query.orders.findMany({
        with: {
          user: true,
          orderItems: {
            with: {
              product: {
                with: {
                  unit: true,
                },
              },
            },
          },
        },
        orderBy: (orders, { desc }) => [desc(orders.createdAt)],
      });
      */

      return vendorOrders.map(order => ({
        id: order.id,
        status: 'pending',
        orderDate: order.createdAt ? order.createdAt.toISOString() : new Date(0).toISOString(),
        totalQuantity: order.orderItems.reduce((sum, item) => sum + parseFloat(item.quantity || '0'), 0),
        products: order.orderItems.map(item => ({
          name: item.product.name,
          quantity: parseFloat(item.quantity || '0'),
          unit: item.product.unit?.shortNotation || 'unit',
        })),
      }))
    }),

  getUpcomingSlots: publicProcedure
    .query(async (): Promise<AdminUpcomingSlotsResult> => {
      const threeHoursAgo = dayjs().subtract(3, 'hour').toDate();
      const slots = await getSlotsAfterDateInDb(threeHoursAgo)

      /*
      // Old implementation - direct DB queries:
      const slots = await db.query.deliverySlotInfo.findMany({
        where: and(
          eq(deliverySlotInfo.isActive, true),
          gt(deliverySlotInfo.deliveryTime, threeHoursAgo)
        ),
        orderBy: asc(deliverySlotInfo.deliveryTime),
      });
      */

      return {
        success: true,
        data: slots.map(slot => ({
          id: slot.id,
          deliveryTime: slot.deliveryTime.toISOString(),
          freezeTime: slot.freezeTime.toISOString(),
          deliverySequence: slot.deliverySequence,
        })),
      }
    }),

  getOrdersBySnippetAndSlot: publicProcedure
    .input(z.object({
      snippetCode: z.string().min(1, "Snippet code is required"),
      slotId: z.number().int().positive("Valid slot ID is required"),
    }))
    .query(async ({ input }): Promise<AdminVendorSnippetOrdersWithSlotResult> => {
      const { snippetCode, slotId } = input;

      const snippet = await getVendorSnippetByCodeInDb(snippetCode)
      const slot = await getVendorSlotByIdInDb(slotId)

      /*
      // Old implementation - direct DB queries:
      // Find the snippet
      const snippet = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.snippetCode, snippetCode),
      });

      if (!snippet) {
        throw new Error("Vendor snippet not found");
      }

      // Find the slot
      const slot = await db.query.deliverySlotInfo.findFirst({
        where: eq(deliverySlotInfo.id, slotId),
      });

      if (!slot) {
        throw new Error("Slot not found");
      }
      
      // Query orders that match the slot and snippet criteria
      const matchingOrders = await db.query.orders.findMany({
        where: eq(orders.slotId, slotId),
        with: {
          orderItems: {
            with: {
              product: {
                with: {
                  unit: true,
                },
              },
            },
          },
          orderStatus: true,
          user: true,
          slot: true,
        },
        orderBy: (orders, { desc }) => [desc(orders.createdAt)],
      });
      */

      if (!snippet) {
        throw new Error('Vendor snippet not found')
      }

      if (!slot) {
        throw new Error('Slot not found')
      }

      const matchingOrders = await getVendorOrdersBySlotIdInDb(slotId)

      // Filter orders that contain at least one of the snippet's products
      const filteredOrders = matchingOrders.filter(order => {
        const status = order.orderStatus;
        if (status[0]?.isCancelled) return false;
        const orderProductIds = order.orderItems.map(item => item.productId);
        return snippet.productIds.some(productId => orderProductIds.includes(productId));
      });

      // Format the response
      const formattedOrders = filteredOrders.map(order => {
        // Filter orderItems to only include products attached to the snippet
        const attachedOrderItems = order.orderItems.filter(item =>
          snippet.productIds.includes(item.productId)
        );

        const products = attachedOrderItems.map(item => ({
          orderItemId: item.id,
          productId: item.productId,
          productName: item.product.name,
          quantity: parseFloat(item.quantity),
          price: parseFloat((item.price ?? 0).toString()),
          unit: item.product.unit?.shortNotation || 'unit',
          subtotal: parseFloat((item.price ?? 0).toString()) * parseFloat(item.quantity),
          productSize: item.product.productQuantity,
          is_packaged: item.is_packaged,
          is_package_verified: item.is_package_verified,
        }));

        const orderTotal = products.reduce((sum, p) => sum + p.subtotal, 0);

        return {
          orderId: `ORD${order.id}`,
          orderDate: order.createdAt ? order.createdAt.toISOString() : new Date(0).toISOString(),
          customerName: order.user.name || '',
          totalAmount: orderTotal,
          slotInfo: order.slot ? {
            time: order.slot.deliveryTime ? order.slot.deliveryTime.toISOString() : new Date(0).toISOString(),
            sequence: order.slot.deliverySequence,
          } : null,
          products,
          matchedProducts: snippet.productIds,
          snippetCode: snippet.snippetCode,
        };
      });

      return {
        success: true,
        data: formattedOrders,
        snippet: {
          id: snippet.id,
          snippetCode: snippet.snippetCode,
          slotId: snippet.slotId,
          productIds: snippet.productIds,
          validTill: snippet.validTill?.toISOString(),
          createdAt: snippet.createdAt ? snippet.createdAt.toISOString() : new Date(0).toISOString(),
          isPermanent: snippet.isPermanent,
        },
        selectedSlot: {
          id: slot.id,
          deliveryTime: slot.deliveryTime ? slot.deliveryTime.toISOString() : new Date(0).toISOString(),
          freezeTime: slot.freezeTime ? slot.freezeTime.toISOString() : new Date(0).toISOString(),
          deliverySequence: slot.deliverySequence,
        },
      }
    }),

  updateOrderItemPackaging: publicProcedure
    .input(z.object({
      orderItemId: z.number().int().positive("Valid order item ID required"),
      is_packaged: z.boolean()
    }))
    .mutation(async ({ input, ctx }): Promise<AdminVendorUpdatePackagingResult> => {
      const { orderItemId, is_packaged } = input;

      // Get staff user ID from auth middleware
      // const staffUserId = ctx.staffUser?.id;
      // if (!staffUserId) {
      //   throw new Error("Unauthorized");
      // }

      const result = await updateVendorOrderItemPackagingInDb(orderItemId, is_packaged)

      /*
      // Old implementation - direct DB queries:
      // Check if order item exists and get related data
      const orderItem = await db.query.orderItems.findFirst({
        where: eq(orderItems.id, orderItemId),
        with: {
          order: {
            with: {
              slot: true
            }
          }
        }
      });

      if (!orderItem) {
        throw new Error("Order item not found");
      }

      // Check if this order item belongs to a slot that has vendor snippets
      // This ensures only order items from vendor-accessible orders can be updated
      if (!orderItem.order.slotId) {
        throw new Error("Order item not associated with a vendor slot");
      }

      const snippetExists = await db.query.vendorSnippets.findFirst({
        where: eq(vendorSnippets.slotId, orderItem.order.slotId),
      });

      if (!snippetExists) {
        throw new Error("No vendor snippet found for this order's slot");
      }

      // Update the is_packaged field
      const result = await db.update(orderItems)
        .set({ is_packaged })
        .where(eq(orderItems.id, orderItemId))
        .returning();

      if (result.length === 0) {
        throw new Error("Failed to update packaging status");
      }

      return {
        success: true,
        orderItemId,
        is_packaged
      };
      */

      if (!result.success) {
        throw new Error(result.message)
      }

      return result
    }),
});
