import prisma from "../config/prisma.js";
import { Prisma } from "../generated/prisma/client.js";

export class CustomerRepository {
  async create(data: Prisma.CustomerUncheckedCreateInput) {
    return prisma.customer.create({ data });
  }

  async findAll(skip?: number, take?: number, search?: string, status?: boolean) {
    const where: Prisma.CustomerWhereInput = { deletedAt: null };

    if (search) {
      where.OR = [
        { name: { contains: search } },
        { tamilName: { contains: search } },
        { mobileNo: { contains: search } },
      ];
    }

    if (status !== undefined) {
      where.status = status;
    }

    const [items, total] = await Promise.all([
      prisma.customer.findMany({
        where,
        ...(skip !== undefined ? { skip } : {}),
        ...(take !== undefined ? { take } : {}),
        include: {
          state: true,
          district: true,
        },
        orderBy: { id: 'desc' },
      }),
      prisma.customer.count({ where }),
    ]);

    return { items, total };
  }

  async findById(id: bigint) {
    return prisma.customer.findFirst({
      where: { id, deletedAt: null },
      include: {
        state: true,
        district: true,
      },
    });
  }

  async findByNameAndMobileNo(name: string, mobileNo: string, excludeId?: bigint) {
    return prisma.customer.findFirst({
      where: {
        name: {
          equals: name.trim(),
        },
        mobileNo: {
          equals: mobileNo.trim(),
        },
        deletedAt: null,
        ...(excludeId ? { id: { not: excludeId } } : {}),
      },
    });
  }


  async update(id: bigint, data: Prisma.CustomerUncheckedUpdateInput) {
    return prisma.customer.update({ where: { id }, data });
  }

  async softDelete(id: bigint) {
    return prisma.customer.update({
      where: { id },
      data: { deletedAt: new Date() },
    });
  }

  // this for customer detail for dashboard
  async activeCustomerCount() {
    const customers = await prisma.customer.count({ where: { status: true, deletedAt: null } });
    return customers;
  }

  // Dashboard: total pending receipts = sum of all customers' outstandingBalance
  async getTotalOutstandingBalance(): Promise<number> {
    const result = await prisma.customer.aggregate({
      _sum: { outstandingBalance: true },
      where: { status: true, deletedAt: null },
    });
    return Number(result._sum.outstandingBalance ?? 0);
  }

  // Dashboard: active, inactive, and total breakdown for customer receipts
  async getCustomerReceiptsBreakdown(): Promise<{ active: number; inactive: number; total: number }> {
    const [activeRes, inactiveRes] = await Promise.all([
      prisma.customer.aggregate({
        _sum: { outstandingBalance: true },
        where: { status: true, deletedAt: null },
      }),
      prisma.customer.aggregate({
        _sum: { outstandingBalance: true },
        where: { status: false, deletedAt: null },
      }),
    ]);
    const active = Number(activeRes._sum.outstandingBalance ?? 0);
    const inactive = Number(inactiveRes._sum.outstandingBalance ?? 0);
    return { active, inactive, total: active + inactive };
  }

  async getCustomerOpeningBalance(
    customerId?: bigint,
    dateBefore?: Date
  ): Promise<number> {
    const customerWhere: any = { deletedAt: null };
    if (customerId) customerWhere.id = customerId;

    // Sum openingBalance from Customer table
    const openingAgg = await prisma.customer.aggregate({
      _sum: { openingBalance: true },
      where: customerWhere,
    });
    let balance = Number(openingAgg._sum.openingBalance ?? 0);

    if (!dateBefore) return balance;

    const saleWhere: any = { deletedAt: null, invoiceDate: { lt: dateBefore }, customerId: { not: null } };
    if (customerId) saleWhere.customerId = customerId;

    const saleAgg = await prisma.sale.aggregate({
      _sum: { grandTotal: true, receivedAmount: true },
      where: saleWhere,
    });
    balance += Number(saleAgg._sum.grandTotal ?? 0);
    balance -= Number(saleAgg._sum.receivedAmount ?? 0);

    const receiptWhere: any = { deletedAt: null, receiptDate: { lt: dateBefore } };
    if (customerId) receiptWhere.customerId = customerId;

    const receiptAgg = await prisma.customerReceipt.aggregate({
      _sum: { amount: true },
      where: receiptWhere,
    });
    balance -= Number(receiptAgg._sum.amount ?? 0);

    // SaleReturns — join through saleItem -> sale to get customerId
    const returnItems = await prisma.saleReturn.findMany({
      where: {
        returnDate: { lt: dateBefore },
        saleItem: {
          sale: customerId ? { customerId, deletedAt: null } : { deletedAt: null },
        },
      },
      select: { returnAmount: true },
    });
    const returnTotal = returnItems.reduce((sum, r) => sum + Number(r.returnAmount ?? 0), 0);
    balance -= returnTotal;

    return balance;
  }

  async getCustomerLedgerTransactions(
    customerId?: bigint,
    dateFrom?: Date,
    dateTo?: Date
  ) {
    const saleWhere: any = { deletedAt: null, customerId: { not: null } };
    if (customerId) saleWhere.customerId = customerId;
    if (dateFrom || dateTo) {
      saleWhere.invoiceDate = {};
      if (dateFrom) saleWhere.invoiceDate.gte = dateFrom;
      if (dateTo) saleWhere.invoiceDate.lte = dateTo;
    }

    const receiptWhere: any = { deletedAt: null };
    if (customerId) receiptWhere.customerId = customerId;
    if (dateFrom || dateTo) {
      receiptWhere.receiptDate = {};
      if (dateFrom) receiptWhere.receiptDate.gte = dateFrom;
      if (dateTo) receiptWhere.receiptDate.lte = dateTo;
    }

    const returnWhere: any = {
      saleItem: {
        sale: customerId ? { customerId, deletedAt: null } : { customerId: { not: null }, deletedAt: null },
      },
    };
    if (dateFrom || dateTo) {
      returnWhere.returnDate = {};
      if (dateFrom) returnWhere.returnDate.gte = dateFrom;
      if (dateTo) returnWhere.returnDate.lte = dateTo;
    }

    const [sales, receipts, returns] = await Promise.all([
      prisma.sale.findMany({
        where: saleWhere,
        select: {
          id: true,
          invoiceNo: true,
          invoiceDate: true,
          grandTotal: true,
          receivedAmount: true,
          walkInName: true,
          customer: { select: { name: true } },
          items: {
            select: { quantity: true, bags: true }
          },
        },
        orderBy: { invoiceDate: 'asc' },
      }),
      prisma.customerReceipt.findMany({
        where: receiptWhere,
        select: {
          id: true,
          receiptDate: true,
          amount: true,
          paymentMode: true,
          referenceNo: true,
          customer: { select: { name: true } },
        },
        orderBy: { receiptDate: 'asc' },
      }),
      prisma.saleReturn.findMany({
        where: returnWhere,
        select: {
          id: true,
          returnDate: true,
          returnAmount: true,
          reason: true,
          quantity: true,
          saleItem: {
            select: {
              saleUnit: true,
              quantity: true,
              sale: {
                select: {
                  invoiceNo: true,
                  walkInName: true,
                  customer: { select: { name: true } },
                },
              },
            },
          },
        },
        orderBy: { returnDate: 'asc' },
      }),
    ]);

    type LedgerEntry = {
      date: Date;
      voucherNo: string;
      customerName: string;
      description: string;
      transactionType: string;
      debit: number;
      credit: number;
    };

    const entries: LedgerEntry[] = [];

    for (const s of sales) {
      const saleCustomerName = s.customer?.name || (s.walkInName ? `${s.walkInName} (Walk-in)` : '');
      
      let totalKg = new Prisma.Decimal(0);
      let totalBags = new Prisma.Decimal(0);
      for (const item of (s.items || [])) {
        totalKg = totalKg.add(new Prisma.Decimal(item.quantity || 0));
        totalBags = totalBags.add(new Prisma.Decimal(item.bags || 0));
      }
      const qtyStrings = [];
      if (totalKg.gt(0)) qtyStrings.push(`${totalKg.toNumber()} KG`);
      if (totalBags.gt(0)) qtyStrings.push(`${totalBags.toNumber()} BAGS`);
      const qtyDesc = qtyStrings.length > 0 ? ` - Qty: ${qtyStrings.join(', ')}` : '';

      entries.push({
        date: s.invoiceDate,
        voucherNo: s.invoiceNo,
        customerName: saleCustomerName,
        description: `Sale Invoice${qtyDesc}`,
        transactionType: 'Sale',
        debit: Number(s.grandTotal ?? 0),
        credit: 0,
      });

      if (s.receivedAmount && Number(s.receivedAmount) > 0) {
        entries.push({
          date: s.invoiceDate,
          voucherNo: s.invoiceNo,
          customerName: saleCustomerName,
          description: `Sale Payment`,
          transactionType: 'Receipt',
          debit: 0,
          credit: Number(s.receivedAmount),
        });
      }
    }

    for (const r of receipts) {
      const modeLabel = r.paymentMode === 'CASH' ? 'Cash Received'
        : r.paymentMode === 'UPI' ? 'UPI Received'
          : 'Bank Transfer';
      entries.push({
        date: r.receiptDate,
        voucherNo: r.referenceNo ?? `REC-${r.id}`,
        customerName: r.customer?.name ?? '',
        description: modeLabel,
        transactionType: 'Receipt',
        debit: 0,
        credit: Number(r.amount ?? 0),
      });
    }

    for (const ret of returns) {
      const saleNo = ret.saleItem?.sale?.invoiceNo ?? '';
      const walkInName = ret.saleItem?.sale?.walkInName;
      const custName = ret.saleItem?.sale?.customer?.name || (walkInName ? `${walkInName} (Walk-in)` : '');
      
      const isBagOnly = ret.saleItem?.saleUnit !== 'KG' && new Prisma.Decimal(ret.saleItem?.quantity || 0).lte(0);
      const returnUnit = isBagOnly ? 'BAGS' : 'KG';
      const qtyDesc = ret.quantity ? ` - Qty: ${ret.quantity} ${returnUnit}` : '';
      const reasonDesc = ret.reason ? ` (${ret.reason})` : '';

      entries.push({
        date: ret.returnDate,
        voucherNo: `SR-${ret.id}`,
        customerName: custName,
        description: `Sales Return${qtyDesc}${reasonDesc}`,
        transactionType: 'Sales Return',
        debit: 0,
        credit: Number(ret.returnAmount ?? 0),
      });
    }

    // Sort by date ascending
    entries.sort((a, b) => a.date.getTime() - b.date.getTime());

    return entries;
  }

  async getWalkInLedgerTransactions(
    dateFrom?: Date,
    dateTo?: Date
  ) {
    const saleWhere: any = { deletedAt: null, customerId: null };
    if (dateFrom || dateTo) {
      saleWhere.invoiceDate = {};
      if (dateFrom) saleWhere.invoiceDate.gte = dateFrom;
      if (dateTo) saleWhere.invoiceDate.lte = dateTo;
    }

    const returnWhere: any = {
      saleItem: {
        sale: { customerId: null, deletedAt: null },
      },
    };
    if (dateFrom || dateTo) {
      returnWhere.returnDate = {};
      if (dateFrom) returnWhere.returnDate.gte = dateFrom;
      if (dateTo) returnWhere.returnDate.lte = dateTo;
    }

    const [sales, returns] = await Promise.all([
      prisma.sale.findMany({
        where: saleWhere,
        select: {
          id: true,
          invoiceNo: true,
          invoiceDate: true,
          grandTotal: true,
          receivedAmount: true,
          walkInName: true,
          items: {
            select: { quantity: true, bags: true }
          },
        },
        orderBy: { invoiceDate: 'asc' },
      }),
      prisma.saleReturn.findMany({
        where: returnWhere,
        select: {
          id: true,
          returnDate: true,
          returnAmount: true,
          reason: true,
          quantity: true,
          saleItem: {
            select: {
              saleUnit: true,
              quantity: true,
              sale: {
                select: {
                  invoiceNo: true,
                  walkInName: true,
                },
              },
            },
          },
        },
        orderBy: { returnDate: 'asc' },
      }),
    ]);

    type LedgerEntry = {
      date: Date;
      voucherNo: string;
      customerName: string;
      description: string;
      transactionType: string;
      debit: number;
      credit: number;
    };

    const entries: LedgerEntry[] = [];

    for (const s of sales) {
      const saleCustomerName = s.walkInName ? `${s.walkInName} (Walk-in)` : 'Walk-in Customer';
      
      let totalKg = new Prisma.Decimal(0);
      let totalBags = new Prisma.Decimal(0);
      for (const item of (s.items || [])) {
        totalKg = totalKg.add(new Prisma.Decimal(item.quantity || 0));
        totalBags = totalBags.add(new Prisma.Decimal(item.bags || 0));
      }
      const qtyStrings = [];
      if (totalKg.gt(0)) qtyStrings.push(`${totalKg.toNumber()} KG`);
      if (totalBags.gt(0)) qtyStrings.push(`${totalBags.toNumber()} BAGS`);
      const qtyDesc = qtyStrings.length > 0 ? ` - Qty: ${qtyStrings.join(', ')}` : '';

      entries.push({
        date: s.invoiceDate,
        voucherNo: s.invoiceNo,
        customerName: saleCustomerName,
        description: `Sale Invoice${qtyDesc}`,
        transactionType: 'Sale',
        debit: Number(s.grandTotal ?? 0),
        credit: 0,
      });

      if (s.receivedAmount && Number(s.receivedAmount) > 0) {
        entries.push({
          date: s.invoiceDate,
          voucherNo: s.invoiceNo,
          customerName: saleCustomerName,
          description: `Sale Payment`,
          transactionType: 'Receipt',
          debit: 0,
          credit: Number(s.receivedAmount),
        });
      }
    }

    for (const ret of returns) {
      const walkInName = ret.saleItem?.sale?.walkInName;
      const custName = walkInName ? `${walkInName} (Walk-in)` : 'Walk-in Customer';
      
      const isBagOnly = ret.saleItem?.saleUnit !== 'KG' && new Prisma.Decimal(ret.saleItem?.quantity || 0).lte(0);
      const returnUnit = isBagOnly ? 'BAGS' : 'KG';
      const qtyDesc = ret.quantity ? ` - Qty: ${ret.quantity} ${returnUnit}` : '';
      const reasonDesc = ret.reason ? ` (${ret.reason})` : '';

      entries.push({
        date: ret.returnDate,
        voucherNo: `SR-${ret.id}`,
        customerName: custName,
        description: `Sales Return${qtyDesc}${reasonDesc}`,
        transactionType: 'Sales Return',
        debit: 0,
        credit: Number(ret.returnAmount ?? 0),
      });

      // Add a synthetic Cash Refund (Debit) so the return is offset
      if (Number(ret.returnAmount ?? 0) > 0) {
        entries.push({
          date: ret.returnDate,
          voucherNo: `SR-${ret.id}`,
          customerName: custName,
          description: `Cash Refund`,
          transactionType: 'Payment',
          debit: Number(ret.returnAmount ?? 0),
          credit: 0,
        });
      }
    }

    entries.sort((a, b) => a.date.getTime() - b.date.getTime());

    return entries;
  }

  async findAllActiveCustomers() {
    return prisma.customer.findMany({
      where: { deletedAt: null, status: true },
      select: { id: true, name: true },
      orderBy: { name: 'asc' },
    });
  }

}