import { HttpException, HttpStatus, Injectable } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { AddPackagesDto, EditPackagesDto } from './dto/app-packages.dto';
import { PaginationDto } from './dto/pagination.dto';

@Injectable()
export class PackagesService {
  constructor(
    private readonly connection: DataSource,
    @InjectDataSource('AdminConnection')
    private readonly dynamicConnection: DataSource,

  ) { }


  async getPackageDetails(package_id: number) {
    try {
      const packages = await this.connection.query(
        `SELECT  
        packages.id,  
        packages.package_name,  
        packages.package_tax,  
        packages.package_amount,  
        packages.description,
        packages.package_category_id,
        CASE 
        WHEN packages.package_status = '0' THEN 'pending'
        WHEN packages.package_status = '1' THEN 'approved'
        WHEN packages.package_status = '2' THEN 'rejected'
        ELSE 'unknown'
        END AS package_status,
        CAST(
              COALESCE(packages.package_amount, 0)
          +
          (COALESCE(packages.package_amount, 0) * COALESCE(packages.package_tax, 0) / 100)
        AS DECIMAL(10,2)) AS packages_total_amount,
        package_categories.name AS category_name,  
        GROUP_CONCAT(charges.id) AS charges_ids,
        GROUP_CONCAT(charges.name) AS charge_names,  
        GROUP_CONCAT(charges.standard_charge) AS standard_charges
      FROM packages
      INNER JOIN package_categories 
        ON packages.package_category_id = package_categories.id
        AND package_categories.status = '1'
      LEFT JOIN packages_item_mapping 
        ON packages.id = packages_item_mapping.package_id
        AND packages_item_mapping.status = '1'
      LEFT JOIN charges 
        ON packages_item_mapping.charges_id = charges.id
      WHERE packages.status = '1'
        AND packages.id = ?
      GROUP BY packages.id
      `,
        [package_id]
      );

      if (packages.length === 0) {
        return {
          status: 'success',
          message: 'No package found',
          data: null
        };
      }

      const pkg = packages[0];

      const chargeIds = pkg.charges_ids ? pkg.charges_ids.split(',') : [];
      const chargeNames = pkg.charge_names ? pkg.charge_names.split(',') : [];
      const chargeAmounts = pkg.standard_charges ? pkg.standard_charges.split(',') : [];

      const charges = chargeIds.map((id: string, index: number) => ({
        id: Number(id),
        name: chargeNames[index] || null,
        standard_charges: Number(chargeAmounts[index]) || 0
      }));
      return {
        status: 'success',
        message: 'Details fetched successfully',
        data: {
          id: pkg.id,
          package_name: pkg.package_name,
          package_tax: pkg.package_tax,
          package_amount: pkg.package_amount,
          packages_total_amount: pkg.packages_total_amount,
          description: pkg.description,
          category_name: pkg.category_name,
          package_status: pkg.package_status,
          package_category_id: pkg.package_category_id,
          charges: charges
        }
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: process.env.ERROR_MESSAGE_V2,
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }


  async findPackagesList(paginationDto: PaginationDto): Promise<any> {
    const offset = Number(paginationDto.limit) * (Number(paginationDto.page) - 1);

    let searchQuery = '';
    const params: any[] = [];
    const countParams: any[] = [];

    if (paginationDto.search && paginationDto.search.trim() !== '') {
      const searchParam = `%${paginationDto.search}%`;

      searchQuery += `
    AND (
       packages.package_name LIKE ?
    )
  `;

      params.push(searchParam);
      countParams.push(searchParam);
    }
    if (paginationDto.package_status && paginationDto.package_status.trim() !== '') {
      if (paginationDto.package_status === 'pending') {
        searchQuery += ` AND packages.package_status = '0' `;
      }
      else if (paginationDto.package_status === 'approved') {
        searchQuery += ` AND packages.package_status = '1' `;
      }
      else if (paginationDto.package_status === 'rejected') {
        searchQuery += ` AND packages.package_status = '2' `;
      }
    }

    params.push(Number(paginationDto.limit), offset);
    try {
      const packages = await this.connection.query(
        `SELECT 
        packages.id,
        packages.package_name,
        packages.package_tax,
        packages.package_amount,
        packages.description,
        packages.package_category_id,
                CAST(
          COALESCE(packages.package_amount, 0)
          +
          (COALESCE(packages.package_amount, 0) * COALESCE(packages.package_tax, 0) / 100)
        AS DECIMAL(10,2)) AS packages_total_amount,
        CASE 
        WHEN packages.package_status = '0' THEN 'pending'
        WHEN packages.package_status = '1' THEN 'approved'
        WHEN packages.package_status = '2' THEN 'rejected'
        ELSE 'unknown'
        END AS package_status,
        staff.name as created_by_name,
        package_categories.name AS category_name
        FROM packages
        INNER JOIN package_categories
        ON packages.package_category_id = package_categories.id
        AND package_categories.status = '1'
        LEFT JOIN staff
        ON packages.created_by = staff.id
        WHERE packages.status = '1' ${searchQuery}
        ORDER BY packages.id DESC
        LIMIT ? OFFSET ? `,
        params
      );
      if (packages.length === 0) {
        return {
          response: { data: [], count: 0 },
          message: "No data found",
          status: "success",
        };
      }

      const packageIds = packages.map(p => p.id);

      const mappings = await this.connection.query(
        `SELECT 
        packages_item_mapping.package_id,
        packages_item_mapping.charges_id,
        charges.name
      FROM packages_item_mapping
      LEFT JOIN charges 
        ON packages_item_mapping.charges_id = charges.id
      WHERE packages_item_mapping.status = '1'
      AND packages_item_mapping.package_id IN (?)`,
        [packageIds]
      );

      const finalResponse = packages.map(pkg => {
        const pkgCharges = mappings.filter(m => m.package_id === pkg.id);

        return {
          ...pkg,
          charges: pkgCharges.map(c => ({
            charges_id: c.charges_id,
            charge_name: c.name,
            charge_amount: c.charge_amount,
          }))
        };
      });


      const countResult = await this.connection.query(
        `
      SELECT COUNT(*) AS total
      FROM packages
      INNER JOIN package_categories
        ON packages.package_category_id = package_categories.id
        AND package_categories.status = '1'
      WHERE packages.status = '1'
      ${searchQuery}
      `,
        countParams
      );

      const totalCount = countResult[0]?.total || 0;

      return {
        response: {
          data: finalResponse,
          count: totalCount
        },
        message: "Data fetched successfully",
        status: "success"
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: process.env.ERROR_MESSAGE_V2,
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }





  async create(createPackages: AddPackagesDto) {
    try {
      createPackages.package_status = createPackages.package_status ?? '0';
      createPackages.created_source = createPackages.created_source || 'hms';
      const [isNameExist] = await this.connection.query(
        `SELECT id FROM packages WHERE LOWER(package_name) = LOWER(?) and package_category_id = ? AND status=?`,
        [createPackages.package_name, createPackages.package_category_id, '1']
      );

      if (isNameExist) {
        return {
          status: 'failed',
          message: 'Package name already exists.'
        };
      }

      const hosAddPackage = await this.connection.query(
        `INSERT INTO packages(
        package_name,
        package_tax,
        package_category_id,
        description,
        package_amount,
        package_status,
        created_by,
        created_source
      ) VALUES (?,?,?,?,?,?,?,?)`,
        [
          createPackages.package_name,
          createPackages.package_tax,
          createPackages.package_category_id,
          createPackages.description,
          createPackages.package_amount,
          createPackages.package_status,
          createPackages.created_by,
          createPackages.created_source
        ]
      );
      const hosPackageId = hosAddPackage.insertId;
      const [hos_staff] = await this.connection.query(`
        SELECT * FROM staff WHERE id=?`, [createPackages.created_by])
      const [admin_staff] = await this.dynamicConnection.query(`
          SELECT * FROM staff WHERE email=?`, [hos_staff.email])
      const adminAddPackage = await this.dynamicConnection.query(
        `INSERT INTO packages(
        package_name,
        package_tax,
        package_category_id,
        description,
        package_amount,
        hos_package_id,
        hospital_id,
        package_status,
        created_by,
        created_source
      ) VALUES (?,?,?,?,?,?,?,?,?,?)`,
        [
          createPackages.package_name,
          createPackages.package_tax,
          createPackages.package_category_id,
          createPackages.description,
          createPackages.package_amount,
          hosPackageId,
          createPackages.hospital_id,
          createPackages.package_status,
          admin_staff.id,
          createPackages.created_source
        ]
      );
      const adminPackageId = adminAddPackage.insertId;
      for (const chargeId of createPackages.charge_type_ids) {

        await this.connection.query(
          `INSERT INTO packages_item_mapping(
          package_id,
          charges_id
        ) VALUES (?,?)`,
          [hosPackageId, chargeId]
        );
      }
      for (const chargeId of createPackages.charge_type_ids) {
        const [findAdminCharge]: any = await this.dynamicConnection.query(
          `SELECT id 
         FROM charges 
         WHERE hospital_charges_id = ? 
           AND hospital_id = ?`,
          [chargeId, createPackages.hospital_id]
        );
        if (findAdminCharge) {
          await this.dynamicConnection.query(
            `INSERT INTO packages_item_mapping(
            package_id,
            charges_id
          ) VALUES (?,?)`,
            [adminPackageId, findAdminCharge.id]
          );
        }
      }

      return {
        status: 'success',
        message: 'Package added successfully'
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: error.message
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }

  async update(package_id: number, updatePackages: EditPackagesDto) {
    try {

      const [nameRows]: any = await this.connection.query(
        `SELECT id FROM packages 
       WHERE LOWER(package_name) = LOWER(?) 
       AND id <> ? AND status=?`,
        [updatePackages.package_name, package_id, '1']
      );

      if (nameRows) {
        return {
          status: 'failed',
          message: 'Package name already exists.'
        };
      }
      const [get_packages] = await this.connection.query(`SELECT * FROM packages 
       WHERE id=?`, [package_id])
       
      let new_package_status = updatePackages?.isEdited !== undefined ? updatePackages.isEdited ? "0" : get_packages?.package_status : get_packages?.package_status;

      await this.connection.query(
        `UPDATE packages SET
        package_name = ?,
        package_tax = ?,
        package_category_id = ?,
        description = ?,
        package_amount = ?,
        package_status = ?
        WHERE id = ?`,
        [
          updatePackages.package_name,
          updatePackages.package_tax,
          updatePackages.package_category_id,
          updatePackages.description,
          updatePackages.package_amount,
          new_package_status,
          package_id
        ]
      );

      const adminRows = await this.dynamicConnection.query(
        `SELECT id 
       FROM packages 
       WHERE hos_package_id = ? 
       AND hospital_id = ?`,
        [package_id, updatePackages.hospital_id]
      );

      const adminPackageId = adminRows.length > 0 ? adminRows[0].id : null;
      if (adminPackageId) {
        await this.dynamicConnection.query(
          `UPDATE packages SET
          package_name = ?,
          package_tax = ?,
          package_category_id = ?,
          description = ?,
          package_amount = ?,
          package_status = ?
        WHERE id = ?`,
          [
            updatePackages.package_name,
            updatePackages.package_tax,
            updatePackages.package_category_id,
            updatePackages.description,
            updatePackages.package_amount,
            new_package_status,
            adminPackageId
          ]
        );
      }

      const existingRows = await this.connection.query(
        `SELECT charges_id FROM packages_item_mapping 
       WHERE package_id = ? AND status = ?`,
        [package_id, '1']
      );
      const existingIds = existingRows.map(r => r.charges_id);
      const updatedIds = updatePackages.charge_type_ids;
      const newChargeIds = updatedIds.filter(x => !existingIds.includes(x));
      const removeChargeIds = existingIds.filter(x => !updatedIds.includes(x));

      for (const chargeId of newChargeIds) {
        await this.connection.query(
          `INSERT INTO packages_item_mapping (package_id, charges_id)
         VALUES (?, ?)`,
          [package_id, chargeId]
        );
      }

      if (removeChargeIds.length > 0) {
        await this.connection.query(
          `UPDATE packages_item_mapping 
         SET status=?
         WHERE package_id = ? AND charges_id IN (?)`,
          ['0', package_id, removeChargeIds]
        );
      }

      if (adminPackageId) {
        const adminExistingRows = await this.dynamicConnection.query(
          `SELECT charges_id FROM packages_item_mapping 
         WHERE package_id = ? AND status = ?`,
          [adminPackageId, '1']
        );
        const adminExistingIds = adminExistingRows.map(r => r.charges_id);
        const adminKeepChargeIds: number[] = [];
        for (const chargeId of updatedIds) {
          const adminChargeRows = await this.dynamicConnection.query(
            `SELECT id FROM charges 
           WHERE hospital_charges_id = ? AND hospital_id = ?`,
            [chargeId, updatePackages.hospital_id]
          );

          const adminCharge = adminChargeRows[0];
          if (!adminCharge) continue;

          adminKeepChargeIds.push(adminCharge.id);


          if (!adminExistingIds.includes(adminCharge.id)) {
            await this.dynamicConnection.query(
              `INSERT INTO packages_item_mapping (package_id, charges_id)
             VALUES (?, ?)`,
              [adminPackageId, adminCharge.id]
            );
          }
        }
        const adminRemoveIds = adminExistingIds.filter(id => !adminKeepChargeIds.includes(id));
        if (adminRemoveIds.length > 0) {
          await this.dynamicConnection.query(
            `UPDATE packages_item_mapping
          SET status=?
           WHERE package_id = ? AND charges_id IN (?)`,
            ['0', adminPackageId, adminRemoveIds]
          );
        }
      }
      return {
        status: 'success',
        message: 'Package updated successfully.'
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: error.message
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }

  async delete(package_id: number) {
    try {
      const checkUsage = await this.connection.query(`select id from patient_charges where package_id = ? and payment_status <> 'paid' `, [
        package_id
      ])
      if (checkUsage.length > 0) {
        return {
          status: 'failed',
          status_code: HttpStatus.BAD_REQUEST || 400,
          message: 'Package is in use and cannot be deleted.'
        }
      }
      await this.connection.query(
        `UPDATE packages 
       SET status = '0' 
       WHERE id = ?`,
        [package_id]
      );

      await this.connection.query(
        `UPDATE packages_item_mapping 
       SET status = '0' 
       WHERE package_id = ?`,
        [package_id]
      );

      const [adminPackage] = await this.dynamicConnection.query(
        `SELECT id 
       FROM packages 
       WHERE hos_package_id = ?`,
        [package_id]
      );

      if (adminPackage) {
        await this.dynamicConnection.query(
          `UPDATE packages 
         SET status = '0' 
         WHERE id = ?`,
          [adminPackage.id]
        );

        await this.dynamicConnection.query(
          `UPDATE packages_item_mapping 
         SET status = '0' 
         WHERE package_id = ?`,
          [adminPackage.id]
        );
      }

      return {
        status: 'success',
        message: 'Package deleted successfully!'
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: error.message,
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }

  async updatePackageStatus(package_id: number, status: string) {
    try {
      await this.connection.query(
        `UPDATE packages 
       SET package_status = ? 
       WHERE id = ?`,
        [status, package_id]
      );


      const [adminPackage] = await this.dynamicConnection.query(
        `SELECT id 
       FROM packages 
       WHERE hos_package_id = ?`,
        [package_id]
      );

      if (adminPackage) {
        await this.dynamicConnection.query(
          `UPDATE packages 
         SET package_status = ? 
         WHERE id = ?`,
          [status, adminPackage.id]
        );
      }

      return {
        status: 'success',
        message: `Package ${status === '1' ? 'approved' : 'rejected'} successfully!`
      };

    } catch (error) {
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: error.message,
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }

  async remove_existing_packages_list(paginationDto: PaginationDto): Promise<any> {
    const offset = Number(paginationDto.limit) * (Number(paginationDto.page) - 1);

    let searchQuery = '';
    let excludeQuery = '';
    const params: any[] = [];
    const countParams: any[] = [];

    if (paginationDto.search && paginationDto.search.trim() !== '') {
      const searchParam = `%${paginationDto.search}%`;

      searchQuery += `
    AND (
       packages.package_name LIKE ?
    )
  `;

      params.push(searchParam);
      countParams.push(searchParam);
    }
    // if (paginationDto.package_status && paginationDto.package_status.trim() !== '') {
    //   if (paginationDto.package_status === 'pending') {
    //     searchQuery += ` AND packages.package_status = '0' `;
    //   }
    //   else if (paginationDto.package_status === 'approved') {
    //     searchQuery += ` AND packages.package_status = '1' `;
    //   }
    //   else if (paginationDto.package_status === 'rejected') {
    //     searchQuery += ` AND packages.package_status = '2' `;
    //   }
    // }
    let ids: number[] = [];

    if (paginationDto.department_id) {
      const existingPackages = await this.connection.query(
        `
      SELECT DISTINCT pc.package_id
      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 patient_charges pc 
        ON (pc.opd_id = opd.id OR pc.ipd_id = ipd.id)
      WHERE a.id = ?
        AND pc.package_id IS NOT NULL
      `,
        [paginationDto.department_id]
      );

      ids = existingPackages.map((row: any) => row.package_id);

      if (ids.length > 0) {
        excludeQuery = ` AND packages.id NOT IN (?) `;
        params.push(ids);
        countParams.push(ids);
      }
    }

    params.push(Number(paginationDto.limit), offset);
    try {
      const packages = await this.connection.query(
        `SELECT 
        packages.id,
        packages.package_name,
        packages.package_tax,
        packages.package_amount,
        packages.description,
        packages.package_category_id,
                CAST(
          COALESCE(packages.package_amount, 0)
          +
          (COALESCE(packages.package_amount, 0) * COALESCE(packages.package_tax, 0) / 100)
        AS DECIMAL(10,2)) AS packages_total_amount,
        CASE 
        WHEN packages.package_status = '0' THEN 'pending'
        WHEN packages.package_status = '1' THEN 'approved'
        WHEN packages.package_status = '2' THEN 'rejected'
        ELSE 'unknown'
        END AS package_status,
        staff.name as created_by_name,
        package_categories.name AS category_name
        FROM packages
        INNER JOIN package_categories
        ON packages.package_category_id = package_categories.id
        AND package_categories.status = '1'
        LEFT JOIN staff
        ON packages.created_by = staff.id
        WHERE packages.status = '1'  AND packages.package_status = '1' ${searchQuery} ${excludeQuery}
        ORDER BY packages.id DESC
        LIMIT ? OFFSET ? `,
        params
      );
      if (packages.length === 0) {
        return {
          response: { data: [], count: 0 },
          message: "No data found",
          status: "success",
        };
      }

      const packageIds = packages.map(p => p.id);

      const mappings = await this.connection.query(
        `SELECT 
        packages_item_mapping.package_id,
        packages_item_mapping.charges_id,
        charges.name
      FROM packages_item_mapping
      LEFT JOIN charges 
        ON packages_item_mapping.charges_id = charges.id
      WHERE packages_item_mapping.status = '1'
      AND packages_item_mapping.package_id IN (?)`,
        [packageIds]
      );

      const finalResponse = packages.map(pkg => {
        const pkgCharges = mappings.filter(m => m.package_id === pkg.id);

        return {
          ...pkg,
          charges: pkgCharges.map(c => ({
            charges_id: c.charges_id,
            charge_name: c.name,
            charge_amount: c.charge_amount,
          }))
        };
      });


      const countResult = await this.connection.query(
        `
      SELECT COUNT(*) AS total
      FROM packages
      INNER JOIN package_categories
        ON packages.package_category_id = package_categories.id
        AND package_categories.status = '1'
      WHERE packages.status = '1'
      ${searchQuery} ${excludeQuery}
      `,
        countParams
      );

      const totalCount = countResult[0]?.total || 0;

      return {
        response: {
          data: finalResponse,
          count: totalCount
        },
        message: "Data fetched successfully",
        status: "success"
      };

    } catch (error) {
      console.log('error', error)
      throw new HttpException(
        {
          statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
          message: process.env.ERROR_MESSAGE_V2,
        },
        HttpStatus.INTERNAL_SERVER_ERROR
      );
    }
  }

}