import { db } from '../db/db_index'
import { productInfo, storeInfo, units } from '../db/schema'
import { and, eq, sql } from 'drizzle-orm'
import type { InferSelectModel } from 'drizzle-orm'
import type { UserStoreDetailData, UserStoreProductData, UserStoreSummaryData, StoreSummary } from '@packages/shared'

type StoreRow = InferSelectModel<typeof storeInfo>
type StoreProductRow = {
  id: number
  name: string
  shortDescription: string | null
  price: string
  marketPrice: string | null
  images: unknown
  isOutOfStock: boolean
  incrementStep: number
  unitShortNotation: string
  productQuantity: number
}

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

export async function getStoreSummaries(): Promise<UserStoreSummaryData[]> {
  const storesData = await db
    .select({
      id: storeInfo.id,
      name: storeInfo.name,
      description: storeInfo.description,
      imageUrl: storeInfo.imageUrl,
      productCount: sql<number>`count(${productInfo.id})`.as('productCount'),
    })
    .from(storeInfo)
    .leftJoin(
      productInfo,
      and(eq(productInfo.storeId, storeInfo.id), eq(productInfo.isSuspended, false))
    )
    .groupBy(storeInfo.id)

  const storesWithDetails = await Promise.all(
    storesData.map(async (store) => {
      const sampleProducts = await db
        .select({
          id: productInfo.id,
          name: productInfo.name,
          images: productInfo.images,
        })
        .from(productInfo)
        .where(and(eq(productInfo.storeId, store.id), eq(productInfo.isSuspended, false)))
        .limit(3)

      return {
        id: store.id,
        name: store.name,
        description: store.description ?? null,
        imageUrl: store.imageUrl ?? null,
        productCount: store.productCount || 0,
        sampleProducts: sampleProducts.map((product) => ({
          id: product.id,
          name: product.name,
          images: getStringArray(product.images),
        })),
      }
    })
  )

  return storesWithDetails
}

export async function getStoreDetail(storeId: number): Promise<UserStoreDetailData | null> {
  const storeData = await db.query.storeInfo.findFirst({
    where: eq(storeInfo.id, storeId),
    columns: {
      id: true,
      name: true,
      description: true,
      imageUrl: true,
    },
  })

  if (!storeData) {
    return null
  }

  const productsData = await db
    .select({
      id: productInfo.id,
      name: productInfo.name,
      shortDescription: productInfo.shortDescription,
      price: productInfo.price,
      marketPrice: productInfo.marketPrice,
      images: productInfo.images,
      isOutOfStock: productInfo.isOutOfStock,
      incrementStep: productInfo.incrementStep,
      unitShortNotation: units.shortNotation,
      productQuantity: productInfo.productQuantity,
    })
    .from(productInfo)
    .innerJoin(units, eq(productInfo.unitId, units.id))
    .where(and(eq(productInfo.storeId, storeId), eq(productInfo.isSuspended, false)))

  const products = productsData.map((product: StoreProductRow): UserStoreProductData => ({
    id: product.id,
    name: product.name,
    shortDescription: product.shortDescription ?? null,
    price: product.price.toString(),
    marketPrice: product.marketPrice ? product.marketPrice.toString() : null,
    incrementStep: product.incrementStep,
    unit: product.unitShortNotation,
    unitNotation: product.unitShortNotation,
    images: getStringArray(product.images),
    isOutOfStock: product.isOutOfStock,
    productQuantity: product.productQuantity,
  }))

  return {
    store: {
      id: storeData.id,
      name: storeData.name,
      description: storeData.description ?? null,
      imageUrl: storeData.imageUrl ?? null,
    },
    products,
  }
}

/**
 * Get simple store summary (id, name, description only)
 * Used for common API endpoints
 */
export async function getStoresSummary(): Promise<StoreSummary[]> {
  return db.query.storeInfo.findMany({
    columns: {
      id: true,
      name: true,
      description: true,
    },
  })
}
