import { HttpService } from '@nestjs/axios';
import { Logger, BadRequestException, ForbiddenException, Injectable, NotFoundException } from '@nestjs/common';
import { Prisma, TransactionStatus, User } from 'generated/prisma/client';
import { firstValueFrom } from 'rxjs';
import { RequestUser } from 'src/auth/types/request-user.type';
import { BootstrapTableRequestDto } from 'src/commons/dto/bootstrap-table-request.dto';
import { BootstrapTableResponseDto } from 'src/commons/dto/bootstrap-table-response.dto';
import { RunumService } from 'src/commons/services/runum.service';
import { buildWhereClause } from 'src/commons/utils/prisma-query-builder';
import { ExportQueryDto } from 'src/export/export.types';
import { NotesService } from 'src/note/notes.service';
import { PrismaService } from 'src/prisma/prisma.service';
import { StorageService } from 'src/storage/storage.service';
import { CancelTransactionDto } from './dto/cancel-transaction.dto';
import { CreatePaymentDto } from './dto/create-payment.dto';
import { CreateTransactionDto } from './dto/create-transaction.dto';
import { TransactionChartQueryDto } from './dto/transaction-chart-query.dto';
import { TransactionPerformanceQueryDto } from './dto/transaction-performance-query.dto';
import { TransactionSummaryQueryDto } from './dto/transaction-summary-query.dto';
import { UpdateTransactionDto } from './dto/update-transaction.dto';
import { ConfigService } from '@nestjs/config';

@Injectable()
export class TransactionsService {
  exportService: any;
  constructor(
    private prisma: PrismaService,
    private storage: StorageService,
    private runum: RunumService,
    private notesService: NotesService,
    private httpService: HttpService,
    private configService: ConfigService,
  ) { }

  private readonly logger = new Logger(TransactionsService.name);
  private readonly MAX_EXPORT_MONTHS = 3;
  private readonly includeAll = {
    items: {
      include: {
        images: true,        // TransactionItemPhoto
        docs: true,        // TransactionItemDoc
      }
    },
    bank: true,              // Bank info
    docs: true,              // TransactionDoc (serah terima, surat pernyataan)
    customer: true,
    payments: {
      include: {
        transferUser: {
          select: {
            id: true,
            firstName: true,
            lastName: true,
            email: true,
          }
        }
      }
    },
    headStoreUser: {        // ✅ add
      select: {
        id: true,
        firstName: true,
        lastName: true,
        email: true,
      }
    },
    completedByUser: {      // ✅ add if you have this relation too
      select: {
        id: true,
        firstName: true,
        lastName: true,
        email: true,
      }
    },
    branch: true,
    createdByUser: {        // ✅ add
      select: {
        id: true,
        firstName: true,
        lastName: true,
        email: true,
      }
    },
  }

  private async checkBranchAccess(user: RequestUser, branchId: string | undefined) {
    const role = user?.role?.code;
    const userId = user?.id;
    const userEmail = user?.email;

    const context = {
      userEmail,
      role,
      branchId,
    };

    this.logger.debug(
      `[ACCESS_CHECK] Checking branch access`,
      JSON.stringify(context),
    );

    const isRestrictedRole = ['FRONTLINER', 'HEAD_STORE'].includes(role || '');

    // 🔹 Missing branchId for restricted roles
    if (isRestrictedRole && !branchId) {
      this.logger.warn(
        `[BAD_REQUEST] Missing branchId for restricted role`,
        JSON.stringify(context),
      );
      throw new BadRequestException('branchId is required for your role');
    }

    if (branchId) {
      const branch = await this.prisma.branch.findUnique({
        where: { id: branchId },
        select: { id: true },
      });

      // 🔹 Branch not found
      if (!branch) {
        this.logger.warn(
          `[NOT_FOUND] Branch not found`,
          JSON.stringify(context),
        );
        throw new NotFoundException('Branch not found');
      }

      // 🔹 Check user assignment (authorization)
      if (isRestrictedRole) {
        const exists = await this.prisma.userBranch.findUnique({
          where: { userId_branchId: { userId, branchId } },
        });

        if (!exists) {
          this.logger.warn(
            `[FORBIDDEN] User not assigned to branch`,
            JSON.stringify(context),
          );
          throw new ForbiddenException('You are not assigned to this branch');
        }
      }
    }

    // 🔹 Success
    this.logger.debug(
      `[ACCESS_GRANTED] Branch access granted`,
      JSON.stringify(context),
    );
  }

  private async generateTrxCode(branchId: string): Promise<string> {
    const branch = await this.prisma.branch.findUniqueOrThrow({
      where: { id: branchId },
      select: { code: true },
    });

    const today = new Date();
    const dd = String(today.getDate()).padStart(2, '0');
    const mm = String(today.getMonth() + 1).padStart(2, '0');
    const yy = String(today.getFullYear()).slice(-2);
    const dateStr = `${dd}${mm}${yy}`; // e.g. 090326

    const reset = `${branchId}${dateStr}`;  // reset key — resets counter per branch per day
    const runumId = `TRX_${branchId}`;     // unique counter identity per branch

    const seq = await this.runum.getNextNumber(runumId, reset);
    const seqStr = String(seq).padStart(3, '0'); // e.g. 001

    return `${branch.code}${dateStr}${seqStr}`; // e.g. SBY090326001
  }

  private normalizeValue(value: any) {

    if (value === null || value === undefined) return null;

    // Prisma Decimal
    if (value instanceof Prisma.Decimal) {
      return value.toString();
    }

    // Date
    if (value instanceof Date) {
      return value.getTime();
    }

    return value;
  }

  private async aggregateTransactions(
    start: Date,
    endExclusive: Date,
    branchId?: string
  ) {
    const result: any = await this.prisma.$queryRaw(
      Prisma.sql`
      SELECT
        COUNT(*) FILTER (WHERE status = 'COMPLETED') as success_count,
        COUNT(*) FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_count,

        SUM("totalAmount") FILTER (WHERE status = 'COMPLETED') as success_amount,
        SUM("totalAmount") FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_amount,

        SUM("totalNetWeight") FILTER (WHERE status = 'COMPLETED') as success_weight,
        SUM("totalNetWeight") FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_weight

      FROM "Transaction"
      WHERE "createdAt" >= ${start}
        AND "createdAt" < ${endExclusive}
        ${branchId
          ? Prisma.sql`AND "branchId" = ${branchId}`
          : Prisma.empty
        }
    `
    );

    const row = result[0] ?? {};

    return {
      transactionCount: {
        success: Number(row.success_count ?? 0),
        bounce: Number(row.bounce_count ?? 0),
      },
      totalAmount: {
        success: Number(row.success_amount ?? 0),
        bounce: Number(row.bounce_amount ?? 0),
      },
      totalNetWeight: {
        success: Number(row.success_weight ?? 0),
        bounce: Number(row.bounce_weight ?? 0),
      },
    }
  }

  private buildAuditLogs(oldObj: any, newObj: any, entityType: string, entityId: string, userId: string,
    headerEntityType: string | null,
    headerEntityId: string | null
  ) {
    const logs: any[] = [];

    for (const key of Object.keys(newObj)) {
      const oldValue = this.normalizeValue(oldObj[key]);
      const newValue = this.normalizeValue(newObj[key]);

      if (oldValue === newValue) continue;

      logs.push({
        entityType,
        entityId,
        fieldName: key,
        headerEntityType,
        headerEntityId,
        oldValue: oldObj[key]?.toString() ?? null,
        newValue: newObj[key]?.toString() ?? null,
        changedBy: userId
      });
    }

    return logs;
  }

  private computeChange(current: number, previous: number) {

    const value = current - previous

    let percent: number | null = null
    if (previous !== 0) {
      percent = Number(((value / previous) * 100).toFixed(2))
    }

    let trend: 'up' | 'down' | 'flat' = 'flat'
    if (value > 0) trend = 'up'
    if (value < 0) trend = 'down'

    return {
      value,
      percent,
      trend,
    }
  }

  async findAll(query: BootstrapTableRequestDto, user: any) {
    const logContext = {
      userEmail: user?.email,
      query,
      filter: query?.filter,
    };

    this.logger.debug(
      `[QUERY_RECEIVED] findAll`,
      JSON.stringify(logContext),
    );
    const allowedFilters = {
      status: 'enum',
      branchId: 'exact',
      createdAt_gte: 'date',
      createdAt_lte: 'date',
      customerId: 'exact',
    } as const

    const where = buildWhereClause(
      query,
      allowedFilters,
      ['trxCode', 'items.itemCode'],  // global search on trxCode
    )

    // branch access restriction
    if (!user.role?.accessAllBranch) {
      const userBranchIds = user.branches.map(b => b.branchId)

      where.branchId = {
        in: userBranchIds,
      }
    }

    const allowedSortColumns = ['trxCode', 'status', 'totalAmount', 'createdAt']

    const orderBy: Prisma.TransactionOrderByWithRelationInput = {}
    if (query.sort && allowedSortColumns.includes(query.sort)) {
      orderBy[query.sort] = query.sortOrder
    } else {
      orderBy.createdAt = 'desc'
    }

    const [total, rows] = await this.prisma.$transaction([
      this.prisma.transaction.count({ where }),
      this.prisma.transaction.findMany({
        where,
        skip: query.skip,
        take: query.take,
        orderBy,
        include: this.includeAll,
      }),
    ])

    return new BootstrapTableResponseDto(total, rows)
  }

  async findOne(id: string) {
    const transaction = await this.prisma.transaction.findUniqueOrThrow({
      where: { id },
      include: this.includeAll,
    })

    const notes = await this.prisma.note.findMany({
      where: { entityType: 'TRANSACTION', entityId: id },
      orderBy: { createdAt: 'asc' },
    })

    return { ...transaction, notes }
  }

  async getAuditLogsForTransaction(id: string, query: BootstrapTableRequestDto) {
    const where = {
      headerEntityType: 'TRANSACTION',
      headerEntityId: id,
    };

    const allowedSortColumns = ['changedAt', 'fieldName', 'entityType', 'oldValue', 'newValue'];

    const orderBy: any = {};
    if (query.sort && allowedSortColumns.includes(query.sort)) {
      orderBy[query.sort] = query.sortOrder;
    } else {
      orderBy.changedAt = 'desc';
    }

    const [total, rows] = await this.prisma.$transaction([
      this.prisma.auditLog.count({ where }),
      this.prisma.auditLog.findMany({
        where,
        skip: query.skip,
        take: query.take,
        orderBy,
        include: {
          changedByUser: {
            select: {
              id: true,
              firstName: true,
              lastName: true,
              email: true,
            },
          },
        },
      }),
    ]);

    return new BootstrapTableResponseDto(total, rows);
  }

  async update(id: string, dto: UpdateTransactionDto, user: User) {
    // TODO: add audit log for changes, especially status changes (e.g. if status changes to CANCELED, log the reason and who made the change)
    const transaction = await this.prisma.transaction.findUniqueOrThrow({
      where: { id },
      include: {
        items: {
          include: { images: true }
        }
      }
    });

    // TODO authorization
    // ensure head store of branch can update
    // ✅ Detect status transition
    const isHeadStoreAssignment =
      transaction.status === 'OPEN' &&
      dto.status === 'WAITING_PAYMENT';
    return this.prisma.$transaction(async (tx) => {

      const updatedTransaction = await tx.transaction.update({
        where: { id },
        data: {
          status: dto.status,
          customerId: dto.customerId,
          referralSource: dto.referralSource,
          knowsFounder: dto.knowsFounder,

          totalGrossWeight: dto.totalGrossWeight,
          totalNetWeight: dto.totalNetWeight,
          totalAmount: dto.totalAmount,

          bankId: dto.bankId,
          bankAccountNo: dto.bankAccountNo,
          bankAccountName: dto.bankAccountName,

          ...(isHeadStoreAssignment && { headStoreId: user.id }),
        }
      });
      const auditLogs: any[] = [];

      // -------------------------------
      // Transaction Header Audit
      // -------------------------------
      auditLogs.push(
        ...this.buildAuditLogs(
          transaction,
          updatedTransaction,
          "TRANSACTION",
          updatedTransaction.id,
          user.id,
          "TRANSACTION",
          updatedTransaction.id
        )
      );

      if (dto.items?.length) {
        for (const item of dto.items) {

          const originalItem = transaction.items.find(i => i.id === item.id);

          const updatedItem = await tx.transactionItem.update({
            where: { id: item.id },
            data: {
              itemCode: item.itemCode,
              itemTypeId: item.itemTypeId,
              caratId: item.caratId,
              purityPercent: item.purityPercent,
              grossWeight: item.grossWeight,
              netWeight: item.netWeight,
              pricePerGram: item.pricePerGram,
              pricePerGramAt: item.pricePerGramAt,
            }
          });

          if (originalItem) {
            auditLogs.push(
              ...this.buildAuditLogs(
                originalItem,
                updatedItem,
                "TRANSACTION_ITEM",
                updatedItem.id,
                user.id,
                "TRANSACTION",
                updatedTransaction.id
              )
            );
          }
        }
      }
      // -------------------------------
      // Save Audit Logs
      // -------------------------------
      if (auditLogs.length > 0) {
        await tx.auditLog.createMany({
          data: auditLogs
        });
      }
      return tx.transaction.findUnique({
        where: { id },
        include: {
          items: {
            include: { images: true }
          }
        }
      });
    });
  }

  async remove(id: string) {
    await this.prisma.transaction.findUniqueOrThrow({
      where: { id },
    });

    return this.prisma.transaction.delete({
      where: { id },
    });
  }

  async cancel(id: string, dto: CancelTransactionDto, user: User) {
    const transaction = await this.prisma.transaction.findUniqueOrThrow({
      where: { id },
    });

    if (transaction.status === 'CANCELED') {
      throw new BadRequestException('Transaction is already cancelled');
    }

    if (transaction.status === 'COMPLETED') {
      throw new BadRequestException('Cannot cancel a completed transaction');
    }

    return this.prisma.$transaction(async (tx) => {
      const updatedTransaction = await tx.transaction.update({
        where: { id },
        data: {
          status: 'CANCELED',
          cancellationReason: dto.reason,
          cancelledBy: user.id,
          cancelledAt: new Date(),
        },
        include: {
          items: {
            include: { images: true }
          }
        }
      });

      const auditLogs: any[] = [];

      // Main audit diff
      auditLogs.push(
        ...this.buildAuditLogs(
          transaction,
          updatedTransaction,
          "TRANSACTION",
          updatedTransaction.id,
          user.id,
          "TRANSACTION",
          updatedTransaction.id
        )
      );

      if (auditLogs.length > 0) {
        await tx.auditLog.createMany({
          data: auditLogs
        });
      }

      return updatedTransaction;
    })
  }

  async getSummary(filter?: { branchId?: string }) {
    const counts = await this.prisma.transaction.groupBy({
      by: ['status'],
      where: {
        ...(filter?.branchId && { branchId: filter.branchId }),
      },
      _count: { id: true },
    })

    const summary = counts.reduce((acc, item) => {
      acc[item.status] = item._count.id
      return acc
    }, {} as Record<string, number>)

    const allStatuses = [
      'OPEN',
      'WAITING_PAYMENT',
      'PAID_WAITING_HS_REVIEW',
      'COMPLETED',
      'CANCELED',
    ]

    for (const status of allStatuses) {
      if (!summary[status]) summary[status] = 0
    }

    return summary
  }

  async create(
    dto: CreateTransactionDto,
    user: User,
  ) {
    const role = await this.prisma.role.findUnique({
      where: { id: user.roleId },
    });
    console.log("create timestamp at transactions.service", new Date().toISOString())
    // --- Role check ---
    if (!role || !['FRONTLINER', 'SUPER_ADMIN', 'HEAD_STORE'].includes(role.code)) {
      throw new ForbiddenException(
        'Only FRONTLINER, HEAD_STORE and SUPER_ADMIN can create transaction',
      );
    }

    // --- Branch existence check ---
    const branch = await this.prisma.branch.findUnique({
      where: { id: dto.branchId },
      select: { id: true },
    });

    if (!branch) {
      throw new NotFoundException('Branch not found');
    }

    // --- Branch membership check (skip for SUPER_ADMIN) ---
    if (role.code !== 'SUPER_ADMIN') {
      const userBranch = await this.prisma.userBranch.findUnique({
        where: {
          userId_branchId: {
            userId: user.id,
            branchId: dto.branchId,
          },
        },
      });

      if (!userBranch) {
        throw new ForbiddenException(
          'User is not assigned to the selected branch',
        );
      }
    }
    const branchId = dto.branchId

    const trxCode = await this.generateTrxCode(dto.branchId);

    let totalNet = new Prisma.Decimal(0)
    let totalGross = new Prisma.Decimal(0)
    let totalAmount = new Prisma.Decimal(0)

    const today = new Date();
    const dd = String(today.getDate()).padStart(2, '0');
    const mm = String(today.getMonth() + 1).padStart(2, '0');
    const yy = String(today.getFullYear()).slice(-2);
    const dateStr = `${dd}${mm}${yy}`;

    console.log("create timestamp before API call at transactions.service", new Date().toISOString())
    const disablePriceValidation = process.env.DISABLE_PRICE_VALIDATION === 'true';
    let priceData: any = {};
    if (!disablePriceValidation) {
      console.log("create timestamp before API call at transactions.service", new Date().toISOString())
      let url = this.configService.get<string>('COMPARISON_API_URL') || "https://super-emas-be.onrender.com/api/comparison-data'";
      const { data } = await firstValueFrom(
        this.httpService.get(url),
      )

      console.log("create timestamp after API call at transactions.service", new Date().toISOString())

      priceData = data.priceData;
    }

    console.log("create timestamp before item processing at transactions.service", new Date().toISOString())
    const itemsData = await Promise.all(
      dto.items.map(async (item) => {
        const gross = new Prisma.Decimal(item.grossWeight)
        const net = new Prisma.Decimal(item.netWeight)

        if (net.gt(gross)) {
          throw new BadRequestException(
            `Net weight cannot exceed gross weight`,
          )
        }

        if (!disablePriceValidation) {

          if (!priceData[item.caratId]) {
            throw new BadRequestException(`Invalid caratId: ${item.caratId}`)
          }
        }

        const clientPrice = new Prisma.Decimal(item.pricePerGram)
        const apiPrice = disablePriceValidation ? clientPrice : new Prisma.Decimal(priceData[item.caratId].buybackPrice)


        if (!clientPrice.equals(apiPrice)) {
          throw new BadRequestException(
            `Price mismatch for ${item.caratId}. Expected ${apiPrice}, received ${clientPrice}`,
          )
        }

        const total = net.mul(apiPrice)

        totalNet = totalNet.add(net)
        totalGross = totalGross.add(gross)
        totalAmount = totalAmount.add(total)

        // const itemCode = await this.generateItemCode(item.itemTypeId, dateStr)

        return {
          itemCode: item.itemCode,
          itemTypeId: item.itemTypeId,
          grossWeight: gross,
          netWeight: net,
          caratId: item.caratId,
          pricePerGram: apiPrice,
          pricePerGramAt: new Date(item.pricePerGramAt),
          purityPercent: item.purityPercent,
          totalPrice: total,
        };
      })
    )

    if (dto.status === TransactionStatus.CANCELED) {
      if (!dto.reason) {
        throw new BadRequestException('A reason is required when cancelling a transaction')
      }
    }

    console.log("create timestamp after item processing at transactions.service", new Date().toISOString())
    return this.prisma.$transaction(async (tx) => {
      const transaction = await tx.transaction.create({
        data: {
          trxCode,
          branchId,
          customerId: dto.customerId,
          createdBy: user.id,
          status: dto.status,
          referralSource: dto.referralSource,   // ✅ now from DTO
          knowsFounder: dto.knowsFounder,     // ✅ now from DTO
          bankId: dto.bankId,           // ✅ new
          bankAccountNo: dto.bankAccountNo,    // ✅ new
          bankAccountName: dto.bankAccountName,  // ✅ new
          totalGrossWeight: totalGross,
          totalNetWeight: totalNet,
          totalAmount,
          items: { create: itemsData },
        },
        include: { items: true },
      })

      // temp photo promotion
      for (let i = 0; i < dto.items.length; i++) {
        const itemDto = dto.items[i];
        const createdItem = transaction.items[i];

        if (!itemDto.tempPhotoIds?.length) continue;

        const tempPhotos = await tx.tempPhoto.findMany({
          where: { id: { in: itemDto.tempPhotoIds } },
        });

        console.log('tempPhotoIds received:', itemDto.tempPhotoIds);
        console.log('tempPhotos found:', tempPhotos);

        for (const [order, temp] of tempPhotos.entries()) {
          const permanentUrl = await this.storage.promoteToPermament(
            temp.url,
            `items/${createdItem.id}`,
          );

          await tx.transactionItemPhoto.create({
            data: {
              itemId: createdItem.id,
              url: permanentUrl,
              filename: temp.filename,  // ✅ add back
              mimetype: temp.mimetype,  // ✅ add back
              size: temp.size,          // ✅ add back
              order,
            },
          });

          await tx.tempPhoto.delete({ where: { id: temp.id } });
        }
      }

      // ── Transaction docs (new) ─────────────────────────────────────────
      const docUploads: { ids: string[], type: 'SERAH_TERIMA' | 'SURAT_PERNYATAAN' }[] = [
        { ids: dto.serahTerimaIds ?? [], type: 'SERAH_TERIMA' },
        { ids: dto.suratPernyataanIds ?? [], type: 'SURAT_PERNYATAAN' },
      ]

      for (const { ids, type } of docUploads) {
        if (!ids.length) continue

        const tempDocs = await tx.tempPhoto.findMany({
          where: { id: { in: ids } },
        })

        for (const [order, temp] of tempDocs.entries()) {
          const permanentUrl = await this.storage.promoteToPermament(
            temp.url,
            `transactions/${transaction.id}/docs`,
          )
          await tx.transactionDoc.create({
            data: {
              transactionId: transaction.id,
              type,
              url: permanentUrl,
              filename: temp.filename,
              mimetype: temp.mimetype,
              size: temp.size,
              order,
            },
          })
          await tx.tempPhoto.delete({ where: { id: temp.id } })
        }
      }
      if (dto.status === TransactionStatus.CANCELED) {
        await tx.transaction.update({
          where: { id: transaction.id },
          data: {
            status: 'CANCELED',
            cancellationReason: dto.reason,
            cancelledBy: user.id,
            cancelledAt: new Date(),
          },
        })
      }

      return tx.transaction.findUnique({
        where: { id: transaction.id },
        include: this.includeAll,
      });
    });
  }

  async addPayment(id: string, dto: CreatePaymentDto, user: User) {
    const transaction = await this.prisma.transaction.findUniqueOrThrow({
      where: { id },
      include: { payments: true }
    });

    if (transaction.status === 'CANCELED') {
      throw new BadRequestException('Cannot add payment to a cancelled transaction');
    }

    if (transaction.status === 'COMPLETED') {
      throw new BadRequestException('Transaction is already completed');
    }

    return this.prisma.$transaction(async (tx) => {

      const auditLogs: any[] = [];

      const temp = await tx.tempPhoto.findUniqueOrThrow({
        where: { id: dto.tempPhotoId },
      });

      const permanentUrl = await this.storage.promoteToPermament(
        temp.url,
        `transactions/${id}/payments`,
      );

      await tx.tempPhoto.delete({ where: { id: temp.id } });

      const oldPayment = transaction.payments;

      const payment = await tx.payment.upsert({
        where: { transactionId: id },
        create: {
          transactionId: id,
          transferBy: dto.transferBy,
          transferDate: new Date(),
          proofUrl: permanentUrl,
          filename: temp.filename,
          mimetype: temp.mimetype,
          size: temp.size,
        },
        update: {
          transferBy: dto.transferBy,
          transferDate: new Date(),
          proofUrl: permanentUrl,
          filename: temp.filename,
          mimetype: temp.mimetype,
          size: temp.size,
        },
      });

      // -------------------------------
      // Payment Audit
      // -------------------------------

      if (oldPayment) {
        auditLogs.push(
          ...this.buildAuditLogs(
            oldPayment,
            payment,
            "PAYMENT",
            payment.id,
            user.id,
            "TRANSACTION",
            id
          )
        );
      } else {
        auditLogs.push({
          entityType: "PAYMENT",
          entityId: payment.id,
          headerEntityType: "TRANSACTION",
          headerEntityId: id,
          fieldName: "CREATE",
          oldValue: null,
          newValue: "Payment created",
          createdBy: user.id
        });
      }

      // -------------------------------
      // Transaction Status Update
      // -------------------------------

      const updatedTransaction = await tx.transaction.update({
        where: { id },
        data: { status: 'PAID_WAITING_HS_REVIEW' },
      });

      auditLogs.push(
        ...this.buildAuditLogs(
          transaction,
          updatedTransaction,
          "TRANSACTION",
          updatedTransaction.id,
          user.id,
          "TRANSACTION",
          updatedTransaction.id
        )
      );

      // -------------------------------
      // Save Audit Logs
      // -------------------------------

      if (auditLogs.length > 0) {
        await tx.auditLog.createMany({
          data: auditLogs
        });
      }

      return payment;
    });
  }

  async complete(id: string, note: string, user: User) {
    const transaction = await this.findOne(id)

    if (!transaction) {
      throw new NotFoundException(`Transaction ${id} not found`)
    }

    if (transaction.status !== 'PAID_WAITING_HS_REVIEW') {
      throw new BadRequestException(
        `Transaction must be PAID_WAITING_HS_REVIEW to complete. Current: ${transaction.status}`
      )
    }
    return this.prisma.$transaction(async (tx) => {
      const updatedTransaction = await tx.transaction.update({
        where: { id },
        data: {
          status: 'COMPLETED',
          completedBy: user.id,    // ✅ inside data
          completedAt: new Date(),
        },
      });

      await this.notesService.createNote(
        'TRANSACTION',
        id,
        'COMPLETED',
        note,  // ✅ from user input
        user.id,
      )

      const auditLogs: any[] = [];

      // Main audit diff
      auditLogs.push(
        ...this.buildAuditLogs(
          transaction,
          updatedTransaction,
          "TRANSACTION",
          updatedTransaction.id,
          user.id,
          "TRANSACTION",
          updatedTransaction.id
        )
      );

      if (auditLogs.length > 0) {
        await tx.auditLog.createMany({
          data: auditLogs
        });
      }

      return updatedTransaction;
    })
  }

  async getTransactionsForExport(query: ExportQueryDto, user: any) {
    let start: Date | undefined;
    let end: Date | undefined;

    const isAllBranch = user.role?.accessAllBranch === true;
    const userBranchIds = (user.branches ?? []).map((b: any) => b.branchId);

    if (!query.startDate && !query.limit) {
      throw new BadRequestException('Provide at least startDate or limit to export');
    }

    if (query.startDate && query.endDate) {
      start = new Date(query.startDate);
      end = new Date(query.endDate);
      end.setHours(23, 59, 59, 999);

      if (start > end) {
        throw new BadRequestException('startDate cannot be after endDate');
      }

      const diffMonths =
        (end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24 * 30);

      if (diffMonths > this.MAX_EXPORT_MONTHS) {
        throw new BadRequestException(
          `Export date range cannot exceed ${this.MAX_EXPORT_MONTHS} months`,
        );
      }
    }

    // Determine branch filter
    let branchFilter: Prisma.TransactionWhereInput = {};

    if (isAllBranch) {
      // Admin: respect query.branchId if provided, otherwise no filter (all branches)
      if (query.branchId) {
        branchFilter = { branchId: query.branchId };
      }
    } else {
      // Restricted user: ALWAYS scope to their own branches, query.branchId is ignored
      branchFilter = userBranchIds.length > 0
        ? { branchId: { in: userBranchIds } }
        : { branchId: 'NO_BRANCH_ACCESS' }; // safety net — return nothing if no branches
    }

    const where: Prisma.TransactionWhereInput = {
      ...branchFilter,
      ...(query.status && { status: query.status }),
      ...(start || end
        ? {
          createdAt: {
            ...(start && { gte: start }),
            ...(end && { lte: end }),
          },
        }
        : {}),
    };

    return this.prisma.transaction.findMany({
      where,
      include: {
        customer: true,
        items: {
          include: {
            itemType: true,
          },
        },
        bank: true,
        branch: true,
        createdByUser: {
          select: { id: true, firstName: true, lastName: true, email: true },
        },
        headStoreUser: {
          select: { id: true, firstName: true, lastName: true, email: true },
        },
      },
      orderBy: { createdAt: 'asc' },
      ...(query.limit !== undefined && { take: query.limit }),
      ...(query.offset !== undefined && { skip: query.offset }),
    });
  }

  async generateItemId() {
    const today = new Date();
    const dd = String(today.getDate()).padStart(2, '0');
    const mm = String(today.getMonth() + 1).padStart(2, '0');
    const yy = String(today.getFullYear()).slice(-2);

    const dateStr = `${yy}${mm}${dd}`;
    const runumId = `TXN_ITEMS`;
    const reset = `${dateStr}`;

    const seq = await this.runum.getNextNumber(runumId, reset);
    const seqStr = String(seq).padStart(3, '0');

    return `${"ITM-"}${dateStr}${seqStr}`; // e.g. ITM-260311001

  }

  async getAnalyticsChart(query: TransactionChartQueryDto, user: RequestUser) {

    const { startDate, endDate, groupBy, branchId } = query
    const start = new Date(`${startDate}T00:00:00.000Z`);
    const endExclusive = new Date(`${endDate}T00:00:00.000Z`);
    endExclusive.setDate(endExclusive.getDate() + 1);

    await this.checkBranchAccess(user, branchId);

    const rows: any = await this.prisma.$queryRawUnsafe(`
    SELECT
      date_trunc('${groupBy}', "createdAt") as period,

      COUNT(*) FILTER (WHERE status IN ('COMPLETED')) as success_count,
      SUM("totalAmount") FILTER (WHERE status IN ('COMPLETED')) as success_amount,
      SUM("totalNetWeight") FILTER (WHERE status IN ('COMPLETED')) as success_weight,

      COUNT(*) FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_count,
      SUM("totalAmount") FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_amount,
      SUM("totalNetWeight") FILTER (WHERE status IN ('REJECTED','CANCELED')) as bounce_weight

    FROM "Transaction"
    WHERE "createdAt" >= $1
      AND "createdAt" < $2
      ${branchId ? 'AND "branchId" = $3' : ''}
    GROUP BY period
    ORDER BY period
  `, ...(branchId ? [start, endExclusive, branchId] : [start, endExclusive]))

    const labels: string[] = []

    const successCount: number[] = []
    const successAmount: number[] = []
    const successWeight: number[] = []

    const bounceCount: number[] = []
    const bounceAmount: number[] = []
    const bounceWeight: number[] = []

    for (const r of rows) {

      labels.push(r.period.toISOString().slice(0, 10))

      successCount.push(Number(r.success_count ?? 0))
      successAmount.push(Number(r.success_amount ?? 0))
      successWeight.push(Number(r.success_weight ?? 0))

      bounceCount.push(Number(r.bounce_count ?? 0))
      bounceAmount.push(Number(r.bounce_amount ?? 0))
      bounceWeight.push(Number(r.bounce_weight ?? 0))
    }

    return {
      labels,
      series: {
        transactionCount: {
          success: successCount,
          bounce: bounceCount,
        },
        totalAmount: {
          success: successAmount,
          bounce: bounceAmount,
        },
        totalNetWeight: {
          success: successWeight,
          bounce: bounceWeight,
        }
      }
    }
  }

  async getAnalyticsSummary(query: TransactionSummaryQueryDto, user: RequestUser) {

    const { startDate, endDate, branchId } = query

    await this.checkBranchAccess(user, branchId);

    // ✅ Normalize date range (UTC + exclusive end)
    const start = new Date(`${startDate}T00:00:00.000Z`);

    const endExclusive = new Date(`${endDate}T00:00:00.000Z`);
    endExclusive.setDate(endExclusive.getDate() + 1);

    // ✅ Accurate diff (no +1 hacks)
    const diffDays = Math.ceil(
      (endExclusive.getTime() - start.getTime()) / (1000 * 60 * 60 * 24)
    );

    // ✅ Previous period (perfect alignment)
    const prevEndExclusive = new Date(start);

    const prevStart = new Date(prevEndExclusive);
    prevStart.setDate(prevStart.getDate() - diffDays);

    const [current, previous] = await Promise.all([
      this.aggregateTransactions(start, endExclusive, branchId),
      this.aggregateTransactions(prevStart, prevEndExclusive, branchId),
    ]);

    const buildMetric = (metric: keyof typeof current) => ({
      currentPeriod: {
        start,
        end: new Date(endExclusive.getTime() - 1), // optional for display
        success: current[metric].success,
        bounce: current[metric].bounce,
      },
      previousPeriod: {
        start: prevStart,
        end: new Date(prevEndExclusive.getTime() - 1),
        success: previous[metric].success,
        bounce: previous[metric].bounce,
      },
      changes: {
        success: this.computeChange(
          current[metric].success,
          previous[metric].success
        ),
        bounce: this.computeChange(
          current[metric].bounce,
          previous[metric].bounce
        ),
      },
    })

    return {
      transactionCount: buildMetric('transactionCount'),
      totalAmount: buildMetric('totalAmount'),
      totalNetWeight: buildMetric('totalNetWeight'),
    }
  }

  async getPerformanceSummary(query: TransactionPerformanceQueryDto, user: RequestUser) {

    const { startDate, endDate, branchId } = query
    const start = new Date(`${startDate}T00:00:00.000Z`);
    const endExclusive = new Date(`${endDate}T00:00:00.000Z`);
    endExclusive.setDate(endExclusive.getDate() + 1);

    await this.checkBranchAccess(user, branchId);

    const result: any = await this.prisma.$queryRaw(
      Prisma.sql`

    SELECT

      COUNT(*) as all_count,
      SUM("totalGrossWeight") as all_gross_weight,
      SUM("totalAmount") as all_amount,

      COUNT(*) FILTER (WHERE status = 'COMPLETED') as completed_count,
      SUM("totalGrossWeight") FILTER (WHERE status = 'COMPLETED') as completed_gross_weight,
      SUM("totalAmount") FILTER (WHERE status = 'COMPLETED') as completed_amount,

      COUNT(*) FILTER (WHERE status = 'CANCELED') as canceled_count,
      SUM("totalGrossWeight") FILTER (WHERE status = 'CANCELED') as canceled_gross_weight,
      SUM("totalAmount") FILTER (WHERE status = 'CANCELED') as canceled_amount,

      COUNT(*) FILTER (WHERE status = 'REJECTED') as rejected_count,
      SUM("totalGrossWeight") FILTER (WHERE status = 'REJECTED') as rejected_gross_weight,
      SUM("totalAmount") FILTER (WHERE status = 'REJECTED') as rejected_amount

    FROM "Transaction"

    WHERE "createdAt" >= ${start}
    AND "createdAt" < ${endExclusive}
    ${branchId ? Prisma.sql`AND "branchId" = ${branchId}` : Prisma.empty}

  `)

    const row = result[0]

    return {
      transactionCount: {
        created: Number(row.all_count ?? 0),
        completed: Number(row.completed_count ?? 0),
        canceled: Number(row.canceled_count ?? 0),
        rejected: Number(row.rejected_count ?? 0),
      },

      totalGrossWeight: {
        created: Number(row.all_gross_weight ?? 0),
        completed: Number(row.completed_gross_weight ?? 0),
        canceled: Number(row.canceled_gross_weight ?? 0),
        rejected: Number(row.rejected_gross_weight ?? 0),
      },

      totalAmount: {
        created: Number(row.all_amount ?? 0),
        completed: Number(row.completed_amount ?? 0),
        canceled: Number(row.canceled_amount ?? 0),
        rejected: Number(row.rejected_amount ?? 0),
      }
    }
  }

  async getCancellationReasons(query: TransactionPerformanceQueryDto, user: RequestUser) {

    const { startDate, endDate, branchId } = query
    const start = new Date(`${startDate}T00:00:00.000Z`);
    const endExclusive = new Date(`${endDate}T00:00:00.000Z`);
    endExclusive.setDate(endExclusive.getDate() + 1);

    await this.checkBranchAccess(user, branchId);

    const rows = await this.prisma.$queryRaw<
      { reason: string; count: bigint }[]
    >(Prisma.sql`

    SELECT
      ds.name AS reason,
      COUNT(t.id) AS count

    FROM "DetailSetting" ds
    JOIN "HeaderSetting" hs
      ON hs.id = ds."headerId"

    LEFT JOIN "Transaction" t
      ON t."cancellationReason" = ds.name
      AND t.status = 'CANCELED'
      AND t."createdAt" >= ${start}
      AND t."createdAt" < ${endExclusive}
      ${branchId ? Prisma.sql`AND t."branchId" = ${branchId}` : Prisma.empty}

    WHERE hs.code = 'CANCELLATION_REASON'
      AND ds.status = 'ACTIVE'

    GROUP BY ds.name
    ORDER BY count DESC
  `)

    return rows.map(r => ({
      reason: r.reason,
      count: Number(r.count),
    }))
  }

  async getPerformanceChart(query: TransactionChartQueryDto, user: RequestUser) {

    const { startDate, endDate, groupBy = 'day', branchId } = query
    const start = new Date(`${startDate}T00:00:00.000Z`);
    const endExclusive = new Date(`${endDate}T00:00:00.000Z`);
    endExclusive.setDate(endExclusive.getDate() + 1);

    await this.checkBranchAccess(user, branchId);

    const rows: any[] = await this.prisma.$queryRaw(
      Prisma.sql`

    SELECT
      date_trunc(${groupBy}, "createdAt") as period,

      COUNT(*) as created_count,
      SUM("totalGrossWeight") as created_weight,
      SUM("totalAmount") as created_amount,

      COUNT(*) FILTER (WHERE status = 'COMPLETED') as success_count,
      SUM("totalGrossWeight") FILTER (WHERE status = 'COMPLETED') as success_weight,
      SUM("totalAmount") FILTER (WHERE status = 'COMPLETED') as success_amount,

      COUNT(*) FILTER (WHERE status = 'CANCELED') as canceled_count,
      SUM("totalGrossWeight") FILTER (WHERE status = 'CANCELED') as canceled_weight,
      SUM("totalAmount") FILTER (WHERE status = 'CANCELED') as canceled_amount,

      COUNT(*) FILTER (WHERE status = 'OPEN') as sla_open,
      COUNT(*) FILTER (WHERE status = 'WAITING_PAYMENT') as sla_payment,
      COUNT(*) FILTER (WHERE status = 'PAID_WAITING_HS_REVIEW') as sla_review_hs,
      COUNT(*) FILTER (WHERE status = 'COMPLETED') as sla_completed

    FROM "Transaction"

    WHERE "createdAt" >= ${start}
      AND "createdAt" < ${endExclusive}
      ${branchId ? Prisma.sql`AND "branchId" = ${branchId}` : Prisma.empty}

    GROUP BY period
    ORDER BY period desc

  `)

    const labels: number[] = []

    const createdCount: number[] = []
    const successCount: number[] = []
    const canceledCount: number[] = []

    const createdWeight: number[] = []
    const successWeight: number[] = []
    const canceledWeight: number[] = []

    const createdAmount: number[] = []
    const successAmount: number[] = []
    const canceledAmount: number[] = []

    const slaOpen: number[] = []
    const slaPayment: number[] = []
    const slaReviewHS: number[] = []
    const slaCompleted: number[] = []

    for (const r of rows) {

      labels.push(r.period.toISOString().slice(0, 10))

      createdCount.push(Number(r.created_count ?? 0))
      successCount.push(Number(r.success_count ?? 0))
      canceledCount.push(Number(r.canceled_count ?? 0))

      createdWeight.push(Number(r.created_weight ?? 0))
      successWeight.push(Number(r.success_weight ?? 0))
      canceledWeight.push(Number(r.canceled_weight ?? 0))

      createdAmount.push(Number(r.created_amount ?? 0))
      successAmount.push(Number(r.success_amount ?? 0))
      canceledAmount.push(Number(r.canceled_amount ?? 0))

      slaOpen.push(Number(r.sla_open ?? 0))
      slaPayment.push(Number(r.sla_payment ?? 0))
      slaReviewHS.push(Number(r.sla_review_hs ?? 0))
      slaCompleted.push(Number(r.sla_completed ?? 0))
    }

    return {
      labels,
      series: {
        transactionCount: {
          created: createdCount,
          success: successCount,
          canceled: canceledCount
        },

        totalGrossWeight: {
          created: createdWeight,
          success: successWeight,
          canceled: canceledWeight
        },

        totalAmount: {
          created: createdAmount,
          success: successAmount,
          canceled: canceledAmount
        },

        sla: {
          open: slaOpen,
          waitingPayment: slaPayment,
          paidWaitingHsReview: slaReviewHS,
          completed: slaCompleted
        }
      }
    }
  }
}
