import User from '#models/user'
import { paymentCollectionReportForDriverByAdminValidator } from '#validators/reports/payment_collection_report'
import type { HttpContext } from '@adonisjs/core/http'
import { PaymentMethods, Roles, TimeZone, UserType } from '../../../types/enum.js'
import DriverTransaction from '#models/driver_transaction'
import ExcelJS from 'exceljs'
import { CURRENCY } from '../../../const/constants.js'
import { DateTime } from 'luxon'

export default class CollectionReportsController {
  async paymentCollectionReportForDriverByAdmin({ request, response }: HttpContext) {
    const {
      delivery_agent_ids,
      filter_by_payment_method,
      filter_by_payment_type,
      filter_by_payment_start_date,
      filter_by_payment_end_date,
      filter_by_customer_id,
      file_name = `payment-collection-report-${DateTime.now().setZone(TimeZone).toFormat('dd-MM-yyyy-hh-mm-a')}`,
    } = await request.validateUsing(paymentCollectionReportForDriverByAdminValidator)

    const deliveryAgents = await User.query()
      .where('role', Roles.Staff)
      .whereIn('user_type', [UserType.DeliveryBoy, UserType.Driver, UserType.DriverCumDeliveryBoy])
      .if(delivery_agent_ids && delivery_agent_ids.length > 0, (query) => {
        query.whereIn('id', delivery_agent_ids as number[])
      })
      .preload('user_details', (profileQuery) => {
        profileQuery.preload('delivery_agent_details')
      })

    const driverTransactions = await DriverTransaction.query()
      .if(delivery_agent_ids && delivery_agent_ids.length > 0, (query) => {
        query.whereIn('driver_id', delivery_agent_ids as number[])
      })
      .if(filter_by_payment_method && filter_by_payment_method.length > 0, (query) => {
        query.whereIn('payment_method', filter_by_payment_method as number[])
      })
      .if(filter_by_payment_type && filter_by_payment_type.length > 0, (query) => {
        query.where((subQuery) => {
          if ((filter_by_payment_type as string[]).includes('shipping')) {
            subQuery.orWhere('is_shipping_charge', true)
          }
          if ((filter_by_payment_type as string[]).includes('cod')) {
            subQuery.orWhere('is_cod', true)
          }
        })
      })
      .if(filter_by_payment_start_date, (query) => {
        query.where('created_at', '>=', filter_by_payment_start_date as Date)
      })
      .if(filter_by_payment_end_date, (query) => {
        query.where('created_at', '<=', filter_by_payment_end_date as Date)
      })
      .if(filter_by_customer_id && filter_by_customer_id.length > 0, (query) => {
        query.whereHas('order_details', (orderQuery) => {
          orderQuery.whereIn('created_for_id', filter_by_customer_id as number[])
        })
      })
      .preload('order_details', (orderQuery) => {
        orderQuery.preload('pickup_block_details')
        orderQuery.preload('destination_block_details')
      })
      .orderBy('created_at', 'desc')

    if (driverTransactions.length === 0) {
      return response
        .status(404)
        .send({ status: false, message: 'No transactions found for the given filters' })
    }

    // Create a new workbook
    const workbook = new ExcelJS.Workbook()

    interface Order {
      orderId: string
      noOfPackages: number
      senderName: string
      receiveName: string
      senderBlock: string
      receiverBlock: string
      amount: number
    }

    interface PaymentSection {
      methodName: string // e.g., "Cash", "Benefit pay"
      orders: Order[]
    }

    interface ReportType {
      title: string // e.g., "Shipping Charge collection report"
      sections: PaymentSection[]
    }

    interface AgentData {
      agentName: string
      cashInHand: string // e.g., "252.652 BHD"
      reports: ReportType[]
    }

    const formatOrder = (agentId: number, type: string): Order[] => {
      return driverTransactions
        .filter((t) => {
          if (t.driver_id !== agentId) return false

          switch (type) {
            case 'shippingCash':
              return t.is_shipping_charge && t.payment_method === PaymentMethods.Cash

            case 'shippingBenefit':
              return t.is_shipping_charge && t.payment_method === PaymentMethods.BenefitPay

            case 'codCash':
              return t.is_cod && t.payment_method === PaymentMethods.Cash

            case 'codBenefit':
              return t.is_cod && t.payment_method === PaymentMethods.BenefitPay

            case 'codCard':
              return t.is_cod && t.payment_method === PaymentMethods.Card

            default:
              return false
          }
        })
        .map((t) => ({
          orderId: t.order_details.generated_order_id,
          noOfPackages: t.order_details?.total_no_of_packages ?? 0,
          senderName: t.order_details.pickup_customer_name,
          receiveName: t.order_details.destination_customer_name,
          senderBlock: t.order_details.pickup_block_details.name,
          receiverBlock: t.order_details.destination_block_details.name,
          amount: t.amount,
        }))
    }

    const agents: AgentData[] = deliveryAgents
      .map((deliveryAgent) => {
        if (delivery_agent_ids && delivery_agent_ids.length > 0) {
          const report = []

          if (filter_by_payment_type && filter_by_payment_type.includes('shipping')) {
            const sections = []
            if (
              filter_by_payment_method &&
              filter_by_payment_method.includes(PaymentMethods.Cash)
            ) {
              sections.push({
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'shippingCash'),
              })
            }
            if (
              filter_by_payment_method &&
              filter_by_payment_method.includes(PaymentMethods.BenefitPay)
            ) {
              sections.push({
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'shippingBenefit'),
              })
            }

            if (!filter_by_payment_method) {
              const cashOrders = formatOrder(deliveryAgent.id, 'shippingCash')
              if (cashOrders.length > 0) {
                sections.push({
                  methodName: 'Cash',
                  orders: cashOrders,
                })
              }
              const benefitOrders = formatOrder(deliveryAgent.id, 'shippingBenefit')
              if (benefitOrders.length > 0) {
                sections.push({
                  methodName: 'Benefit pay',
                  orders: benefitOrders,
                })
              }
            }

            report.push({
              title: 'Shipping Charge collection report',
              sections: sections,
            })
          }

          if (filter_by_payment_type && filter_by_payment_type.includes('cod')) {
            const sections = []
            if (
              filter_by_payment_method &&
              filter_by_payment_method.includes(PaymentMethods.Cash)
            ) {
              sections.push({
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'codCash'),
              })
            }
            if (
              filter_by_payment_method &&
              filter_by_payment_method.includes(PaymentMethods.BenefitPay)
            ) {
              sections.push({
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'codBenefit'),
              })
            }

            if (
              filter_by_payment_method &&
              filter_by_payment_method.includes(PaymentMethods.Card)
            ) {
              const cardOrders = formatOrder(deliveryAgent.id, 'codCard')
              sections.push({
                methodName: 'Card',
                orders: cardOrders,
              })
            }

            if (!filter_by_payment_method) {
              const cashOrders = formatOrder(deliveryAgent.id, 'codCash')
              if (cashOrders.length > 0) {
                sections.push({
                  methodName: 'Cash',
                  orders: cashOrders,
                })
              }
              const benefitOrders = formatOrder(deliveryAgent.id, 'codBenefit')
              if (benefitOrders.length > 0) {
                sections.push({
                  methodName: 'Benefit pay',
                  orders: benefitOrders,
                })
              }

              const cardOrders = formatOrder(deliveryAgent.id, 'codCard')
              if (cardOrders.length > 0) {
                sections.push({
                  methodName: 'Card',
                  orders: cardOrders,
                })
              }
            }

            report.push({
              title: 'COD Amount collection report',
              sections: sections,
            })
          }

          if (!filter_by_payment_method && !filter_by_payment_type) {
            const shippingSections = [
              {
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'shippingCash'),
              },
              {
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'shippingBenefit'),
              },
            ]
            report.push({
              title: 'Shipping Charge collection report',
              sections: shippingSections,
            })

            const codSections = [
              {
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'codCash'),
              },
              {
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'codBenefit'),
              },
              { methodName: 'Card', orders: formatOrder(deliveryAgent.id, 'codCard') },
            ]
            report.push({
              title: 'COD Amount collection report',
              sections: codSections,
            })
          }

          if (filter_by_payment_method && !filter_by_payment_type) {
            const shippingSections = []
            const codSections = []

            if (filter_by_payment_method.includes(PaymentMethods.Cash)) {
              shippingSections.push({
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'shippingCash'),
              })
              codSections.push({
                methodName: 'Cash',
                orders: formatOrder(deliveryAgent.id, 'codCash'),
              })
            }
            if (filter_by_payment_method.includes(PaymentMethods.BenefitPay)) {
              shippingSections.push({
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'shippingBenefit'),
              })
              codSections.push({
                methodName: 'Benefit pay',
                orders: formatOrder(deliveryAgent.id, 'codBenefit'),
              })
            }

            if (filter_by_payment_method.includes(PaymentMethods.Card)) {
              codSections.push({
                methodName: 'Card',
                orders: formatOrder(deliveryAgent.id, 'codCard'),
              })
            }

            report.push({
              title: 'Shipping Charge collection report',
              sections: shippingSections,
            })
            report.push({
              title: 'COD Amount collection report',
              sections: codSections,
            })
          }

          return {
            agentName: `${deliveryAgent.first_name} ${deliveryAgent.last_name}`,
            cashInHand: deliveryAgent.user_details?.delivery_agent_details
              ? `${deliveryAgent.user_details.delivery_agent_details.cash_in_hand} ${CURRENCY}`
              : '0.000 ' + CURRENCY,
            reports: report,
          }
        } else {
          const shippingChargeCash = formatOrder(deliveryAgent.id, 'shippingCash')
          const shippingChargeBenefitPay = formatOrder(deliveryAgent.id, 'shippingBenefit')
          const codAmountCash = formatOrder(deliveryAgent.id, 'codCash')
          const codAmountBenefitPay = formatOrder(deliveryAgent.id, 'codBenefit')
          const codAmountCard = formatOrder(deliveryAgent.id, 'codCard')

          if (
            shippingChargeCash.length === 0 &&
            shippingChargeBenefitPay.length === 0 &&
            codAmountCash.length === 0 &&
            codAmountBenefitPay.length === 0 &&
            codAmountCard.length === 0
          ) {
            //ignore agent if no transactions, return null gives error in excel generation
          } else {
            const report = []

            if (filter_by_payment_type && filter_by_payment_type.includes('shipping')) {
              const sections = []
              if (
                filter_by_payment_method &&
                filter_by_payment_method.includes(PaymentMethods.Cash)
              ) {
                sections.push({
                  methodName: 'Cash',
                  orders: formatOrder(deliveryAgent.id, 'shippingCash'),
                })
              }
              if (
                filter_by_payment_method &&
                filter_by_payment_method.includes(PaymentMethods.BenefitPay)
              ) {
                sections.push({
                  methodName: 'Benefit pay',
                  orders: formatOrder(deliveryAgent.id, 'shippingBenefit'),
                })
              }
              report.push({
                title: 'Shipping Charge collection report',
                sections: sections,
              })
            } else {
              if (shippingChargeCash.length > 0 || shippingChargeBenefitPay.length > 0) {
                const sections = []
                if (shippingChargeCash.length > 0) {
                  sections.push({
                    methodName: 'Cash',
                    orders: formatOrder(deliveryAgent.id, 'shippingCash'),
                  })
                }
                if (shippingChargeBenefitPay.length > 0) {
                  sections.push({
                    methodName: 'Benefit pay',
                    orders: formatOrder(deliveryAgent.id, 'shippingBenefit'),
                  })
                }

                report.push({
                  title: 'Shipping Charge collection report',
                  sections: sections,
                })
              }
            }

            if (filter_by_payment_type && filter_by_payment_type.includes('cod')) {
              const sections = []
              if (
                filter_by_payment_method &&
                filter_by_payment_method.includes(PaymentMethods.Cash)
              ) {
                sections.push({
                  methodName: 'Cash',
                  orders: formatOrder(deliveryAgent.id, 'codCash'),
                })
              }
              if (
                filter_by_payment_method &&
                filter_by_payment_method.includes(PaymentMethods.BenefitPay)
              ) {
                sections.push({
                  methodName: 'Benefit pay',
                  orders: formatOrder(deliveryAgent.id, 'codBenefit'),
                })
              }
              if (
                filter_by_payment_method &&
                filter_by_payment_method.includes(PaymentMethods.Card)
              ) {
                sections.push({
                  methodName: 'Card',
                  orders: formatOrder(deliveryAgent.id, 'codCard'),
                })
              }
              report.push({
                title: 'COD Amount collection report',
                sections: sections,
              })
            } else {
              if (codAmountCash.length > 0 || codAmountBenefitPay.length > 0) {
                const sections = []
                if (codAmountCash.length > 0) {
                  sections.push({
                    methodName: 'Cash',
                    orders: formatOrder(deliveryAgent.id, 'codCash'),
                  })
                }
                if (codAmountBenefitPay.length > 0) {
                  sections.push({
                    methodName: 'Benefit pay',
                    orders: formatOrder(deliveryAgent.id, 'codBenefit'),
                  })
                }

                if (codAmountCard.length > 0) {
                  sections.push({
                    methodName: 'Card',
                    orders: formatOrder(deliveryAgent.id, 'codCard'),
                  })
                }

                report.push({
                  title: 'COD Amount collection report',
                  sections: sections,
                })
              }
            }

            return {
              agentName: `${deliveryAgent.first_name} ${deliveryAgent.last_name}`,
              cashInHand: deliveryAgent.user_details?.delivery_agent_details
                ? `${deliveryAgent.user_details.delivery_agent_details.cash_in_hand} ${CURRENCY}`
                : '0.000 ' + CURRENCY,
              reports: report,
            }
          }
        }
      })
      .filter(Boolean) as AgentData[]

    // return agents

    // --- Excel Generation Logic ---

    agents.forEach((agent) => {
      const sheet = workbook.addWorksheet(agent.agentName, {
        properties: { tabColor: { argb: '90FCB7' } },
      })

      // 1. Define Columns
      sheet.columns = [
        { header: '', key: 'sl_no', width: 8 }, // A
        { header: '', key: 'order_id', width: 20 }, // B
        { header: '', key: 'packages', width: 15 }, // C
        { header: '', key: 'sender_n', width: 26 }, // D
        { header: '', key: 'recv_n', width: 26 }, // E
        { header: '', key: 'sender_b', width: 26 }, // F
        { header: '', key: 'recv_b', width: 26 }, // G
        { header: '', key: 'amount', width: 20 }, // H
      ]

      // 2. Add Agent Header Info
      // Row 1: Agent Name
      const row1 = sheet.addRow([`Delivery Agent Name: ${agent.agentName}`])
      row1.eachCell((cell) => {
        cell.font = { bold: true, size: 20 }
        cell.alignment = { horizontal: 'center', vertical: 'middle' }
        cell.fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'D9D9D9' },
        }
      })

      // Merge cells for agent name
      sheet.mergeCells(`A${row1.number}:H${row1.number}`)

      // Row 2: Cash in hand
      const row2 = sheet.addRow([`cash in hand : ${agent.cashInHand}`])
      row2.eachCell((cell) => {
        cell.font = { bold: true, size: 18 }
        cell.alignment = { horizontal: 'center', vertical: 'middle' }
        cell.fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'D9D9D9' },
        }
      })

      // Merge cells for cash in hand
      sheet.mergeCells(`A${row2.number}:H${row2.number}`)

      sheet.addRow([]) // Empty row after header info

      // 3. Loop through Reports (Shipping Charge / COD Amount)
      agent.reports.forEach((report) => {
        // Report Title
        const titleRow = sheet.addRow([report.title])
        titleRow.font = { bold: true, size: 18, color: { argb: '4D93D9' } }
        sheet.mergeCells(`A${titleRow.number}:H${titleRow.number}`) // Merge title across columns
        titleRow.alignment = { horizontal: 'center', vertical: 'middle' }

        // Empty row after title
        sheet.addRow([])

        // Loop through Payment Methods (Cash / Benefit Pay)
        report.sections.forEach((section) => {
          // Payment Method Header
          const methodRow = sheet.addRow([`Payment Method: ${section.methodName}`])
          sheet.mergeCells(`A${methodRow.number}:H${methodRow.number + 1}`) // Merge across columns
          methodRow.eachCell((cell) => {
            cell.font = { bold: true, size: 16, color: { argb: '595959' } }
            cell.alignment = { horizontal: 'center', vertical: 'middle' }
            cell.border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' },
            }
          })
          // Table Headers
          const headerRow = sheet.addRow([
            'Sl No',
            'Order ID',
            'No of Packages',
            'Sender name',
            'Receiver name',
            'Sender block',
            'Receiver block',
            `Amount(${CURRENCY})`,
          ])

          // Style the Table Header
          headerRow.eachCell((cell) => {
            cell.font = { bold: true, size: 14 }
            cell.alignment = { horizontal: 'center' }
            cell.border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' },
            }
          })

          // Data Rows
          let totalAmount = 0
          section.orders.forEach((order, index) => {
            const row = sheet.addRow([
              index + 1,
              order.orderId,
              order.noOfPackages,
              order.senderName,
              order.receiveName,
              order.senderBlock,
              order.receiverBlock,
              order.amount,
            ])

            row.font = { size: 12 }

            totalAmount += Number(order.amount)

            // Style Data Cells
            row.eachCell((cell) => {
              cell.alignment = { horizontal: 'center' }
              cell.border = {
                top: { style: 'thin' },
                left: { style: 'thin' },
                bottom: { style: 'thin' },
                right: { style: 'thin' },
              }
            })
          })

          // Total Row
          const totalRow = sheet.addRow([
            'Total',
            '',
            '',
            '',
            '',
            '',
            '',
            totalAmount.toFixed(3), // Keeping precision as seen in file
          ])

          // merge cells for "Total" label
          sheet.mergeCells(`A${totalRow.number}:G${totalRow.number}`)

          // Style Total Row
          totalRow.getCell(1).font = { bold: true, size: 14 } // "Total" label
          totalRow.getCell(8).font = { bold: true, size: 14 } // Amount value
          totalRow.eachCell((cell) => {
            cell.alignment = { horizontal: 'center' }
            cell.border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' },
            }
          })

          // Add spacer rows between tables
          sheet.addRow([])
          sheet.addRow([])
        })
      })
    })

    const buffer = await workbook.xlsx.writeBuffer()

    response.header(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response.append('Content-Disposition', `attachment; filename="${file_name}.xlsx"`)
    return response.send(buffer)
  }
}