import { Injectable } from '@nestjs/common';
import { TenantConnectionService } from '../../database/tenant-connection.service';
import { SavingsAccount } from '../../database/tenant/entities/savings-account.entity';
import { SavingsTransaction } from '../../database/tenant/entities/savings-transaction.entity';
import { LoanAccount } from '../../database/tenant/entities/loan-account.entity';
import { LoanPayment } from '../../database/tenant/entities/loan-payment.entity';
import { Member } from '../../database/tenant/entities/member.entity';

@Injectable()
export class ReportsService {
  constructor(private tenantConn: TenantConnectionService) {}

  private async ds(sub: string) { return this.tenantConn.getTenantDataSource(sub); }

  async memberReport(sub: string, opts: { officeId?: string; status?: string } = {}) {
    const ds = await this.ds(sub);
    const r = ds.getRepository(Member);
    const qb = r.createQueryBuilder('m').where('m.deletedAt IS NULL');
    if (opts.officeId) qb.andWhere('m.officeId = :officeId', { officeId: opts.officeId });
    if (opts.status) qb.andWhere('m.status = :status', { status: opts.status });
    const total = await qb.getCount();
    const active = await r.createQueryBuilder('m').where('m.deletedAt IS NULL').andWhere('m.status = :s', { s: 'active' }).getCount();
    const byType = await r.createQueryBuilder('m').select('m.memberType', 'type').addSelect('COUNT(*)', 'count').where('m.deletedAt IS NULL').groupBy('m.memberType').getRawMany();
    const byGender = await r.createQueryBuilder('m').select('m.gender', 'gender').addSelect('COUNT(*)', 'count').where('m.deletedAt IS NULL').groupBy('m.gender').getRawMany();
    const recent = await r.find({ where: {}, order: { createdAt: 'DESC' }, take: 10 });
    return { total, active, byType, byGender, recent };
  }

  async savingsReport(sub: string, opts: { officeId?: string; savingsType?: string; fiscalYear?: string } = {}) {
    const ds = await this.ds(sub);
    const sr = ds.getRepository(SavingsAccount);
    const tr = ds.getRepository(SavingsTransaction);
    const qb = sr.createQueryBuilder('s').where('s.deletedAt IS NULL');
    if (opts.officeId) qb.andWhere('s.officeId = :officeId', { officeId: opts.officeId });
    if (opts.savingsType) qb.andWhere('s.savingsType = :savingsType', { savingsType: opts.savingsType });
    const [accounts, totalAccounts] = await qb.getManyAndCount();
    const totalBalance = accounts.reduce((s, a) => s + Number((a as any).currentBalance ?? (a as any).balance ?? 0), 0);
    const activeAccounts = accounts.filter(a => a.status === 'active').length;
    const byType = await sr.createQueryBuilder('s').select('s.savingsType', 'type').addSelect('COUNT(*)', 'count').addSelect('SUM(s.balance)', 'balance').where('s.deletedAt IS NULL').groupBy('s.savingsType').getRawMany();
    const txQb = tr.createQueryBuilder('t').select('t.type', 'type').addSelect('SUM(t.amount)', 'total').addSelect('COUNT(*)', 'count');
    if (opts.fiscalYear) txQb.where('t.fiscalYear = :fiscalYear', { fiscalYear: opts.fiscalYear });
    txQb.groupBy('t.type');
    const txSummary = await txQb.getRawMany();
    return { totalAccounts, activeAccounts, totalBalance, byType, txSummary };
  }

  async loanReport(sub: string, opts: { officeId?: string; status?: string; loanType?: string } = {}) {
    const ds = await this.ds(sub);
    const lr = ds.getRepository(LoanAccount);
    const pr = ds.getRepository(LoanPayment);
    const qb = lr.createQueryBuilder('l').where('l.deletedAt IS NULL');
    if (opts.officeId) qb.andWhere('l.officeId = :officeId', { officeId: opts.officeId });
    if (opts.status) qb.andWhere('l.status = :status', { status: opts.status });
    if (opts.loanType) qb.andWhere('l.loanType = :loanType', { loanType: opts.loanType });
    const [loans, totalLoans] = await qb.getManyAndCount();
    const totalDisbursed = loans.reduce((s, l) => s + Number((l as any).disbursedAmount ?? (l as any).sanctionedAmount ?? (l as any).loanAmount ?? 0), 0);
    const totalOutstanding = loans.reduce((s, l) => s + Number(l.outstandingBalance ?? 0), 0);
    const activeLoans = loans.filter(l => l.status === 'active').length;
    const nplLoans = loans.filter(l => l.status === 'npl' || (l as any).isNPL).length;
    const byType = await lr.createQueryBuilder('l').select('l.loanType', 'type').addSelect('COUNT(*)', 'count').addSelect('SUM(l.outstandingBalance)', 'outstanding').where('l.deletedAt IS NULL').groupBy('l.loanType').getRawMany();
    const byStatus = await lr.createQueryBuilder('l').select('l.status', 'status').addSelect('COUNT(*)', 'count').where('l.deletedAt IS NULL').groupBy('l.status').getRawMany();
    const recentPayments = await pr.find({ order: { createdAt: 'DESC' }, take: 10 });
    return { totalLoans, activeLoans, nplLoans, totalDisbursed, totalOutstanding, byType, byStatus, recentPayments };
  }

  async trialBalance(sub: string) {
    const ds = await this.ds(sub);
    const sr = ds.getRepository(SavingsAccount);
    const lr = ds.getRepository(LoanAccount);
    const savingsBalance = await sr.createQueryBuilder('s').select('SUM(s.balance)', 'total').where('s.deletedAt IS NULL').andWhere('s.status = :s', { s: 'active' }).getRawOne();
    const loanOutstanding = await lr.createQueryBuilder('l').select('SUM(l.outstandingBalance)', 'total').where('l.deletedAt IS NULL').andWhere('l.status = :s', { s: 'active' }).getRawOne();
    const savingsByType = await sr.createQueryBuilder('s').select('s.savingsType', 'type').addSelect('SUM(s.balance)', 'balance').where('s.deletedAt IS NULL').groupBy('s.savingsType').getRawMany();
    const loansByType = await lr.createQueryBuilder('l').select('l.loanType', 'type').addSelect('SUM(l.outstandingBalance)', 'outstanding').where('l.deletedAt IS NULL').groupBy('l.loanType').getRawMany();
    return {
      totalSavings: Number(savingsBalance?.total ?? 0),
      totalLoanOutstanding: Number(loanOutstanding?.total ?? 0),
      savingsByType, loansByType,
      asOf: new Date().toISOString(),
    };
  }

  async incomeExpense(sub: string, opts: { fiscalYear?: string } = {}) {
    const ds = await this.ds(sub);
    const pr = ds.getRepository(LoanPayment);
    const tr = ds.getRepository(SavingsTransaction);
    const payQb = pr.createQueryBuilder('p').select('SUM(p.interestAmount)', 'totalInterest').addSelect('SUM(p.penaltyAmount)', 'totalPenalty');
    if (opts.fiscalYear) payQb.where('p.fiscalYear = :fiscalYear', { fiscalYear: opts.fiscalYear });
    const income = await payQb.getRawOne();
    const txQb = tr.createQueryBuilder('t').select('t.type', 'type').addSelect('SUM(t.amount)', 'total');
    if (opts.fiscalYear) txQb.where('t.fiscalYear = :fiscalYear', { fiscalYear: opts.fiscalYear });
    const txByType = await txQb.groupBy('t.type').getRawMany();
    return {
      interestIncome: Number(income?.totalInterest ?? 0),
      penaltyIncome: Number(income?.totalPenalty ?? 0),
      txByType,
      fiscalYear: opts.fiscalYear ?? 'All',
    };
  }
}