health-petal/packages/data-manager-sqlite/src/dashboard.ts

import { count, sum, eq, and, gte, lt, lte } from 'drizzle-orm'
import { db } from './db-instance'
import { products } from './schema/products'
import { bills } from './schema/bills'
import { stockBatches } from './schema/stockBatches'
import { units } from './schema/units'

export interface DashboardStats {
	totalProducts: number
	lowStockProducts: number
	todaySales: number
	monthlyRevenue: number
	weeklyRevenue: number
	todayBillsCount: number
	totalInvoices: number
	totalStockUnits: number
	outOfStock: number
	criticalStock: number
	expiringBatches: number
	categoryBreakdown: { category: string; count: number }[]
	recentProducts: {
		id: number
		name: string
		brand: string
		category: string
		selling_price: number
		stock: number
		reorder_level: number
		unit_name: string
	}[]
}

export type DashboardRepo = {
	getStats: (enterpriseId: number) => Promise<DashboardStats>
}

function todayStr(): string {
	return new Date().toISOString().slice(0, 10)
}

function daysLater(n: number): string {
	const d = new Date()
	d.setDate(d.getDate() + n)
	return d.toISOString().slice(0, 10)
}

function monthStartStr(): string {
	const d = new Date()
	return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-01`
}

function nextMonthStartStr(): string {
	const d = new Date()
	const next = new Date(d.getFullYear(), d.getMonth() + 1, 1)
	return next.toISOString().slice(0, 10)
}

function weekStartStr(): string {
	const d = new Date()
	const day = d.getDay()
	const diff = d.getDate() - day + (day === 0 ? -6 : 1)
	const monday = new Date(d.getFullYear(), d.getMonth(), diff)
	return monday.toISOString().slice(0, 10)
}

export function createDashboardRepo(): { repo: DashboardRepo } {
	const repo: DashboardRepo = {
		getStats(enterpriseId) {
			const today = todayStr()
			const tomorrow = daysLater(1)
			const monthStart = monthStartStr()
			const nextMonthStart = nextMonthStartStr()
			const weekStart = weekStartStr()
			const thirtyDays = daysLater(30)

			const totalProducts = db
				.select({ count: count() })
				.from(products)
				.where(eq(products.enterpriseId, enterpriseId))
				.get()!

			const todaySales = db
				.select({ total: sum(bills.total) })
				.from(bills)
				.where(
					and(
						eq(bills.enterpriseId, enterpriseId),
						gte(bills.createdAt, today),
						lt(bills.createdAt, tomorrow),
					),
				)
				.get()!

			const monthlyRevenue = db
				.select({ total: sum(bills.total) })
				.from(bills)
				.where(
					and(
						eq(bills.enterpriseId, enterpriseId),
						gte(bills.createdAt, monthStart),
						lt(bills.createdAt, nextMonthStart),
					),
				)
				.get()!

			const weeklyRevenue = db
				.select({ total: sum(bills.total) })
				.from(bills)
				.where(
					and(
						eq(bills.enterpriseId, enterpriseId),
						gte(bills.createdAt, weekStart),
					),
				)
				.get()!

			const todayBillsCount = db
				.select({ count: count() })
				.from(bills)
				.where(
					and(
						eq(bills.enterpriseId, enterpriseId),
						gte(bills.createdAt, today),
						lt(bills.createdAt, tomorrow),
					),
				)
				.get()!

			const totalInvoices = db
				.select({ count: count() })
				.from(bills)
				.where(eq(bills.enterpriseId, enterpriseId))
				.get()!

			const totalStockUnits = db
				.select({ total: sum(stockBatches.quantity) })
				.from(stockBatches)
				.where(eq(stockBatches.enterpriseId, enterpriseId))
				.get()!

			const expiringBatches = db
				.select({ count: count() })
				.from(stockBatches)
				.where(
					and(
						eq(stockBatches.enterpriseId, enterpriseId),
						gte(stockBatches.expiry, today),
						lte(stockBatches.expiry, thirtyDays),
					),
				)
				.get()!

			const categoryBreakdown = db
				.select({ category: products.category, count: count() })
				.from(products)
				.where(eq(products.enterpriseId, enterpriseId))
				.groupBy(products.category)
				.all()

			const allProducts = db
				.select()
				.from(products)
				.innerJoin(units, eq(units.id, products.unitId))
				.where(eq(products.enterpriseId, enterpriseId))
				.all()

			const stockRows = db
				.select({ productId: stockBatches.productId, totalStock: sum(stockBatches.quantity) })
				.from(stockBatches)
				.where(eq(stockBatches.enterpriseId, enterpriseId))
				.groupBy(stockBatches.productId)
				.all()

			const stockMap = new Map<number, number>()
			for (const row of stockRows) {
				stockMap.set(row.productId, Number(row.totalStock) || 0)
			}

			let lowStockProducts = 0
			let outOfStock = 0
			let criticalStock = 0

			const recentProducts: DashboardStats['recentProducts'] = []

			for (const row of allProducts) {
				const product = row.products
				const unit = row.units
				const stock = stockMap.get(product.id) ?? 0

				if (stock <= 0) {
					outOfStock++
				} else if (stock <= product.reorderLevel * 0.5) {
					criticalStock++
				} else if (stock <= product.reorderLevel) {
					lowStockProducts++
				}

				recentProducts.push({
					id: product.id,
					name: product.name,
					brand: product.brand,
					category: product.category,
					selling_price: product.sellingPrice,
					stock,
					reorder_level: product.reorderLevel,
					unit_name: unit.name,
				})
			}

			recentProducts.sort((a, b) => a.stock - b.stock || a.name.localeCompare(b.name))

			return Promise.resolve({
				totalProducts: Number(totalProducts.count),
				lowStockProducts,
				todaySales: Number(todaySales.total) || 0,
				monthlyRevenue: Number(monthlyRevenue.total) || 0,
				weeklyRevenue: Number(weeklyRevenue.total) || 0,
				todayBillsCount: Number(todayBillsCount.count),
				totalInvoices: Number(totalInvoices.count),
				totalStockUnits: Number(totalStockUnits.total) || 0,
				outOfStock,
				criticalStock,
				expiringBatches: Number(expiringBatches.count),
				categoryBreakdown,
				recentProducts: recentProducts.slice(0, 10),
			})
		},
	}

	return { repo }
}