import { Injectable } from "@nestjs/common";
import { DataSource } from "typeorm";
import { HosDocDetailsDto, HospitalDetailsDto } from "./entities/hospital.dto";
import axios from "axios";
import { InjectModel } from "@nestjs/mongoose";
import { Model, Types } from "mongoose";
import { Hospital } from "./entities/hospital.entity";
const moment = require("moment");
const Razorpay = require("razorpay");

@Injectable()
export class HospitalService {
  createQueryBuilder: any;
  constructor(
    private readonly connection: DataSource,
    @InjectModel(Hospital.name, "secondConnection")
    private readonly ServiceModel: Model<Hospital>
  ) { }

  async getHospitalDetails(): Promise<any[]> {
    const query = `
    select distinct
    hospitals.plenome_id hospital_id,
    hospitals.logo,
    hospitals.hospital_name,
    concat(hospital_opening_timing," - ",hospital_closing_timing) hospital_timing,
    hospitals.hospital_consulting_charge,
    concat(hospitals.tax_percentage,"%") taxPercentage,
    hospitals.tax_amount taxAmount,
    hospitals.lattitude,
    hospitals.longitude,
    (hospitals.tax_amount + hospitals.hospital_consulting_charge) totalAmount ,
    CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address,
    (select count(*) from hospital_rating where hospital_rating.hospital_id = hospitals.plenome_id ) ratingcount,
    ROUND(((ROUND((SELECT AVG(hospital_rating.rating) FROM hospital_rating WHERE hospital_rating.hospital_id = hospitals.plenome_id), 1))), 1) AS rating
    from hospitals 
    left join hospital_staffs on hospital_staffs.hospital_id = hospitals.plenome_id
    `;
    return this.connection.query(query);
  }

  async searchHospitalsByName(name: string): Promise<any[]> {
    const searchQuery = `
    select distinct
    hospitals.plenome_id hospital_id,
    hospitals.logo,
    hospitals.hospital_name,
    concat(hospital_opening_timing," - ",hospital_closing_timing) hospital_timing,
    hospitals.hospital_consulting_charge,
    concat(hospitals.tax_percentage,"%") taxPercentage,
    hospitals.tax_amount taxAmount,
    hospitals.lattitude,
    hospitals.longitude,
    (hospitals.tax_amount + hospitals.hospital_consulting_charge) totalAmount ,
        CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address,
    (select count(*) from hospital_rating where hospital_rating.hospital_id = hospitals.plenome_id ) ratingcount,
    ROUND(((ROUND((SELECT AVG(hospital_rating.rating) FROM hospital_rating WHERE hospital_rating.hospital_id = hospitals.plenome_id), 1))), 1) AS rating
    from hospitals 
    left join hospital_staffs on hospital_staffs.hospital_id = hospitals.plenome_id
      WHERE hospitals.hospital_name LIKE ?
    `;
    return this.connection.query(searchQuery, [`%${name}%`]);
  }

  async getHospitalDetailsById(id: number): Promise<any> {
    const query = `
    SELECT
    h.plenome_id AS hospital_id,
    h.hospital_name,
    h.address,
    h.image,
    h.logo,
        h.lattitude,
    h.longitude,
    h.hospital_consulting_charge,
    CONCAT(h.tax_percentage, "%") AS tax_percentage,
    h.tax_amount,
     concat(hospital_opening_timing," - ",hospital_closing_timing) hospital_opening_timing,
    (h.hospital_consulting_charge + h.tax_amount) AS total_amount,
    h.contact_no AS phone_number,
    h.overview,
    GROUP_CONCAT(hs.services) AS services
FROM
    hospitals h
LEFT JOIN
    hospital_service hs ON hs.hospital_id = h.plenome_id
WHERE
    h.plenome_id = ?
GROUP BY
    h.plenome_id, h.hospital_name, h.address, h.image, h.logo, h.contact_no, h.overview, h.hospital_consulting_charge, h.tax_percentage, h.tax_amount;
;
    `;

    return this.connection.query(query, [id]);
  }
  async convertTo12HourFormat(time) {
    return moment(time, "HH:mm:ss").format("h:mm A");
  }

  async getHospitalAppointmentHistoryById(
    patient_id: number,
    hospital_id: number
  ) {
    try {
      const [get_base_url] = await this.connection.query(
        `select phr_api_base_url from hospitals where plenome_id = ?`,
        [hospital_id]
      );
      const response = await axios.get(
        `${get_base_url.phr_api_base_url}/phr-hospitals/appointment-history/${patient_id}?hospitalId=${hospital_id}`
      );
      return response.data;
    } catch (error) {
      return [
        {
          status_code: process.env.ERROR_STATUS_CODE,
          status: process.env.ERROR_STATUS,
          message: process.env.ERROR_MESSAGE,
        },
      ];
    }
  }

  async getHospitalDoctorsById(id: number): Promise<any[]> {
    let query = `SELECT distinct
    CONCAT("Dr. ",staff.name, " ", staff.surname) AS doctor_name,
          staff.id AS doctor_id,
          staff.image,  
            hospitals.lattitude,
    hospitals.longitude,

          hospitals.plenome_id AS hospital_id,
          hospitals.hospital_name,
          CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address,
          staff.gender,
    coalesce( staff.work_exp,"-") AS experience,
    coalesce( staff.qualification,"-") qualification,
          staff_designation.designation AS doctor_designation,
    COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known,            
    coalesce(  ROUND(((ROUND((SELECT AVG(staff_rating.rating) FROM staff_rating WHERE staff_rating.staff_id = staff.id), 1) / 5) * 5), 0),"-" )AS rating,
    coalesce(     GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names,
   concat( DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " ,
    DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id,
    (SELECT ROUND((charges.standard_charge + (charges.standard_charge * ((tax_category.percentage) / 100))), 2) amount
          FROM charges
          JOIN tax_category ON charges.tax_category_id = tax_category.id
          WHERE charges.id = shift_details.charge_id) amount
    FROM staff 
LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1
LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id
LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id
left join hospital_staffs on hospital_staffs.staff_id = staff.id
left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id
LEFT JOIN shift_details on shift_details.staff_id = staff.id
left join charges on charges.id = shift_details.charge_id
left join tax_category on charges.tax_category_id = tax_category.id
LEFT JOIN staff_designation ON staff_designation.id = staff.staff_designation_id

LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1
WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 and hospital_staffs.hospital_id = ?
`;
    const values = [id];
    query += `GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name,
      hospital_opening_timing,qualification,hospital_id`;
    try {
      const oneDoctors = await this.connection.query(query, values);
      return oneDoctors;
    } catch (error) {
      return error;
    }
  }


  async getHospitalDetailslist(
    limit: number,
    page: number,
    search?: string
  ): Promise<HospitalDetailsDto> {
    const offset = limit * (page - 1);
    let query = `
    SELECT DISTINCT
    hospitals.plenome_id hospital_id,
    hospitals.logo,
    hospitals.hospital_name,
    CONCAT(hospital_opening_timing," - ",hospital_closing_timing) AS hospital_timing,
    hospitals.hospital_consulting_charge,
    CONCAT(hospitals.tax_percentage,"%") AS taxPercentage,
    hospitals.tax_amount AS taxAmount,
    hospitals.lattitude,
    hospitals.longitude,
    (hospitals.tax_amount + hospitals.hospital_consulting_charge) AS totalAmount,
    CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address,
    (SELECT COUNT(*) FROM hospital_rating WHERE hospital_rating.hospital_id = hospitals.plenome_id) AS ratingcount,
    ROUND(((ROUND((SELECT AVG(hospital_rating.rating) FROM hospital_rating WHERE hospital_rating.hospital_id = hospitals.plenome_id), 1))), 1) AS rating
    FROM hospitals
    LEFT JOIN hospital_staffs ON hospital_staffs.hospital_id = hospitals.plenome_id
    `;
    const values: any[] = [];
    if (search) {
      query += ` WHERE hospitals.hospital_name LIKE ?`;
      values.push(`%${search}%`);
    }
    query += ` LIMIT ? OFFSET ?`;
    values.push(limit, offset);
    const total_res = await this.connection.query(query, values);
    let count_query = `SELECT COUNT(plenome_id) AS total FROM hospitals`;
    const count_values: any[] = [];
    if (search) {
      count_query += ` WHERE hospitals.hospital_name LIKE ?`;
      count_values.push(`%${search}%`);
    }
    const [total_hos] = await this.connection.query(count_query, count_values);
    return {
      details: total_res,
      total: total_hos.total,
    };
  }

  async getHospitalDoctorsByIdlist(
    id: number,
    limit: number,
    page: number
  ): Promise<HosDocDetailsDto> {
    const offset = limit * (page - 1);
    let query = `SELECT distinct
    CONCAT("Dr. ",staff.name, " ", staff.surname) AS doctor_name,
          staff.id AS doctor_id,
          staff.image,
          hospitals.plenome_id AS hospital_id,
          hospitals.hospital_name,
          CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", hospitals.pincode) AS address,
          staff.gender,
    coalesce( staff.work_exp,"-") AS experience,
    coalesce( staff.qualification,"-") qualification,
          staff_designation.designation AS doctor_designation,
    COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known,            
    coalesce(  ROUND(((ROUND((SELECT AVG(staff_rating.rating) FROM staff_rating WHERE staff_rating.staff_id = staff.id), 1) / 5) * 5), 0),"-" )AS rating,
    coalesce(     GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names,
   concat( DATE_FORMAT(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " ,
    DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id,
    (SELECT ROUND((charges.standard_charge + (charges.standard_charge * ((tax_category.percentage) / 100))), 2) amount
          FROM charges
          JOIN tax_category ON charges.tax_category_id = tax_category.id
          WHERE charges.id = shift_details.charge_id) amount
    FROM staff 
LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1
LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id
LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id
left join hospital_staffs on hospital_staffs.staff_id = staff.id
left join hospitals on hospitals.plenome_id = hospital_staffs.hospital_id
LEFT JOIN shift_details on shift_details.staff_id = staff.id
left join charges on charges.id = shift_details.charge_id
left join tax_category on charges.tax_category_id = tax_category.id
LEFT JOIN staff_designation ON staff_designation.id = staff.staff_designation_id

LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1
WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 and hospital_staffs.hospital_id = ?
`;

    query += `GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name,
      hospital_opening_timing,qualification,hospital_id  LIMIT ? OFFSET ?`;
    const values = [id, limit, offset];
    let [total_count] = await this.connection
      .query(`select count(staff.id) as total from staff LEFT JOIN staff_roles ON staff.id = staff_roles.staff_id
      left join hospital_staffs on hospital_staffs.staff_id = staff.id
   WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0 and hospital_staffs.hospital_id = ${id}`);

    try {
      const oneDoctors = await this.connection.query(query, values);
      let hos_docres = {
        details: oneDoctors,
        total: total_count.total,
      };
      return hos_docres;
    } catch (error) {
      return error;
    }
  }

  async getHospitalAppointmentHistoryByIdlist(
    patient_id: number,
    hospital_id: number,
    limit: number,
    page: number
  ) {
    try {
      const [get_base_url] = await this.connection.query(
        `select phr_api_base_url from hospitals where plenome_id = ?`,
        [hospital_id]
      );
      const response = await axios.get(
        `${get_base_url.phr_api_base_url}/phr-hospitals/v2/appointment-history/${patient_id}?hospitalId=${hospital_id}&limit=${limit}&page=${page}`
      );
      return response.data;
    } catch (error) {
      return {
        status_code: process.env.ERROR_STATUS_CODE,
        status: process.env.ERROR_STATUS,
        message: process.env.ERROR_MESSAGE,
      };
    }
  }

  async getHospitalNames() {
    const [data] = await Promise.all([this.ServiceModel.find()]);

    return {
      status: process.env.SUCCESS_STATUS_ABHA_ADDRESS_MAPPING,
      status_code: 200,
      message: process.env.DATA_FETCHED_SUCCESSFULLY,
      data: data,
    };
  }
  catch(error) {
    return {
      status: process.env.FAILED_STATUS_ABHA_ADDRESS_MAPPING,
      status_code: 500,
      message: process.env.API_SERVICE_UNAVAILABLE_TEMPORARYILY,
    };
  }
}