import { db } from '../db/db_index';
import { vendorSnippets, deliverySlotInfo, productInfo, orders, orderItems, orderStatus } from '../db/schema';
import { eq, and, inArray, gt, sql, asc, desc } from 'drizzle-orm';

export async function checkVendorSnippetExists(snippetCode: string): Promise<boolean> {
  const existingSnippet = await db.query.vendorSnippets.findFirst({
    where: eq(vendorSnippets.snippetCode, snippetCode),
  });
  return !!existingSnippet;
}

export async function getVendorSnippetById(id: number): Promise<any | null> {
  return await db.query.vendorSnippets.findFirst({
    where: eq(vendorSnippets.id, id),
    with: {
      slot: true,
    },
  });
}

export async function getVendorSnippetByCode(snippetCode: string): Promise<any | null> {
  return await db.query.vendorSnippets.findFirst({
    where: eq(vendorSnippets.snippetCode, snippetCode),
  });
}

export async function getAllVendorSnippets(): Promise<any[]> {
  return await db.query.vendorSnippets.findMany({
    with: {
      slot: true,
    },
    orderBy: desc(vendorSnippets.createdAt),
  });
}

export interface CreateVendorSnippetInput {
  snippetCode: string;
  slotId?: number;
  productIds: number[];
  isPermanent: boolean;
  validTill?: Date;
}

export async function createVendorSnippet(input: CreateVendorSnippetInput): Promise<any> {
  const [result] = await db.insert(vendorSnippets).values({
    snippetCode: input.snippetCode,
    slotId: input.slotId,
    productIds: input.productIds,
    isPermanent: input.isPermanent,
    validTill: input.validTill,
  }).returning();

  return result;
}

export async function updateVendorSnippet(id: number, updates: any): Promise<any> {
  const [result] = await db.update(vendorSnippets)
    .set(updates)
    .where(eq(vendorSnippets.id, id))
    .returning();

  return result;
}

export async function deleteVendorSnippet(id: number): Promise<void> {
  await db.delete(vendorSnippets)
    .where(eq(vendorSnippets.id, id));
}

export async function getProductsByIds(productIds: number[]): Promise<any[]> {
  return await db.query.productInfo.findMany({
    where: inArray(productInfo.id, productIds),
    columns: { id: true, name: true },
  });
}

export async function getVendorSlotById(slotId: number): Promise<any | null> {
  return await db.query.deliverySlotInfo.findFirst({
    where: eq(deliverySlotInfo.id, slotId),
  });
}

export async function getVendorOrdersBySlotId(slotId: number): Promise<any[]> {
  return await db.query.orders.findMany({
    where: eq(orders.slotId, slotId),
    with: {
      orderItems: {
        with: {
          product: {
            with: {
              unit: true,
            },
          },
        },
      },
      orderStatus: true,
      user: true,
      slot: true,
    },
    orderBy: desc(orders.createdAt),
  });
}

export async function getOrderItemsByOrderIds(orderIds: number[]): Promise<any[]> {
  return await db.query.orderItems.findMany({
    where: inArray(orderItems.orderId, orderIds),
    with: {
      product: {
        with: {
          unit: true,
        },
      },
    },
  });
}

export async function getOrderStatusByOrderIds(orderIds: number[]): Promise<any[]> {
  return await db.query.orderStatus.findMany({
    where: inArray(orderStatus.orderId, orderIds),
  });
}

export async function updateVendorOrderItemPackaging(orderItemId: number, isPackaged: boolean, isPackageVerified: boolean): Promise<void> {
  await db.update(orderItems)
    .set({
      is_packaged: isPackaged,
      is_package_verified: isPackageVerified,
    })
    .where(eq(orderItems.id, orderItemId));
}
