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

export class SupplierRepository {
  async create(data: Prisma.SupplierUncheckedCreateInput) {
    return prisma.supplier.create({ data });
  }

  async findAll(skip?: number, take?: number, search?: string, status?: boolean) {
    const where: Prisma.SupplierWhereInput = { 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.supplier.findMany({
        where,
        ...(skip !== undefined ? { skip } : {}),
        ...(take !== undefined ? { take } : {}),
        include: {
          state: true,
          district: true,
        },
        orderBy: { id: 'desc' },
      }),
      prisma.supplier.count({ where }),
    ]);

    return { items, total };
  }

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

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


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

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

  async getStatesAndDistricts() {
    return prisma.state.findMany({
      where: { status: true },
      select: {
        id: true,
        name: true,
        tamilName: true,
        districts: {
          where: { status: true },
          select: {
            id: true,
            name: true,
            tamilName: true,
          },
        },
      },
      orderBy: { name: 'asc' },
    });
  }

  //Dashboard Data gets the count of active supliers.
  async activeSupplierCount() {
    const totalActiveSupplier = await prisma.supplier.count({
      where: {
        status: true,
        deletedAt: null,
      },
    });
    return totalActiveSupplier;
  }

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

  // Dashboard: active, inactive, and total breakdown for vendor payments
  async getVendorPaymentsBreakdown(): Promise<{ active: number; inactive: number; total: number }> {
    const [activeRes, inactiveRes] = await Promise.all([
      prisma.supplier.aggregate({
        _sum: { outstandingBalance: true },
        where: { status: true, deletedAt: null },
      }),
      prisma.supplier.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 getSupplierOpeningBalance(
    supplierId?: bigint,
    dateBefore?: Date
  ): Promise<number> {
    const supplierWhere: any = { deletedAt: null };
    if (supplierId) supplierWhere.id = supplierId;

    // openingBalance in suppliers table = what we owe them at start (positive = payable).
    // We store it as positive (payable) so we negate it for our runningBalance convention
    // where negative = payable and positive = advance.
    const openingAgg = await prisma.supplier.aggregate({
      _sum: { openingBalance: true },
      where: supplierWhere,
    });
    // Negate: DB positive openingBalance = payable = negative runningBalance
    let balance = -Number(openingAgg._sum.openingBalance ?? 0);

    if (!dateBefore) return balance;

    // Purchases before start date: use balanceAmount (= grandTotal - advanceAmount),
    // same as DB does (PurchaseService increments outstanding by balanceAmount)
    const purchaseWhere: any = {
      deletedAt: null,
      purchaseDate: { lt: dateBefore },
      supplierId: { not: null },
      purchaseType: { not: 'COMMISSION_ARRIVAL' },
    };
    if (supplierId) purchaseWhere.supplierId = supplierId;

    const purchaseAgg = await prisma.purchase.aggregate({
      _sum: { balanceAmount: true },
      where: purchaseWhere,
    });
    // Purchases increase payable → subtract from balance
    balance -= Number(purchaseAgg._sum.balanceAmount ?? 0);

    const paymentWhere: any = {
      deletedAt: null,
      paymentDate: { lt: dateBefore },
      amount: { gt: 0 },
    };
    if (supplierId) paymentWhere.supplierId = supplierId;

    const paymentAgg = await prisma.supplierPayment.aggregate({
      _sum: { amount: true },
      where: paymentWhere,
    });
    // Payments reduce payable → add to balance
    balance += Number(paymentAgg._sum.amount ?? 0);

    // PurchaseReturns before start date: reduce what we owe → add to balance
    const returnWhere: any = { returnDate: { lt: dateBefore } };
    if (supplierId) {
      returnWhere.purchase = { supplierId, deletedAt: null, purchaseType: { not: 'COMMISSION_ARRIVAL' } };
    } else {
      returnWhere.purchase = { supplierId: { not: null }, deletedAt: null, purchaseType: { not: 'COMMISSION_ARRIVAL' } };
    }

    const returnAgg = await prisma.purchaseReturn.aggregate({
      _sum: { returnAmount: true },
      where: returnWhere,
    });
    balance += Number(returnAgg._sum.returnAmount ?? 0);

    return balance;
  }

  async getSupplierLedgerTransactions(
    supplierId?: bigint,
    dateFrom?: Date,
    dateTo?: Date
  ) {
    const purchaseWhere: any = {
      deletedAt: null,
      supplierId: { not: null },
      purchaseType: { not: 'COMMISSION_ARRIVAL' },
    };
    if (supplierId) purchaseWhere.supplierId = supplierId;
    if (dateFrom || dateTo) {
      purchaseWhere.purchaseDate = {};
      if (dateFrom) purchaseWhere.purchaseDate.gte = dateFrom;
      if (dateTo) purchaseWhere.purchaseDate.lte = dateTo;
    }

    const paymentWhere: any = {
      deletedAt: null,
      amount: { gt: 0 },
      type: 'DIRECT',
    };
    if (supplierId) paymentWhere.supplierId = supplierId;
    if (dateFrom || dateTo) {
      paymentWhere.paymentDate = {};
      if (dateFrom) paymentWhere.paymentDate.gte = dateFrom;
      if (dateTo) paymentWhere.paymentDate.lte = dateTo;
    }

    const returnWhere: any = {
      purchase: supplierId
        ? { supplierId, deletedAt: null, purchaseType: { not: 'COMMISSION_ARRIVAL' } }
        : { supplierId: { not: null }, deletedAt: null, purchaseType: { not: 'COMMISSION_ARRIVAL' } },
    };
    if (dateFrom || dateTo) {
      returnWhere.returnDate = {};
      if (dateFrom) returnWhere.returnDate.gte = dateFrom;
      if (dateTo) returnWhere.returnDate.lte = dateTo;
    }

    const [purchases, payments, returns] = await Promise.all([
      prisma.purchase.findMany({
        where: purchaseWhere,
        select: {
          id: true,
          billNo: true,
          lotNo: true,
          purchaseDate: true,
          grandTotal: true,
          balanceAmount: true,
          advanceAmount: true,
          cashSupplierName: true,
          supplier: { select: { name: true } },
          vegetable: { select: { name: true } },
          quantity: true,
          bags: true,
          unit: true,
        },
        orderBy: { purchaseDate: 'asc' },
      }),
      prisma.supplierPayment.findMany({
        where: paymentWhere,
        select: {
          id: true,
          paymentDate: true,
          amount: true,
          paymentMode: true,
          referenceNo: true,
          remarks: true,
          supplier: { select: { name: true } },
        },
        orderBy: { paymentDate: 'asc' },
      }),
      prisma.purchaseReturn.findMany({
        where: returnWhere,
        select: {
          id: true,
          returnDate: true,
          returnAmount: true,
          reason: true,
          quantity: true,
          bags: true,
          purchase: {
            select: {
              billNo: true,
              lotNo: true,
              cashSupplierName: true,
              supplier: { select: { name: true } },
              unit: true,
            },
          },
        },
        orderBy: { returnDate: 'asc' },
      }),
    ]);

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

    const entries: LedgerEntry[] = [];

    for (const p of purchases) {
      const supplierName = p.supplier?.name || p.cashSupplierName || '';
      
      let qtyStrings = [];
      if (Number(p.quantity ?? 0) > 0) qtyStrings.push(`${Number(p.quantity)} KG`);
      if (Number(p.bags ?? 0) > 0) qtyStrings.push(`${Number(p.bags)} BAGS/BOX`);
      const qtyDesc = qtyStrings.length > 0 ? ` - Qty: ${qtyStrings.join(', ')}` : '';
      const vegDesc = p.vegetable?.name ? ` (${p.vegetable.name})` : '';

      // Use balanceAmount (= grandTotal - advanceAmount) to match how DB tracks outstanding.
      // This avoids showing a phantom "Advance Payment" row and keeps balance consistent.
      const purchaseCredit = Number(p.balanceAmount ?? 0);
      if (purchaseCredit > 0) {
        entries.push({
          date: p.purchaseDate,
          voucherNo: p.billNo || p.lotNo || `PUR-${p.id}`,
          supplierName,
          description: `Purchase${vegDesc}${qtyDesc}`,
          transactionType: 'Purchase',
          debit: 0,
          credit: purchaseCredit,
        });
      }
    }

    for (const pay of payments) {
      const modeLabel = pay.paymentMode === 'CASH' ? 'Cash Paid'
        : pay.paymentMode === 'UPI' ? 'UPI Paid'
          : 'Bank Transfer';

      entries.push({
        date: pay.paymentDate,
        voucherNo: pay.referenceNo ?? `PAY-${pay.id}`,
        supplierName: pay.supplier?.name ?? '',
        description: pay.remarks ? `${modeLabel} - ${pay.remarks}` : modeLabel,
        transactionType: 'Payment',
        debit: Number(pay.amount ?? 0),
        credit: 0,
      });
    }

    for (const ret of returns) {
      const billNo = ret.purchase?.billNo || ret.purchase?.lotNo || '';
      const supplierName = ret.purchase?.supplier?.name || ret.purchase?.cashSupplierName || '';
      
      const isBagOnly = ret.purchase?.unit !== 'KG' && new Prisma.Decimal(ret.quantity || 0).lte(0);
      const returnUnit = isBagOnly ? 'BAGS' : 'KG';
      const returnQty = isBagOnly ? ret.bags : ret.quantity;
      const qtyDesc = returnQty ? ` - Qty: ${returnQty} ${returnUnit}` : '';
      const reasonDesc = ret.reason ? ` (${ret.reason})` : '';

      entries.push({
        date: ret.returnDate,
        voucherNo: `PR-${ret.id}${billNo ? ` (${billNo})` : ''}`,
        supplierName,
        description: `Purchase Return${qtyDesc}${reasonDesc}`,
        transactionType: 'Purchase Return',
        debit: Number(ret.returnAmount ?? 0),
        credit: 0, // Reduces supplier balance (which means debit supplier)
      });
    }

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

    return entries;
  }

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