<?php

namespace App\Models;

use CodeIgniter\Model;

class HistoryMachineModel extends Model
{
    protected $table            = 'tbl_history_machine';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'object';
    protected $useSoftDeletes   = false;
    protected $protectFields    = false;
    protected $allowedFields    = [];

    protected bool $allowEmptyInserts = false;
    protected bool $updateOnlyChanged = true;

    protected array $casts = [];
    protected array $castHandlers = [];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert   = [];
    protected $afterInsert    = [];
    protected $beforeUpdate   = [];
    protected $afterUpdate    = [];
    protected $beforeFind     = [];
    protected $afterFind      = [];
    protected $beforeDelete   = [];
    protected $afterDelete    = [];




    public function getTotalOperationToday($id)
    {

        $builder = $this->db->table('tbl_machine_operation');
        $builder->where('DATE(start_date)',date("Y-m-d"));
        $builder->where('machine_id',$id);
        $builder->where('has_reset',0);
        $builder->orderBy('id',0);


        $query = $builder->get();
        $results = $query->getResult();
        $totalSecondsToday = 0;
        if(empty($results)){
            return  $totalSecondsToday;
        }


        forEach($results AS $result){
            if($result->end_date==null||$result->end_time==null){

                $startOperation = date('Y-m-d', strtotime($result->start_date))." ".date('H:i:s', strtotime($result->start_time));
                $start_time = strtotime($startOperation);
                $end_time = time();
                $diff_seconds = $end_time - $start_time;
                $totalSecondsToday += $diff_seconds;

                $hours = floor($totalSecondsToday / 3600);
                $minutes = floor(($totalSecondsToday % 3600) / 60);

                return "$hours.$minutes";
                break;

            }else{

                $startOperation = date('Y-m-d', strtotime($result->start_date))." ".date('H:i:s', strtotime($result->start_time));
                $endOperation = date('Y-m-d', strtotime($result->end_date))." ".date('H:i:s', strtotime($result->end_time));
                
                $start_time = strtotime($startOperation);
                $end_time = strtotime($endOperation);
                $diff_seconds = $end_time - $start_time;
                $totalSecondsToday += $diff_seconds;
                
            }
        }
        

        $hours = floor($totalSecondsToday / 3600);
        $minutes = floor(($totalSecondsToday % 3600) / 60);

        return "$hours.$minutes";


        // $builder = $this->db->table('tbl_machine_operation');
        // $builder->select('SUM(TIMESTAMPDIFF(SECOND, 
        //             GREATEST(CONCAT(start_date, " ", start_time), CURDATE()), 
        //             IF(end_date IS NULL, NOW(), LEAST(CONCAT(end_date, " ", end_time), NOW())))) / 3600 as total_operation_today');
        // $builder->where('machine_id', $id);
        // $builder->where('has_reset', 0);
        // $builder->where('(start_date = CURDATE() OR end_date IS NULL OR end_date = CURDATE())');
        // $query = $builder->get();

        // $row = $query->getRow();
        // return empty($row->total_operation_today) ? 0 : $row->total_operation_today;

        // $this->select('id, status, created_at, to_value');
        // $this->where('DATE(created_at)',date('Y-m-d'));
        // $this->where('UPPER(status)!=','CONTINUE');
        // $this->where('machine_id',$id);
        // $this->where('has_reset',0);
        // $this->orderBy('created_at',"ASC");
        // $results = $this->findAll();
        
        // $totalSeconds = 0;
        // $skip = false;
        // if(!empty( (array) $results )){
        //     foreach ($results as $index => $item) {               

        //         if($skip){
        //             $skip = false;
        //             continue;
        //         }

        //         if(floatval($item->to_value)>0){
        //             if(isset($results[$index + 1])){
        //                 if(floatval($results[$index + 1]->to_value)<=0){
        //                     $start_time     = strtotime($item->created_at);
        //                     $end_time       = strtotime($results[$index + 1]->created_at);
        //                     $totalSeconds   += $end_time - $start_time;
        //                     $skip = true;
        //                 }else{
        //                     $start_time     = strtotime($item->created_at);
        //                     $end_time       = time();
        //                     $totalSeconds   += $end_time - $start_time; 
        //                 }
        //             }else{

        //                 $start_time     = strtotime($item->created_at);
        //                 $end_time       = time();
        //                 $totalSeconds   += $end_time - $start_time; 
        //             }

        //         }else{
        //             $start_time     = strtotime("00:00:00");
        //             $end_time       = strtotime($item->created_at);
        //             $totalSeconds   += $end_time - $start_time;
        //         }
        //     }
                        
        // }

        // return $totalSeconds;

    }



    public function getAverageCurrentToday($id)
    {

        $this->select('FORMAT(AVG(to_value), 1) as average_value');
        $this->where('DATE(created_at)',date('Y-m-d'));
        $this->where('to_value>',0);
        $this->where('machine_id',$id);
        $this->where('has_reset', 0);
        $this->orderBy('created_at',"ASC");
        $row = $this->first();

        if(!empty( (array) $row )){
            return !empty($row->average_value) ? $row->average_value : 0 ;
        }
        
    }


    public function getAverageOperationDaily($id)
    {
        $builder = $this->db->table('tbl_machine_operation');
        $builder->select('AVG(TIMESTAMPDIFF(SECOND, CONCAT(start_date, " ", start_time), 
                    IF(end_date IS NULL, NOW(), CONCAT(end_date, " ", end_time)))) / 3600 as avg_operation_daily');
        $builder->where('machine_id', $id);
        // $builder->where('has_reset', 0);
        $query = $builder->get();
        $row = $query->getRow();

        return empty($row->avg_operation_daily) ? 0 : number_format($row->avg_operation_daily, 1);

    }

    // public function getTotalOperatingTimePerDay($machine_id)
    // {
    //     $sql = "
    //         WITH status_fixed AS (
    //             SELECT *,
    //                 CASE 
    //                     WHEN status = 'CONTINUE' THEN 
    //                         LAG(status) OVER (PARTITION BY machine_id ORDER BY created_at) 
    //                     ELSE status 
    //                 END AS fixed_status
    //             FROM tbl_history_machine
    //             WHERE machine_id = ?
    //         ),
    //         operation_intervals AS (
    //             SELECT 
    //                 machine_id,
    //                 created_at AS start_time,
    //                 LEAD(created_at) OVER (PARTITION BY machine_id ORDER BY created_at) AS end_time
    //             FROM status_fixed
    //             WHERE fixed_status = 'ON'
    //         )
    //         SELECT 
    //             DATE(start_time) AS operation_date,
    //             SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS total_operating_time
    //         FROM operation_intervals
    //         WHERE end_time IS NOT NULL
    //         GROUP BY operation_date;
    //     ";

    //     return $this->db->query($sql, [$machine_id])->getResult();
    // }

    // public function getAverageOperatingTimePerDay($machine_id)
    // {
    //     $sql = "
    //         WITH status_fixed AS (
    //             SELECT *,
    //                 CASE 
    //                     WHEN status = 'CONTINUE' THEN 
    //                         LAG(status) OVER (PARTITION BY machine_id ORDER BY created_at) 
    //                     ELSE status 
    //                 END AS fixed_status
    //             FROM tbl_history_machine
    //             WHERE machine_id = ?
    //         ),
    //         operation_intervals AS (
    //             SELECT 
    //                 machine_id,
    //                 created_at AS start_time,
    //                 LEAD(created_at) OVER (PARTITION BY machine_id ORDER BY created_at) AS end_time
    //             FROM status_fixed
    //             WHERE fixed_status = 'ON'
    //         ),
    //         daily_operations AS (
    //             SELECT 
    //                 DATE(start_time) AS operation_date,
    //                 SUM(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS total_operating_time
    //             FROM operation_intervals
    //             WHERE end_time IS NOT NULL
    //             GROUP BY operation_date
    //         )
    //         SELECT 
    //             ROUND(AVG(total_operating_time) / 3600, 1) AS avg_operation_hours
    //         FROM daily_operations;
    //     ";

    //     return $this->db->query($sql, [$machine_id])->getRow();
    // }


    public function getLastedHistoryMachine($id)
    {

        $this->select('created_at, to_value');
        $this->where('machine_id',$id);
        $this->orderBy('id',"DESC");
        $this->limit(10);
        $results = $this->findAll();

        $values = [];
        $labels = [];
        if(!empty($results)){                
            foreach($results AS $row){
                $values[] = $row->to_value;
                $labels[] = date("H:i:s", strtotime($row->created_at));
            }
        }

        return [
            'value' => !empty($results[0]) ? $results[0]->to_value : 0,
            'history'   => [
                'labels'    => array_reverse($labels),
                'values'    => array_reverse($values),
            ]
        ];


    }




    public function getTotalOperation($id, $secondsTotal = 0)
    {   


        $secondsTotal = intval($secondsTotal);
        $this->select('created_at, to_value');
        $this->where('machine_id',$id);
        $this->where('has_reset',0);
        $this->orderBy('id',"DESC");
        $lastHistory = $this->first();

        if(empty( (array) $lastHistory )){
            return $secondsTotal;
        }

        if(floatval($lastHistory->to_value)>0){

            $start_time     = strtotime($lastHistory->created_at);
            $end_time       = time();
            $secondsTotal   += $end_time - $start_time;             

            return $secondsTotal;
        }else{
            return $secondsTotal;
        }

    }

}

