import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class ServiceDoctorsService {
  constructor(private readonly connection: DataSource) { }

  async findAll(date: string, gender: string, speciality: string, language: string) {
    let query = `SELECT distinct
            CONCAT("Dr. ",staff.name, " ", staff.surname) AS doctor_name,
                  staff.id AS doctor_id,
                  staff.image,
                  staff.gender,
                  hospitals.plenome_id AS hospital_id,
                  hospitals.hospital_name,
                      hospitals.lattitude,
    hospitals.longitude,

                  CONCAT(hospitals.address, " ", hospitals.state, " ", hospitals.district, " - ", hospitals.pincode) AS address,
            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(hospitals.hospital_opening_timing, '%h:%i %p')," - " ,
            DATE_FORMAT(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 
      `
    let values = []
    if (date) {
      query += ` and  doctor_shift.day = dayname(?) `
      values.push(date)
    } else {
      const currentDate = new Date()
      query += ` and doctor_shift.day = dayname(?) `
      values.push(currentDate)
    }
    if (gender) {
      query += ` and staff.gender = ?`
      values.push(gender)
    }

    let endQuery = ` GROUP BY doctor_id, doctor_name, plenome_id, hospital_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id    `
    let final = query + endQuery

    if (speciality || language) {
      final += ` HAVING `

      if (speciality && language) {
        if (speciality) {
          final += `  FIND_IN_SET( ?, specialist_names) > 0 `
          values.push(speciality)
        }
        if (language) {
          final += ` AND FIND_IN_SET( ?, languages_known) > 0 `
          values.push(language)
        }
      } else {
        if (speciality) {
          final += `  FIND_IN_SET( ?, specialist_names) > 0 `
          values.push(speciality)
        }
        if (language) {
          final += `  FIND_IN_SET( ?, languagesknown) > 0 `
          values.push(language)
        }
      }


    }


    const get_all_doctors = await this.connection.query(final, values)
    return get_all_doctors;
  }

  findOne(id: number) {
    return `This action returns a #${id} serviceDoctor`;
  }

  ///////////////////////pagination
  async findAlldoctorlist(date: string, gender: string, speciality: string, language: string, limit: number, page: number, search: string) {
    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,
                    staff.gender,
                    hospitals.plenome_id AS hospital_id,
                    hospitals.hospital_name,
                    CONCAT(hospitals.address, " ", hospitals.state, " ", hospitals.district, " - ", hospitals.pincode) AS address,
              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(hospitals.hospital_opening_timing, '%h:%i %p')," - " ,
              DATE_FORMAT(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 
        `
    let values = []
    let count_query = `select  count(distinct staff.id) as total,
                COALESCE( GROUP_CONCAT(DISTINCT languages.language),"-") AS languages_known,  
                              coalesce(     GROUP_CONCAT(DISTINCT specialist.specialist_name) ,"-")AS specialist_names          
 from staff LEFT JOIN staff_roles ON staff.id = staff_roles.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 specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1
        LEFT JOIN languages ON JSON_CONTAINS(staff.languagesKnown, CAST(languages.id AS JSON), '$') = 1
  left join doctor_shift on doctor_shift.staff_id = staff.id WHERE staff_roles.role_id = 3 and staff.is_active = 1 and staff.is_deleted = 0  `
    let count_values = []
    if (date) {
      query += ` and  doctor_shift.day = dayname(?) `
      count_query += ` and  doctor_shift.day = dayname(?) `
      values.push(date)
      count_values.push(date)
    }
    // else {
    //   const currentDate = new Date()
    //   query += ` and doctor_shift.day = dayname(?) `
    //  values.push(currentDate)
    //  count_query += ` and doctor_shift.day = dayname(?) `
    //  count_values.push(currentDate)
    // }
    if (gender) {
      query += ` and staff.gender = ?`
      values.push(gender)
      count_query += ` and staff.gender = ?`
      count_values.push(gender)
    }

    if (search) {
      query += ` and (staff.name like ? or hospitals.hospital_name like ? )`

      values.push(`%${search}%`, `%${search}%`);

      count_query += ` and (staff.name like ? or hospitals.hospital_name like ? )`
      count_values.push(`%${search}%`, `%${search}%`);
      // query += `  AND (
      //     CONCAT("Dr. ", staff.name, " ", staff.surname) LIKE CONCAT(${"%"+search+"%"} )
      //     OR hospitals.hospital_name LIKE CONCAT(${"%"+search+"%"} )
      // ) `
    }

    let endQuery = ` GROUP BY doctor_id, doctor_name, plenome_id, hospital_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id`
    let count_endQuery = ` GROUP BY staff.id  `

    let final = query + endQuery
    let count_final = count_query + count_endQuery


    if (speciality || language) {
      const havingConditions = [];

      if (speciality) {
        const specialityList = speciality.split(',').map((item) => item.trim()).filter(Boolean);
        if (specialityList.length > 0) {
          const specConditions = specialityList.map(() => `FIND_IN_SET(?, specialist_names) > 0`);
          havingConditions.push(`(${specConditions.join(' OR ')})`);
          values.push(...specialityList);
        }
      }

      if (language) {
        const languageList = language.split(',').map((item) => item.trim()).filter(Boolean);
        if (languageList.length > 0) {
          const langConditions = languageList.map(() => `FIND_IN_SET(?, languages_known) > 0`);
          havingConditions.push(`(${langConditions.join(' OR ')})`);
          values.push(...languageList);
        }
      }

      if (havingConditions.length > 0) {
        final += ` HAVING ` + havingConditions.join(' AND ');
      }
    }
    final += ` limit ? offset ?`
    values.push(limit, offset)



    const get_all_doctors = await this.connection.query(final, values)
    let total_count = await this.connection.query(count_final, count_values)

    return {
      status: "success",
      message: "Service Doctor list fetched successfully",
      data: get_all_doctors,
      total: total_count.length
    };
  }


}