import { db } from '../db/db_index'
import {
  deliverySlotInfo,
  productInfo,
  vendorSnippets,
  productGroupInfo,
} from '../db/schema'
import { and, asc, desc, eq, gt, inArray } from 'drizzle-orm'
import type {
  AdminDeliverySlot,
  AdminSlotWithProducts,
  AdminSlotWithProductsAndSnippetsBase,
  AdminSlotCreateResult,
  AdminSlotUpdateResult,
  AdminVendorSnippet,
  AdminSlotProductSummary,
  AdminUpdateSlotCapacityResult,
} from '@packages/shared'
import { coerceDate } from '../lib/date'

type SlotSnippetInput = {
  name: string
  productIds: number[]
  validTill?: string
}

const getStringArray = (value: unknown): string[] | null => {
  if (!Array.isArray(value)) return null
  return value.map((item) => String(item))
}

const getNumberArray = (value: unknown): number[] => {
  if (!Array.isArray(value)) return []
  return value.map((item) => Number(item))
}

const normalizeProductIds = (value: unknown): number[] => {
  if (!Array.isArray(value)) return []
  const ids = value
    .map((item) => Number(item))
    .filter((item) => Number.isFinite(item))

  return Array.from(new Set(ids))
}

const chunkArray = <T>(items: T[], size: number): T[][] => {
  if (size <= 0) return [items]
  const chunks: T[][] = []
  for (let i = 0; i < items.length; i += size) {
    chunks.push(items.slice(i, i + size))
  }
  return chunks
}

const PRODUCT_ID_CHUNK_SIZE = 40

const fetchExistingProductIds = async (tx: any, productIds: number[]) => {
  const existingIds = new Set<number>()
  const chunks = chunkArray(productIds, PRODUCT_ID_CHUNK_SIZE)
  for (const chunk of chunks) {
    if (chunk.length === 0) continue
    const products = await tx.query.productInfo.findMany({
      where: inArray(productInfo.id, chunk),
      columns: { id: true },
    })
    products.forEach((product: { id: number }) => existingIds.add(product.id))
  }
  return existingIds
}

const mapDeliverySlot = (slot: typeof deliverySlotInfo.$inferSelect): AdminDeliverySlot => ({
  id: slot.id,
  deliveryTime: coerceDate(slot.deliveryTime) ?? new Date(0),
  freezeTime: coerceDate(slot.freezeTime) ?? new Date(0),
  isActive: slot.isActive,
  isFlash: slot.isFlash,
  isCapacityFull: slot.isCapacityFull,
  deliverySequence: slot.deliverySequence,
  groupIds: slot.groupIds,
})

const mapSlotProductSummary = (product: { id: number; name: string; images: unknown }): AdminSlotProductSummary => ({
  id: product.id,
  name: product.name,
  images: getStringArray(product.images),
})

const mapVendorSnippet = (snippet: typeof vendorSnippets.$inferSelect): AdminVendorSnippet => ({
  id: snippet.id,
  snippetCode: snippet.snippetCode,
  slotId: snippet.slotId ?? null,
  productIds: snippet.productIds || [],
  isPermanent: snippet.isPermanent,
  validTill: coerceDate(snippet.validTill),
  createdAt: coerceDate(snippet.createdAt) ?? new Date(0),
})

export async function getActiveSlotsWithProducts(limit: number = 20): Promise<AdminSlotWithProducts[]> {
  const slots = await db.query.deliverySlotInfo
    .findMany({
      where: eq(deliverySlotInfo.isActive, true),
      orderBy: desc(deliverySlotInfo.deliveryTime),
      limit,
    })

  // Get all unique product IDs from all slots
  const allProductIds = new Set<number>()
  for (const slot of slots) {
    for (const productId of (slot.productIds || [])) {
      allProductIds.add(productId)
    }
  }

  // Fetch all products in one query
  const productIdsArray = Array.from(allProductIds)
  const productIdsSet = new Set(productIdsArray)
  // const productsData = productIdsArray.length > 0
  //   ? await db.query.productInfo.findMany({
  //       where: inArray(productInfo.id, productIdsArray),
  //       columns: { id: true, name: true, images: true },
  //     })
  //   : []

  let productsData = await db.query.productInfo.findMany({});
  productsData = productsData.filter(item => productIdsSet.has(item.id))

  // Create a map for quick lookup
  const productMap = new Map(productsData.map(p => [p.id, p]))

  return slots.map((slot) => ({
    ...mapDeliverySlot(slot),
    deliverySequence: getNumberArray(slot.deliverySequence),
    products: (slot.productIds || [])
      .map(productId => productMap.get(productId))
      .filter((p): p is NonNullable<typeof p> => p != null)
      .map(product => mapSlotProductSummary(product)),
  }))
}

export async function staleSlotsCleanup(): Promise<number> {
  // Get the 20 most recent slot IDs
  const recentSlots = await db
    .select({ id: deliverySlotInfo.id })
    .from(deliverySlotInfo)
    .orderBy(desc(deliverySlotInfo.id))
    .limit(20)

  if (recentSlots.length < 20) {
    return 0 // Less than 20 slots exist, nothing to clean
  }

  // Get the threshold (the 20th most recent slot ID - minimum of the 20)
  const threshold = Math.min(...recentSlots.map((s) => s.id))

  // Clear productIds for all slots older than threshold
  const result = await db
    .update(deliverySlotInfo)
    .set({ productIds: [] })
    .where(eq(deliverySlotInfo.id, threshold))

  return 1
}

export async function getActiveSlots(): Promise<AdminDeliverySlot[]> {
  const slots = await db.query.deliverySlotInfo.findMany({
    where: eq(deliverySlotInfo.isActive, true),
  })

  return slots.map(mapDeliverySlot)
}

export async function getSlotsAfterDate(afterDate: Date): Promise<AdminDeliverySlot[]> {
  const slots = await db.query.deliverySlotInfo.findMany({
    where: and(
      eq(deliverySlotInfo.isActive, true),
      gt(deliverySlotInfo.deliveryTime, afterDate)
    ),
    orderBy: asc(deliverySlotInfo.deliveryTime),
  })

  return slots.map(mapDeliverySlot)
}

export async function getSlotByIdWithRelations(id: number): Promise<AdminSlotWithProductsAndSnippetsBase | null> {
  const slot = await db.query.deliverySlotInfo.findFirst({
    where: eq(deliverySlotInfo.id, id),
    with: {
      vendorSnippets: true,
    },
  })

  if (!slot) {
    return null
  }

  // Fetch products for this slot
  const productIds = slot.productIds || []
  const productIdSet = new Set(productIds);
  // const productsData = productIds.length > 0
  //   ? await db.query.productInfo.findMany({
  //       where: inArray(productInfo.id, productIds),
  //       columns: { id: true, name: true, images: true },
  //     })
  //   : []
  let productsData = productIds.length > 0
    ? await db.query.productInfo.findMany({
        columns: { id: true, name: true, images: true },
      })
    : []
  productsData = productsData.filter(item => productIdSet.has(item.id))
  return {
    ...mapDeliverySlot(slot),
    deliverySequence: getNumberArray(slot.deliverySequence),
    groupIds: getNumberArray(slot.groupIds),
    products: productsData.map(product => mapSlotProductSummary(product)),
    vendorSnippets: slot.vendorSnippets.map(mapVendorSnippet),
  }
}

export async function createSlotWithRelations(input: {
  deliveryTime: string
  freezeTime: string
  isActive?: boolean
  productIds?: number[]
  vendorSnippets?: SlotSnippetInput[]
  groupIds?: number[]
}): Promise<AdminSlotCreateResult> {
  const { deliveryTime, freezeTime, isActive, productIds, vendorSnippets: snippets, groupIds } = input

  const normalizedProductIds = normalizeProductIds(productIds)

  const result = await db.transaction(async (tx) => {
    // Validate product IDs if provided
    if (normalizedProductIds.length > 0) {
      const existingIds = await fetchExistingProductIds(tx, normalizedProductIds)
      const missingIds = normalizedProductIds.filter((productId) => !existingIds.has(productId))
      if (missingIds.length > 0) {
        throw new Error(`Invalid product IDs: ${missingIds.join(', ')}`)
      }
    }

    const [newSlot] = await tx
      .insert(deliverySlotInfo)
      .values({
        deliveryTime: new Date(deliveryTime),
        freezeTime: new Date(freezeTime),
        isActive: isActive !== undefined ? isActive : true,
        groupIds: groupIds !== undefined ? groupIds : [],
        productIds: normalizedProductIds,
      })
      .returning()

    let createdSnippets: AdminVendorSnippet[] = []
    if (snippets && snippets.length > 0) {
      for (const snippet of snippets) {
        const products = await tx.query.productInfo.findMany({
          where: inArray(productInfo.id, snippet.productIds),
        })
        if (products.length !== snippet.productIds.length) {
          throw new Error(`One or more invalid product IDs in snippet "${snippet.name}"`)
        }

        const existingSnippet = await tx.query.vendorSnippets.findFirst({
          where: eq(vendorSnippets.snippetCode, snippet.name),
        })
        if (existingSnippet) {
          throw new Error(`Snippet name "${snippet.name}" already exists`)
        }

        const [createdSnippet] = await tx.insert(vendorSnippets).values({
          snippetCode: snippet.name,
          slotId: newSlot.id,
          productIds: snippet.productIds,
          validTill: snippet.validTill ? new Date(snippet.validTill) : undefined,
        }).returning()

        createdSnippets.push(mapVendorSnippet(createdSnippet))
      }
    }

    return {
      slot: mapDeliverySlot(newSlot),
      createdSnippets,
      message: 'Slot created successfully',
    }
  })

  return result
}

export async function updateSlotWithRelations(input: {
  id: number
  deliveryTime: string
  freezeTime: string
  isActive?: boolean
  productIds?: number[]
  vendorSnippets?: SlotSnippetInput[]
  groupIds?: number[]
}): Promise<AdminSlotUpdateResult | null> {
  const { id, deliveryTime, freezeTime, isActive, productIds, vendorSnippets: snippets, groupIds } = input

  let validGroupIds = groupIds
  if (groupIds && groupIds.length > 0) {
    const existingGroups = await db.query.productGroupInfo.findMany({
      where: inArray(productGroupInfo.id, groupIds),
      columns: { id: true },
    })
    validGroupIds = existingGroups.map((group: { id: number }) => group.id)
  }

  const normalizedProductIds = productIds !== undefined ? normalizeProductIds(productIds) : undefined

  const result = await db.transaction(async (tx) => {
    // Validate product IDs if provided
    if (normalizedProductIds !== undefined && normalizedProductIds.length > 0) {
      const existingIds = await fetchExistingProductIds(tx, normalizedProductIds)
      const missingIds = normalizedProductIds.filter((productId) => !existingIds.has(productId))
      if (missingIds.length > 0) {
        throw new Error(`Invalid product IDs: ${missingIds.join(', ')}`)
      }
    }

    const [updatedSlot] = await tx
      .update(deliverySlotInfo)
      .set({
        deliveryTime: new Date(deliveryTime),
        freezeTime: new Date(freezeTime),
        isActive: isActive !== undefined ? isActive : true,
        groupIds: validGroupIds !== undefined ? validGroupIds : [],
        ...(normalizedProductIds !== undefined && { productIds: normalizedProductIds }),
      })
      .where(eq(deliverySlotInfo.id, id))
      .returning()

    if (!updatedSlot) {
      return null
    }

    let createdSnippets: AdminVendorSnippet[] = []
    if (snippets && snippets.length > 0) {
      for (const snippet of snippets) {
        const products = await tx.query.productInfo.findMany({
          where: inArray(productInfo.id, snippet.productIds),
        })
        if (products.length !== snippet.productIds.length) {
          throw new Error(`One or more invalid product IDs in snippet "${snippet.name}"`)
        }

        const existingSnippet = await tx.query.vendorSnippets.findFirst({
          where: eq(vendorSnippets.snippetCode, snippet.name),
        })
        if (existingSnippet) {
          throw new Error(`Snippet name "${snippet.name}" already exists`)
        }

        const [createdSnippet] = await tx.insert(vendorSnippets).values({
          snippetCode: snippet.name,
          slotId: id,
          productIds: snippet.productIds,
          validTill: snippet.validTill ? new Date(snippet.validTill) : undefined,
        }).returning()

        createdSnippets.push(mapVendorSnippet(createdSnippet))
      }
    }

    return {
      slot: mapDeliverySlot(updatedSlot),
      createdSnippets,
      message: 'Slot updated successfully',
    }
  })

  return result
}

export async function deleteSlotById(id: number): Promise<AdminDeliverySlot | null> {
  const [deletedSlot] = await db
    .update(deliverySlotInfo)
    .set({ isActive: false })
    .where(eq(deliverySlotInfo.id, id))
    .returning()

  if (!deletedSlot) {
    return null
  }

  return mapDeliverySlot(deletedSlot)
}

export async function getSlotDeliverySequence(slotId: number): Promise<AdminDeliverySlot | null> {
  const slot = await db.query.deliverySlotInfo.findFirst({
    where: eq(deliverySlotInfo.id, slotId),
  })

  if (!slot) {
    return null
  }

  return mapDeliverySlot(slot)
}

export async function updateSlotDeliverySequence(slotId: number, sequence: unknown) {
  const [updatedSlot] = await db
    .update(deliverySlotInfo)
    .set({ deliverySequence: sequence as Record<string, number> })
    .where(eq(deliverySlotInfo.id, slotId))
    .returning({
      id: deliverySlotInfo.id,
      deliverySequence: deliverySlotInfo.deliverySequence,
    })

  return updatedSlot || null
}

export async function updateSlotCapacity(slotId: number, isCapacityFull: boolean): Promise<AdminUpdateSlotCapacityResult | null> {
  const [updatedSlot] = await db
    .update(deliverySlotInfo)
    .set({ isCapacityFull })
    .where(eq(deliverySlotInfo.id, slotId))
    .returning()

  if (!updatedSlot) {
    return null
  }

  return {
    success: true,
    slot: mapDeliverySlot(updatedSlot),
    message: `Slot ${isCapacityFull ? 'marked as full capacity' : 'capacity reset'}`,
  }
}
