import { BadRequestException, HttpException, HttpStatus, Injectable, InternalServerErrorException } from '@nestjs/common';
import { CreateNewBillingDto } from './dto/create-new-billing.dto';
import { UpdateNewBillingDto } from './dto/update-new-billing.dto';
import { ConnectionNotFoundError, DataSource } from 'typeorm';
import { InjectDataSource } from '@nestjs/typeorm';
import { makepaymentV3 } from './entities/new-billing.entity';
import { InjectModel } from '@nestjs/mongoose';
import { Model, Types } from 'mongoose';
import { transaction_details } from './entities/new-billing.entity';
import { customAlphabet } from 'nanoid';
import * as moment from 'moment';

@Injectable()
export class NewBillingService {
  constructor(
    @InjectDataSource('AdminConnection')
    private readonly connection: DataSource,
    private readonly dynamicConnection: DataSource,
    @InjectModel('transaction_details')
    private readonly ServiceModel: Model<transaction_details>,
  ) { }
  async validateDuplicateOpdPackage(items: any[]) {
    const seen = new Set<string>();

    for (let i = 0; i < items.length; i++) {
      const { department_id, package_id } = items[i];
      
      // Rule 1: At least one must exist
      if (!department_id) {
        throw new BadRequestException(
          `Either department_id is required at index ${i}`,
        );
      }


      // Build unique key
      if (package_id) {
        let key: string;

        key = `${department_id}-${package_id}`;


        // Rule 3 & 4: Duplicate check
        if (seen.has(key)) {
          const [packageName] = await this.dynamicConnection.query(`select package_name from packages where id = ?`, [package_id]);
          throw new BadRequestException(
            // `Duplicate entry found for ${department_id}
            //  and package_id=${package_id} at index ${i}`,
            `${packageName?.package_name} already added for the ${department_id} `
          );
        }

        seen.add(key);
      }
    }
  }
  async create(createNewBillingDto: CreateNewBillingDto[], patient_id: number) {
    await this.validateDuplicateOpdPackage(createNewBillingDto);
    try {
      const [getAayushUniqueId] = await this.dynamicConnection.query(
        `select aayush_unique_id from patients where id = ?`,
        [patient_id],
      );
      const [getAdminPat_id] = await this.connection.query(
        `select id from patients where aayush_unique_id = ?`,
        [getAayushUniqueId.aayush_unique_id],
      );
      for (const charge of createNewBillingDto) {
        const letters = charge.department_id.match(/[A-Za-z]+/)?.[0] ?? '';
        let id = charge.department_id.match(/\d+/)?.[0] ?? null;

        let column: 'opd_id' | 'ipd_id' = 'opd_id';

        switch (letters) {
          case 'IPDN':
            column = 'ipd_id';
            break;

          case 'OPDN':
            column = 'opd_id';
            break;

          case 'APPN': {
            const [result] = await this.dynamicConnection.query(
              `SELECT opd_details_id
         FROM visit_details
         LEFT JOIN appointment
           ON visit_details.id = appointment.visit_details_id
         WHERE appointment.id = ?`,
              [id],
            );

            id = result?.opd_details_id ?? null;
            column = 'opd_id';
            break;
          }

          default:
            id = null;
            column = 'opd_id';
        }

        if (!id) continue;

        const [existingCharge] = await this.dynamicConnection.query(
          `
    SELECT id
    FROM patient_charges
    WHERE ${column} = ?
      AND package_id = ?
      AND payment_status = 'unpaid'
    `,
          [id, charge.package_id],
        );

        if (existingCharge) {
          const [packageName] = await this.dynamicConnection.query(`select package_name from packages where id = ?`, [charge.package_id]);

          throw new BadRequestException(
            `${packageName?.package_name} already exists for the ${charge.department_id} `
            // `Duplicate unpaid package charge exists for department_id ${charge.department_id} and package_id ${charge.package_id}`,
          );
        }
      }

      for (const charges_entity of createNewBillingDto) {
        const letters =
          charges_entity.department_id.match(/[A-Za-z]+/)?.[0] ?? '';
        let numbers = charges_entity.department_id.match(/\d+/)?.[0] ?? '';
        let adminNumbers;
        let query_key;
        switch (letters) {
          case 'IPDN':
            const [getAdminIpd] = await this.connection.query(
              `select id from ipd_details where hospital_id = ? and hospital_ipd_details_id = ?`,
              [charges_entity.Hospital_id, numbers],
            );
            adminNumbers = getAdminIpd.id;
            query_key = 'ipd_id';

            break;
          case 'OPDN':
            const [getAdminOpd] = await this.connection.query(
              `select id from opd_details where Hospital_id = ? and hos_opd_id = ?`,
              [charges_entity.Hospital_id, numbers],
            );
            adminNumbers = getAdminOpd.id;
            query_key = 'opd_id';
            break;
          case 'APPN':
            const [getOpd_id] = await this.dynamicConnection.query(
              `select opd_details_id from visit_details left join appointment on visit_details.id = appointment.visit_details_id where appointment.id = ?`,
              [numbers],
            );
            numbers = getOpd_id.opd_details_id;
            const [AdminOpd] = await this.connection.query(
              `select id from opd_details where Hospital_id = ? and hos_opd_id = ?`,
              [charges_entity.Hospital_id, numbers],
            );
            adminNumbers = AdminOpd.id;
            query_key = 'opd_id';
            break;
          default:
            query_key = 'opd_id';
            numbers = null;
            adminNumbers = null;
          // Handle default case
        }

        const result = await this.dynamicConnection.query(
          `INSERT INTO patient_charges
  (date, ${query_key},  qty, charge_id, standard_charge,
   tpa_charge, tax, apply_charge, amount, note, patient_id, payment_status, total,balance, package_id)
   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
          [
            charges_entity.date,
            numbers,
            // charges_entity.appointment_id || null,
            charges_entity.qty,
            charges_entity.charge_id ?? null,
            charges_entity.standard_charge,
            charges_entity.tpa_charge,
            charges_entity.tax,
            charges_entity.apply_charge,
            charges_entity.amount,
            charges_entity.note,
            patient_id,
            'unpaid',
            charges_entity.amount,

            Number(charges_entity.amount) * -1,
            charges_entity.package_id,
          ],
        );

        const [charges] = await this.connection.query(
          `SELECT id FROM charges WHERE Hospital_id = ? AND hospital_charges_id = ?`,
          [charges_entity.Hospital_id, charges_entity.charge_id],
        );

        await this.connection.query(
          `INSERT INTO patient_charges
          (date, ${query_key},  qty, charge_id, standard_charge,
          tpa_charge, tax, apply_charge, amount, note, patient_id, Hospital_id,
          hos_patient_charges_id, payment_status, total,balance, package_id)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
          [
            charges_entity.date,
            adminNumbers,
            charges_entity.qty,
            charges?.id ?? null,
            charges_entity.standard_charge,
            charges_entity.tpa_charge,
            charges_entity.tax ?? 0,
            charges_entity.apply_charge,
            charges_entity.amount,
            charges_entity.note,
            getAdminPat_id.id,
            charges_entity.Hospital_id,
            result.insertId,
            'unpaid',
            charges_entity.amount,
            Number(charges_entity.amount) * -1,
            charges_entity.package_id,
          ],
        );
      }

      return {
        status: process.env.SUCCESS_STATUS_V2,
        message: process.env.PATIENT_CHARGES,
      };
    } catch (error) {
      if (error instanceof HttpException) {
        throw error; // 👈 rethrow
      }

      throw new InternalServerErrorException(
        'THE API SERVICE IS TEMPORARILY UNAVAILABLE. PLEASE TRY AGAIN LATER.',
      );
    }
  }

  async findAll(
    patient_id: string,
    payment_module: string,
    bill_type: string,
    limit: number,
    page: number,
    search_text?: string,
    appointment_id?: string, // New filter
  ) {
    const sanitizedLimit = Math.max(1, Number(limit));
    const sanitizedPage = Math.max(1, Number(page));
    const offset = sanitizedLimit * (sanitizedPage - 1);

    const [patient_data] = await this.dynamicConnection.query(
      `
    SELECT 
      p.id, p.image, p.patient_name, p.gender, p.age, p.dob, p.mobileno, 
      p.guardian_name, p.emergency_mobile_no, p.marital_status, 
      p.aayush_unique_id, p.ABHA_number, p.email, 
      p.employer_name, p.employer_id, p.employee_id, 
      p.insurance_provider, bbp.name AS blood_bank_product_name, 
      p.communication_address, p.address
    FROM patients p
    LEFT JOIN blood_bank_products bbp ON bbp.id = p.blood_bank_product_id
    WHERE p.id = ?
    `,
      [patient_id],
    );

    const [abhaResult] = await this.connection.query(
      `
    SELECT abhaAddress
    FROM patient_abha_address 
    WHERE patient_id = ? 
    ORDER BY created_at DESC 
    LIMIT 1
    `,
      [patient_id],
    );
    patient_data.pat_abha_address = abhaResult?.abhaAddress ?? '';

    const baseBillingQuery = `
    FROM appointment a
    LEFT JOIN visit_details vd ON vd.id = a.visit_details_id
    LEFT JOIN opd_details opd ON opd.id = vd.opd_details_id
    LEFT JOIN ipd_details ipd ON ipd.case_reference_id = a.case_reference_id
    LEFT JOIN staff s ON s.id = a.doctor
    LEFT JOIN patient_charges pc ON (pc.opd_id = opd.id OR pc.ipd_id = ipd.id)
    LEFT JOIN transactions t ON t.id = pc.transaction_id
    LEFT JOIN charges c ON c.id = pc.charge_id
    LEFT JOIN charge_categories cc ON cc.id = c.charge_category_id
    LEFT JOIN charge_type_master ctm ON ctm.id = cc.charge_type_id
    LEFT JOIN packages pg ON pg.id = pc.package_id
    LEFT JOIN package_categories pgc ON pg.package_category_id = pgc.id
    WHERE pc.patient_id = ?
  `;

    const queryParams: any[] = [patient_id];
    const countParams: any[] = [patient_id];
    const filters: string[] = [];

    if (bill_type === 'due') {
      filters.push(`pc.payment_status <> 'paid' AND a.appointment_status_id NOT IN('1','4')`);
    } else if (bill_type === 'paid') {
      filters.push(`pc.payment_status = 'paid'`);
    }

    if (payment_module) {
      const module = payment_module.toUpperCase();
      if (['OPD', 'IPD', 'APPOINTMENT'].includes(module)) {

        let moduleFilter = "";

        if (module === "APPOINTMENT") {
          moduleFilter = `a.module = "APPOINTMENT" AND pc.opd_id IS NOT NULL`;
        } else if (module === "OPD") {
          moduleFilter = `a.module = "OPD" AND pc.opd_id IS NOT NULL`;
        } else if (module === "IPD") {
          moduleFilter = `
        (
          a.module = "IPD"
          OR (
                a.module IN ("APPOINTMENT","OPD")
                AND pc.ipd_id IS NOT NULL
             )
        )
      `;
        }
        filters.push(moduleFilter.trim());
      }
    }

    if (search_text?.trim()) {
      const search = `%${search_text.trim()}%`;
      filters.push(`
      (
        CONCAT('APPN', a.id) LIKE ?
        OR s.name LIKE ?
        OR s.surname LIKE ?
      )
    `);
      queryParams.push(search, search, search);
      countParams.push(search, search, search);
    }

    if (appointment_id?.trim()) {
      const apptIdNum = appointment_id.replace(/[^0-9]/g, ''); // Remove prefix APPN/IPDN
      filters.push(`a.id = ?`);
      queryParams.push(apptIdNum);
      countParams.push(apptIdNum);
    }

    const whereClause = filters.length ? ` AND ${filters.join(' AND ')}` : '';

    const billingQuery = `
    SELECT 
      CONCAT('APPN', a.id) AS appointment_id,
      CASE 
        WHEN a.module = "OPD" AND pc.opd_id IS NOT NULL THEN  "OPD"
        WHEN (
          a.module = "IPD"
          OR (
                a.module IN ("APPOINTMENT","OPD")
                AND pc.ipd_id IS NOT NULL
             )
        ) THEN "IPD"
        WHEN a.module = "APPOINTMENT" AND pc.opd_id IS NOT NULL THEN "APPOINTMENT"
        ELSE '-' 
      END AS module,
      CASE 
        WHEN a.module = "OPD" AND pc.opd_id IS NOT NULL THEN CONCAT('OPDN', opd.id)
        WHEN (
          a.module = "IPD"
          OR (
                a.module IN ("APPOINTMENT","OPD")
                AND pc.ipd_id IS NOT NULL
             )
        ) THEN CONCAT('IPDN', ipd.id)
        WHEN a.module = "APPOINTMENT" AND pc.opd_id IS NOT NULL THEN CONCAT('APPN', a.id)
        ELSE '-' 
      END AS department_id,
      ctm.charge_type,
      cc.name AS charge_category,
      s.name,
      s.surname,
      s.id AS doctor_id,
      a.case_reference_id AS case_id,
      pc.id AS patient_charge_id,
     DATE(CONVERT_TZ(pc.date, '+00:00', '+05:30')) AS billed_date,
TIME(pc.date) AS billed_time,
      pc.qty AS Qty,
      CAST(COALESCE(pc.standard_charge, 0) AS DECIMAL(10,2)) AS Standard,
      CAST(COALESCE(pc.temp_standard_charge, 0) AS DECIMAL(10,2)) AS tem_standard,
      COALESCE(pc.apply_charge, 0) AS Apply,
      CAST(COALESCE(pc.discount_amount, 0) AS DECIMAL(10,2)) AS Discount,
      COALESCE(pc.apply_charge,0) AS apply_charge,
      COALESCE(pc.temp_apply_charge,0) AS temp_apply_charge,
      COALESCE(pc.additional_charge,0) AS additional_charge,
      COALESCE(pc.discount_amount,0) AS discount_amount,
      COALESCE(pg.package_tax,0) AS package_tax,
      pc.discount_percentage,
      pc.payment_status,
      pc.opd_id,
      pc.ipd_id,
    pc.transaction_id,
      ((COALESCE(pc.apply_charge,0) - COALESCE(pc.discount_amount,0) + COALESCE(pc.additional_charge,0)) ) as sub_total,
      CAST(COALESCE(pc.balance, 0) AS DECIMAL(10,2)) AS balance,
      CAST(COALESCE(pc.tax, 0) AS DECIMAL(10,2)) AS tax,
      CAST(COALESCE(pc.temp_tax, 0) AS DECIMAL(10,2)) AS temp_tax,
      CAST(COALESCE(pc.total, 0) AS DECIMAL(10,2)) AS Billed,
      CAST(COALESCE(pc.additional_charge, 0) AS DECIMAL(10,2)) AS additional_charge,
      CAST(COALESCE(pc.amount, 0) AS DECIMAL(10,2)) AS amount,
      CAST(COALESCE(pc.temp_amount, 0) AS DECIMAL(10,2)) AS temp_amount,
      CAST(COALESCE(pc.total, 0) AS DECIMAL(10,2)) AS total,
      pg.id as package_id,
      pg.package_name,
      pg.package_tax,
      pg.package_amount,
      pg.package_category_id,
      pgc.name as package_category_name,
      pc.created_at AS created_at -- NEW

    ${baseBillingQuery}
    ${whereClause}
    ORDER BY pc.created_at DESC
    LIMIT ? OFFSET ?
  `;

    queryParams.push(sanitizedLimit, offset);

    const countQuery = `
    SELECT COUNT(pc.id) AS totalcount
    ${baseBillingQuery}
    ${whereClause}
  `;

    const billing_summary = await this.dynamicConnection.query(
      billingQuery,
      queryParams,
    );

    // NEW: compute earliest patient_charges.created_at per appointment_id
    const earliestByAppointment = new Map<string, number>();
    for (const row of billing_summary) {

      if (row.module == 'IPD') {
        row.is_editable = true
      } else {

        let opd_id = row.opd_id;
        if (opd_id) {
          const [getFirstCharge] = await this.dynamicConnection.query(`select id from patient_charges where opd_id = ? order by created_at asc limit 1`, [opd_id])

          if (row.patient_charge_id === getFirstCharge.id) {
            row.is_editable = false
          } else {
            row.is_editable = true
          }
        } else {
          row.is_editable = true
        }

      }

      // const apptId = row.appointment_id as string;
      // const createdTime = new Date(row.created_at).getTime();
      // const prev = earliestByAppointment.get(apptId);
      // if (prev === undefined || createdTime < prev) {
      //   earliestByAppointment.set(apptId, createdTime);
      // }
    }

    const data = billing_summary.map((res) => {
      const applyCharge = res.doctor_id
        ? Number(res.apply_charge || 0)
        : Number(res.temp_apply_charge || 0);

      const taxPercent = res.doctor_id
        ? Number(res.tax || 0)
        : Number(res.temp_tax || 0);

      const additional = Number(res.additional_charge || 0);
      const discount = Number(res.discount_amount || 0);
      const totalBill = Number(res.total || 0);
      const oldBalance = Number(res.balance || 0);
      const amount = res.doctor_id
        ? Number(res.amount || 0)
        : Number(res.temp_amount || 0);

      const sub_total = res.sub_total;

      let paid = 0;
      let Due = 0;
      let balance = 0;

      if (res.payment_status === 'paid') {
        paid = totalBill;
        balance = 0;
        Due = 0;
      } else if (res.payment_status === 'partially_paid') {
        paid = totalBill - Math.abs(oldBalance);
        balance = Math.abs(oldBalance);
        Due = Math.abs(oldBalance);
      } else if (res.payment_status === 'unpaid') {
        paid = 0;
        balance = totalBill;
        Due = totalBill;
      }

      // NEW: determine is_editable based on earliest created_at per appointment
      // const createdTime = new Date(res.created_at).getTime();
      // const earliestTime = earliestByAppointment.get(res.appointment_id);
      // console.log(earliestTime, "earliestTime");

      // const isFirstChargeForAppointment =
      //   earliestTime !== undefined && createdTime === earliestTime;

      // const is_editable = !isFirstChargeForAppointment; // first charge => not editable

      return {
        appointment_id: res.appointment_id,
        transaction_id: res.transaction_id,
        opd_id: res.opd_id,
        ipd_id: res.ipd_id,
        module: res.module,
        department_id: res.department_id,
        charge_type: res.charge_type,
        charge_category: res.charge_category,
        name: res.name,
        surname: res.surname,
        doctor_id: res.doctor_id,
        case_id: res.case_id,
        patient_charge_id: res.patient_charge_id,
        billed_date: res.billed_date,
        billed_time: res.billed_time,
        Qty: res.Qty,
        Standard: res.doctor_id ? res.Standard : res.tem_standard,
        Apply: res.doctor_id ? res.apply_charge : res.temp_apply_charge,
        Discount: res.Discount,
        discount_percentage: res.discount_percentage,
        payment_status: res.payment_status,
        sub_total: sub_total,
        balance: balance,
        TAX: taxPercent,
        Billed: totalBill,
        additional_charge: additional,
        paid: paid,
        Due: Due,
        package_id: res.package_id,
        package_name: res.package_name,
        package_tax: res.package_tax,
        package_amount: res.package_amount,
        package_category_id: res.package_category_id,
        package_category_name: res.package_category_name,
        is_editable: res.is_editable, // NEW FIELD IN RESPONSE
      };
    });

    const [count] = await this.dynamicConnection.query(countQuery, countParams);

    const amountFields = [
      'Standard',
      'Apply',
      'Discount',
      'sub_total',
      'balance',
      'TAX',
      'Billed',
      'additional_charge',
      'paid',
      'Due',
    ];
    data.forEach((row) => {
      row[`Qty`] = row.Qty ? row.Qty : 0
      for (const field of amountFields) {
        const val = Number(row[field]);
        row[field] = isNaN(val)
          ? 0
          : Math.round((val + Number.EPSILON) * 100) / 100;
      }
    });

    return {
      status: 'success',
      status_code: 200,
      message: 'Data fetched successfully',
      data: { patient_data, billing_summary: data },
      count: count?.totalcount ?? 0,
    };
  }


  async findtransactiondetails(
    patient_id: number,
    payment_module: string,
    limit: number,
    page: number,
    appointment_id: number
  ) {

    try {
      const offset = limit * (page - 1);
      const [getAayushUniqueId] = await this.dynamicConnection.query(
        `select aayush_unique_id from patients where id = ${patient_id}`,
      );

      const [getAdminPatientId] = await this.connection.query(
        `select id from patients where aayush_unique_id = ?`,
        [getAayushUniqueId.aayush_unique_id],
      );

      let appoinment_filter = ''
      if (appointment_id) {
        const data = await this.dynamicConnection.query(`
    SELECT transactions.id
    FROM appointment
    LEFT JOIN patients 
      ON patients.id = appointment.patient_id
    LEFT JOIN opd_details 
      ON opd_details.case_reference_id = appointment.case_reference_id
    LEFT JOIN appointment_status 
      ON appointment_status.id = appointment.appointment_status_id
    LEFT JOIN staff 
      ON staff.id = appointment.doctor
    LEFT JOIN patient_charges 
      ON patient_charges.opd_id = opd_details.id
    LEFT JOIN transactions 
      ON transactions.id = patient_charges.transaction_id
    WHERE appointment.id = ?`, [appointment_id])
        const trans_ids = [...new Set(data.map(res => res.id))];

        if (trans_ids.length > 0) {
          appoinment_filter = ` AND transactions.hos_transaction_id IN (${trans_ids.join(',')})`;
        }
      }


      let query = `select   
  DATE(CONVERT_TZ(patient_charges.created_at, '+00:00', '+05:30')) AS billed_date,

  time(patient_charges.created_at) as billed_time,
    patient_charges.id as patient_charge_id,
DATE(CONVERT_TZ(payment_date, '+00:00', '+05:30')) AS paid_date,
TIME_FORMAT(
  CONVERT_TZ(payment_date, '+00:00', '+05:30'),
  '%H:%i:%s'
) AS paid_time,
CASE
  WHEN transactions.payment_mode = 'aayush_coins' THEN
    'aayush_coins'

  ELSE
    CONCAT(
      COALESCE(transactions.net_banking_transaction_id, ''),
      COALESCE(transactions.card_transaction_id, ''),
      COALESCE(transactions.upi_transaction_id, ''),
      COALESCE(transactions.payment_reference_number, ''),
      COALESCE(transactions.cash_transaction_id, '')
    )
END AS payment_transaction_id,
            transactions.id plenome_transaction_id,
            transactions.hos_transaction_id hos_transaction_id,
            transactions.payment_mode,
            "success" payment_status,
            (COALESCE(transactions.amount,0) + COALESCE(transactions.temp_appt_amount,0)) amount_paid
            from appointment
left join visit_details on visit_details.id = appointment.visit_details_id
left join opd_details on opd_details.id = visit_details.opd_details_id
left join ipd_details on ipd_details.case_reference_id = appointment.case_reference_id
left join patient_charges on (patient_charges.opd_id = opd_details.id or patient_charges.ipd_id = ipd_details.id)
left join transactions on transactions.id = patient_charges.transaction_id where transactions.patient_id = ${getAdminPatientId.id} ${appoinment_filter}`;

      let countquery = `
    SELECT 
        COUNT(DISTINCT transactions.id) AS total
    FROM appointment
    LEFT JOIN visit_details 
        ON visit_details.id = appointment.visit_details_id
    LEFT JOIN opd_details 
        ON opd_details.id = visit_details.opd_details_id
    LEFT JOIN ipd_details 
        ON ipd_details.case_reference_id = appointment.case_reference_id
    LEFT JOIN patient_charges 
        ON (
            patient_charges.opd_id = opd_details.id 
            OR patient_charges.ipd_id = ipd_details.id
        )
    LEFT JOIN transactions 
        ON transactions.id = patient_charges.transaction_id
    WHERE transactions.patient_id = ${getAdminPatientId.id} ${appoinment_filter}`;

      if (payment_module) {

        switch (payment_module) {
          case 'opd':

            query += ` and appointment.module = 'OPD' AND patient_charges.opd_id IS NOT NULL`;
            countquery += ` and appointment.module = 'OPD' AND patient_charges.opd_id IS NOT NULL`;

            break;
          case 'ipd':

            query += ` AND (
    appointment.module = 'IPD'
    OR (
        appointment.module IN ("APPOINTMENT", "OPD")
        AND patient_charges.ipd_id IS NOT NULL
    )
)`;

            countquery += ` AND (
    appointment.module = 'IPD'
    OR (
        appointment.module IN ("APPOINTMENT", "OPD")
        AND patient_charges.ipd_id IS NOT NULL
    )
)`;
            break;
          case 'appointment':

            query += ` and appointment.module = 'APPOINTMENT'`;
            countquery += ` and appointment.module = 'APPOINTMENT'`;
            break;
          default:

            query += ` and appointment.module = 'ABCD'`;
            countquery += ` and appointment.module = 'ABCD'`;
            break;
        }
      }

      let [count] = await this.connection.query(countquery);

      const transaction_data = await this.connection.query(
        query +
        ` GROUP BY transactions.id ORDER BY patient_charges.created_at DESC limit ${limit} offset ${offset}`,
      );

      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        data: transaction_data,
        count: count?.total || 0,
      };
    } catch (error) {
      console.log(error, 'lkjhgfd');
    }
  }

  async patient_wallet(patient_id: string) {
    const [patient_mobile_num] = await this.dynamicConnection.query(
      `select mobileno from patients WHERE id = ?`,
      [patient_id],
    );
    const [value_per_coin] = await this.connection.query(
      `select * from coin_value order by created_at desc limit 1`,
    );

    if (!patient_mobile_num || !patient_mobile_num.mobileno) {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No mobile number found for given patient_id',
      };
    }

    let num = patient_mobile_num.mobileno;

    if (num.length === 10) {
      num = `91${num}`;
    }

    const [existing_user]: any = await this.connection.query(
      `select user_id from users where username = ?`,
      [num],
    );

    if (existing_user) {

      const aayush_coins = await this.connection.query(
        `select coalesce(sum(total_coins_remaining), 0) coins_remaining from aayush_coins where user_id = ${existing_user.user_id} and coins_usage <> 'fully_used'  and is_expired = 0`,
      );
      aayush_coins[0].value_per_coin = value_per_coin;
      //aayush_coins[0].existing_user = existing_user;
      //aayush_coins[0].patient_mobile_num = patient_mobile_num;
      //aayush_coins[0].num = num;

      if (aayush_coins.length === 0) {
        return {
          status: 'failed',
          status_code: 201,
          message: 'No Aayush coins available for the patient',
        };
      }

      return aayush_coins;
    } else {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No user found with mobile number',
      };
    }
  }

  async coins_used(patient_id: number, coins_used: number) {
    coins_used = Number(coins_used);
    const [patient_mobile_num] = await this.dynamicConnection.query(
      `select mobileno from patients WHERE id = ?`,
      [patient_id],
    );

    if (!patient_mobile_num || !patient_mobile_num.mobileno) {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No mobile number found for given patient_id',
      };
    }

    let num = patient_mobile_num.mobileno;

    if (num.length === 10) {
      num = `91${num}`;
    }

    const [existing_user]: any = await this.connection.query(
      `select id user_id from users where username = ?`,
      [num],
    );
    if (existing_user) {
      const [total_aayush_coins] = await this.connection.query(
        `select coalesce(sum(total_coins_remaining), 0) coins_remaining from aayush_coins where user_id = ${existing_user.user_id} and coins_usage <> 'fully_used'  and is_expired = 0`,
      );

      if (
        parseFloat(Number(total_aayush_coins.coins_remaining).toFixed(2)) <
        parseFloat(Number(coins_used).toFixed(2))
      ) {
        return {
          status: 'failed',
          status_code: 400,
          message: 'coins_used must be lesser than or equal to available coins',
        };
      }
      const aayush_coins = await this.connection.query(
        `select * from aayush_coins where user_id = ${existing_user.user_id} and coins_usage <> 'fully_used'  and is_expired = 0 order by created_at asc`,
      );

      if (aayush_coins.length === 0) {
        return {
          status: 'failed',
          status_code: 201,
          message: 'No Aayush coins available for the patient',
        };
      }

      for (const a of aayush_coins) {
        if (coins_used > 0) {
          if (
            parseFloat(Number(a.total_coins_remaining).toFixed(2)) <=
            parseFloat(Number(coins_used).toFixed(2))
          ) {
            let total_coins_used =
              parseFloat(Number(a.total_coins_used).toFixed(2)) +
              parseFloat(Number(a.total_coins_remaining).toFixed(2));

            const query = await this.connection.query(
              `update aayush_coins set total_coins_remaining = 0, total_coins_used = ${total_coins_used}, coins_usage= 'fully_used' where id = ?`,
              [Number(a.id)],
            );

            coins_used =
              parseFloat(Number(coins_used).toFixed(2)) -
              parseFloat(Number(a.total_coins_remaining).toFixed(2));
          } else {
            let total_coins_remaining =
              parseFloat(Number(a.total_coins_remaining).toFixed(2)) -
              parseFloat(Number(coins_used).toFixed(2));

            let total_coins_used =
              parseFloat(Number(a.total_coins_used).toFixed(2)) +
              parseFloat(Number(coins_used).toFixed(2));

            const query = await this.connection.query(
              `update aayush_coins set total_coins_remaining = ${parseFloat(
                Number(total_coins_remaining).toFixed(2),
              )}, total_coins_used = ${parseFloat(
                Number(total_coins_used).toFixed(2),
              )}, coins_usage= 'partially_used' where id = ?`,
              [Number(a.id)],
            );
            coins_used = 0;
          }
        }
      }
      const aayush_coins_after_usage = await this.connection.query(
        `select * from aayush_coins where user_id = ${existing_user.user_id} and coins_usage <> 'fully_used'  and is_expired = 0 order by created_at asc`,
      );

      return aayush_coins_after_usage;
    } else {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No user found with mobile number',
      };
    }
  }

  async mongodata(Entity: transaction_details) {
    const response = new this.ServiceModel({
      hospital_id: Entity.hospital_id,
      patient_aayush_id: Entity.patient_aayush_id,
      plenome_transaction_id: Entity.plenome_transaction_id,
      hos_transaction_id: Entity.hos_transaction_id,
      transaction_details: Entity.transaction_details,
      patient_details: Entity.patient_details,
      primary_cons_doctor: Entity.primary_cons_doctor,
      last_cons_doctor: Entity.last_cons_doctor,
      amount_paid: Entity.amount_paid,
      payment_mode: Entity.payment_mode,
      payment_status: Entity.payment_status,
      payment_transaction_id: Entity.payment_transaction_id,
      created_at: new Date(),
    });
    let result = await response.save();
    return result;
  }

  async makePaymentV3(Entity: makepaymentV3) {
    if (Entity.payment_mode == 'online') {
      if (!Entity.payment_gateway) {
        return {
          status: process.env.ERROR_STATUS,
          message: process.env.PAYMENT_GATEWAY,
        };
      }

      if (Entity.payment_gateway.toLocaleLowerCase() == 'razorpay') {
        if (!Entity.payment_reference_number || !Entity.payment_id) {
          if (!Entity.payment_reference_number) {
            Entity.payment_reference_number = 'NA';
          }
          if (!Entity.payment_id) {
            Entity.payment_id = 'NA';
          }
          return {
            status: process.env.ERROR_STATUS,
            message: process.env.PAYMENT_REFERENCE,
          };
        }
      }
    }
    let getVal = Entity.paymentDetails;

    const ids = getVal.map((item) => item.patient_charge_id);

    let amount = Number(Entity.totalDue);
    let calculated_amt = 0;
    for (const a of ids) {
      const [new_amt] = await this.dynamicConnection.query(
        `select balance as  amt from patient_charges where id = ${a}`,
      );
      calculated_amt = calculated_amt + (Number(new_amt.amt) * -1);
    }

    // if (calculated_amt > amount) {
    //   return {
    //     status: 'failed',
    //     status_code: 400,
    //     message: 'due amount exceeds the given amount',
    //   };
    // }
    if (Entity.payment_mode.toLocaleLowerCase() == 'cash') {
      const generateUpperAlphaNumId = customAlphabet(
        '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
        10,
      );

      Entity.cash_transaction_id = generateUpperAlphaNumId();
    }
    const firstSectionId = getVal[0]?.department_id;
    const allSameSectionId = getVal.every(
      (charge) => charge.department_id === firstSectionId,
    );
    const letters = firstSectionId.match(/[A-Za-z]+/)?.[0] ?? '';
    let numbers = firstSectionId.match(/\d+/)?.[0] ?? '';
    let sectionIdName;
    let idkey;
    let adminKey;

    if (!allSameSectionId) {
      sectionIdName = 'Payment';
      idkey = 'appointment_id';
      adminKey = null;
      numbers = null;
    } else {
      switch (letters) {
        case 'APPN':
          sectionIdName = 'Appointment';
          idkey = 'appointment_id';
          const [getAdminAppId] = await this.connection.query(
            `select id from appointment where Hospital_id = ? and hos_appointment_id = ?`,
            [Entity.Hospital_id, numbers],
          );

          adminKey = getAdminAppId.id;
          break;
        case 'OPDN':
          sectionIdName = 'OPD';
          idkey = 'opd_id';
          const [getAdminOpdId] = await this.connection.query(
            `select id from opd_details where Hospital_id = ? and hos_opd_id = ?`,
            [Entity.Hospital_id, numbers],
          );
          adminKey = getAdminOpdId.id;
          break;
        case 'IPDN':
          sectionIdName = 'IPD';
          idkey = 'ipd_id';
          const [getAdminIpdId] = await this.connection.query(
            `select id from ipd_details where hospital_id = ? and hospital_ipd_details_id = ?`,
            [Entity.Hospital_id, numbers],
          );
          adminKey = getAdminIpdId.id;
          break;
      }
    }

    try {
      if (!Entity.txn_id) {
        Entity.txn_id = 'NA';
      }
      if (!Entity.bank_ref_id) {
        Entity.bank_ref_id = 'NA';
      }
      if (!Entity.pg_ref_id) {
        Entity.pg_ref_id = 'NA';
      }
      try {

        await this.dynamicConnection.query(
          `ALTER TABLE \`transactions\` 
    ADD COLUMN \`payment_method\`            VARCHAR(45)  NULL AFTER \`received_by_name\`,
    ADD COLUMN \`card_division\`             VARCHAR(255) NULL AFTER \`payment_method\`,
    ADD COLUMN \`card_type\`                 VARCHAR(255) NULL AFTER \`card_division\`,
    ADD COLUMN \`card_transaction_id\`       VARCHAR(255) NULL AFTER \`card_type\`,
    ADD COLUMN \`card_bank_name\`            VARCHAR(255) NULL AFTER \`card_transaction_id\`,
    ADD COLUMN \`net_banking_division\`      VARCHAR(255) NULL AFTER \`card_bank_name\`,
    ADD COLUMN \`net_banking_transaction_id\` VARCHAR(255) NULL AFTER \`net_banking_division\`,
    ADD COLUMN \`upi_id\`                    VARCHAR(255) NULL AFTER \`net_banking_transaction_id\`,
    ADD COLUMN \`upi_bank_name\`             VARCHAR(255) NULL AFTER \`upi_id\`,
    ADD COLUMN \`upi_transaction_id\`        VARCHAR(255) NULL AFTER \`upi_bank_name\`,
      ADD COLUMN \`cash_transaction_id\`     VARCHAR(255) NULL AFTER \`upi_transaction_id\`,
  ;`,
        );
      } catch (error) {
        console.log('error in adding new columns to transactions table');
      }

      try {

        await this.connection.query(
          `ALTER TABLE \`transactions\` 
    ADD COLUMN \`payment_method\`            VARCHAR(45)  NULL AFTER \`received_by_name\`,
    ADD COLUMN \`card_division\`             VARCHAR(255) NULL AFTER \`payment_method\`,
    ADD COLUMN \`card_type\`                 VARCHAR(255) NULL AFTER \`card_division\`,
    ADD COLUMN \`card_transaction_id\`       VARCHAR(255) NULL AFTER \`card_type\`,
    ADD COLUMN \`card_bank_name\`            VARCHAR(255) NULL AFTER \`card_transaction_id\`,
    ADD COLUMN \`net_banking_division\`      VARCHAR(255) NULL AFTER \`card_bank_name\`,
    ADD COLUMN \`net_banking_transaction_id\` VARCHAR(255) NULL AFTER \`net_banking_division\`,
    ADD COLUMN \`upi_id\`                    VARCHAR(255) NULL AFTER \`net_banking_transaction_id\`,
    ADD COLUMN \`upi_bank_name\`             VARCHAR(255) NULL AFTER \`upi_id\`,
    ADD COLUMN \`upi_transaction_id\`        VARCHAR(255) NULL AFTER \`upi_bank_name\`,
      ADD COLUMN \`cash_transaction_id\`     VARCHAR(255) NULL AFTER \`upi_transaction_id\`,
  ;`,
        );
      } catch (error) {
        console.log('error in adding new columns to transactions table');
      }
      const insertTransaction = await this.dynamicConnection.query(
        `insert into transactions (
          txn_id,
          pg_ref_id,
          bank_ref_id,
          type,
          patient_id,
          amount,
          payment_mode,
          section,
          payment_date,
          received_by_name,
          payment_method,
          cash_transaction_id,
          card_division,
          card_type,
          card_transaction_id,
          card_bank_name,
          net_banking_division,
          net_banking_transaction_id,
          upi_id,
          upi_bank_name,
          upi_transaction_id,
          payment_reference_number,
          payment_gateway,
          payment_id,
          actual_paid_amount,
          wallet_paid_amount,
          ${idkey}
          ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [
          Entity.txn_id,
          Entity.pg_ref_id,
          Entity.bank_ref_id,
          'payment',
          Entity.patient_id,
          amount,
          Entity.payment_mode,
          sectionIdName,
          Entity.payment_date,
          Entity.received_by_name,
          Entity.payment_method,
          Entity.cash_transaction_id,
          Entity.card_division,
          Entity.card_type,
          Entity.card_transaction_id,
          Entity.card_bank_name,
          Entity.net_banking_division,
          Entity.net_banking_transaction_id,
          Entity.upi_id,
          Entity.upi_bank_name,
          Entity.upi_transaction_id,
          Entity.payment_reference_number,
          Entity.payment_gateway,
          Entity.payment_id,
          Entity.actual_amount_paid,
          Entity.amount_from_coins,
          numbers,
        ],
      );
      const [getPatMobileno] = await this.dynamicConnection.query(
        `select aayush_unique_id from patients where id = ?`,
        [Entity.patient_id],
      );

      const [getAdminPatientId] = await this.connection.query(
        `select id from patients where aayush_unique_id = ?`,
        [getPatMobileno.aayush_unique_id],
      );

      const AdmininsertTransaction = await this.connection.query(
        `insert into transactions (
        txn_id,
        pg_ref_id,
        bank_ref_id,
        type,
        patient_id,
        amount,
        payment_mode,
        cash_transaction_id,
        Hospital_id,
        hos_transaction_id,
        section,
        payment_date,
        received_by_name,
        payment_method,
          card_division,
          card_type,
          card_transaction_id,
          card_bank_name,
          net_banking_division,
          net_banking_transaction_id,
          upi_id,
          upi_bank_name,
          upi_transaction_id,
          payment_reference_number,
          payment_gateway,
          payment_id,
          actual_paid_amount,
          wallet_paid_amount,
        ${idkey}
          ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [
          Entity.txn_id,
          Entity.pg_ref_id,
          Entity.bank_ref_id,
          'payment',
          getAdminPatientId.id,
          amount,
          Entity.payment_mode,
          Entity.cash_transaction_id,
          Entity.Hospital_id,
          insertTransaction.insertId,
          sectionIdName,
          Entity.payment_date,
          Entity.received_by_name,
          Entity.payment_method,
          Entity.card_division,
          Entity.card_type,
          Entity.card_transaction_id,
          Entity.card_bank_name,
          Entity.net_banking_division,
          Entity.net_banking_transaction_id,
          Entity.upi_id,
          Entity.upi_bank_name,
          Entity.upi_transaction_id,
          Entity.payment_reference_number,
          Entity.payment_gateway,
          Entity.payment_id,
          Entity.actual_amount_paid,
          Entity.amount_from_coins,
          adminKey,
        ],
      );
      for (const id of ids) {
        try {
          let [getAdminChargeId] = await this.connection.query(
            `select id from patient_charges where Hospital_id = ? and hos_patient_charges_id = ?`,
            [Entity.Hospital_id, id],
          );

          await this.dynamicConnection.query(
            `update patient_charges set payment_status = ?,transaction_id = ?, balance = 0 where id = ?`,
            ['paid', insertTransaction.insertId, id],
          );

          this.connection.query(
            `update patient_charges set payment_status = ?,transaction_id = ?, balance = 0 where id = ?`,
            ['paid', AdmininsertTransaction.insertId, getAdminChargeId.id],
          );
        } catch (error) {
          console.log(error, 'error');
        }
      }

      const [getLastConsDoctor] = await this.dynamicConnection.query(
        `select staff.name, staff.surname, staff.employee_id from staff
         left join appointment on appointment.doctor = staff.id where appointment.patient_id = ${Entity.patient_id}
          and date(appointment.date) < date(now()) and  appointment.doctor order by appointment.date desc, appointment.time desc limit 1`,
      );
      const [FrequentVisitDoctorId] = await this.dynamicConnection.query(
        `select count(id) count,doctor from appointment where patient_id = ? and doctor group by doctor order by count desc limit 1`,
        [Entity.patient_id],
      );
      const [getPrimaryConsDoctor] = await this.dynamicConnection.query(
        `select staff.name, staff.surname, staff.employee_id from staff  where id = ?`,
        [FrequentVisitDoctorId.doctor],
      );
      try {
        await this.makePayment_if_app_as_same(insertTransaction.insertId, AdmininsertTransaction.insertId, Entity)
        this.addtransactionInfo(AdmininsertTransaction.insertId);
      } catch (error) {
        console.log(
          'Error occuer while adding transaction info to mongodb:',
          error,
        );
      }

      // const mongoBody = {
      //   hospital_id: Entity.Hospital_id,
      //   patient_aayush_id: getPatMobileno.aayush_unique_id,
      //   hos_transaction_id: insertTransaction.insertId,
      //   plenome_transaction_id: AdmininsertTransaction.insertId,
      //   transaction_details: Entity.paymentDetails,
      //   patient_details: Entity.patient_details,
      //   primary_cons_doctor: getPrimaryConsDoctor,
      //   last_cons_doctor: getLastConsDoctor,
      //   amount_paid: amount,
      //   payment_mode: Entity.payment_mode,
      //   payment_status: 'success',
      //   payment_transaction_id:
      //     Entity.upi_transaction_id +
      //     Entity.net_banking_transaction_id +
      //     Entity.card_transaction_id +
      //     Entity.cash_transaction_id +
      //     Entity.payment_reference_number,
      // };
      // this.mongodata(mongoBody);
      return {
        status: 'success',
        message: 'payment done successfully',
        transactionId: 'TRID' + insertTransaction.insertId,
      };
    } catch (error) {
      console.log(error, 'error');

      return error;
    }
  }
  async makePayment_if_app_as_same(
    hos_transaction_id: number,
    admin_transaction_id: number,
    entity: makepaymentV3,
  ): Promise<boolean> {
    const details = entity?.paymentDetails;

    if (!details || details.length === 0) {
      return false;
    }

    const firstAppointmentId = details[0]?.appointment_id;
    if (!firstAppointmentId) {
      return false;
    }

    const hospital_id = entity.Hospital_id;
    const numbers = firstAppointmentId.match(/\d+/)?.[0];

    if (!numbers) {
      return false;
    }

    const isSameAppointment = details
      .filter(item => item?.appointment_id)
      .every(item => item.appointment_id === firstAppointmentId);

    if (!isSameAppointment) {
      return false;
    }

    const [adminAppointment]: any = await this.connection.query(
      `
    SELECT id
    FROM appointment
    WHERE Hospital_id = ? AND hos_appointment_id = ?
    `,
      [hospital_id, numbers],
    );

    if (!adminAppointment?.id) {
      return false;
    }

    await this.connection.query(
      `
    UPDATE transactions
    SET appointment_id = ?
    WHERE id = ?
    `,
      [adminAppointment.id, admin_transaction_id],
    );

    await this.dynamicConnection.query(
      `
    UPDATE transactions
    SET appointment_id = ?
    WHERE id = ?
    `,
      [numbers, hos_transaction_id],
    );

    return true;
  }


  async get_transaction_details(
    hospital_id: number,
    hos_transaction_id: string,
  ) {
    try {
      const records = await this.ServiceModel
        .find({ hospital_id, hos_transaction_id })
        .sort({ _id: 1 })
        .limit(2);

      const data =
        records.length === 0
          ? null
          : records.length > 1
            ? records[records.length - 1]
            : records[0];

      const tem_data = records.length > 1 ? records[0] : null;

      if (data?.transaction_details?.length) {
        data.transaction_details = data.transaction_details.map((res) => ({
          ...res,
          hub_billed_date: moment
            .utc(res.billed_date)
            .add(5, 'hours')
            .add(30, 'minutes')
            .toISOString()
        }));
      }

      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        data: data ? [data] : [],
        tem_data: tem_data ? [tem_data] : [],
      };
    } catch (error) {
      console.error('get_transaction_details error:', error);
      return {
        status: 'failed',
        status_code: 500,
        message: 'API SERVICE UNAVAILABLE TEMPORARILY',
      };
    }
  }

  async add_overall_charges(
    aayush_unique_id: string,
    hospital_id: number,
    body: any,
  ) {
    if (!body.due_amount) {
      body.due_amount = null;
    }
    if (!body.overall_subtotal_amount) {
      body.overall_subtotal_amount = null;
    }
    if (!body.total_billed_amount) {
      body.total_billed_amount = null;
    }
    if (!body.total_tax_amount) {
      body.total_tax_amount = null;
    }
    try {
      const [hos_pat_id] = await this.dynamicConnection.query(
        `select id from patients where aayush_unique_id= ?`,
        [aayush_unique_id],
      );

      const [admin_pat_id] = await this.connection.query(
        `select id, mobileno from patients where aayush_unique_id = ?`,
        [aayush_unique_id],
      );

      const [admin_user_id] = await this.connection.query(
        `select id from users where username = ?`,
        [91 + admin_pat_id.mobileno],
      );

      let user_id;
      const hospital_patient_id = hos_pat_id.id;
      const admin_patient_id = admin_pat_id.id;

      if (!admin_user_id) {

        const user = await this.connection.query(
          `insert into users (username,role) values (?, ?)`,
          [91 + admin_pat_id.mobileno, 'patient'],
        );
        user_id = user.insertId;
      } else {

        user_id = admin_user_id.id;
      }

      const [check_user_payment_summary] = await this.connection.query(
        `select id from user_payment_summary where user_id = ?`,
        [user_id],
      );

      let user_payment_summary_id;
      if (!check_user_payment_summary) {
        const insert_ad = await this.connection.query(
          `insert into user_payment_summary (user_id, wallet_amount_paid, paid_amount, due_amount, actual_amount_paid,total_online_payments, total_offline_payments, total_billed_amount, total_tax_amount, overall_subtotal_amount) values (?, 0,0,0,0,0,0,0,0,0)`,
          [user_id],
        );
        user_payment_summary_id = insert_ad.insertId;
      } else {
        user_payment_summary_id = check_user_payment_summary.id;
      }

      const [getExisting] = await this.connection.query(
        `select * from user_payment_summary where id = ?`,
        [user_payment_summary_id],
      );

      const existingDueAmt = Number(getExisting.due_amount) || 0;

      const existingTotalBilledAmt =
        Number(getExisting.total_billed_amount) || 0;

      const existingTotalTaxAmt = Number(getExisting.total_tax_amount) || 0;

      const existingOverallSubtotalAmt =
        Number(getExisting.overall_subtotal_amount) || 0;

      const NewDueAmt = existingDueAmt + Number(body.due_amount);

      const NewTotalBilledAmt =
        existingTotalBilledAmt + Number(body.total_billed_amount);

      const NewTotalTaxAmt =
        existingTotalTaxAmt + Number(body.total_tax_amount);

      const NewOverallSubtotalAmt =
        existingOverallSubtotalAmt + Number(body.overall_subtotal_amount);

      const updated_data = await this.connection.query(
        `update user_payment_summary set due_amount = ?,total_billed_amount = ?,total_tax_amount = ?,overall_subtotal_amount = ? where id = ?`,
        [
          NewDueAmt,
          NewTotalBilledAmt,
          NewTotalTaxAmt,
          NewOverallSubtotalAmt,
          user_payment_summary_id,
        ],
      );

      let patient_id = admin_patient_id;
      const [check_patient_payment_summary] = await this.connection.query(
        `select id from patient_payment_summary where patient_id = ?`,
        [patient_id],
      );
      let patient_payment_summary_id;
      if (!check_patient_payment_summary) {
        const insert_ad = await this.connection.query(
          `insert into patient_payment_summary (patient_id, wallet_amount_paid, paid_amount, due_amount, actual_amount_paid,total_online_payments, total_offline_payments, total_billed_amount, total_tax_amount, overall_subtotal_amount) values (?, 0,0,0,0,0,0,0,0,0)`,
          [patient_id],
        );
        patient_payment_summary_id = insert_ad.insertId;
      } else {
        patient_payment_summary_id = check_patient_payment_summary.id;
      }
      const [getExistingPat] = await this.connection.query(
        `select * from patient_payment_summary where id = ?`,
        [patient_payment_summary_id],
      );
      const existingDueAmt1 = Number(getExistingPat.due_amount) || 0;
      const existingTotalBilledAmt1 =
        Number(getExistingPat.total_billed_amount) || 0;
      const existingTotalTaxAmt1 = Number(getExistingPat.total_tax_amount) || 0;
      const existingOverallSubtotalAmt1 =
        Number(getExistingPat.overall_subtotal_amount) || 0;
      const NewDueAmt1 = existingDueAmt1 + Number(body.due_amount);
      const NewTotalBilledAmt1 =
        existingTotalBilledAmt1 + Number(body.total_billed_amount);
      const NewTotalTaxAmt1 =
        existingTotalTaxAmt1 + Number(body.total_tax_amount);
      const NewOverallSubtotalAmt1 =
        existingOverallSubtotalAmt1 + Number(body.overall_subtotal_amount);
      const updated_data1 = await this.connection.query(
        `update patient_payment_summary set due_amount = ?,total_billed_amount = ?,total_tax_amount = ?,overall_subtotal_amount = ? where id = ?`,
        [
          NewDueAmt1,
          NewTotalBilledAmt1,
          NewTotalTaxAmt1,
          NewOverallSubtotalAmt1,
          patient_payment_summary_id,
        ],
      );

      const [hos_check_patient_payment_summary] =
        await this.dynamicConnection.query(
          `select id from patient_payment_summary where patient_id = ?`,
          [hospital_patient_id],
        );
      let hos_patient_payment_summary_id;
      if (!hos_check_patient_payment_summary) {
        const insert_ad = await this.dynamicConnection.query(
          `insert into patient_payment_summary (patient_id, wallet_amount_paid, paid_amount, due_amount, actual_amount_paid,total_online_payments, total_offline_payments, total_billed_amount, total_tax_amount, overall_subtotal_amount) values (?, 0,0,0,0,0,0,0,0,0)`,
          [hospital_patient_id],
        );
        hos_patient_payment_summary_id = insert_ad.insertId;
      } else {
        hos_patient_payment_summary_id = hos_check_patient_payment_summary.id;
      }
      const updated_data2 = await this.dynamicConnection.query(
        `update patient_payment_summary set due_amount = ?,total_billed_amount = ?,total_tax_amount = ?,overall_subtotal_amount = ? where id = ?`,
        [
          NewDueAmt1,
          NewTotalBilledAmt1,
          NewTotalTaxAmt1,
          NewOverallSubtotalAmt1,
          hos_patient_payment_summary_id,
        ],
      );

      const [check_hospital_payment_summary] = await this.connection.query(
        `select id from hospital_payment_summary where hospital_id = ?`,
        [hospital_id],
      );
      let hos_payment_summary_id;
      if (!check_hospital_payment_summary) {
        const insert_ad = await this.connection.query(
          `insert into hospital_payment_summary (hospital_id, wallet_amount_paid, paid_amount, due_amount, actual_amount_paid,total_online_payments, total_offline_payments, total_billed_amount, total_tax_amount, overall_subtotal_amount) values (?, 0,0,0,0,0,0,0,0,0)`,
          [hospital_id],
        );
        hos_payment_summary_id = insert_ad.insertId;
      } else {
        hos_payment_summary_id = check_hospital_payment_summary.id;
      }
      const [getExistingPaymentid] = await this.connection.query(
        `select * from hospital_payment_summary where id = ?`,
        [hos_payment_summary_id],
      );
      const hosexistingDueAmt1 = Number(getExistingPaymentid.due_amount) || 0;
      const hosexistingTotalBilledAmt1 =
        Number(getExistingPaymentid.total_billed_amount) || 0;
      const hosexistingTotalTaxAmt1 =
        Number(getExistingPaymentid.total_tax_amount) || 0;
      const hosexistingOverallSubtotalAmt1 =
        Number(getExistingPaymentid.overall_subtotal_amount) || 0;
      const hosNewDueAmt1 = hosexistingDueAmt1 + Number(body.due_amount);
      const hosNewTotalBilledAmt1 =
        hosexistingTotalBilledAmt1 + Number(body.total_billed_amount);
      const hosNewTotalTaxAmt1 =
        hosexistingTotalTaxAmt1 + Number(body.total_tax_amount);
      const hosNewOverallSubtotalAmt1 =
        hosexistingOverallSubtotalAmt1 + Number(body.overall_subtotal_amount);
      const hos_updated_data1 = await this.connection.query(
        `update hospital_payment_summary set due_amount = ?,total_billed_amount = ?,total_tax_amount = ?,overall_subtotal_amount = ? where id = ?`,
        [
          hosNewDueAmt1,
          hosNewTotalBilledAmt1,
          hosNewTotalTaxAmt1,
          hosNewOverallSubtotalAmt1,
          hos_payment_summary_id,
        ],
      );

      return {
        status: 'success',
        status_code: 200,
        message: 'payment added successfully',
      };
    } catch (error) {
      console.log(error, 'err');

      return {
        status: 'failed',
        status_code: 500,
        message: 'API SERVICE UNAVAILABLE TEMPORARYILY',
      };
    }
  }

  async finddepartment_id(
    patient_id: number,
    hospital_id: number,
    module: string,
  ) {
    module = module?.toLocaleLowerCase();
    if (module == 'appn') {
      const appointment = `
    SELECT
      CASE
        WHEN appointment.module = 'appointment' THEN CONCAT('APPN', appointment.id)
        WHEN appointment.module = 'OPD' THEN CONCAT('OPDN', opd_details.id)
      END AS department_id
    FROM appointment
    LEFT JOIN visit_details ON appointment.visit_details_id = visit_details.id
    LEFT JOIN opd_details ON visit_details.opd_details_id = opd_details.id
    LEFT JOIN patients ON appointment.patient_id = patients.id
    WHERE patients.id = ?
      AND appointment.module != 'IPD'
            AND appointment.module != 'OPD'
      AND appointment_status_id NOT IN (1,2,3,4,6)
  `;

      const app = await this.dynamicConnection.query(appointment, [patient_id]);

      const final_result = [...app];

      return final_result;
    } else if (module == 'opdn') {
      const appointment = `
    SELECT
      CASE
        WHEN appointment.module = 'appointment' THEN CONCAT('APPN', appointment.id)
        WHEN appointment.module = 'OPD' THEN CONCAT('OPDN', opd_details.id)
      END AS department_id
    FROM appointment
    LEFT JOIN visit_details ON appointment.visit_details_id = visit_details.id
    LEFT JOIN opd_details ON visit_details.opd_details_id = opd_details.id
    LEFT JOIN patients ON appointment.patient_id = patients.id
    WHERE patients.id = ?
      AND appointment.module != 'IPD'
            AND appointment.module != 'APPOINTMENT'
      AND appointment_status_id NOT IN (1,2,3,4,6)
  `;

      const app = await this.dynamicConnection.query(appointment, [patient_id]);

      const final_result = [...app];

      return final_result;
    } else if (module == 'ipdn') {
      const ipd = `
     SELECT distinct CONCAT('IPDN', ipd_details.id) AS department_id
    FROM ipd_details
    left join patients on ipd_details.patient_id = patients.id
    left join appointment on appointment.patient_id = patients.id
    WHERE patients.id = ? AND appointment_status_id NOT IN (1,2,3,4,6)
  `;

      const many = await this.dynamicConnection.query(ipd, [patient_id]);

      const final_result = [...many];

      return final_result;
    } else {
      const appointment = `
    SELECT
      CASE
        WHEN appointment.module = 'appointment' THEN CONCAT('APPN', appointment.id)
        WHEN appointment.module = 'OPD' THEN CONCAT('OPDN', opd_details.id)
      END AS department_id
    FROM appointment
    LEFT JOIN visit_details ON appointment.visit_details_id = visit_details.id
    LEFT JOIN opd_details ON visit_details.opd_details_id = opd_details.id
    LEFT JOIN patients ON appointment.patient_id = patients.id
    WHERE patients.id = ?
      AND appointment.module != 'IPD'
      AND appointment_status_id NOT IN (1,2,3,4,6)
  `;

      const ipd = `
     SELECT distinct CONCAT('IPDN', ipd_details.id) AS department_id
    FROM ipd_details
    left join patients on ipd_details.patient_id = patients.id
    left join appointment on appointment.patient_id = patients.id
    WHERE patients.id = ? AND appointment_status NOT IN (1,2,3,4,6)
  `;

      const app = await this.dynamicConnection.query(appointment, [patient_id]);
      const many = await this.dynamicConnection.query(ipd, [patient_id]);

      const final_result = [...app, ...many];

      return final_result;
    }
  }

  async update_overall_charges(
    aayush_unique_id: string,
    hospital_id: number,
    body: any,
  ) {
    if (!body.wallet_amount_paid) {
      body.wallet_amount_paid = null;
    }
    if (!body.paid_amount) {
      body.paid_amount = null;
    }
    if (!body.actual_amount_paid) {
      body.actual_amount_paid = null;
    }
    if (!body.total_online_payments) {
      body.total_online_payments = null;
    }
    if (!body.total_offline_payments) {
      body.total_offline_payments = null;
    }
    try {
      const [hos_pat_id] = await this.dynamicConnection.query(
        `select id from patients where aayush_unique_id= ?`,
        [aayush_unique_id],
      );

      const [admin_pat_id] = await this.connection.query(
        `select id, mobileno from patients where aayush_unique_id = ?`,
        [aayush_unique_id],
      );

      const [admin_user_id] = await this.connection.query(
        `select id,user_id from users where username = ?`,
        [91 + admin_pat_id.mobileno],
      );

      let user_id;
      const hospital_patient_id = hos_pat_id.id;
      const admin_patient_id = admin_pat_id.id;

      if (!admin_user_id) {
        const user = await this.connection.query(
          `insert into users (username,role,user_id) values (?, ?,?)`,
          [91 + admin_pat_id.mobileno, 'patient', admin_patient_id],
        );
        user_id = admin_patient_id;
      } else {
        user_id = admin_user_id.id;
      }
      const [user_coins] = await this.connection.query(
        `select sum(total_coins_remaining) coinsAvailable from aayush_coins where user_id = ?`,
        [user_id],
      );

      const remaining = parseFloat(
        Number(user_coins.coinsAvailable).toFixed(2),
      );

      const used = parseFloat(Number(body.wallet_amount_paid).toFixed(2));

      if (remaining < used) {
        return {
          status: 'failed',
          status_code: 400,
          message: 'Available coins lesser than the given coins.',
        };
      }

      const [check_user_payment_summary] = await this.connection.query(
        `select id from user_payment_summary where user_id = ?`,
        [user_id],
      );

      let user_payment_summary_id;
      if (check_user_payment_summary) {
        user_payment_summary_id = check_user_payment_summary.id;
      } else {
        return {
          status_code: 400,
          status: 'failed',
          message: 'Add charges for user to proceed for payment',
        };
      }

      const [getExisting] = await this.connection.query(
        `select * from user_payment_summary where id = ?`,
        [user_payment_summary_id],
      );
      const due = Number(getExisting.due_amount) || 0;

      const existingDueAmt = Number(getExisting.wallet_amount_paid) || 0;

      const existingTotalBilledAmt = Number(getExisting.paid_amount) || 0;

      const existingTotalTaxAmt = Number(getExisting.actual_amount_paid) || 0;

      const existingOverallSubtotalAmt =
        Number(getExisting.total_online_payments) || 0;

      const existingOverallSubtotalAmtoff =
        Number(getExisting.total_offline_payments) || 0;

      const reducedDue = due - Number(body.paid_amount);
      const NewDueAmt = existingDueAmt + Number(body.wallet_amount_paid);

      const NewTotalBilledAmt =
        existingTotalBilledAmt + Number(body.paid_amount);

      const NewTotalTaxAmt =
        existingTotalTaxAmt + Number(body.actual_amount_paid);

      const NewOverallSubtotalAmt =
        existingOverallSubtotalAmt + Number(body.total_online_payments);

      const NewOverallSubtotalAmtoff =
        existingOverallSubtotalAmtoff + Number(body.total_offline_payments);

      const updated_data = await this.connection.query(
        `update user_payment_summary set wallet_amount_paid = ?,due_amount = ?,paid_amount = ?,actual_amount_paid = ?,total_online_payments = ?, total_offline_payments = ? where id = ?`,
        [
          NewDueAmt,
          reducedDue,
          NewTotalBilledAmt,
          NewTotalTaxAmt,
          NewOverallSubtotalAmt,
          NewOverallSubtotalAmtoff,
          user_payment_summary_id,
        ],
      );

      let patient_id = admin_patient_id;
      const [check_patient_payment_summary] = await this.connection.query(
        `select id from patient_payment_summary where patient_id = ?`,
        [patient_id],
      );
      let patient_payment_summary_id;
      if (check_patient_payment_summary) {
        patient_payment_summary_id = check_patient_payment_summary.id;
      }
      const [getExistingPat] = await this.connection.query(
        `select * from patient_payment_summary where id = ?`,
        [patient_payment_summary_id],
      );
      const due1 = Number(getExistingPat.due_amount) || 0;
      const existingDueAmt1 = Number(getExistingPat.wallet_amount_paid) || 0;
      const existingTotalBilledAmt1 = Number(getExistingPat.paid_amount) || 0;
      const existingTotalTaxAmt1 =
        Number(getExistingPat.actual_amount_paid) || 0;
      const existingOverallSubtotalAmt1 =
        Number(getExistingPat.total_online_payments) || 0;
      const existingOverallSubtotalAmt1off =
        Number(getExistingPat.total_offline_payments) || 0;
      const NewDueAmt1 = existingDueAmt1 + Number(body.wallet_amount_paid);
      const reducedDue1 = due1 - Number(body.paid_amount);
      const NewTotalBilledAmt1 =
        existingTotalBilledAmt1 + Number(body.paid_amount);
      const NewTotalTaxAmt1 =
        existingTotalTaxAmt1 + Number(body.actual_amount_paid);
      const NewOverallSubtotalAmt1 =
        existingOverallSubtotalAmt1 + Number(body.total_online_payments);
      const NewOverallSubtotalAmt1off =
        existingOverallSubtotalAmt1off + Number(body.total_offline_payments);
      const updated_data1 = await this.connection.query(
        `update patient_payment_summary set wallet_amount_paid = ?,due_amount = ?,paid_amount = ?,actual_amount_paid = ?,total_online_payments = ?, total_offline_payments = ? where id = ?`,
        [
          NewDueAmt1,
          reducedDue1,
          NewTotalBilledAmt1,
          NewTotalTaxAmt1,
          NewOverallSubtotalAmt1,
          NewOverallSubtotalAmt1off,
          patient_payment_summary_id,
        ],
      );

      const [hos_check_patient_payment_summary] =
        await this.dynamicConnection.query(
          `select id from patient_payment_summary where patient_id = ?`,
          [hospital_patient_id],
        );
      let hos_patient_payment_summary_id;
      if (hos_check_patient_payment_summary) {
        hos_patient_payment_summary_id = hos_check_patient_payment_summary.id;
      }
      const updated_data2 = await this.dynamicConnection.query(
        `update patient_payment_summary set wallet_amount_paid = ?,due_amount = ?,paid_amount = ?,actual_amount_paid = ?,total_online_payments = ?, total_offline_payments = ? where id = ?`,
        [
          NewDueAmt1,
          reducedDue1,
          NewTotalBilledAmt1,
          NewTotalTaxAmt1,
          NewOverallSubtotalAmt1,
          NewOverallSubtotalAmt1off,
          hos_patient_payment_summary_id,
        ],
      );

      const [check_hos_payment_summary] = await this.connection.query(
        `select id from hospital_payment_summary where hospital_id = ?`,
        [hospital_id],
      );
      let Hospital_payment_summary_id;
      if (check_hos_payment_summary) {
        Hospital_payment_summary_id = check_hos_payment_summary.id;
      }
      const [getExistinghos] = await this.connection.query(
        `select * from hospital_payment_summary where id = ?`,
        [Hospital_payment_summary_id],
      );
      const hosdue1 = Number(getExistinghos.due_amount) || 0;
      const hosexistingDueAmt1 = Number(getExistinghos.wallet_amount_paid) || 0;
      const hosexistingTotalBilledAmt1 =
        Number(getExistinghos.paid_amount) || 0;
      const hosexistingTotalTaxAmt1 =
        Number(getExistinghos.actual_amount_paid) || 0;
      const hosexistingOverallSubtotalAmt1 =
        Number(getExistinghos.total_online_payments) || 0;
      const hosexistingOverallSubtotalAmt1off =
        Number(getExistinghos.total_offline_payments) || 0;
      const hosNewDueAmt1 =
        hosexistingDueAmt1 + Number(body.wallet_amount_paid);
      const hosreducedDue1 = hosdue1 - Number(body.paid_amount);
      const hosNewTotalBilledAmt1 =
        hosexistingTotalBilledAmt1 + Number(body.paid_amount);
      const hosNewTotalTaxAmt1 =
        hosexistingTotalTaxAmt1 + Number(body.actual_amount_paid);
      const hosNewOverallSubtotalAmt1 =
        hosexistingOverallSubtotalAmt1 + Number(body.total_online_payments);
      const hosNewOverallSubtotalAmt1off =
        hosexistingOverallSubtotalAmt1off + Number(body.total_offline_payments);
      const hos_updated_data1 = await this.connection.query(
        `update hospital_payment_summary set wallet_amount_paid = ?,due_amount = ?,paid_amount = ?,actual_amount_paid = ?,total_online_payments = ?, total_offline_payments = ? where id = ?`,
        [
          hosNewDueAmt1,
          hosreducedDue1,
          hosNewTotalBilledAmt1,
          hosNewTotalTaxAmt1,
          hosNewOverallSubtotalAmt1,
          hosNewOverallSubtotalAmt1off,
          Hospital_payment_summary_id,
        ],
      );

      return {
        status: 'success',
        status_code: 200,
        message: 'payment updated successfully',
      };
    } catch (error) {
      return {
        status: 'failed',
        status_code: 500,
        message: 'API SERVICE UNAVAILABLE TEMPORARYILY',
      };
    }
  }
  async overall_dashboard_data(
    limit: number,
    page: number,
    payment_status?: string,
    search?: string,
  ) {
    const offset = limit * (page - 1);

    const filters: string[] = [];
    const params: (string | number)[] = [];

    if (search) {
      filters.push(`(
      p.patient_name LIKE ?
      OR CAST(p.mobileno AS CHAR) LIKE ?
      OR p.aayush_unique_id LIKE ?
      OR pg.package_name LIKE ?
    )`);
      params.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }

    const whereClause = filters.length
      ? `WHERE ${filters.join(' AND ')}`
      : `WHERE pc.patient_id IS NOT NULL`;

    const query = `
    SELECT
      p.id AS patient_id,
      p.patient_name,
      p.age,
      p.gender,
      p.aayush_unique_id,
      p.dial_code,
      p.mobileno,
      COALESCE(pg.package_name, 'N/A') AS package_name,

      COALESCE(pc.apply_charge, 0) AS apply_charge,
      COALESCE(pc.temp_apply_charge, 0) AS temp_apply_charge,
      COALESCE(pc.additional_charge, 0) AS additional_charge,
      COALESCE(pc.discount_amount, 0) AS discount_amount,
      COALESCE(pc.tax, 0) AS tax,
      COALESCE(pc.temp_tax, 0) AS temp_tax,
      COALESCE(pc.total, 0) AS total,
      COALESCE(pc.balance, 0) AS balance,
      pc.payment_status,
      COALESCE(pc.amount,0) AS paid_amount,
      a.doctor AS doctor_id,
      pc.id AS patient_charges_id

    FROM patients p
LEFT JOIN patient_charges pc
  ON pc.patient_id = p.id
 AND (
       pc.temp_payment_status != 'refunded'
    OR pc.payment_status      != 'refunded'
 )
    LEFT JOIN opd_details opd ON opd.id = pc.opd_id
    LEFT JOIN ipd_details ipd ON ipd.id = pc.ipd_id
    LEFT JOIN appointment a 
      ON a.case_reference_id = COALESCE(opd.case_reference_id, ipd.case_reference_id)
    LEFT JOIN packages pg ON pg.id = pc.package_id

    ${whereClause}
    ORDER BY pc.created_at DESC;
  `;

    const countQuery = `
    SELECT COUNT(DISTINCT p.id) AS total_count
    FROM patients p
    LEFT JOIN patient_charges pc
  ON pc.patient_id = p.id
 AND (
       pc.temp_payment_status != 'refunded'
    OR pc.payment_status      != 'refunded'
 )
    LEFT JOIN packages pg ON pg.id = pc.package_id
    ${whereClause};
  `;

    try {
      const rows: any[] = await this.dynamicConnection.query(query, params);
      const totalCount = Number(
        (await this.dynamicConnection.query(countQuery, params))[0]
          ?.total_count || 0,
      );

      const grouped: Record<number, any[]> = {};

      for (const r of rows) {
        const applyCharge = r.doctor_id ? r.apply_charge : r.temp_apply_charge;
        const taxPercent = r.doctor_id ? r.tax : r.temp_tax;

        const additional = Number(r.additional_charge || 0);
        const discount = Number(r.discount_amount || 0);
        const totalBill = Number(r.total || 0);
        const oldBalance = Number(r.balance || 0);

        const sub_total = applyCharge + additional - discount;
        const taxAmount = Number(((sub_total * taxPercent) / 100).toFixed(2));

        let paid = 0,
          balance = 0,
          due = 0;

        if (r.payment_status === 'paid') {
          paid = totalBill;
        } else if (r.payment_status === 'partially_paid') {
          paid = totalBill - Math.abs(oldBalance);
          balance = Math.abs(oldBalance);
          due = Math.abs(oldBalance);
        } else if (r.payment_status === 'unpaid') {
          balance = due = totalBill;
        }

        const formattedRow = {
          ...r,
          applyCharge,
          taxPercent,
          taxAmount,
          sub_total,
          totalBill,
          paid,
          due,
          balance,
        };

        if (!grouped[r.patient_id]) grouped[r.patient_id] = [];
        grouped[r.patient_id].push(formattedRow);
      }

      const data = Object.values(grouped).map((group) => {
        const first = group[0];

        const sub_total_amount = group.reduce((s, r) => s + r.sub_total, 0);
        const total_tax_percent = group.reduce((s, r) => s + r.taxPercent, 0) / group.length;
        const total_tax_amount = group.reduce((s, r) => s + r.taxAmount, 0);
        const total_billed_amount = group.reduce((s, r) => s + r.totalBill, 0);
        const paid_amount = group.reduce((s, r) => s + r.paid, 0);
        const total_due_amount = total_billed_amount - paid_amount;

        const status =
          total_due_amount <= 0
            ? 'paid'
            : paid_amount > 0
              ? 'partially_paid'
              : 'unpaid';

        return {
          id: first.patient_id,
          patient_name: first.patient_name,
          age: first.age,
          gender: first.gender,
          aayush_unique_id: first.aayush_unique_id,
          dial_code: first.dial_code,
          mobileno: first.mobileno,
          package_name: first.package_name,
          total_tax_percent: total_tax_percent.toFixed(2),
          sub_total_amount: sub_total_amount.toFixed(2),
          total_tax_amount: total_tax_amount.toFixed(2),
          total_billed_amount: total_billed_amount.toFixed(2),
          paid_amount: paid_amount.toFixed(2),
          total_due_amount: total_due_amount.toFixed(2),

          payment_status: status.charAt(0).toUpperCase() + status.slice(1).toLowerCase(),
        };
      });
      const filteredData =
        payment_status === 'paid'
          ? data.filter((d) => d.payment_status.toLocaleLowerCase() === 'paid')
          : payment_status === 'due'
            ? data.filter((d) => d.payment_status.toLocaleLowerCase() === 'unpaid') : payment_status === 'partially_paid' ? data.filter((d) => d.payment_status.toLocaleLowerCase() === 'partially_paid')
              : data;

      const paginated = filteredData.slice(offset, offset + limit);

      return {
        status: 'success',
        status_code: 200,
        message: 'Data fetched successfully',
        data: paginated,
        count: filteredData.length,
      };
    } catch (error: any) {
      return {
        status: 'error',
        status_code: 500,
        message: 'Something went wrong while fetching data.',
        error: error?.message ?? String(error),
      };
    }
  }

  async dashboard_mongo_data(
    limit: number,
    page: number,
    search: string,
    start_date: any,
    end_date: any,
  ) {
    try {
      const offset = limit * (page - 1);
      const query: any = {};

      if (search) {
        if (Types.ObjectId.isValid(search)) {
          query.$or = [
            { _id: new Types.ObjectId(search) },
            {
              'patient_details.patient_name': { $regex: search, $options: 'i' },
            },
            { 'patient_details.id': { $regex: search, $options: 'i' } },
          ];
        } else {
          query.$or = [
            {
              'patient_details.patient_name': { $regex: search, $options: 'i' },
            },
            { 'patient_details.id': { $regex: search, $options: 'i' } },
          ];
        }
      }

      if (start_date && end_date) {
        const start = new Date(start_date);
        start.setHours(0, 0, 0, 0); // Start of day

        const end = new Date(end_date);
        end.setHours(23, 59, 59, 999); // End of day

        query.created_at = { $gte: start, $lte: end };
      } else if (start_date) {
        const start = new Date(start_date);
        start.setHours(0, 0, 0, 0);
        query.created_at = { $gte: start };
      } else if (end_date) {
        const end = new Date(end_date);
        end.setHours(23, 59, 59, 999);
        query.created_at = { $lte: end };
      }

      const [data, overallCount] = await Promise.all([
        this.ServiceModel.find(query)
          .sort({ created_at: -1 })
          .skip(offset)
          .limit(limit),
        this.ServiceModel.countDocuments(query),
      ]);

      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        data: data,
        count: overallCount,
      };
    } catch (error) {
      return {
        status: 'failed',
        status_code: 500,
        message: 'API SERVICE UNAVAILABLE TEMPORARYILY',
      };
    }
  }

  async dashboard_count(hospital_id: number) {
    const [getPatientCount] = await this.dynamicConnection.query(
      `select count(id) as patient_count from patients`,
    );
    const [data] = await this.connection.query(
      `select * from hospital_payment_summary where hospital_id = ?
`,
      [hospital_id],
    );
    data.patient_count = getPatientCount.patient_count;
    return {
      status: 'success',
      status_code: 200,
      message: 'data fetched successfully',
      data: data,
    };
  }

  async balance_by_patient(aayush_unique_id: string) {
    try {
      const [admin_pat_id] = await this.dynamicConnection.query(
        `select id from patients where aayush_unique_id = ?`,
        [aayush_unique_id],
      );

      const [adminnn_pat_id] = await this.dynamicConnection.query(
        `select id, mobileno from patients where aayush_unique_id = ?`,
        [aayush_unique_id],
      );

      let [admin_user_id] = await this.connection.query(
        `select id as user_id from users where username = ?`,
        [91 + adminnn_pat_id.mobileno],
      );
      let useDatas: any[] = [];
      if (!admin_user_id) {
        let a = await this.connection.query(
          `insert into users (user_id, username,role) values (?, ?, ?)`,
          [admin_pat_id.id, 91 + adminnn_pat_id.mobileno, 'patient'],
        );
        // admin_user_id = await a.insertId;
        useDatas = await this.connection.query(
          `select user_id from users where username = ?`,
          [91 + adminnn_pat_id.mobileno],
        );
      }
      let setUserId: number = 0;
      if (!admin_user_id) setUserId = useDatas[0].user_id;
      else setUserId = admin_user_id.user_id;

      const [aayush_coins] = await this.connection.query(
        `select coalesce(sum(total_coins_remaining), 0) coins_remaining from aayush_coins where user_id = ${setUserId} and coins_usage <> 'fully_used'  and is_expired = 0`,
      );
      const query = `SELECT
          CAST(GREATEST(pps.total_billed_amount, 0) AS DECIMAL(10,2)) AS total_billed_amount,
          CAST(GREATEST(pps.total_tax_amount, 0) AS DECIMAL(10,2)) AS total_tax_amount,
          CAST(GREATEST(pps.overall_subtotal_amount, 0) AS DECIMAL(10,2)) AS overall_subtotal_amount,
          CAST(GREATEST(pps.paid_amount, 0) AS DECIMAL(10,2)) AS paid_amount,
          CAST(GREATEST(pps.due_amount, 0) AS DECIMAL(10,2)) AS due_amount,
          CASE
            WHEN CAST(GREATEST(pps.due_amount, 0) AS DECIMAL(10,2)) = CAST(0.00 AS DECIMAL(10,2))
                AND CAST(GREATEST(pps.paid_amount, 0) AS DECIMAL(10,2)) > CAST(0.00 AS DECIMAL(10,2))
              THEN 'paid'

            WHEN CAST(GREATEST(pps.paid_amount, 0) AS DECIMAL(10,2)) > CAST(0.00 AS DECIMAL(10,2))
                AND CAST(GREATEST(pps.due_amount, 0) AS DECIMAL(10,2)) > CAST(0.00 AS DECIMAL(10,2))
              THEN 'partially_paid'
            ELSE 'due'
          END AS status
        FROM patient_payment_summary pps
        WHERE pps.patient_id = ?`;

      const [result] = await this.dynamicConnection.query(query, [
        admin_pat_id.id,
      ]);

      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        data: result || {},
        coin_balance: aayush_coins || {},
      };
    } catch (error) {
      return {
        status: 'failed',
        status_code: 201,
        message: error,
      };
    }
  }

  async phr_patient_wallet(aayush_unique_id: string) {
    const [existing_user]: any = await this.connection.query(
      `select user_id from patients where aayush_unique_id = ?`,
      [aayush_unique_id],
    );
    const [value_per_coin] = await this.connection.query(
      `select * from coin_value order by created_at desc limit 1`,
    );

    if (existing_user) {
      const aayush_coins = await this.connection.query(
        `select coalesce(sum(total_coins_remaining), 0) coins_remaining from aayush_coins where user_id = ${existing_user.user_id} and coins_usage <> 'fully_used'  and is_expired = 0`,
      );
      aayush_coins[0].value_per_coin = value_per_coin;

      if (aayush_coins.length === 0) {
        return {
          status: 'failed',
          status_code: 201,
          message: 'No Aayush coins available for the patient',
        };
      }

      return aayush_coins;
    } else {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No user found',
      };
    }
  }

  async phr_coins_used(aayush_unique_id: string, coins_used: number) {
    coins_used = Number(coins_used);

    const [existing_user]: any = await this.connection.query(
      `select id,mobileno from patients where aayush_unique_id = ?`,
      [aayush_unique_id],
    );
    const [getUserID] = await this.connection.query(
      `select id user_id from users where username = ?`,
      ['91' + existing_user.mobileno],
    );
    if (getUserID) {
      const [total_aayush_coins] = await this.connection.query(
        `select coalesce(sum(total_coins_remaining), 0) coins_remaining from aayush_coins where user_id = ${getUserID.user_id} and coins_usage <> 'fully_used'  and is_expired = 0`,
      );

      if (Number(total_aayush_coins.coins_remaining) < coins_used) {
        return {
          status: 'failed',
          status_code: 400,
          message: 'coins_used must be lesser than or equal to available coins',
        };
      }
      const aayush_coins = await this.connection.query(
        `select * from aayush_coins where user_id = ${getUserID.user_id} and coins_usage <> 'fully_used'  and is_expired = 0 order by created_at asc`,
      );
      if (aayush_coins.length === 0) {
        return {
          status: 'failed',
          status_code: 201,
          message: 'No Aayush coins available for the patient',
        };
      }

      for (const a of aayush_coins) {
        if (coins_used > 0) {
          if (Number(a.total_coins_remaining) <= coins_used) {
            let total_coins_used =
              Number(a.total_coins_used) + Number(a.total_coins_remaining);
            const query = await this.connection.query(
              `update aayush_coins set total_coins_remaining = 0, total_coins_used = ${total_coins_used}, coins_usage= 'fully_used' where id = ?`,
              [Number(a.id)],
            );

            coins_used = coins_used - Number(a.total_coins_remaining);
          } else {
            let total_coins_remaining =
              Number(a.total_coins_remaining) - coins_used;
            let total_coins_used =
              Number(a.total_coins_used) + Number(coins_used);
            const query = await this.connection.query(
              `update aayush_coins set total_coins_remaining = ${total_coins_remaining}, total_coins_used = ${total_coins_used}, coins_usage= 'partially_used' where id = ?`,
              [Number(a.id)],
            );

            coins_used = coins_used - Number(a.total_coins_remaining);
          }
        }
      }
      const aayush_coins_after_usage = await this.connection.query(
        `select * from aayush_coins where user_id = ${getUserID.user_id} and coins_usage <> 'fully_used'  and is_expired = 0 order by created_at asc`,
      );
      return aayush_coins_after_usage;
    } else {
      return {
        status: 'failed',
        status_code: 400,
        message: 'No user found',
      };
    }
  }

  async phr_findtransactiondetails(
    aayush_unique_id: number,
    payment_module: string,
    limit: number,
    page: number,
  ) {
    try {
      const offset = limit * (page - 1);

      const [getAdminPatientId] = await this.connection.query(
        `select id from patients where aayush_unique_id = ?`,
        [aayush_unique_id],
      );

      let query = `select
  date(patient_charges.date) as billed_date,
  time(patient_charges.date) as billed_time,
    patient_charges.id as patient_charge_id,
date(transactions.payment_date) paid_date,
time(transactions.payment_date) paid_time,
            concat(COALESCE(transactions.net_banking_transaction_id,""),COALESCE(transactions.card_transaction_id,""),COALESCE(transactions.upi_transaction_id,""),
            COALESCE(transactions.payment_reference_number,""),
            COALESCE(transactions.cash_transaction_id,"")) payment_transaction_id,
            transactions.id plenome_transaction_id,
            transactions.hos_transaction_id hos_transaction_id,
            transactions.payment_mode,
            transactions.amount amount_paid
            from appointment
left join visit_details on visit_details.id = appointment.visit_details_id
left join opd_details on opd_details.id = visit_details.opd_details_id
left join ipd_details on ipd_details.case_reference_id = appointment.case_reference_id
left join patient_charges on (patient_charges.opd_id = opd_details.id or patient_charges.ipd_id = ipd_details.id)
left join transactions on transactions.id = patient_charges.transaction_id where transactions.patient_id = ${getAdminPatientId.id} `;

      let countquery = `select count(appointment.id) total from appointment
       left join visit_details on visit_details.id = appointment.visit_details_id
left join opd_details on opd_details.id = visit_details.opd_details_id
left join ipd_details on ipd_details.case_reference_id = appointment.case_reference_id
left join patient_charges on (patient_charges.opd_id = opd_details.id or patient_charges.ipd_id = ipd_details.id)
left join transactions on transactions.id = patient_charges.transaction_id where transactions.patient_id = ${getAdminPatientId.id}`;

      if (payment_module) {
        switch (payment_module) {
          case 'opd':
            query += ` and appointment.module = 'OPD'`;
            countquery += ` and appointment.module = 'OPD'`;
            break;
          case 'ipd':
            query += ` and appointment.module = 'IPD'`;
            countquery += ` and appointment.module = 'IPD'`;
            break;
          case 'appointment':
            query += ` and appointment.module = 'APPOINTMENT'`;
            countquery += ` and appointment.module = 'APPOINTMENT'`;
            break;
          default:
            query += ` and appointment.module = 'ABCD'`;
            countquery += ` and appointment.module = 'ABCD'`;
            break;
        }
      }

      let [count] = await this.connection.query(countquery);

      const transaction_data = await this.connection.query(
        query + `limit ${limit} offset ${offset}`,
      );

      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        data: transaction_data,
        count: count.total,
      };
    } catch (error) {
      console.log(error, 'lkjhgfd');
    }
  }

  async phr_findAll(
    aayush_unique_id: string,
    payment_module: string,
    bill_type: string,
    limit: number,
    page: number,
    search_text?: string,
  ) {
    const sanitizedLimit = Number(limit);
    const sanitizedPage = Number(page);
    const offset = sanitizedLimit * (sanitizedPage - 1);

    // 1. Fetch Patient Details
    const [patient_data] = await this.dynamicConnection.query(
      `
    SELECT 
      p.id, p.image, p.patient_name, p.gender, p.age, p.dob, p.mobileno, 
      p.guardian_name, p.emergency_mobile_no, p.marital_status, 
      p.aayush_unique_id, p.ABHA_number, p.email, 
      p.employer_name, p.employer_id, p.employee_id, 
      p.insurance_provider, bbp.name AS blood_bank_product_name, 
      p.communication_address, p.address 
    FROM patients p
    LEFT JOIN blood_bank_products bbp 
      ON bbp.id = p.blood_bank_product_id
    WHERE p.id = ?
    `,
      [aayush_unique_id],
    );
    let patient_id = await patient_data.id;
    // 2. Fetch ABHA Address
    const [abhaResult] = await this.connection.query(
      `
    SELECT abhaAddress 
    FROM patient_abha_address 
    WHERE patient_id = ? 
    ORDER BY created_at DESC 
    LIMIT 1
    `,
      [patient_id],
    );

    patient_data.pat_abha_address = abhaResult?.abhaAddress ?? '';

    // 3. Build Base Billing Query
    const baseBillingQuery = `
    FROM appointment a
    LEFT JOIN visit_details vd ON vd.id = a.visit_details_id
    LEFT JOIN opd_details opd ON opd.id = vd.opd_details_id
    LEFT JOIN ipd_details ipd ON ipd.case_reference_id = a.case_reference_id
    LEFT JOIN staff s ON s.id = a.doctor
    LEFT JOIN patient_charges pc ON (
      pc.opd_id = opd.id OR pc.ipd_id = ipd.id
    )
    LEFT JOIN transactions ON transactions.patient_charges_id = pc.id
    LEFT JOIN charges c ON c.id = pc.charge_id
    LEFT JOIN charge_categories cc ON cc.id = c.charge_category_id
    LEFT JOIN charge_type_master ctm ON ctm.id = cc.charge_type_id
    LEFT JOIN packages pg 
    ON pg.id = pc.package_id
    WHERE pc.patient_id = ?
  `;

    const queryParams: (string | number)[] = [patient_id];
    const countParams: (string | number)[] = [patient_id];

    const filters: string[] = [];

    // 4. Apply Filters
    if (bill_type === 'due') {
      filters.push(`pc.payment_status <> 'paid'`);
    } else if (bill_type === 'paid') {
      filters.push(`pc.payment_status = 'paid'`);
    }

    if (payment_module) {
      const module = payment_module.toUpperCase();
      if (['OPD', 'IPD', 'APPOINTMENT'].includes(module)) {
        filters.push(`a.module = '${module}'`);
      } else {
        filters.push(`a.module = 'ABCD'`);
      }
    }

    // 5. Apply Text Search Filter
    if (search_text && search_text.trim() !== '') {
      const search = `%${search_text.trim()}%`;
      filters.push(`
      (
        CONCAT('APPN', a.id) LIKE ?
        OR s.name LIKE ?
        OR s.surname LIKE ?
      )
    `);
      queryParams.push(search, search, search);
      countParams.push(search, search, search);
    }

    const whereClause = filters.length ? ` AND ${filters.join(' AND ')}` : '';

    // 6. Billing Summary Query
    const billingQuery = `
       SELECT 
    CONCAT('APPN', a.id) AS appointment_id,
    a.module,
    CASE 
        WHEN a.module = 'OPD' THEN CONCAT('OPDN', opd.id)
        WHEN a.module = 'IPD' THEN CONCAT('IPDN', ipd.id)
        WHEN a.module = 'APPOINTMENT' THEN CONCAT('APPN', a.id)
        ELSE '-' 
    END AS department_id,
    ctm.charge_type,
    cc.name AS charge_category,
    s.name,
    s.surname,
    s.id AS doctor_id,
    a.case_reference_id AS case_id,
    pc.id AS patient_charge_id,
   DATE(CONVERT_TZ(pc.date, '+00:00', '+05:30')) AS billed_date,
    TIME(pc.date) AS billed_time,
    pc.qty AS Qty,
  ABS(CAST(COALESCE(pc.standard_charge, 0) AS DECIMAL(10,2))) AS Standard,
ABS(CAST(COALESCE(pc.apply_charge * pc.qty, 0) AS DECIMAL(10,2))) AS Apply,

   
    ABS(pc.discount_amount) AS Discount,
    pc.discount_percentage,
    pc.payment_status,
    ABS(CAST(
        COALESCE(pc.amount - COALESCE(
            ROUND(
                (
                    (COALESCE(pc.apply_charge, 0) 
                     + COALESCE(pc.additional_charge, 0) 
                     - COALESCE(pc.discount_amount, 0))
                    * COALESCE(pg.package_tax, pc.tax, 0) / 100
                ),
                2
            ),
            0.00
        ) ,0)
        AS DECIMAL(10,2)
  )) AS sub_total,

    ABS(CAST(COALESCE(pc.balance,0) AS DECIMAL(10,2))) AS balance,
    pc.tax AS TAX,
  CAST(COALESCE(pc.total, 0) AS DECIMAL(10,2)) AS Billed,
    CAST(COALESCE(pc.additional_charge,0) AS DECIMAL(10,2)) AS additional_charge,
   CAST(COALESCE(transactions.amount, 0) AS DECIMAL(10,2)) AS paid,
ABS(CAST(COALESCE(pc.total, 0) - COALESCE(transactions.amount, 0) AS DECIMAL(10,2))) AS Due

    ${baseBillingQuery}
    ${whereClause}
    ORDER BY pc.created_at DESC
    LIMIT ? OFFSET ?
  `;

    const countQuery = `
    SELECT COUNT(pc.id) AS totalcount
    ${baseBillingQuery}
    ${whereClause}
  `;

    // Add pagination params
    queryParams.push(sanitizedLimit);
    queryParams.push(offset);

    // 7. Execute Queries
    const billing_summary = await this.dynamicConnection.query(
      billingQuery,
      queryParams,
    );
    const [count] = await this.dynamicConnection.query(countQuery, countParams);

    // 8. Return Response
    return {
      status: 'success',
      status_code: 200,
      message: 'data fetched successfully',
      data: {
        patient_data,
        billing_summary,
      },
      count: count.totalcount,
    };
  }

  async phr_wallet_credit_transactions(
    aayush_unique_id: string,
    transaction_status: string,
  ) {
    const [patient_data] = await this.connection.query(
      `select id, mobileno from patients where aayush_unique_id = ?`,
      [aayush_unique_id],
    );

    if (!patient_data?.mobileno) {
      return {
        status: 'failed',
        status_code: 500,
        message: 'Enter valid aayush_unique_id',
      };
    }
    let num = patient_data.mobileno;

    if (num.length === 10) {
      num = `91${num}`;
    }

    const [user_id] = await this.connection.query(
      `select id user_id from users where username = ?`,
      [num],
    );

    const [coin_amt] = await this.connection.query(
      `select * from coin_value order by created_at desc limit 1`,
    );

    const coin_value = await coin_amt.coin_amount;

    const debit_transaction = await this.connection.query(
      `
  SELECT 
      id,
      payment_date,
      "Wallet Usage",
      (wallet_paid_amount / ?) AS coins_used
  FROM transactions
  WHERE patient_id = ?
    AND wallet_paid_amount > 0

  UNION ALL

  SELECT 
      id,
      payment_date,
      "Temp Appointment Wallet Usage",
      (temp_wallet_paid_amount / ?) AS coins_used
  FROM transactions
  WHERE patient_id = ?
    AND temp_wallet_paid_amount > 0
  `,
      [coin_value, patient_data.id, coin_value, patient_data.id],
    );

    const user_aayush_coins = await this.connection.query(
      `select total_coins_issued, created_at as issued_date, module from aayush_coins where user_id = ${user_id.user_id}`,
    );
    if (transaction_status == 'credited') {
      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        credited_coins: user_aayush_coins,
      };
    }
    if (transaction_status == 'debited') {
      return {
        status: 'success',
        status_code: 200,
        message: 'data fetched successfully',
        debited_coins: debit_transaction,
      };
    }
    return {
      status: 'success',
      status_code: 200,
      message: 'data fetched successfully',
      debited_coins: debit_transaction,
      credited_coins: user_aayush_coins,
    };
  }

  async addtransactionInfo(transaction_id: string) {
    const [transaction] = await this.connection.query(
      `SELECT id, Hospital_id, patient_id, hos_transaction_id
     FROM transactions 
     WHERE id = ?`,
      [transaction_id],
    );


    if (!transaction) {
      throw new Error('Transaction not found');
    }
    const [admin_patient] = await this.connection.query(`SELECT * FROM patients WHERE id= ?`, [transaction.patient_id])
    const [hos_patient] = await this.dynamicConnection.query(`SELECT * FROM patients WHERE aayush_unique_id= ?`, [admin_patient.aayush_unique_id])
    const [patientDetails] = await this.dynamicConnection.query(
      `SELECT 
    ABHA_number,
    aayush_unique_id,
    address,
    age,
    communication_address,
    dob,
    gender,
    guardian_name,
    patients.id,
    image,
    mobileno,
    email,
    pincode,
    state_name,
    district_name,
    patient_name ,blood_bank_products.name blood_group
     FROM patients left join blood_bank_products on blood_bank_products.id = patients.blood_bank_product_id
     WHERE patients.id = ?`,
      [hos_patient.id],
    );
    const [hosTransaction] = await this.dynamicConnection.query(
      `SELECT 
        patient_id,
        upi_transaction_id,
        cash_transaction_id,
        card_transaction_id,
        net_banking_transaction_id,
        payment_reference_number,
        payment_mode,
        payment_id,
        payment_reference_number,
        payment_mode,
        partial_payment_mode temp_payment_mode,
        payment_date,
        temp_actual_paid_amount,
        temp_appt_payment_id,
        temp_appt_payment_reference_number temp_payment_transaction_id,
        temp_wallet_paid_amount,
        wallet_paid_amount,
        actual_paid_amount,
        concat(COALESCE(net_banking_transaction_id,""),COALESCE(card_transaction_id,""),COALESCE(upi_transaction_id,""),
        COALESCE(payment_reference_number,""),
        COALESCE(cash_transaction_id,"")) payment_transaction_id,
        COALESCE(amount, 0) AS amount,
        COALESCE(temp_appt_amount, 0) AS temp_appt_amount

     FROM transactions 
     WHERE id = ?`,
      [transaction.hos_transaction_id],
    );
    if (!hosTransaction) {
      throw new Error('HMS transaction not found');
    }

    const [FrequentVisitDoctor] = await this.dynamicConnection.query(
      `SELECT COUNT(id) AS count, doctor
     FROM appointment 
     WHERE patient_id = ? AND doctor IS NOT NULL
     GROUP BY doctor
     ORDER BY count DESC 
     LIMIT 1`,
      [hosTransaction.patient_id],
    );
    let primaryConsDoctor = null;

    if (FrequentVisitDoctor?.doctor) {
      [primaryConsDoctor] = await this.dynamicConnection.query(
        `SELECT name, surname, employee_id 
       FROM staff 
       WHERE id = ?`,
        [FrequentVisitDoctor.doctor],
      );
    }
    const [lastConsDoctor] = await this.dynamicConnection.query(
      `SELECT staff.name, staff.surname, staff.employee_id
     FROM appointment 
     LEFT JOIN staff ON staff.id = appointment.doctor
     WHERE appointment.patient_id = ?
     ORDER BY appointment.id DESC
     LIMIT 1`,
      [hosTransaction.patient_id],
    );
    const payment_transaction_id =
      (hosTransaction.upi_transaction_id ?? '') +
      (hosTransaction.net_banking_transaction_id ?? '') +
      (hosTransaction.card_transaction_id ?? '') +
      (hosTransaction.cash_transaction_id ?? '') +
      (hosTransaction.payment_reference_number ?? '');
    const transDetails = await this.dynamicConnection.query(
      `SELECT
    pt.id AS patient_id,
    pt.patient_name,
    pt.age,
    pt.gender,
    pt.aayush_unique_id,
    pt.dial_code,
    pt.mobileno,
    pc.id AS patient_charges_id,
    pc.payment_status,
    opd.id as opd_id,
    ipd.id as ipd_id,
    COALESCE(pc.apply_charge, 0) AS apply_charge,
    COALESCE(pc.temp_apply_charge, 0) AS temp_apply_charge,
    COALESCE(pc.additional_charge, 0) AS additional_charge,
    COALESCE(pc.discount_amount, 0) AS discount_amount,
    COALESCE(pc.discount_percentage, 0) AS discount_percentage,
    COALESCE(pc.total, 0) AS total,
    COALESCE(pc.tax, 0) AS tax,
    COALESCE(pc.standard_charge, 0) AS standard_charge,
    COALESCE(pc.temp_standard_charge, 0)  AS temp_standard_charge,
    COALESCE(pc.temp_tax, 0) AS temp_tax,
    COALESCE(pc.balance, 0) AS balance,
    COALESCE(pc.qty, 0)  AS qty,
    tn.id AS transaction_id,
    tn.amount AS paid_amount,
    tn.created_at AS billedDate,
    tn.payment_mode,
    tn.created_at AS transaction_date,
    ap.id AS appointmentId,
    ap.doctor AS doctorId,
    ap.module,
    ap.case_reference_id AS caseId,
    staff.name,
    staff.surname,
    charge_type_master.charge_type AS charge_type,
    packages.package_name,
    packages.id as package_id,
package_categories.name as package_category_name
FROM transactions tn
LEFT JOIN patient_charges pc
    ON pc.transaction_id = tn.id
LEFT JOIN opd_details opd
    ON opd.id = pc.opd_id
LEFT JOIN ipd_details ipd
    ON ipd.id = pc.ipd_id
LEFT JOIN appointment ap
    ON ap.case_reference_id = COALESCE(opd.case_reference_id, ipd.case_reference_id)
LEFT JOIN staff
    ON staff.id = ap.doctor
LEFT JOIN patients pt
    ON pt.id = pc.patient_id
LEFT JOIN charges
    ON pc.charge_id = charges.id
LEFT JOIN charge_categories
    ON charges.charge_category_id = charge_categories.id
LEFT JOIN charge_type_master
    ON charge_categories.charge_type_id = charge_type_master.id
    LEFT JOIN packages
    ON pc.package_id = packages.id
    LEFT JOIN package_categories
    ON packages.package_category_id	 = package_categories.id
WHERE tn.id = ?
ORDER BY pc.created_at DESC`,
      [transaction.hos_transaction_id],
    );
    let result = [];
    transDetails.forEach((res) => {
      const standard_charge = res.doctorId
        ? Number(res.standard_charge || 0)
        : Number(res.temp_apply_charge || 0);

      const applyCharge = res.doctorId
        ? Number(res.apply_charge || 0)
        : Number(res.temp_apply_charge || 0);

      const taxPercent = res.doctorId
        ? Number(res.tax || 0)
        : Number(res.temp_tax || 0);

      const additional = Number(res.additional_charge || 0);
      const discount = Number(res.discount_amount || 0);
      const totalBill = Number(res.total || 0);
      const oldBalance = Number(res.balance || 0);

      const sub_total = Number(applyCharge + additional - discount).toFixed(2);
      const sub_total_tax = Number(
        ((applyCharge + additional - discount) * (taxPercent / 100)).toFixed(2),
      );

      let paid = 0;
      let Due = 0;
      let balance = 0;

      if (res.payment_status === "paid") {
        paid = totalBill;
        balance = 0;
        Due = 0;
      }
      else if (res.payment_status === "partially_paid") {
        paid = totalBill - Math.abs(oldBalance);
        balance = Math.abs(oldBalance);
        Due = Math.abs(oldBalance);
      }
      else if (res.payment_status === "unpaid") {
        paid = 0;
        balance = totalBill;
        Due = totalBill;
      }
      let app_id = res.module == 'OPD' ? `OPDN${res.opd_id}` : res.module == 'IPD' ? `OPDN${res.ipd_id}` : res.doctorId ? `APPN${res.appointmentId}` : `TEMP${res.appointmentId}`;
      let dto = {
        additional_charge: res.additional_charge,
        billed_date: res.billedDate,
        case_id: res.caseId,
        charge_type: res.charge_type,
        department_id: app_id,
        discount_amount: res.discount_amount,
        doctor_id: res.doctorId,
        module: res.module,
        name: res.name,
        patient_charge_id: res.patient_charges_id,
        qty: res.qty,
        surname: res.surname,
        appointment_id: app_id,
        package_id: res.package_id ? res.package_id : null,
        package_name: res.package_name ? res.package_name : null,
        package_category_name: res.package_category_name ? res.package_category_name : null
      };
      result.push({
        ...dto,
        tax: taxPercent,
        total_billed_amount: totalBill,
        paid_amount: paid,
        due_amount: Due,
        overall_subtotal_amount: sub_total,
        total_tax_amount: sub_total_tax,
        standard_charge: standard_charge,
        discount_percentage: res.discount_percentage
      });
    });
    const mongoBody = {
      hospital_id: transaction.Hospital_id,
      patient_aayush_id: patientDetails.aayush_unique_id,
      hos_transaction_id: transaction.hos_transaction_id,
      plenome_transaction_id: transaction.id,
      transaction_details: result,
      patient_details: patientDetails,
      primary_cons_doctor: primaryConsDoctor,
      last_cons_doctor: lastConsDoctor,
      amount_paid: hosTransaction.payment_mode ? hosTransaction.amount : hosTransaction.temp_appt_amount,
      payment_mode: hosTransaction.payment_mode ?? hosTransaction.temp_payment_mode,
      payment_status: 'success',
      payment_transaction_id,
    };
    this.mongodata(mongoBody);
    return true;
  }

  async remove(patient_charge_id: number, opd_id: string, hospital_id: number, ipd_id: any) {
    if (ipd_id) {
      try {
        await this.deduct_amount_from_summery(
          patient_charge_id,
          hospital_id,
          opd_id ? Number(opd_id) : null,
          ipd_id ? Number(ipd_id) : null,
        );
      } catch (error) {
        console.log('delete charges error:', error)
      }
      await this.dynamicConnection.query(
        `delete from patient_charges where id = ?`,
        [patient_charge_id],
      );

      await this.connection.query(
        `delete from patient_charges where hos_patient_charges_id = ? and hospital_id = ?`,
        [patient_charge_id, hospital_id],
      );
      return [
        {
          status_code: 200,
          status: 'success',
          message: 'Charges deleted successfully ',
        },
      ];
    }
    try {
      const [row] = await this.dynamicConnection.query(
        `select * from patient_charges where opd_id = ? order by created_at asc limit 1`,
        [opd_id],
      );

      if (row.id == patient_charge_id) {
        return [
          {
            status_code: 400,
            status: 'failed',
            message: 'Cannot delete Primary charges',
          },
        ];
      }
      try {
        await this.deduct_amount_from_summery(
          patient_charge_id,
          hospital_id,
          opd_id ? Number(opd_id) : null,
          ipd_id ? Number(ipd_id) : null,
        );
      } catch (error) {
        console.log('delete charges error:', error)
      }

      await this.dynamicConnection.query(
        `delete from patient_charges where id = ?`,
        [patient_charge_id],
      );

      await this.connection.query(
        `delete from patient_charges where hos_patient_charges_id = ? and hospital_id = ?`,
        [patient_charge_id, hospital_id],
      );

      return [
        {
          status_code: 200,
          status: 'success',
          message: 'Charges deleted successfully ',
        },
      ];
    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: process.env.ERROR_MESSAGE,
        },
        HttpStatus.INTERNAL_SERVER_ERROR,
      );
    }
  }

  async deduct_amount_from_summery(patient_charges_id: number, hospital_id: number, opd_id?: number, ipd_id?: number, appointment_id?: number) {
    const round2 = (n: number) =>
      Number((n + Number.EPSILON).toFixed(2));

    let appointment_ids = appointment_id
    const [charge] = await this.dynamicConnection.query(
      `SELECT * FROM patient_charges WHERE id = ?`,
      [patient_charges_id]
    );

    if (!appointment_ids) {
      if (opd_id) {
        const [opd_data] = await this.dynamicConnection.query(`
        SELECT appointment.doctor,appointment.id FROM opd_details
        INNER JOIN appointment ON opd_details.case_reference_id=appointment.case_reference_id WHERE opd_details.id=?`, [opd_id])
        appointment_ids = opd_data.id
      }
      if (ipd_id) {
        const [ipd_data] = await this.dynamicConnection.query(`
        SELECT appointment.doctor,appointment.id FROM ipd_details
        INNER JOIN appointment ON ipd_details.case_reference_id=appointment.case_reference_id WHERE ipd_details.id=?`, [ipd_id])
        appointment_ids = ipd_data.id
      }
    }
    const [appointment] = await this.dynamicConnection.query(
      `SELECT * FROM appointment WHERE id = ?`,
      [appointment_ids]
    );
    const baseAmount =
      Number(charge.apply_charge) + Number(charge.additional_charge);

    const taxPercent = appointment.doctor
      ? Number(charge.tax)
      : Number(charge.temp_tax);


    const subtotalAfter = baseAmount - charge.discount_amount;
    const taxAfter = (subtotalAfter * taxPercent) / 100;
    const finalAfter = subtotalAfter + taxAfter;

    const [patientDue] = await this.dynamicConnection.query(
      `SELECT * FROM patient_payment_summary WHERE patient_id = ?`,
      [appointment.patient_id]
    );
    const updatedDueAmount =
      Number(patientDue?.due_amount || 0) - round2(finalAfter);

    const updatedSubTotalAmount =
      Number(patientDue?.overall_subtotal_amount || 0) - round2(subtotalAfter);

    const updatedTaxAmount =
      Number(patientDue?.total_tax_amount || 0) - round2(taxAfter);

    const updatedBilledAmount =
      Number(patientDue?.total_billed_amount || 0) - round2(finalAfter);
    const paid_amounts = charge.payment_status === 'refunded' || charge.temp_payment_status === 'refunded' ? Number(patientDue?.paid_amount || 0) - Number(charge?.total || 0) : Number(patientDue?.paid_amount || 0)

    await this.dynamicConnection.query(
      `
    UPDATE patient_payment_summary
    SET
      due_amount = ?,
      overall_subtotal_amount = ?,
      total_tax_amount = ?,
      total_billed_amount=?,
      paid_amount=?
    WHERE patient_id = ?
    `,
      [
        updatedDueAmount,
        updatedSubTotalAmount,
        updatedTaxAmount,
        updatedBilledAmount,
        paid_amounts,
        appointment.patient_id
      ]
    );

    const [admin_appointment] = await this.connection.query(
      `
  SELECT id, patient_id
  FROM appointment
  WHERE hos_appointment_id = ? AND Hospital_id = ?
  `,
      [appointment_ids, hospital_id]
    );
    const [admin_patientDue] = await this.connection.query(
      `SELECT * FROM patient_payment_summary WHERE patient_id = ?`,
      [admin_appointment.patient_id]
    );
    const admin_updatedDueAmount =
      Number(admin_patientDue?.due_amount || 0) - round2(finalAfter);

    const admin_updatedSubTotalAmount =
      Number(admin_patientDue?.overall_subtotal_amount || 0) - round2(subtotalAfter);

    const admin_updatedTaxAmount =
      Number(admin_patientDue?.total_tax_amount || 0) - round2(taxAfter);

    const admin_updatedBilledAmount =
      Number(admin_patientDue?.total_billed_amount || 0) - round2(finalAfter);
    const admin_paid_amounts = charge.payment_status === 'refunded' || charge.temp_payment_status === 'refunded' ? Number(admin_patientDue?.paid_amount || 0) - Number(charge?.total || 0) : Number(admin_patientDue?.paid_amount || 0)
    await this.connection.query(
      `
    UPDATE patient_payment_summary
    SET
      due_amount = ?,
      overall_subtotal_amount = ?,
      total_tax_amount = ?,
      total_billed_amount=?,
      paid_amount=?
    WHERE patient_id = ?
    `,
      [
        admin_updatedDueAmount,
        admin_updatedSubTotalAmount,
        admin_updatedTaxAmount,
        admin_updatedBilledAmount,
        admin_paid_amounts,
        admin_appointment.patient_id
      ]
    );

    const [hospitalDue] = await this.connection.query(
      `SELECT * FROM hospital_payment_summary WHERE hospital_id = ?`,
      [hospital_id]
    );
    const hospital_updatedDueAmount =
      Number(hospitalDue?.due_amount || 0) - round2(finalAfter);

    const hospital_updatedSubTotalAmount =
      Number(hospitalDue?.overall_subtotal_amount || 0) - round2(subtotalAfter);

    const hospital_updatedTaxAmount =
      Number(hospitalDue?.total_tax_amount || 0) - round2(taxAfter);

    const hospital_updatedBilledAmount =
      Number(hospitalDue?.total_billed_amount || 0) - round2(finalAfter);
    const hos_paid_amounts = charge.payment_status === 'refunded' || charge.temp_payment_status === 'refunded' ? Number(hospitalDue?.paid_amount || 0) - Number(charge?.total || 0) : Number(hospitalDue?.paid_amount || 0)

    await this.connection.query(
      `
    UPDATE hospital_payment_summary
    SET
      due_amount = ?,
      overall_subtotal_amount = ?,
      total_tax_amount = ?,
      total_billed_amount = ?,
      paid_amount=?
    WHERE hospital_id = ?
    `,
      [
        hospital_updatedDueAmount,
        hospital_updatedSubTotalAmount,
        hospital_updatedTaxAmount,
        hospital_updatedBilledAmount,
        hos_paid_amounts,
        hospital_id
      ]
    );

    const [user_patient] = await this.connection.query(
      `SELECT * FROM patients WHERE id = ?`,
      [admin_appointment.patient_id])
    const [admin_user_id] = await this.connection.query(
      `select id,user_id from users where username = ?`,
      [91 + user_patient.mobileno])
    const [user_Due] = await this.connection.query(
      `SELECT * FROM user_payment_summary WHERE user_id = ?`,
      [admin_user_id.user_id]
    );
    const user_updatedDueAmount =
      Number(user_Due?.due_amount || 0) - round2(finalAfter);

    const user_updatedSubTotalAmount =
      Number(user_Due?.overall_subtotal_amount || 0) - round2(subtotalAfter);

    const user_updatedTaxAmount =
      Number(user_Due?.total_tax_amount || 0) - round2(taxAfter);

    const user_updatedBilledAmount =
      Number(user_Due?.total_billed_amount || 0) - round2(finalAfter);
    const user_paid_amounts = charge.payment_status === 'refunded' || charge.temp_payment_status === 'refunded' ? Number(user_Due?.paid_amount || 0) - Number(charge?.total || 0) : Number(user_Due?.paid_amount || 0)
    await this.connection.query(
      `
    UPDATE user_payment_summary
    SET
      due_amount = ?,
      overall_subtotal_amount = ?,
      total_tax_amount = ?,
      total_billed_amount = ?,
       paid_amount=?
    WHERE user_id = ?
    `,
      [
        user_updatedDueAmount,
        user_updatedSubTotalAmount,
        user_updatedTaxAmount,
        user_updatedBilledAmount,
        user_paid_amounts,
        user_Due.user_id
      ]
    );

    return {
      status: 'success',
      message: 'Summery updated successfully'

    };
  }

  async coinValue() {
    const [value_per_coin] = await this.connection.query(
      `select * from coin_value order by created_at desc limit 1`,
    );
    return value_per_coin;
  }
}