import { Injectable } from '@nestjs/common';
import { InjectConnection } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { DoctorProfile } from './entities/doctor_profile.entity';
import { log } from 'console';
import { DocReviewDetailsDto } from './entities/doctor_profile.dto';

@Injectable()
export class DoctorProfileService {
  constructor(@InjectConnection() private connection: Connection) { }

  async create(StaffReview: DoctorProfile) {
    const [check] = await this.connection.query(`select id from staff_rating where patient_id = ? and staff_id = ?`, [
      StaffReview.patient_id,
      StaffReview.staff_id
    ])

    if (check) {
      const update = await this.connection.query(`update staff_rating set review = ?,
      rating = ?,
      is_recommended = ?,
      start_on_time = ? where id = ?
      `, [
        StaffReview.review,
        StaffReview.rating,
        StaffReview.is_recommended,
        StaffReview.start_on_time,
        check.id
      ])
      return [{
        messege: process.env.DOC_RATING_UPDATE_SUCCESS,
        review_details: await this.connection.query('select * from staff_rating where staff_rating.id = ?', [check.id])

      }];
    }
    else {
      const addReview = await this.connection.query(
        'insert into staff_rating(patient_id,staff_id,review,rating,is_recommended,start_on_time) values (?,?,?,?,?,?)',
        [StaffReview.patient_id,
        StaffReview.staff_id,
        StaffReview.review,
        StaffReview.rating,
        StaffReview.is_recommended,
        StaffReview.start_on_time
        ]
      )
      return [{
        messege: process.env.DOC_RATING_POST_SUCCESS,
        review_details: await this.connection.query('select * from staff_rating where staff_rating.id = ?', [addReview.insertId])

      }];
    }

  }


  async findOne(id: number) {
    let query = `SELECT distinct
            CONCAT("Dr. ",staff.name, " ", staff.surname," (",staff.employee_id,")") AS doctor_name,
                  staff.id AS doctor_id,
                  staff.image,
                  hospitals.plenome_id AS hospital_id,
                      hospitals.lattitude,
    hospitals.longitude,

                  hospitals.hospital_name,
                  CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", 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(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " ,
            DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id,
            charges.standard_charge,
            round((charges.standard_charge * ((tax_category.percentage) / 100)),2) tax,
            concat(tax_category.percentage,"%") taxPercentage,
            (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.id = ?
      GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name,standard_charge,
      tax,
              hospital_opening_timing,qualification,hospital_id`
    let values = [id]

    const getDetails = await this.connection.query(query, values)

    return getDetails;
  }

  async findAbout(id: number) {
    let query = `select 
    staff.id staff_id,
    staff.image,
    staff.note about,
    GROUP_CONCAT(DISTINCT specialist.specialist_name) AS speciality_names,
    staff.specialization,
    department.department_name,staff_designation.designation,
    staff.qualification,
    staff.Health_Professional_Registry,
      GROUP_CONCAT(DISTINCT staff_certifications.certificate_name," (",staff_certifications.issued_year,")") AS certifications,
    staff.instagram,
    staff.facebook,
    staff.linkedin,staff.twitter
    from staff 
    LEFT JOIN specialist ON JSON_CONTAINS(staff.specialist, CAST(specialist.id AS JSON), '$') = 1
     LEFT JOIN staff_certifications ON staff_certifications.staff_id = staff.id
    left join department on department.id = staff.department_id
    left join staff_designation on staff_designation.id = staff.staff_designation_id
    where staff.id = ?
    GROUP BY specialization, staff.id, staff.note  `
    let values = [id]
    const getDetails = await this.connection.query(query, values)
    return getDetails;
  }


  async findCalender(id: number, date: string) {
    let query = `select staff.id,
    staff.image,
    GROUP_CONCAT(DISTINCT CONCAT(start_time, " - ", end_time,"(",doctor_shift.id,")") ORDER BY doctor_shift.id) AS 'timings(shift_id)',
    doctor_shift.day,
    GROUP_CONCAT( distinct doctor_shift.id) AS shift_id
    from staff
      LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id
      where staff.id = ? and doctor_shift.day = dayname(now())
    group by staff.id,doctor_shift.day `
    let values = []
    values.push(id)
    if (date) {
      query = `select staff.id,
     staff.image,
    GROUP_CONCAT(DISTINCT CONCAT(start_time, " - ", end_time,"(",doctor_shift.id,")") ORDER BY doctor_shift.id) AS 'timings(shift_id)',
    doctor_shift.day,
    GROUP_CONCAT( distinct doctor_shift.id) AS shift_id
    from staff
      LEFT JOIN doctor_shift ON doctor_shift.staff_id = staff.id
      where staff.id = ? and doctor_shift.day = dayname(?)
    group by staff.id,doctor_shift.day `
      values.push(date)
    }
    const getcalander = await this.connection.query(query, values)
    return getcalander;
  }
  async finddocHos(id) {
    let query = `SELECT distinct
            CONCAT("Dr. ",staff.name, " ", staff.surname," (",staff.employee_id,")") AS doctor_name,
                  staff.id AS doctor_id,
                  staff.image,
                  hospitals.plenome_id AS hospital_id,
                      hospitals.lattitude,
    hospitals.longitude,
                  hospitals.hospital_name,
                  CONCAT(hospitals.address, ", ", hospitals.district, ", ", hospitals.state, " - ", 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(time(hospitals.hospital_opening_timing), '%h:%i %p')," - " ,
            DATE_FORMAT(time(hospitals.hospital_closing_timing), '%h:%i %p')) timings_shift_id,
            charges.standard_charge,
            round((charges.standard_charge * ((tax_category.percentage) / 100)),2) tax,
            concat(tax_category.percentage,"%") taxPercentage,
            (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.id = ?
      GROUP BY doctor_id, doctor_name, doctor_shift.day, doctor_shift.global_shift_id,charge_id,hospital_name,standard_charge,
      tax,
              hospital_opening_timing,qualification,hospital_id`
    let values = [id]
    const getStaffHos = await this.connection.query(query, values)
    return getStaffHos
  }

  async findDocRating(id) {
    let query = `select round((((select count(*) from staff_rating where staff_rating.is_recommended = 1 and staff_rating.staff_id = staff.id )/(select count(*) from staff_rating where staff_rating.staff_id = staff.id))*100),0) recommended,
  round((((select count(*) from staff_rating where staff_rating.start_on_time = 1 and staff_rating.staff_id = staff.id)/(select count(*) from staff_rating where staff_rating.staff_id = staff.id ))*100),0) on_time,
  round((select avg(staff_rating.rating)from staff_rating where staff_rating.staff_id = staff.id),1) rating 
  from staff where staff.id = ?`
    let values = [id]
    const getStaffHos = await this.connection.query(query, values)
    return getStaffHos
  }

  async findDocReview(id) {
    let query = `select staff_rating.review,patients.patient_name,staff_rating.rating,staff_rating.created_at from 
    staff_rating left join patients on patients.id = staff_rating.patient_id where staff_rating.staff_id = ?`
    let values = [id]
    const getStaffHos = await this.connection.query(query, values)
    return getStaffHos
  }
  async findDocReviewlist(id, limit: number, page: number): Promise<DocReviewDetailsDto> {
    const offset = limit * (page - 1);
    let query = `select staff_rating.review,patients.patient_name,staff_rating.rating,staff_rating.created_at from 
  staff_rating left join patients on patients.id = staff_rating.patient_id where staff_rating.staff_id = ? limit ? offset ?`
    let values = [id, limit, offset]
    const getStaffHos = await this.connection.query(query, values)
    let [total_count] = await this.connection.query(`select count(id) as total from staff_rating where staff_rating.staff_id = ${id}`, [id])
    let result = {
      details: getStaffHos,
      total: total_count.total
    };
    return result;
  }
}