<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Advice extends Admin_Controller
{

    const CSV_FIELDS = [
        'travel_date', // | 2019-11-19 08:58:00
        'duration', // | 26
        'cost_raw', // | SGD 13.00
        'cost', // | 13.00
        'updated', // | 2019-11-19 03:53:48.809183
        'distance', // | 10.77
        'transport_provider_id', // | 3
        'travel_date_end', // | 2019-11-19 09:24:55
        'location_start_id', // | 7276
        'location_end_id', // | 7277
        'location_start_address', // | 65 Jurong West Central 3, Singapore, 648332
        'location_start_lat', // | 1.339345
        'location_start_lng', // | 103.705984
        'location_start_postal', // |
        'location_start_country', // | SG
        'location_start_description', // |
        'location_start_tz', // | Asia/Singapore
        'location_end_address', // | 3 Engineering Drive 3, Singapore, 117582
        'location_end_lat', // | 1.299255
        'location_end_lng', // | 103.772206
        'location_end_postal', // |
        'location_end_country', // | SG
        'location_end_description', // |
        'location_end_tz' // | Asia/Singapore
    ];

    public function __construct()
    {
        parent::__construct();

        $this->load->model('advice_model');
        $this->load->model('receipt_advice_model');

        //  Load form validation library
        $this->load->library('form_validation');

        //  Load file helper
        $this->load->helper('file');
    }

    public function index()
    {
        $data = array();
        $this->load->helper('url');
        $this->advicelist();
    }

    protected function renderAdvicePage($page_name, $data)
    {
        $this->load->view('admin/view_admin_header', $data);
        $this->load->view('advice/' . $page_name, $data);
        $this->load->view('admin/view_admin_footer', $data);
    }

    public function advicelist()
    {
        $data = [];
        $data["page_title"] = "Advice List";
        $data['advice_list'] = [];
        $data['pagination_link'] = [];

        $params = [];
        $params = $this->input->get();

        if (!empty($params)) {
            $this->form_validation->set_data($params);
            $this->setSearchRules();
            if ($this->form_validation->run() == false) {
                $this->renderAdvicePage("view_advice", $data);
                return;
            }
        }

        $query = $this->advice_model->getAdviceQuery($params);
        $tableData = $this->returnAdminTable(
            [
                'count_query' => $query,
                'query' => $query,
            ],
            'advice/advicelist',
            [
                'per_page' => 100,
                'reuse_query_string' => TRUE,
            ]
        );

        $data['advice_list'] = $tableData['output_table'];
        $data['pagination_link'] = $tableData['links'];
        $this->renderAdvicePage("view_advice", $data);
    }

    public function advicejson()
    {
        $id = $this->input->get('id');
        if ($id == '' || $id < 1) {
            echo '{"message":"Invalid ID"}';
            return;
        }
        $q = "SELECT * FROM parsedemail_item WHERE id=" . $id;
        $r = $this->read_replica->query($q);
        $parsedemail_item = $r->row_array();

        $q = "SELECT * FROM parsedemail_item_advice_google WHERE parsedemail_item_id = ${id}";
        $r = $this->read_replica->query($q);
        $advices = [];
        foreach ($r->result() as $f) {
            $advices[] = (array) $f;
        }
        foreach ($advices as $j => $advice) {
            $q = "SELECT * FROM google_directions_legs WHERE parsedemail_item_advice_google_id = " . $advice["id"];
            $r = $this->read_replica->query($q);
            $legs = [];
            foreach ($r->result() as $f) {
                $legs[] = (array) $f;
            }
            foreach ($legs as $i => $leg) {
                $q = "SELECT a.id AS step_id,a.distance,a.duration,a.travel_mode,a.location_start_lat,a.location_start_lng,a.location_end_lat,a.location_end_lng,a.html_instructions,a.polyline,b.* ";
                $q .= ",c.name,c.service,c.board,c.alight,c.distance AS quote_distance,c.fare,c.fare_raw,c.distance_raw";
                $q .= " FROM google_directions_leg_steps a LEFT JOIN google_directions_leg_step_details b ON (b.google_directions_leg_step_id=a.id) ";
                $q .= " LEFT JOIN leg_step_quote c ON c.google_directions_leg_step_id=a.id ";
                $q .= " WHERE a.google_directions_leg_id=" . $leg['id'];
                $r = $this->read_replica->query($q);
                $steps = [];
                foreach ($r->result() as $f) {
                    $data = (array) $f;
                    foreach ($data as $key => $val) {
                        if ($val == NULL || $val == "") {
                            unset($data[$key]);
                        }
                    }
                    $steps[] = $data;
                }
                $leg['steps'] = $steps;
                $legs[$i] = $leg;
            }
            $advice["legs"] = $legs;
            $advices[$j] = $advice;
        }
        $parsedemail_item["advices"] = $advices;

        echo json_encode($parsedemail_item);
        /* echo '{
            "id": 224583,
            "location_start": "97 Meyer Rd, 93, Singapore 437918",
            "location_end": "1 Fusionopolis Way, #01-07 & #02-14, Connexis, Singapore 138632",
            "cost_raw": "14.6",
            "trackedemail_item_id": 0,
            "cost": "14.60",
            "transport_provider_id": 4,
            "location_start_lat": "1.2970576",
            "location_start_lng": "103.8925856",
            "location_end_lat": "1.2987049",
            "location_end_lng": "103.7875699",
            "request_date": "2020-01-09T13:09:00.955Z",
            "started": "2020-01-09T13:09:04.203Z",
            "complete": "2020-01-09T13:09:04.743Z",
            "status": 0,
            "message": "android_automation_job_detail",
            "attempts": 0,
            "automation_id": 206441,
            "completed": "2020-01-09T13:09:04.743Z",
            "created": "2020-01-09 13:09:01.072305",
            "location_start_id": "4426",
            "location_end_id": "4386",
            "member_id": "13",
            "deeplink": "ComfortDelGroTaxi:\/\/\/?action=setBooking&endingLat=1.298705&endingLong=103.787570&startingLat=1.297085&startingLong=103.892571"
        }'; // */
    }

    private function setSearchRules()
    {
        $config = [
            [
                'field' => 'travel_date',
                'label' => 'Travel date',
                'rules' => 'trim',
            ],
            [
                'field' => 'duration_from',
                'label' => 'Duration from',
                'rules' => 'trim|numeric',
            ],
            [
                'field' => 'duration_to',
                'label' => 'Duration to',
                'rules' => 'trim|numeric',
            ],
            [
                'field' => 'distance_from',
                'label' => 'Distance from',
                'rules' => 'trim|numeric',
            ],
            [
                'field' => 'distance_to',
                'label' => 'Distance to',
                'rules' => 'trim|numeric',
            ],
            [
                'field' => 'location_start_id',
                'label' => 'Location start',
                'rules' => 'integer',
            ],
            [
                'field' => 'location_start_name',
                'label' => 'Location start name',
                'rules' => 'trim',
            ],
            [
                'field' => 'location_end_id',
                'label' => 'Location end',
                'rules' => 'integer',
            ],
            [
                'field' => 'location_end_name',
                'label' => 'Location end name',
                'rules' => 'trim',
            ],
        ];

        $this->form_validation->set_rules($config);
    }

    public function exportCSV()
    {
        $this->load->helper('export_csv');

        $data = [];
        $data["page_title"] = "Advice List";
        $data['advice_list'] = [];
        $data['pagination_link'] = [];

        $params = [];
        $params = $this->input->get();

        if (!empty($params)) {
            $this->form_validation->set_data($params);
            $this->setSearchRules();
            if ($this->form_validation->run() == false) {
                $this->renderAdvicePage("view_advice", $data);
                return;
            }
        }

        $params = array_filter($params, function ($val) {
            return $val !== '';
        });

        $query = $this->advice_model->getAdviceQuery($params);
        $tableData = $this->returnAdminTable(
            [
                'count_query' => $query,
                'query' => $query,
            ],
            'advice/advicelist',
            [
                'per_page' => 100,
                'reuse_query_string' => TRUE,
            ]
        );

        $data['advice_list'] = $tableData['output_table'];
        $data['pagination_link'] = $tableData['links'];

        $query = $this->advice_model->get_query_parsed_email_quote_records($params);

        $r = $this->read_replica->query($query);

        if ($r->result_array()[0]['all_count'] === '0') {
            $this->session->set_flashdata('error', 'Data not found !!!');
            $this->renderAdvicePage("view_advice", $data);
            return;
        }

        $this->read_replica->save_queries = false;
        $query = $this->advice_model->get_query_parsed_email_quote_records(
            $params,
            self::NONE_COUNT_RECORD
        );
        $r = $this->read_replica->query($query);
        export($r);
    }

    public function upload()
    {
        $this->load->helper('upload_file');
        upload_file();
    }

    public function importCSV()
    {
        $this->load->helper('directory');
        $row = 0;

        $file_path = $_SERVER['DOCUMENT_ROOT']
                    . DIRECTORY_SEPARATOR
                    . self::TEMP_DIRECTORY_UPLOAD;
        $file_name = scandir($file_path);
        if ( ! $file_name) {
            echo json_encode([
                'code' => '404',
                'message' => 'File Not Found !!!'
            ]);
        }

        if (($handle = fopen($file_path . '/' . $file_name[2], "r")) !== FALSE) {
            deleteDir($file_path);

            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $num = count($data);
                $f = [];
                for ($c=0; $c < $num; $c++) {
                    $f[self::CSV_FIELDS[$c]] = $data[$c];
                }
                if ($row>0) {
                    $this->save_record($f);
                }
                $row++;
            }
            fclose($handle);
        }

        echo json_encode([
            'code' => '201',
            'message' => 'Imported Successfully !!!'
        ]);
    }

    function save_record($f)
    {
        $member_id = $this->input->post()['member_id'];
        $location_start_id = $this->get_address($f, 'start');
        $location_end_id = $this->get_address($f, 'end');
        if ($location_start_id < 1 || $location_end_id < 1) {
            echo "Invalid address\n";
            return NULL;
        }
        $q = "INSERT INTO trackedemail_item (member_id) VALUES(${member_id}) RETURNING id";
        $trackedemail_item_id = NULL;
        $r = $this->db->query($q);
        if (isset($r->result_array()[0]['id'])) {
            $trackedemail_item_id = $r->result_array()[0]['id'];
        } else {
            return NULL;
        }

        $q = "INSERT INTO parsedemail_item (travel_date,duration,cost_raw,trackedemail_item_id,cost,updated,distance,transport_provider_id,travel_date_end,location_start_id,location_end_id) VALUES (";
        $q .= "'" . pg_escape_string($f["travel_date"]) . "',"; // | 2019-11-19 08:58:00
        $q .= $f['duration'] == '' ? "NULL," : (((int) $f["duration"]) . ","); // | 26
        $q .= "'" . pg_escape_string($f["cost_raw"]) . "',"; // | SGD 13.00
        $q .= $trackedemail_item_id == NULL ? "NULL," : (((int) $trackedemail_item_id) . ",");
        $q .= $f['cost'] == '' ? "NULL," : (floatval($f["cost"]) . ","); // | 13.00
        $q .= $f['updated'] == '' ? "NULL," : ("'" . pg_escape_string($f["travel_date"]) . "',"); // | 2019-11-19 03:53:48.809183
        $q .= $f['distance'] == '' ? "NULL," : (floatval($f["distance"]) . ","); // | 10.77
        $q .= $f['transport_provider_id'] == '' ? "NULL," : (((int) $f["transport_provider_id"]) . ","); // | 3
        $q .= $f['travel_date_end'] == '' ? "NULL," : ("'" . pg_escape_string($f["travel_date_end"]) . "',"); // | 2019-11-19 09:24:55
        $q .= $location_start_id . "," . $location_end_id;
        $q .= ") RETURNING id";
        $r = $this->db->query($q)->result_array();
        if (count($r) === 0) {
            echo $q . "\n";
            if ($trackedemail_item_id > 0) {
                $q = "DELETE FROM trackedemail_item WHERE member_id = ${member_id} AND id = ${trackedemail_item_id}";
                $this->db->query($q);
            }
            return NULL;
        }
    }

    private function get_address($f, $what)
    {
        $tzid = $this->get_timezone($f, $what);
        $q = "SELECT * FROM address WHERE lower(address)=lower('" . pg_escape_string($f["location_${what}_address"]) . "') and latitude=";
        $q .= floatval($f["location_${what}_lat"]) . " AND longitude=" . floatval($f["location_${what}_lng"]);
        $r = $this->read_replica->query($q)->result_array();
        if (count($r)) {
            return $r[0]['id'];
        } else {
            echo $q . "\n";
        }

        $q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry,description) VALUES(";
        $q .= "'" . pg_escape_string($f["location_${what}_address"]) . "',"; // | 65 Jurong West Central 3, Singapore, 648332
        $q .= floatval($f["location_${what}_lat"]) . ","; // | 1.339345
        $q .= floatval($f["location_${what}_lng"]) . ","; // | 103.705984
        $q .= $tzid . ",NOW(),"; // |
        $q .= "'" . pg_escape_string($f["location_${what}_postal"]) . "',"; //
        $q .= "'" . pg_escape_string($f["location_${what}_country"]) . "',"; // | SG
        $q .= "st_setsrid(st_point(" . $f["location_${what}_lng"] . "," . $f["location_${what}_lat"] . "),4326),";
        if ($f["location_${what}_description"] == '') {
            $q .= "NULL";
        } else {
            $q .= "'" . pg_escape_string($f["location_${what}_description"]) . "'"; // |
        }
        $q .= ") RETURNING id";
        $r = $this->db->query($q)->result_array();
        if (count($r)) {
            return $r[0]['id'];
        } else {
            echo $q . "\n";
        }
        return NULL;
    }

    private function get_timezone($f, $what)
    {
        $q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('" . pg_escape_string($f["location_${what}_tz"]) . "')";
        $r = $this->read_replica->query($q)->result_array();
        if (count($r)) {
            return $r[0]['id'];
        }
        $q = "INSERT INTO address_timezone (timezone) VALUES('" . pg_escape_string($f["location_${what}_tz"]) . "') RETURNING id";
        $r = $this->db->query($q)->result_array();
        if (count($r)) {
            return $r[0]['id'];
        }
        return NULL;
    }

    // Receipt Advice
    public function receiptadvice()
    {
        $data = [];
        $data["page_title"] = "Receipt Advice";
        $data['receipt_advice'] = [];
        $data['pagination_link'] = [];

        $params = [];
        $params = $this->input->get();

        if (!empty($params)) {
            $this->form_validation->set_data($params);
            $this->setSearchRules();
            if ($this->form_validation->run() == false) {
                $this->renderAdvicePage("view_receipt_advice", $data);
                return;
            }
        }

        $query = $this->receipt_advice_model->getAdviceQuery($params);
        $tableData = $this->returnAdminTable(
            [
                'count_query' => $query,
                'query' => $query,
            ],
            'advice/receiptadvice',
            [
                'per_page' => 100,
                'reuse_query_string' => TRUE,
            ]
        );

        $data['receipt_advice'] = $tableData['output_table'];
        $data['pagination_link'] = $tableData['links'];
        $this->renderAdvicePage("view_receipt_advice", $data);
    }

    public function exportCSV_receipt_advice()
    {
        $this->load->helper('export_csv');

        $data = [];
        $data["page_title"] = "Advice List";
        $data['receipt_advice'] = [];
        $data['pagination_link'] = [];

        $params = [];
        $params = $this->input->get();

        if (!empty($params)) {
            $this->form_validation->set_data($params);
            $this->setSearchRules();
            if ($this->form_validation->run() == false) {
                $this->renderAdvicePage("view_receipt_advice", $data);
                return;
            }
        }

        $params = array_filter($params, function ($val) {
            return $val !== '';
        });

        $query = $this->receipt_advice_model->getAdviceQuery($params);
        $tableData = $this->returnAdminTable(
            [
                'count_query' => $query,
                'query' => $query,
            ],
            'advice/receiptadvice',
            [
                'per_page' => 100,
                'reuse_query_string' => TRUE,
            ]
        );

        $data['receipt_advice'] = $tableData['output_table'];
        $data['pagination_link'] = $tableData['links'];

        $query = $this->receipt_advice_model->get_query_parsed_email_quote_records($params);

        $r = $this->read_replica->query($query);

        if ($r->result_array()[0]['all_count'] === '0') {
            $this->session->set_flashdata('error', 'Data not found !!!');
            $this->renderAdvicePage("view_receipt_advice", $data);
            return;
        }

        $this->read_replica->save_queries = false;
        $query = $this->receipt_advice_model->get_query_parsed_email_quote_records(
            $params,
            self::NONE_COUNT_RECORD
        );
        $r = $this->read_replica->query($query);
        export($r);
    }
}