// Store Helpers - Database operations for cache initialization
// These are used by stores in apps/backend/src/stores/

import { db } from '../db/db_index'
import {
  homeBanners,
  productInfo,
  units,
  deliverySlotInfo,
  specialDeals,
  storeInfo,
  productTags,
  productTagInfo,
  userIncidents,
} from '../db/schema'
import { eq, and, gt, sql, inArray, isNotNull, asc, sum } from 'drizzle-orm'

// ============================================================================
// BANNER STORE HELPERS
// ============================================================================

export interface BannerData {
  id: number
  name: string
  imageUrl: string | null
  serialNum: number | null
  productIds: number[] | null
  createdAt: Date
}

export async function getAllBannersForCache(): Promise<BannerData[]> {
  return db.query.homeBanners.findMany({
    where: isNotNull(homeBanners.serialNum),
    orderBy: asc(homeBanners.serialNum),
  })
}

// ============================================================================
// PRODUCT STORE HELPERS
// ============================================================================

export interface ProductBasicData {
  id: number
  name: string
  shortDescription: string | null
  longDescription: string | null
  price: string
  marketPrice: string | null
  images: unknown
  isOutOfStock: boolean
  storeId: number | null
  unitShortNotation: string
  incrementStep: number
  productQuantity: number
  isFlashAvailable: boolean
  flashPrice: string | null
}

export interface StoreBasicData {
  id: number
  name: string
  description: string | null
}

export interface DeliverySlotData {
  productId: number
  id: number
  deliveryTime: Date
  freezeTime: Date
  isCapacityFull: boolean
}

export interface SpecialDealData {
  productId: number
  quantity: string
  price: string
  validTill: Date
}

export interface ProductTagData {
  productId: number
  tagName: string
}

export async function getAllProductsForCache(): Promise<ProductBasicData[]> {
  return db
    .select({
      id: productInfo.id,
      name: productInfo.name,
      shortDescription: productInfo.shortDescription,
      longDescription: productInfo.longDescription,
      price: productInfo.price,
      marketPrice: productInfo.marketPrice,
      images: productInfo.images,
      isOutOfStock: productInfo.isOutOfStock,
      storeId: productInfo.storeId,
      unitShortNotation: units.shortNotation,
      incrementStep: productInfo.incrementStep,
      productQuantity: productInfo.productQuantity,
      isFlashAvailable: productInfo.isFlashAvailable,
      flashPrice: productInfo.flashPrice,
    })
    .from(productInfo)
    .innerJoin(units, eq(productInfo.unitId, units.id))
}

export async function getAllStoresForCache(): Promise<StoreBasicData[]> {
  return db.query.storeInfo.findMany({
    columns: { id: true, name: true, description: true },
  })
}

export async function getAllDeliverySlotsForCache(): Promise<DeliverySlotData[]> {
  const slots = await db.query.deliverySlotInfo.findMany({
    where: and(
      eq(deliverySlotInfo.isActive, true),
      eq(deliverySlotInfo.isCapacityFull, false),
      gt(deliverySlotInfo.deliveryTime, sql`NOW()`)
    ),
  })

  // Flatten slots with their product IDs
  const result: DeliverySlotData[] = []
  for (const slot of slots) {
    const productIds = slot.productIds || []
    for (const productId of productIds) {
      result.push({
        productId,
        id: slot.id,
        deliveryTime: slot.deliveryTime,
        freezeTime: slot.freezeTime,
        isCapacityFull: slot.isCapacityFull,
      })
    }
  }

  return result
}

export async function getAllSpecialDealsForCache(): Promise<SpecialDealData[]> {
  return db
    .select({
      productId: specialDeals.productId,
      quantity: specialDeals.quantity,
      price: specialDeals.price,
      validTill: specialDeals.validTill,
    })
    .from(specialDeals)
    .where(gt(specialDeals.validTill, sql`NOW()`))
}

export async function getAllProductTagsForCache(): Promise<ProductTagData[]> {
  return db
    .select({
      productId: productTags.productId,
      tagName: productTagInfo.tagName,
    })
    .from(productTags)
    .innerJoin(productTagInfo, eq(productTags.tagId, productTagInfo.id))
}

// ============================================================================
// PRODUCT TAG STORE HELPERS
// ============================================================================

export interface TagBasicData {
  id: number
  tagName: string
  tagDescription: string | null
  imageUrl: string | null
  isDashboardTag: boolean
  relatedStores: unknown
}

export interface TagProductMapping {
  tagId: number
  productId: number
}

export async function getAllTagsForCache(): Promise<TagBasicData[]> {
  return db
    .select({
      id: productTagInfo.id,
      tagName: productTagInfo.tagName,
      tagDescription: productTagInfo.tagDescription,
      imageUrl: productTagInfo.imageUrl,
      isDashboardTag: productTagInfo.isDashboardTag,
      relatedStores: productTagInfo.relatedStores,
    })
    .from(productTagInfo)
}

export async function getAllTagProductMappings(): Promise<TagProductMapping[]> {
  return db
    .select({
      tagId: productTags.tagId,
      productId: productTags.productId,
    })
    .from(productTags)
}

// ============================================================================
// SLOT STORE HELPERS
// ============================================================================

export interface SlotWithProductsData {
  id: number
  deliveryTime: Date
  freezeTime: Date
  isActive: boolean
  isCapacityFull: boolean
  products: Array<{
    id: number
    name: string
    productQuantity: number
    shortDescription: string | null
    price: string
    marketPrice: string | null
    unit: { shortNotation: string } | null
    store: { id: number; name: string; description: string | null } | null
    images: unknown
    isOutOfStock: boolean
    storeId: number | null
  }>
}

export async function getAllSlotsWithProductsForCache(): Promise<SlotWithProductsData[]> {
  const now = new Date()
  
  // Get all active future slots
  const slots = await db.query.deliverySlotInfo.findMany({
    where: and(
      eq(deliverySlotInfo.isActive, true),
      gt(deliverySlotInfo.deliveryTime, now)
    ),
    orderBy: asc(deliverySlotInfo.deliveryTime),
  })

  // 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 productsData = productIdsArray.length > 0 
    ? await db.query.productInfo.findMany({
        where: inArray(productInfo.id, productIdsArray),
        with: {
          unit: true,
          store: true,
        },
      })
    : []

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

  // Build the result
  return slots.map(slot => ({
    id: slot.id,
    deliveryTime: slot.deliveryTime,
    freezeTime: slot.freezeTime,
    isActive: slot.isActive,
    isCapacityFull: slot.isCapacityFull,
    products: (slot.productIds || [])
      .map(productId => productMap.get(productId))
      .filter((p): p is NonNullable<typeof p> => p != null)
      .map(product => ({
        id: product.id,
        name: product.name,
        productQuantity: product.productQuantity,
        shortDescription: product.shortDescription,
        price: String(product.price ?? '0'),
        marketPrice: product.marketPrice ? String(product.marketPrice) : null,
        unit: product.unit ? { shortNotation: product.unit.shortNotation } : null,
        store: product.store ? { 
          id: product.store.id, 
          name: product.store.name, 
          description: product.store.description 
        } : null,
        images: product.images,
        isOutOfStock: product.isOutOfStock,
        storeId: product.storeId,
      })),
  })) as SlotWithProductsData[]
}

// ============================================================================
// USER NEGATIVITY STORE HELPERS
// ============================================================================

export interface UserNegativityData {
  userId: number
  totalNegativityScore: number
}

export async function getAllUserNegativityScores(): Promise<UserNegativityData[]> {
  return db
    .select({
      userId: userIncidents.userId,
      totalNegativityScore: sum(userIncidents.negativityScore).mapWith(Number),
    })
    .from(userIncidents)
    .groupBy(userIncidents.userId)
}

export async function getUserNegativityScore(userId: number): Promise<number> {
  const [result] = await db
    .select({
      totalNegativityScore: sum(userIncidents.negativityScore).mapWith(Number),
    })
    .from(userIncidents)
    .where(eq(userIncidents.userId, userId))
    .limit(1)

  return result?.totalNegativityScore || 0
}
