<?php

class JobsMapper extends Mapper
{
    public function getJobs() {
        $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)";
        $stmt = $this->db->query($sql);

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

    public function getJobsbyOwner($owener_id){
        $sql = "SELECT j.id, j.job_title, j.company, j.company_depart, j.place, t.job_type, j.visible, j.verify,j.date_mod,j.date_post, 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.owener_id= ".$owener_id;
        
        // $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;
        $stmt = $this->db->query($sql);

        $results = [];
        while($row = $stmt->fetch()) {
            $results[] = new JobsEntity($row);
        }
        return $results;
    }
    public function getJobById($job_id) {
        $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.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]);
        // print_r($stmt->fetch());
        // 
        $out = [];
        if($r = $stmt->fetch()) {
            $out[] = new JobsEntity($r);
        }else{
            $out = array('error'=>'此 job 不存在或是已下架');
        }
        return $out;
    } 
    public function getJobByIdLimitVisible($job_id) {
        $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.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 and t.visible=1 and t.verify=1 and t.date_expired > ".date("Y-m-d");
        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute(["job_id" => $job_id]);
        // print_r($stmt->fetch());
        // 
        $out = [];
        if($r = $stmt->fetch()) {
            $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 save(JobsEntity $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,date_post,logo_path,company_depart) 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,:date_post,:logo_path,:company_depart)";

        $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(),
            "date_post" => $jobs->getDatePost(),
            "logo_path" => $jobs->getLogoPath(),
            "company_depart" => $jobs->getCompanyDepart()
        ]);

        if(!$result) {
            throw new Exception("could not save record");
        }
    }
    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 save record");
        }else{
            return array('success'=>'成功修改');
        }
    }
    public function update(JobsEntity $jobs) {
        $sql = "update jobs set 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_mon_min= :salary_mon_min,salary_mon_max= :salary_mon_max, date_expired=:date_expired, logo_path=:logo_path,company_depart=:company_depart where id=".$jobs->getId();

        $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_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(),
            "verify" => $jobs->getVerify(),
            "date_expired" => $jobs->getDateExpired(),
            "logo_path" => $jobs->getLogoPath(),
            "company_depart" => $jobs->getCompanyDepart()
        ]);

        if(!$result) {
            throw new Exception("could not save record");
        }
    }
    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;
    }
}