<?php
class JobsMapper extends Mapper
{
    const TBL = 'jobs';
    private $entityClass = 'JobsEntity'; # TODO: change to const

    protected $sql_get_job_by_id = "SELECT t.id, t.job_title, t.company, t.place, j.job_type, e.experience, c.category, t.job_description, t.website, t.salary_text, t.salary_mon_min, t.salary_mon_max, t.experience_id, t.job_type_id, t.category_id, t.owener_id, t.visible, t.verify, t.is_trash,t.date_expired,t.date_post, u.name, u.email,t.logo_path,t.logo_url,t.company_depart,t.more_notify_emails
    from jobs t
    left join category c on (c.id = t.category_id)
    left join job_type j on ( j.id = t.job_type_id)
    left join experience e on ( e.id = t.experience_id)
    left join users u on (u.id = t.owener_id)
    where t.id = :job_id";

    public function getWait4VerifyJobs($is_exclude_trash=true){
        $sql = "SELECT *,jobs.id AS job_id FROM jobs
         LEFT JOIN users ON owener_id=users.id
         LEFT JOIN category ON jobs.category_id=category.id
         LEFT JOIN job_type ON jobs.job_type_id=job_type.id WHERE `verify`=0".($is_exclude_trash ? ' AND `is_trash`=0' : '')." AND `date_expired`>='".date('Y-m-d 00:00:00')."' ORDER BY date_expired ASC";
        #if(isset($_GET['wq'])){echo$sql;}
        return $this->_findAll($sql);
    }

    public function getJobs($where='', $orderby='') {
        $sql = "SELECT j.id, j.job_title, j.company, j.place, t.job_type, j.visible, j.verify
            from jobs j
            join job_type t on (t.id = j.job_type_id) " . ($where ? ' WHERE '.$where : '') . ($orderby ? ' ORDER BY '.$orderby : '');
        return $this->_findAll($sql, $this->entityClass);
    }

    public function getJobsByIds($ary_ids){
      $sqlwhere = $ary_ids ? ' WHERE j.id IN ('.implode(',', $ary_ids).')' : ' WHERE j.id=0';
      $sqlwhere.=' AND j.is_trash=0';
      $sql = "SELECT j.id
      , j.logo_path
      , j.submit_count
      , j.job_title
      , j.company
      , j.company_depart
      , j.place
      , t.job_type
      , j.visible
      , j.verify
      ,j.date_mod
      ,j.date_post
      ,j.date_expired
      ,j.more_notify_emails
      , u.name
      , u.email
            from jobs j
            join job_type t on (t.id = j.job_type_id)
            join users u on (u.id = j.owener_id)" . $sqlwhere;

      return $this->_findAll($sql, $this->entityClass);
    }

    public function getJobsbyOwner($owener_id){
        $sql = "SELECT j.id
        , j.logo_path
        , j.submit_count
        , j.job_title
        , j.company
        , j.company_depart
        , j.place
        , t.job_type
        , j.visible
        , j.verify
        ,j.date_mod
        ,j.date_post
        ,j.more_notify_emails
        , u.name
        , u.email
              from jobs j
              join job_type t on (t.id = j.job_type_id)
              join users u on (u.id = j.owener_id)
              where j.is_trash=0 AND j.owener_id= ".$owener_id
              ." ORDER BY j.visible DESC";

        // $sql = "SELECT j.id, j.job_title, j.company, j.place, t.job_type, j.visible, j.verify
        //           from jobs j
        //           join job_type t on (t.id = j.job_type_id) 
        //           where j.owener_id= ".$owener_id;
        return $this->_findAll($sql, $this->entityClass);
    }
    public function getJobById($job_id) {
        $sql = $this->sql_get_job_by_id;
        $r = $this->_getOne($sql, ["job_id" => $job_id]);
        $out = [];
        if($r) {
            $out[] = new JobsEntity($r);
        }else{
            $out = array('error'=>'此 job 不存在或是已下架');
        }
        return $out;
    } 
    public function getSingleJobById($job_id){
      $sql = $this->sql_get_job_by_id;
      return $this->_getOne($sql, ['job_id'=>$job_id], $this->entityClass);
    }
    public function getJobByIdLimitVisible($job_id, $is_auto_update_browse=true) {
        $sql = "SELECT t.id, t.job_title, t.company, t.place, j.job_type, e.experience, c.category, t.job_description, t.website, t.salary_text, t.salary_mon_min, t.salary_mon_max, t.experience_id, t.job_type_id, t.category_id, t.owener_id, t.visible, t.verify,t.date_expired,t.date_post, u.name, u.email,t.logo_path,t.logo_url,t.company_depart,t.browse_count,t.submit_count
            from jobs t
            left join category c on (c.id = t.category_id)
            left join job_type j on ( j.id = t.job_type_id)
            left join experience e on ( e.id = t.experience_id)
            left join users u on (u.id = t.owener_id)
            where t.id = :job_id and t.date_expired > ".date("Y-m-d");
        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute(["job_id" => $job_id]);
        //print_r($stmt->fetch());
		$user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : '';
        $user_mapper = new UserMapper($this->db);
        $user_fetch = $user_mapper->is_admin($user_id);
        $BROWSE_SESSION_KEY = "viewjob{$job_id}";
        $set_session = false;
        if (!isset($_SESSION[$BROWSE_SESSION_KEY])) {
            $set_session = true;
        } else {
            $browse_minus_time = time() - $_SESSION[$BROWSE_SESSION_KEY];
            if ($browse_minus_time > BROWSE_COUNDT_BURING_TIME) {
                $set_session = true;
            }
        }

        $r = $stmt->fetch();
		$is_admin = !!$user_fetch;
        $is_owner = $r && isset($r['owener_id']) && ($user_id == $r['owener_id']);
        if (!$is_admin && !$is_owner && $set_session) {
          if($is_auto_update_browse){
            $this->save_job_browse($job_id);
            $this->updateBrowseCount($job_id);
            $_SESSION[$BROWSE_SESSION_KEY] = time();
          }
        }
		$out = [];
        if ($r && ($is_admin or $is_owner)) {
            $out[] = new JobsEntity($r);
        } elseif ($r && $r['visible'] && $r['verify']) {
            $out[] = new JobsEntity($r);
        } else {
          $out = array('error'=>'此 job 不存在或是已下架');
		}
		
		return $out;
    } 
    public function applyCount($job_id){
        $sql = "SELECT * FROM application WHERE job_id = ".$job_id;
        $stmt = $this->db->query($sql);
        return $stmt->rowCount();
    }
    public function getAllApplyApplicationByJobId($job_id){
        $sql = "SELECT * FROM application WHERE job_id = ".$job_id. ' ORDER BY send_time DESC';
        return $this->_findAll($sql);
    }

    public function save_job_browse($job_id) {
      $sql = "INSERT INTO `job_browse` (`job_id`,`user_id`,`datetime`,`ip`,`ua`,`refer`) VALUES (:job_id, :user_id, :datetime, :ip, :ua, :refer);";
      $stmt = $this->db->prepare($sql);
      $user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : 0;
      $result = $stmt->execute([
        'job_id'=>$job_id,
        'user_id'=>$user_id,
        'datetime'=>date('Y-m-d H:i:s'),
        'ip'=>$_SERVER['REMOTE_ADDR'],
        'ua'=>isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '',
        'refer'=>isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '',
      ]);
      return $this->db->lastInsertId();
    }

    protected function _valid_save_update($jobs){
        // valid start
        $ary_fields_which_are_varchar_and_inputed_by_user = array(
                 'job_title'=>['工作職稱',$jobs->getJobTitle()],
                 'company'=>['公司名稱',$jobs->getCompany()], 
                 'company_depart'=>['部門名稱',$jobs->getCompanyDepart()],
                 'salary_text'=>['薪資',$jobs->get_salary_text()],
                 'place'=>['公司地點',$jobs->getPlace()],
                 'logo_url'=>['Logo網址',$jobs->getLogoUrl()],
        );
        parent::_valid_save_update_base(self::TBL, $ary_fields_which_are_varchar_and_inputed_by_user);
        // valid end
    }
    public function save(JobsEntity $jobs) {
           $this->_valid_save_update($jobs);
           $sql = "INSERT INTO jobs
            (job_title, company, place,job_type_id,experience_id,category_id,job_description,website,salary_text,salary_mon_min,salary_mon_max,owener_id,date_expired,visible,verify,date_post,logo_path,logo_url,company_depart,more_notify_emails) VALUES
            (:job_title, :company, :place,:job_type_id,:experience_id,:category_id,:job_description,:website,:salary_text,:salary_mon_min,:salary_mon_max,:owener_id,:date_expired,:visible,:verify,:date_post,:logo_path,:logo_url,:company_depart, :more_notify_emails)";

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            "job_title" => $jobs->getJobTitle(),
            "company" => $jobs->getCompany(),
            "place" => $jobs->getPlace(),
            "job_type_id" => $jobs->getJobType(),
            "experience_id" => $jobs->getExperience(),
            "category_id" => $jobs->getCategory(),
            "job_description" => $jobs->getJobDescription(),
            "website" => $jobs->getWebsite(),
            "salary_text" => $jobs->get_salary_text(),
            "salary_mon_min" => $jobs->get_salary_mon_min(),
            "salary_mon_max" => $jobs->get_salary_mon_max(),
            // "salary_year_max" => $jobs->get_salary_year_max(),
            "owener_id" => $jobs->get_owener_id(),
            "date_expired" => $jobs->getDateExpired(),
            "visible" => $jobs->getVisible(),
            "verify" => $jobs->getVerify(),
            "date_post" => $jobs->getDatePost(),
            "logo_path" => $jobs->getLogoPath(),
            "logo_url" => $jobs->getLogoUrl(),
            "company_depart" => $jobs->getCompanyDepart(),
            "more_notify_emails" => $jobs->getMoreNotifyEmails(),
        ]);
        return $this->db->lastInsertId();
    }
    public function update_status(JobsEntity $jobs) {
        $sql = "update jobs set 
             visible = :visible where id=".$jobs->getId();

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            "visible" => $jobs->getVisible()
        ]);
        // print_r($result);
        if(!$result) {
            throw new Exception("could not update visible record");
        }else{
            return array('success'=>'成功修改');
        }
    }
    public function set_verify(JobsEntity $jobs, $is_verify, $need_depends_on_trash){
        if($need_depends_on_trash){
            $jobs = $this->getJobById($jobs->getId())[0];
            if($jobs->getIsTrash()){
                return array('error'=>'在回收桶的職缺不能更新審核');
            }
        }
        $sql = "UPDATE jobs SET 
             verify = :verify WHERE id=".$jobs->getId();

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            "verify" => $is_verify ? 1 : 0
        ]);
        // print_r($result);
        if(!$result) {
            throw new Exception("could not set verify record");
        }else{
            return array('success'=>'成功修改審核狀態');
        }
    }
    public function set_visible(JobsEntity $jobs, $is_show, $need_depends_on_verify){
        if($need_depends_on_verify){
            $jobs = $this->getJobById($jobs->getId())[0];
            if(!$jobs->getVerify()){
                return array('error'=>'請確定審核通過，再更新上/下架');
            }
        }
        $sql = "UPDATE jobs SET 
             visible = :visible WHERE id=".$jobs->getId();

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            "visible" => $is_show ? 1 : 0
        ]);
        // print_r($result);
        if(!$result) {
            throw new Exception("could not set visible record");
        }else{
            return array('success'=>'成功修改上/下架狀態');
        }
    }
    public function set_trash(JobsEntity $jobs){
        $sql = "UPDATE jobs SET 
             is_trash = :is_trash WHERE id=".$jobs->getId();

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            "is_trash" => 1
        ]);
        // print_r($result);
        if(!$result) {
            throw new Exception("could not set trash record");
        }else{
            return array('success'=>'成功放入回收桶');
        }
    }
    public function update(JobsEntity $jobs, $_this_session, $is_admin_update=false) {
        $this->_valid_save_update($jobs);
        // print_r($jobs);exit();
        function_when_job_add_or_update('_UPDATE_', $this, $this->db, $_this_session,
            array('jobs'=>$jobs, 'changed_result'=>$this->is_changed($jobs))
            );

        $sql = "update jobs set date_mod=:date_mod, verify = :verify, visible = :visible, job_title = :job_title, company = :company, place= :place,job_type_id= :job_type_id,experience_id= :experience_id,category_id= :category_id,job_description= :job_description,website= :website,salary_text= :salary_text, salary_mon_min= :salary_mon_min,salary_mon_max= :salary_mon_max, date_expired=:date_expired, logo_path=:logo_path, logo_url=:logo_url, company_depart=:company_depart{extra} where id=".$jobs->getId();
        $s = $is_admin_update ? '' : ', more_notify_emails=:more_notify_emails';
        $sql = str_replace('{extra}', $s, $sql);

        $stmt = $this->db->prepare($sql);
        $ary_update = [
          "job_title" => $jobs->getJobTitle(),
          "company" => $jobs->getCompany(),
          "place" => $jobs->getPlace(),
          "job_type_id" => $jobs->getJobType(),
          "experience_id" => $jobs->getExperience(),
          "category_id" => $jobs->getCategory(),
          "job_description" => $jobs->getJobDescription(),
          "website" => $jobs->getWebsite(),
          "salary_text" => $jobs->get_salary_text(),
          "salary_mon_min" => $jobs->get_salary_mon_min(),
          "salary_mon_max" => $jobs->get_salary_mon_max(),
          // "salary_year_max" => $jobs->get_salary_year_max(),
          "visible" => $jobs->getVisible(),
          "date_mod" => date('Y-m-d H:i:s'),
          "verify" => $jobs->getVerify(),
          "date_expired" => $jobs->getDateExpired(),
          "logo_path" => $jobs->getLogoPath(),
          "logo_url" => $jobs->getLogoUrl(),
          "company_depart" => $jobs->getCompanyDepart(),
        ];
        if(!$is_admin_update){
          $ary_update["more_notify_emails"] = $jobs->getMoreNotifyEmails();
        }
        $result = $stmt->execute($ary_update);

        if(!$result) {
            throw new Exception("could not save record");
        }
    }

    private function normalize_date_value($date){
        return str_replace(array('/', '-', '.'), '', (string)$date);
    }

    private function is_changed($jobs) {
        $bool_changed = false;        
        $content = '';        
        $job_id = $jobs->getId();
        $sql = "SELECT t.id, t.job_title, t.company, t.place, j.job_type, e.experience, c.category, t.job_description, t.website, t.salary_text, t.salary_mon_min, t.salary_mon_max, t.experience_id, t.job_type_id, t.category_id, t.owener_id, t.visible, t.verify,t.date_expired,t.date_post, u.name, u.email,t.logo_path,t.logo_url,t.company_depart
            from jobs t
            left join category c on (c.id = t.category_id)
            left join job_type j on ( j.id = t.job_type_id)
            left join experience e on ( e.id = t.experience_id)
            left join users u on (u.id = t.owener_id)
            where t.id = :job_id";
        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute(["job_id" => $job_id]);

        $datas = $stmt->fetch();
        
        if ($datas['job_title'] != $jobs->getJobTitle()) {
            $bool_changed = true;        
            $content .= "工作職稱編緝後是 {$jobs->getJobTitle()} 編緝前是 {$datas['job_title']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['company'] != $jobs->getCompany()) {

            $bool_changed = true;        
            $content .= "公司名稱編緝後是 {$jobs->getCompany()} 編緝前是 {$datas['company']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['place'] != $jobs->getPlace()) {

            $bool_changed = true;        
            $content .= "工作地點編緝後是 {$jobs->getPlace()} 編緝前是 {$datas['place']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['job_type_id'] != $jobs->getJobType()) {

            $bool_changed = true;        
            $content .= "工作類型編緝後是 {$jobs->getJobType()} 編緝前是 {$datas['job_type_id']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['experience_id'] != $jobs->getExperience()) {
            $bool_changed = true;        
            $content .= "經驗需求編緝後是 {$jobs->getExperience()} 編緝前是 {$datas['experience_id']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['category_id'] != $jobs->getCategory()) {
            $bool_changed = true;        
            $content .= "職務分類編緝後是 {$jobs->getCategory()} 編緝前是 {$datas['category_id']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['job_description'] != $jobs->getJobDescription()) {
            $bool_changed = true;        
            $content .= "工作內容描述編緝後是 {$jobs->getJobDescription()} 編緝前是 {$datas['job_description']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['website'] != $jobs->getWebsite()) {
            $bool_changed = true;        
            $content .= "網址標題編緝後是 {$jobs->getWebsite()} 編緝前是 {$datas['website']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['salary_text'] != $jobs->get_salary_text()) {
            $bool_changed = true;        
            $content .= "薪資欄位編緝後是 {$jobs->get_salary_text()} 編緝前是 {$datas['salary_text']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['salary_mon_min'] != $jobs->get_salary_mon_min()) {
            $bool_changed = true;        
            $content .= "最低薪資編緝後是 {$jobs->get_salary_mon_min()} 編緝前是 {$datas['salary_mon_min']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['salary_mon_max'] != $jobs->get_salary_mon_max()) {
            $bool_changed = true;        
            $content .= "最高薪資編緝後是 {$jobs->get_salary_mon_max()} 編緝前是 {$datas['salary_mon_max']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($this->normalize_date_value($datas['date_expired']) != $this->normalize_date_value($jobs->getDateExpired())) {
            $bool_changed = true;        
            $content .= "有效期限編緝後是 {$jobs->getDateExpired()} 編緝前是 {$datas['date_expired']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['logo_path'] != $jobs->getLogoPath()) {
            $bool_changed = true;        
            $content .= "logo路徑編緝後是 {$jobs->getLogoPath()} 編緝前是 {$datas['logo_path']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['logo_url'] != $jobs->getLogoUrl()) {
            $bool_changed = true;        
            $content .= "logo url編緝後是 {$jobs->getLogoUrl()} 編緝前是 {$datas['logo_url']}" . JOB_MODIFICATIONS_SPLIT;
        }
        if ($datas['company_depart'] != $jobs->getCompanyDepart()) {
            $bool_changed = true;        
            $content .= "工作部門編緝後是 {$jobs->getCompanyDepart()} 編緝前是 {$datas['company_depart']}" . JOB_MODIFICATIONS_SPLIT;
        }
        $changed_result = array(
            'is_changed' => $bool_changed,
            'content' => $content,
        );
        return $changed_result;
    }
    public function getOwnerbyJobId($job_id){
        $sql = "SELECT u.name, u.email
              from jobs j
              join users u on (u.id = j.owener_id)
              where j.id = ".$job_id;
        
        $stmt = $this->db->query($sql);

        $results = [];
        while($row = $stmt->fetch()) {
            return $row;
            // $results[] = new JobsEntity($row);
        }
        // return $results;
    }

    public function updateBrowseCount($job_id) {
        $sql = "UPDATE jobs SET
            browse_count = browse_count + 1  WHERE id=".$job_id;

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute();
    }

    public function updateSubmitCount($job_id) {
        $sql = "UPDATE jobs SET
            submit_count = submit_count + 1  WHERE id=".$job_id;

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute();
    }

    public function getExpiredJobs() {
      #$sql = "UPDATE jobs SET
      #    `visible`=0  WHERE date_expired<='".date('Y-m-d')."'";
      $today = date('Y-m-d');
      $sql = 'SELECT * FROM jobs WHERE date_expired<"'.$today.'"';
      $jobs = $this->_findAll($sql, $this->entityClass);
      return$jobs;
    }
    public function setJobsAsInvisible($ary_ids){
      $sql = "UPDATE jobs SET
            `visible`=0  WHERE id IN (".implode(',',$ary_ids).")";
      $stmt = $this->db->prepare($sql);
      $result = $stmt->execute();
    }
    public function setExpiredJobAsInvisible(){
      $jobs = $this->getExpiredJobs();
      $ary_ids = array();
      foreach($jobs as$v){ $ary_ids[] = $v->getId(); }
      #
      $this->setJobsAsInvisible($ary_ids);
    }

    public function getReadyExpiredJobs($ready_day_num=3){
        $target_date_expired = date('Y-m-d',strtotime('+'.$ready_day_num.' days'));
        $sql = 'SELECT * FROM jobs WHERE date_expired<="'.$target_date_expired.'" AND date_expired>="'.date('Y-m-d').'"';
        $sql.=' AND visible=1 '; # only notify ready-expired jobs which are still visible=1. (decide by SWC)
        return $this->_findAll($sql, $this->entityClass);
    }
    public function getRecentlyExpiredJobs($recently_day_num=1){
        $target_date_expired = date('Y-m-d',strtotime('-'.$recently_day_num.' days'));
        $sql = 'SELECT id,job_title, date_post, owener_id, date_expired FROM jobs WHERE date_expired>="'.$target_date_expired.'" AND date_expired<"'.date('Y-m-d').'"';
        return $this->_findAll($sql, $this->entityClass);
    }

    public function updateSysAutoEmailNotify4Expired($job_id){
        $job_id += 0;
        $sql = "UPDATE `jobs` SET date_sysautoemailnotify4expired='"
                .date('Y-m-d H:i:s')."' WHERE id=".$job_id." LIMIT 1";

        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute();
    }

}