<?php

namespace Hubleto\App\Community\Projects\Controllers\Api;

use Hubleto\App\Community\Auth\Models\User;
use Hubleto\App\Community\Projects\Models\Project;
use Hubleto\App\Community\Projects\Models\ProjectTask;
use Hubleto\App\Community\Tasks\Models\Task;
use Hubleto\App\Community\Worksheets\Models\Activity;

class GetStatistics extends \Hubleto\Erp\Controllers\ApiController
{
  public function renderJson(): array
  {
    $idProject = $this->router()->urlParamAsInteger("idProject");

    $statistics = [];

    $mProject = $this->getService(Project::class);
    $mTask = $this->getService(Task::class);
    $mProjectTask = $this->getService(ProjectTask::class);
    $mActivity = $this->getService(Activity::class);
    $mUser = $this->getService(User::class);

    try {
      $project = $mProject->record->prepareReadQuery()->where($mProject->table.".id", $idProject)->first();
      $statistics['project'] = $project;

      $averageHourlyCosts = $project->average_hourly_costs ?? 0;

      $projectTasksIds = $mProjectTask->record->prepareReadQuery()
        ->where($mProjectTask->table . '.id_project', $idProject)
        ->pluck('id_task')
        ?->toArray()
      ;

      if (count($projectTasksIds) == 0) $projectTasksIds = [0];

      // workedByUser
      $workedByUser = $this->db()->fetchAll('
        select
          `' . $mActivity->table . '`.`id_worker` as `id_worker`,
          concat(ifnull(`u`.`first_name`, ""), " ", ifnull(`u`.`last_name`, "")) as `worker_name`,
          sum(`' . $mActivity->table . '`.`worked_hours`) as `worked_hours`
        from `' . $mActivity->table . '`
        left join `' . $mTask->table . '` `t` on `t`.`id` = `' . $mActivity->table . '`.`id_task`
        left join `' . $mUser->table . '` `u` on `u`.`id` = `' . $mActivity->table . '`.`id_worker`
        where
          `t`.`id` in (' . join(',', $projectTasksIds) . ')
        group by
          `id_worker`
      ');

      $statistics['workedByUser'] = $workedByUser;

      // workedByMonth
      $workedByMonth = $this->db()->fetchAll('
        select
          month(`' . $mActivity->table . '`.`date_worked`) as `month`,
          year(`' . $mActivity->table . '`.`date_worked`) as `year`,
          sum(`' . $mActivity->table . '`.`worked_hours`) as `worked_hours`,
          sum(`' . $mActivity->table . '`.`worked_hours`) * ' . $averageHourlyCosts . ' as `costs`
        from `' . $mActivity->table . '`
        left join `' . $mTask->table . '` on `' . $mTask->table . '`.`id` = `' . $mActivity->table . '`.`id_task`
        where
          `' . $mTask->table . '`.`id` in (' . join(',', $projectTasksIds) . ')
        group by
          concat(year(date_worked), month(date_worked))
      ');

      $statistics['workedByMonth'] = $workedByMonth;

      // chargeableByMonth
      $chargeableByMonth = $this->db()->fetchAll('
        select
          month(`' . $mActivity->table . '`.`date_worked`) as `month`,
          year(`' . $mActivity->table . '`.`date_worked`) as `year`,
          sum(`' . $mActivity->table . '`.`worked_hours`) as `worked_hours`
        from `' . $mActivity->table . '`
        left join `' . $mTask->table . '` on `' . $mTask->table . '`.`id` = `' . $mActivity->table . '`.`id_task`
        where
          `' . $mTask->table . '`.`id` in (' . join(',', $projectTasksIds) . ')
          and `' . $mTask->table . '`.`is_chargeable` = 1
          and `' . $mActivity->table . '`.`is_chargeable` = 1
        group by
          concat(year(date_worked), month(date_worked))
      ');

      $statistics['chargeableByMonth'] = $chargeableByMonth;

    } catch (\Exception $e) {
      return [
        "status" => "failed",
        "error" => $e->getMessage(),
      ];
    }

    return $statistics;
  }

}
