<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\UserMaster;
use App\Models\PatientModel;
use App\Models\VisitModel;
use App\Models\DescriptionModel;
use App\Models\TreatmentModel;
use App\Models\OptoModel;
use App\Models\DischargeModel;
use App\Models\SurgeryNoteModel;
use Illuminate\Support\Carbon;
use Session;

class MasterController extends Controller
{

    function cityList(){
        $query = DB::select("SELECT DISTINCT(City) AS City FROM tbl_patients");
        return $query;
    }

    function stateList(){
        $query = DB::select("SELECT DISTINCT(State) AS State FROM tbl_patients");
        return $query;
    }

    function consultantList(){
        $UserType = Session::get('UserType');
        $UserId = Session::get('UserId');
        if($UserType == 'Admin'){
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Consultant'");
        }
        else{
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Consultant'");
            //$result = DB::select("SELECT * FROM tbl_users WHERE Type='Consultant' AND id='$UserId'");
        }
        
        return $result;
    }

     function certificateList(){
       
            $result = DB::select("SELECT * FROM tbl_type_of_certificates");
        
        return $result;
    }


    function surgeonList(){
        $UserType = Session::get('UserType');
        $UserId = Session::get('UserId');
        if($UserType == 'Admin'){
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Consultant'");
        }
        else{
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Consultant'");
        }
        
        return $result;
    }

    function optometristList(){
        $UserType = Session::get('UserType');
        $UserId = Session::get('UserId');
        if($UserType == 'Admin' || $UserType == 'Consultant'){
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Optometrist'");
        }
        else{
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Optometrist' AND id='$UserId'");
        }

        return $result;
    }

    function receptionList(){
        $UserType = Session::get('UserType');
        $UserId = Session::get('UserId');
        
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Reception'");
        

        return $result;
    }

    function optometristList2(){
        $UserType = Session::get('UserType');
        $UserId = Session::get('UserId');
       
            $result = DB::select("SELECT * FROM tbl_users WHERE Type='Optometrist'");
       
        return $result;
    }
    
    function patientList(){
        $query = DB::select("SELECT MrdNo,PatientName,ConsultantName,Phone,Address,City FROM tbl_patients LIMIT 1");
        //$query = DB::select("CALL getPatients2()");
        return $query;
    }

    function ipdPatientList(){
        //$query = DB::select("SELECT MrdNo,PatientName,ConsultantName,Phone FROM tbl_patients");
        $query = DB::select("CALL getIpdPatients()");
        return $query;
    }

    function getPatientList(Request $req){
        $val = $req->val;
        $query = DB::table('tbl_patients')->where('Status','1')
                ->where('PatientName', 'LIKE', '%'.$val.'%')
                ->orWhere('Fname', 'LIKE', '%'.$val.'%')
                ->orWhere('Mname', 'LIKE', '%'.$val.'%')
                ->orWhere('Lname', 'LIKE', '%'.$val.'%')
                ->orWhere('MrdNo', 'LIKE', '%'.$val.'%')
                ->orWhere('Phone', 'LIKE', '%'.$val.'%')->get();
        $count = $query->count();
        if($count > 0){        
        $result = view('masters/patientlist',['query'=>$query]);
        }
        else{
            $result = 0;    
        }
        return $result;
    }

    function getPatientDetails(Request $req){
        $val = $req->id;
        $seperate = explode(" / ", $val);
        $searchVal = $seperate[0];
        
        // Try to find by MRD No first
        $result = PatientModel::select()->where(['MrdNo'=>$searchVal])->first();
        
        // If not found, try to find by UHID (if UHID field exists)
        if(!$result) {
            $result = PatientModel::select()->where('UhidNo', $searchVal)->orWhere('UHID', $searchVal)->first();
        }
        
        // If still not found, try as direct MRD match
        if(!$result) {
            $result = PatientModel::select()->where('MrdNo', $searchVal)->first();
        }
        
        return json_encode($result);
    }

    function getPatientDetails2(Request $req) {
        $val = $req->id;
        $separate = explode(" / ", $val);
        $MrdNo = $separate[0];
    
        // 1. Get patient data
        $patient = PatientModel::where('MrdNo', $MrdNo)->first();
       $lastvisitdetails = DB::table('tbl_queue_managements')
    ->select('InDate', DB::raw('DATEDIFF(CURDATE(), DATE(InDate)) as DaysAgo'))
    ->where('MrdNo', $MrdNo)
    ->whereDate('InDate', '<', now()->toDateString())
    ->orderByDesc('InDate')
    ->limit(1)
    ->first();
        // 2. Get last 400 payment date
        $last400Date = DB::table('tbl_queue_managements')
            ->where('MrdNo', $MrdNo)
            ->where('Amount', 400)
            ->orderByDesc('InDate')
            ->value('InDate');
    
        
            // 2. Format or label the last 400 date
if ($last400Date) {
    $Last400VisitDate = Carbon::parse($last400Date)->format('d/m/Y');
    $Total400Days = Carbon::parse($last400Date)->diffInDays(Carbon::today());
    $amount = $Total400Days <= 40 ? 100 : 400;
} else {
    $Last400VisitDate = 'Still Not Paid';
    $Total400Days = '0';
    $amount = 400; 
}

        // 4. Combine result
        return response()->json([
            'patient' => $patient,
            'amount_to_pay_today' => $amount,
            'lastvisitdate'=>$lastvisitdetails->InDate,
            'totdays'=>$lastvisitdetails->DaysAgo,
            'last400Date'=>$Last400VisitDate,
            'daysSince'=>$Total400Days
        ]);
    }

    function getIpdPatientDetails(Request $req){
        $val = $req->id;
        $seperate = explode(" / ", $val);
        $ipdno = $seperate[1];
        $result = DischargeModel::select()->where(['IpdNo'=>$ipdno])->first();;
        return json_encode($result);
    }

    function getPatientVisit($mrdno){
        $data = OptoModel::select('VisitNo','VisitDate')->where(['MrdNo'=>$mrdno])->orderBy('VisitDate','desc')->get();
        return $data;     
    }


    function compDays(){
        $data = DB::table('tbl_comp_days')->get();
        return $data;     
    }

    function visitdate(){
        return 1;
    }

    function getDescList(Request $req){
        $ModalValue = $req->ModalValue;
        $srquery = $req->srquery;
        if($srquery != ''){
            $data = DB::table('tbl_decriptions')->where('Type',$ModalValue)
            ->where('Description', 'LIKE', '%'.$srquery.'%')->orderBy('id','desc')->get();   
        }
        else{
        $data = DB::table('tbl_decriptions')->where('Type',$ModalValue)->orderBy('id','desc')->get();
        }
       $result = view('masters/descriptionlist',['data'=>$data]);
       return $result;
    }

    function saveDesc(Request $req){
        //return $req->action;exit();
        if($req->action == 'Save'){
            $Description = new DescriptionModel;
            }
            else{
            $Description = DescriptionModel::find($req->id);
            }
            $Description->Description=$req->Description;
            $Description->Type=$req->ModalValue;
            $Description->CreatedBy = 19;
            $Description->ModifiedBy = 19;
            $Description->save();
            return 1;
    }

    function updateDesc(Request $req){
        $result = DescriptionModel::find($req->id);
        return json_encode($result);
    }

    function deleteDesc(Request $req){
        $data=DescriptionModel::find($req->id)->delete();
        return 1;

    }

    public static function getOptoDetails($mrdno,$visitdate){
        //$cnt = OptoModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>'2019-03-19'])->count();
        $result = DB::select("SELECT * FROM tbl_optometrist WHERE MrdNo='$mrdno' AND VisitDate='$visitdate'");
        return $result;
    }

    public static function getConsDetails($mrdno,$visitdate){
        $result = DB::select("SELECT * FROM tbl_consultants WHERE MrdNo='$mrdno' AND VisitDate='$visitdate'");
        return $result;
    }

    public static function getVisitDetails($mrdno,$visitdate){
        $result = DB::select("SELECT * FROM tbl_visits WHERE MrdNo='$mrdno' AND VisitDate='$visitdate'");
        //$result = VisitModel::select()->where(['MrdNo'=>$mrdno,'VisitDate'=>$visitdate])->get();
        return $result;
    }

    function newPatientDataList(){
        $result = DB::select("CALL getPatients2()");
        return view('masters/incpatientdatalist',['data'=>$result]);
    }

    function drugList(){
        $query = DB::select("SELECT Name FROM tbl_drugs");
        return $query;
    }

    function findCurrAge(Request $req){
        $CurrDate = date('Y-m-d');
        $mrdno = $req->mrdno;
        $result = PatientModel::select('Age','CreatedDate')->where(['MrdNo'=>$mrdno])->get();
        $cnt = $result->count();
        if($cnt > 0){
            $PrevAge = $result[0]->Age;
            $RegDate = $result[0]->CreatedDate;
            //$curryrs = \Carbon\Carbon::parse($RegDate)->diff(\Carbon\Carbon::now());
            $year = $curryrs->format('%y');
            $Age = $year + $PrevAge;
            return $Age;
        }
        else{
            return $result[0]->Age;
        }
    }

    function diagnosisList(){
        $result = DescriptionModel::where('Type','Diagnosis')->get();
        return $result;
    }

    function surgerynotesList(){
        $result = SurgeryNoteModel::select('Name')->distinct()->get();
        return $result;
    }

    function getSurgeryNotes(Request $req){
        $result = SurgeryNoteModel::where('Name',$req->type)->first();
        return json_encode($result);
    }

    function getDescList2(Request $req){
        $ModalValue = $req->ModalValue;
        $srquery = $req->srquery;
        if($srquery != ''){
            $data = DB::table('tbl_decriptions')->where('Type',$ModalValue)
            ->where('Description', 'LIKE', '%'.$srquery.'%')->orderBy('id','desc')->get();   
        }
        else{
        $data = DB::table('tbl_decriptions')->where('Type',$ModalValue)->orderBy('id','desc')->get();
        }
       $result = view('masters/descriptionlist2',['data'=>$data]);
       return $result;
    }

    function durationList(){
        $data = DB::table('tbl_duration')->get();
        return $data;     
    }

    function descriptionList($type){
        $query = DB::select("SELECT Description FROM tbl_decriptions WHERE Type='$type'");
        return $query;
    }

    function getBrochures(){
        $query = DB::select("SELECT * FROM tbl_brochures WHERE Status=1 ORDER BY Name");
        return $query;
    }

    
}