// app/Controllers/Http/DashboardController.ts
import type { HttpContext } from '@adonisjs/core/http'
import { DateTime } from 'luxon'
import { OrderStatus, ShipmentStatus } from '../../types/enum.js'
import db from '@adonisjs/lucid/services/db'
import Shipment from '#models/shipment'
import { getCustomerCounts, getOrderStatusName } from '../Helper/Helper.js'

export default class DashboardController {
  public async index({ response }: HttpContext) {
    try {
      const today = DateTime.now().toString()
      const tomorrow = DateTime.now().plus({ days: 1 }).toString()

      // 💰 Total Due (unpaid invoices)
      const totalDue = await db
        .from('orders')
        .where('is_invoice_created', true)
        .andWhere('is_invoice_paid', false)
        .sum('invoice_payable_amount as total')

      // 💵 COD Unsettled (COD collected but not yet remitted)
      const codUnsettled = await db
        .from('orders')
        .where('is_cod', true)
        .andWhere('is_cod_collected', true)
        .andWhere('is_cod_remitted', false)
        .sum('cod_amount as total')

      // 🚚 Today / Tomorrow Pickup & Delivery counts
      const [todayPickupResult] = await db
        .from('orders')
        .where('pickup_preference_date', today)
        .count('* as count')

      const [todayDeliveryResult] = await db
        .from('orders')
        .where('delivery_preference_date', today)
        .count('* as count')

      const [tomorrowPickupResult] = await db
        .from('orders')
        .where('pickup_preference_date', tomorrow)
        .count('* as count')

      const [tomorrowDeliveryResult] = await db
        .from('orders')
        .where('delivery_preference_date', tomorrow)
        .count('* as count')

      // 📦 Orders by status with id, name, and count
      const orderStatusCountsRaw = await db
        .from('orders')
        .select('status')
        .count('* as count')
        .groupBy('status')

      const orderStatusCounts = orderStatusCountsRaw.map((item) => ({
        id: Number(item.status),
        name: getOrderStatusName(Number(item.status)),
        count: Number(item.count),
      }))

      // 🧾 Monthly placed & delivered
      const monthlyStats = await db
        .from('orders')
        .select(db.raw('MONTH(created_at) as month'))
        .count('* as placed')
        .select(
          db.raw(`SUM(CASE WHEN status = ${OrderStatus.Delivered} THEN 1 ELSE 0 END) as delivered`)
        )
        .groupBy('month')

      // 🧍 Ready to Ship
      const readyToShip = await db
        .from('shipments')
        .join('customers', 'shipments.shipped_by_customer_id', 'customers.id')
        .select('customers.company_name as customer_name')
        .count('shipments.id as no_of_shipments')
        .where('shipments.status', ShipmentStatus.Created)
        .andWhere('shipments.is_manifest_uploaded', 1)
        .andWhere('shipments.is_manifest_accepted', 1)
        .groupBy('customers.company_name')
        .limit(10)

      // 🚚 Live Shipments
      const liveShipments = await Shipment.query()
        .whereIn('status', [ShipmentStatus.Shipped, ShipmentStatus.Arrived])
        .preload('shipped_by_customer_id_details') // preload customer info
        .preload('shipped_by_warehouse_id_details') // preload warehouse info
        .orderBy('shipment_date', 'desc') // latest shipment first
        .limit(10)

      const customerCounts = await getCustomerCounts()

      return response.ok({
        status: true,
        message: 'Dashboard data fetched successfully',
        data: {
          totals: {
            totalDue: Number(totalDue[0]?.total || 0),
            codUnsettled: Number(codUnsettled[0]?.total || 0),
          },
          today: {
            pickup: Number(todayPickupResult.count || 0),
            delivery: Number(todayDeliveryResult.count || 0),
          },
          tomorrow: {
            pickup: Number(tomorrowPickupResult.count || 0),
            delivery: Number(tomorrowDeliveryResult.count || 0),
          },
          orderStatusCounts,
          monthlyStats,
          readyToShip,
          liveShipments,
          customerCounts,
        },
      })
    } catch (error) {
      console.error(error)
      return response.internalServerError({
        status: false,
        message: 'Failed to fetch dashboard data',
        error: error.message,
      })
    }
  }
}