<?php

defined('BASEPATH') or exit('No direct script access allowed');
require __DIR__ . '../../../vendor/autoload.php';
require __DIR__ . '../../../CRONS/common/Logger.php';
class Tools extends Admin_Controller {

    const CSV_FIELDS = [
        'gac_id',
        'gac_city',
        'gac_country',
        'gac_latitude',
        'gac_longitude',
        'gac_location',
        'gac_radius',
        'gac_status',
        'gacs_id',
        'gacs_status',
        'gacs_geofence_area_city',
        'gacs_image_url',
        'gacs_is_fectched_data',
        'ta_id',
        'ta_attraction',
        'ta_active',
        'ta_address_id',
        'ta_city_id',
        'ta_feature_image',
        'ta_description',
        'a_address'
    ];

    public $viewData = array();

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

        $this->load->library('table');
        $this->table->set_template($this->template);
        $this->load->model('combo_model');

        $this->load->model('geofence_area_city_model');
        $this->load->model('tourist_attraction_model');
        $this->load->model('geofence_area_city_settings_model');

        $this->viewData['errMsg'] = null;
        $this->viewData['msg'] = null;
        $this->viewData['card_country_value'] = trim($this->input->post('card_country'));
        $this->viewData['card_country'] = $this->combo_model->getCountryCombo('card_country', $this->viewData['card_country_value']);
        $this->viewData['card_country_filter'] = $this->combo_model->getCountryCombo('card_country_filter', $this->input->get('card_country_filter'));
        $this->viewData['card_timezone'] = $this->combo_model->getTimezoneCombo('card_timezone');
        $this->viewData['card_neighborhood'] = $this->combo_model->getCountryNeighborhoodCombo('card_neighborhood', '', '', '');

        // Load Pagination library10
        $this->load->library('pagination');
    }

    public function index() {

    }

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

    public function neighborhood() {
        $this->viewData["page_title"] = "Neighborhood Settings";
        $this->viewData = array_merge($this->viewData, ['search_result' => []]);
        $this->renderToolsPage("view_neighborhood", $this->viewData);
    }

    // Fetch records
    public function getData($rowno, $rowperpage, $params = []) {
        $this->db->select('*, COALESCE(description, \'\') AS description');
        $this->db->from('address');

        if (!empty($params['address_id'])) {
            $this->db->where('id', $params['address_id']);
        }

        if (!empty($params['city_id'])) {
            $this->db->where('city_id', $params['city_id']);
        }

        if (!empty($params['latitude']) && !empty($params['longitude']) && !empty($params['radius'])) {
            $radius_search_query = "ST_DWithin(geometry, ST_SetSRID(ST_MakePoint({$params['longitude']}, {$params['latitude']}), 4326), {$params['radius']})";
            $this->db->where($radius_search_query);
        }

        if (!empty($params['country'])) {
            $this->db->where("lower(country) like lower('%" . pg_escape_string($params['country']) . "%')");
        }

        $this->db->limit($rowperpage, $rowno);
        $query = $this->db->get();

        return $query->result_array();
    }

    // Select total records
    public function getrecordCount($params = []) {

        $this->db->select('count(*) as allcount');
        $this->db->from('address');

        if (!empty($params['address_id'])) {
            $this->db->where('id', $params['address_id']);
        }

        if (!empty($params['city_id'])) {
            $this->db->where('city_id', $params['city_id']);
        }

        if (!empty($params['latitude']) && !empty($params['longitude']) && !empty($params['radius'])) {
            $radius_search_query = "ST_DWithin(geometry, ST_SetSRID(ST_MakePoint({$params['longitude']}, {$params['latitude']}), 4326), {$params['radius']})";
            $this->db->where($radius_search_query);
        }

        if (!empty($params['country'])) {
            $this->db->where("lower(country) like lower('%" . pg_escape_string($params['country']) . "%')");
        }

        $query = $this->db->get();
        $result = $query->result_array();

        return $result[0]['allcount'];
    }

    public function loadRecord() {
        $rowno = $this->input->get('rowno');
        $params = $this->input->get();

        // Row per page
        $rowperpage = 10;
        $cur_page = $rowno;

        // Row position
        if ($rowno != 0) {
            $rowno = ($rowno - 1) * $rowperpage;
        }

        // All records count
        $allcount = $this->getrecordCount($params);

        // Get records
        $users_record = $this->getData($rowno, $rowperpage, $params);

        // Pagination Configuration
        $config['base_url'] = '/tools/loadRecord';
        $config['use_page_numbers'] = TRUE;
        $config['total_rows'] = $allcount;
        $config['per_page'] = $rowperpage;
        $config['cur_page'] = $cur_page;

        // Initialize
        $this->pagination->initialize($config);

        // Initialize $data Array
        $data['pagination'] = $this->pagination->create_links();
        $data['result'] = $users_record;
        $data['row'] = $rowno;

        echo json_encode($data);
    }

    public function neighborhood_search() {
        $this->load->helper(array('form', 'url'));
        $this->load->library('form_validation');

        $params = $this->getFormValue();

        $query = "SELECT * FROM address
              WHERE country = '" . $params['country'] . "'
              AND lower(address) like '%" . pg_escape_string($params['address']) . "%'
              LIMIT 20";

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

        $this->viewData = array_merge($this->viewData, [
            'search_result' => $rs->result_array(),
        ]);

        $this->renderToolsPage("view_neighborhood", $this->viewData);
    }

    //==========================


    public function pointsofinterest() {
        //  id  | country |      description       |                        address                         |  latitude  |  longitude  |                      geometry
        //------+---------+------------------------+--------------------------------------------------------+------------+-------------+----------------------------------------------------
        // 1679 | US      | Olu Office Location GA | 3500 Riverwood Pkwy, Atlanta, GA 30339, USA            | 33.8799694 |  -84.457898 | 0101000020E6100000D8B969334E1D55C0893D59D6A2F04040
        // 1675 | US      | Olu Home Location GA   | 4201 Defoors Farm Trail, Powder Springs, GA 30127, USA | 33.8527901 | -84.7181367 | 0101000020E6100000AC23A2F3F52D55C021EDDA3928ED4040

        // if ($this->input->post()) {
        /*    $data = $this->countryImageUpload($data);
            $search_text = trim($this->input->post('search_text'));
            $search_text = $this->db->escape_like_str($search_text);
            $whereQuery .= ' AND a.country ILIKE \'%' . $search_text . '%\'';
            //$page =0;
         *
         */
        // }

        global $savvyext;

        $this->viewData['page_title'] = 'Points of Interest';

        $this->db->from('geofence_area_city');
        $this->db->order_by('city');
        $this->viewData['city_list'] = $this->db->get()->result();

        $this->viewData['storage'] = $savvyext->cfgReadChar('system.storage_url');

        $this->renderToolsPage("view_pointsofinterest", $this->viewData);
    }

    public function create() {
        $type = 'create';
        $this->createOrUpdate($type);
    }

    public function update() {
        $tourist_selected = $this->input->post('tourist_selected');
        if (!$tourist_selected) {
            redirect('/tools/pointsofinterest');
        }

        $type = 'update';
        $this->createOrUpdate($type);
    }

    public function countLocations() {
        if (!$this->input->is_ajax_request()) {
            exit('No direct script access allowed');
        }

        global $savvyext;
        $params = $this->input->get();
        list($results, $error) = $this->checkCloneDataStatusForCity($params['city_id']);
        if (isset($error)) {
            echo $error;
            return;
        }

        $allCount = $this->getRecordCountAddress(['city_id' => [$params['city_id']]]);
        echo json_encode(['count' => $allCount, 'fetch_status' => $results]);
    }

    public function createAJAX() {
        if (!$this->input->is_ajax_request()) {
            exit('No direct script access allowed');
        }

        global $savvyext;
        $type = 'create';
        $this->load->helper(array('form', 'url'));
        $this->load->database();
        $this->load->library('form_validation');
        $this->setFormRule();
        $params = $this->getFormValue();

        if ($this->form_validation->run() == false) {
            $errMsg = validation_errors();
            Logger::debug('Create data for local attraction error:');
            Logger::debug('Form validation fail');
            Logger::debug($errMsg);
            Logger::debug($params);
            echo json_encode(['success' => false, 'error' => $errMsg]);
            return;
        }


        $data = $this->geocode($params);
        if (!$data['geocode']) {
            Logger::debug('Create data for local attraction error:');
            Logger::debug('Form validation fail');
            Logger::debug('Address is invalid');
            Logger::debug($params);
            echo json_encode(['success' => false, 'error' => 'Address is invalid']);
            return;
        }

        $params = array_merge($params, [
            'address_id' => $data['geocode']['id'],
            'country' => $data['geocode']['country'],
            'active' => false
        ]);

        // update the description of address
        // $this->updateAddress($params);

        $createError = $this->insertOrUpdateTouristAttraction($type, $params);
        if (!$createError) {
            echo json_encode(['success' => true]);
        } else {
            echo json_encode(['success' => false, 'error' => $createError]);
        }
    }

    public function updateAJAX() {
        // Update status only for now
        if (!$this->input->is_ajax_request()) {
            exit('No direct script access allowed');
        }

        global $savvyext;
        $type = 'update';

        $id = $this->input->post('id');
        $active = $this->input->post('status');
        $params = [
            'active' => $active,
        ];
        if (!$id) {
            echo json_encode(['success' => false, 'error' => 'Missing id.']);
        }
        $this->db->where('id', $id);
        if (!$this->db->update('tourist_attraction', $params)) {
            $updateError = $this->db->error();
            echo json_encode(['success' => false, 'error' => $updateError]);
        } else {
            echo json_encode(['success' => true]);
        }
    }

    public function updateFetchStatus() {
        if (!$this->input->is_ajax_request()) {
            exit('No direct script access allowed');
        }

        global $savvyext;

        $params = $this->input->get();
        $this->updateFetchStatusOfCity($params['city_id']);
        echo json_encode(['success' => true]);
    }

    private function createOrUpdate($type) {
        global $savvyext;
        $this->load->helper(array('form', 'url'));
        $this->load->database();
        $this->load->library('form_validation');
        $this->setFormRule();
        $this->viewData['storage'] = $savvyext->cfgReadChar('system.storage_url');
        $this->viewData['city_list'] = $this->read_replica->get('geofence_area_city')->result();

        if ($this->form_validation->run() == false) {
            $this->viewData['errMsg'] = validation_errors();
            $this->renderToolsPage('view_pointsofinterest', $this->viewData);
            return;
        }

        $params = $this->getFormValue();

        $data = $this->geocode($params);
        if (!$data['geocode']) {
            $this->viewData['errMsg'] = 'Address is invalid';
            $this->renderToolsPage('view_pointsofinterest', $this->viewData);
            return;
        }

        $params = array_merge($params, [
            'address_id' => $data['geocode']['id'],
            'country' => $data['geocode']['country'],
            'active' => $params['active'] ?? false
        ]);

        // update the description of address
        // $this->updateAddress($params);

        $this->insertOrUpdateTouristAttraction($type, $params);

        $msg = "Tourist Attraction " . ($type == 'create' ? 'created.' : 'updated.');
        $this->session->set_flashdata('success', $msg);
        redirect('/tools/pointsofinterest');
    }

    private function setFormRule() {
        $this->form_validation->set_rules('card_country', 'Country', 'required');
        $this->form_validation->set_rules('address', 'Address', 'required|max_length[200]');
        $this->form_validation->set_rules('loc_name', 'Location', 'required|max_length[200]');
        $this->form_validation->set_rules('city_id', 'City', 'required|integer');
        $this->form_validation->set_rules('description', 'Description', 'callback_check_unique_description');
        $this->form_validation->set_rules('feature_image', 'Image', 'max_length[300]');
    }

    // Updated to use description of tourist_attractions table instead of description of address table.
    public function check_unique_description($description) {
        if (empty($description)) {
            return true;
        }
        $id = $this->input->post('tourist_selected');

        // for update
        if ($id) {
            /* get address id from tourist attraction id
            $this->db->where('id', $id);
            $query = $this->db->get('tourist_attraction');
            $result = $query->result();
            if (count($result) == 0) {
                return false;
            }
            $address_id = $result[0]->address_id;
            $this->db->where_not_in('id', $address_id);
            End get address id from tourist attraction id */

            // Search for other tourist_attraction.
            $this->read_replica->where_not_in('id', $id);
        }
        $this->read_replica->where('description', $description);
        // $result = $this->db->get('address')->num_rows();

        // Check for description in tourist_attraction table
        $result = $this->read_replica->get('tourist_attraction')->num_rows();

        if ($result == 0) {
            return true;
        } else {
            $this->form_validation->set_message('check_unique_description', 'Description must be unique');
            return false;
        }
    }

    private function getFormValue() {
        $location = $this->input->post('loc_name');
        $address = $this->input->post('address');
        $description = $this->input->post('description');
        $tourist_selected = $this->input->post('tourist_selected');
        $city_id = $this->input->post('city_id');
        $feature_image = $this->input->post('feature_image');
        $country = $this->input->post('card_country');
        $active = $this->input->post('status');

        return [
            'attraction' => $location,
            'address' => $address,
            'description' => $description,
            'tourist_selected' => $tourist_selected,
            'city_id' => $city_id,
            'feature_image' => $feature_image,
            'country' => $country,
            'active' => $active,
        ];
    }

    private function checkAddressExists($address_id) {
        $this->read_replica->select("count(*) as count");
        $this->read_replica->from("address");
        $this->read_replica->where("id", $address_id);

        $rs = $this->read_replica->get();
        return ($rs->row_array()['count'] > 0) ? true : false;
    }

    private function updateAddress($params) {
        $query = "UPDATE address
              SET description =  '" . $params['description'] . "'
              WHERE address like '" . pg_escape_string($params['address']) . "' RETURNING id";

        $rs = $this->db->query($query);
        return $rs->row_array()['id'];
    }

    private function insertOrUpdateTouristAttraction($type, $params) {
        $data = array(
            'attraction' => $params['attraction'],
            'active' => $params['active'],
            'address_id' => $params['address_id'],
            'city_id' => $params['city_id'],
            'feature_image' => $params['feature_image'],
            'description' => $params['description']
        );

        if ($type == 'create') {
            if (!$this->db->insert('tourist_attraction', $data)) {
                $createError = $this->db->error();
                Logger::debug('Create location attraction error:');
                Logger::debug($data);
                Logger::debug($createError);
                return $createError;
            } else {
                return null;
            };
        } else { // update
            $id = $params['tourist_selected'];
            $this->db->where('id', $id);
            $this->db->update('tourist_attraction', $data);
        }
    }

    public function destroy() {
        $this->load->helper(array('form', 'url'));

        $params = $this->getFormValue();
        $id = $params['tourist_selected'];
        $this->db->delete('tourist_attraction', ['id' => $id]);

        $this->session->set_flashdata('success', 'Tourist Attraction deleted.');
        redirect('/tools/pointsofinterest');
    }

    private function convertToGeometry($params) {
        $query = "";

        $query = "SELECT(ST_GeomFromText('"
                . "POINT(" . $params['latitude'] . " "
                . $params['longitude'] . ")', 4326)) AS geometry";

        $rs = $this->read_replica->query($query);
        return $rs->row_array()['geometry'];
    }

    public function setNeighborHood() {
        $data = array(
            'address_id' => $this->input->post('addressId'),
            'country' => $this->input->post('country'),
            'neighborhood_id' => $this->input->post('neighborId')
        );

        $query = $this->read_replica->get_where('neighborhood_address', $data);

        $count = $query->num_rows();

        if ($count) {
            echo json_encode([
                'message' => 'Duplicate'
            ]);

            return;
        }

        $this->db->insert('neighborhood_address', $data);
        echo json_encode([
            'message' => 'Inserted'
        ]);
    }

    public function addtestacc() {
        if ($_POST) {
            $username = trim($this->input->post('username'));
            if ($username != '') {
                $mysql_test = "SELECT id AS member_id FROM members WHERE lower(username) = lower('$username') ";
                $query = $this->read_replica->query($mysql_test);
                $row = $query->row_array();

                if (isset($row)) {
                    $member_id = $row["member_id"];
                    if ($member_id > 0) {

                        $mysql_confirm = "SELECT * FROM test_accounts WHERE member_id = $member_id";
                        $query2 = $this->read_replica->query($mysql_confirm);
                        $row2 = $query2->row_array();
                        if (isset($row2)) {
                            // up already
                            $mysql_update = "UPDATE test_accounts SET status = 1 WHERE member_id = $member_id";
                            $this->db->query($mysql_update);
                        } else {
                            // Add new entry
                            $mysql_insert = "INSERT INTO  test_accounts (member_id) VALUES($member_id)";
                            $this->db->query($mysql_insert);
                        }
                    }// valid member_id
                }
            }
        }
        $this->testaccounts();
    }

    public function removetest() {

        if ($_GET) {
            $test_id = trim($this->input->get('test_id'));
            if ($test_id > 0) {
                $mysql_update = "UPDATE test_accounts SET status = 0 WHERE id = $test_id";
                $this->db->query($mysql_update);
                echo 'Disabled';
            }
        }
    }

    public function testaccounts() {

        $this->load->model('test_accounts_model');

        $data = array("message" => "");
        $data["images"] = array();
        $data["page_title"] = "Test Accounts";

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

        $this->load->library('table');
        $this->table->set_heading(
            ['data' => 'MID', 'style' => 'width:50px'],
            ['data' => 'Firstname', 'style' => 'width:150px'],
            ['data' => 'Lastname', 'style' => 'width:50px'],
            'Username',
            ['data' => 'Group', 'style' => 'width:150px'],
            ['data' => 'Status', 'style' => 'width:100px'],
            ['data' => 'Action', 'style' => 'width:50px']
        );

        $query = $this->test_accounts_model->getAccountQuery($params);
        $tableData = $this->returnAdminTable(
            [
                'count_query' => $query,
                'query' => $query,
            ],
            '/tools/testaccounts',
            [
                'per_page' => 20,
                'reuse_query_string' => TRUE,
            ]
        );

        $status_custom_options = [
            '0' => 'Disabled'
        ];
        $data['status_dropdown'] = $this->combo_model->getStatusComboWithAll('status', $params['status'] ?? -1, $status_custom_options);
        $data['filterData'] = $params;
        $data['links'] = $tableData['links'];
        $data['test_account_table'] = $tableData['output_table'];


        $this->renderToolsPage('view_testaccounts', $data);
    }

    // Fetch records address
    public function getDataAddress($rowno, $rowperpage, $params) {
        // process filter
        $whereQuery = '';
        if (!empty($params['address_id'])) {
            $whereQuery .= " AND a.address_id = " . pg_escape_string($params['address_id']) . " ";
        }

        if (!empty($params['city_id'])) {
            if (count($params['city_id']) > 1) {
                foreach ($params['city_id'] as $key => $value) {
                    if($key === 0) {
                        $whereQuery .= " AND ( a.city_id = " . pg_escape_string($value) . " ";
                    } else {
                        $whereQuery .= " OR a.city_id = " . pg_escape_string($value) . " ";
                    }
                }
                $whereQuery .= " ) ";
            } else {
                $whereQuery .= " AND a.city_id = " . pg_escape_string($params['city_id'][0]) . " ";
            }
        }

        if (!empty($params['latitude']) && !empty($params['longitude']) && !empty($params['radius'])) {
            $whereQuery = " AND ST_DWithin(b.geometry, ST_SetSRID(ST_MakePoint({$params['longitude']}, {$params['latitude']}), 4326), {$params['radius']})";
        }

        if (!empty($params['attraction'])) {
            $whereQuery .= " AND a.attraction  ILIKE '%" . pg_escape_string(trim($params['attraction'])) . "%' ";
        }

        $all_address = $this->read_replica->query(
                "SELECT c.country, a.id, a.attraction, a.active, a.feature_image, a.description AS description, b.plus_code,b.latitude, b.longitude, b.address, b.timezone,
                        b.postal, COALESCE(b.description, '') AS address_description, c.city, c.id as city_id
                FROM tourist_attraction a, address b, geofence_area_city c
                WHERE
                    b.id = a.address_id
                    AND b.country != ''
                    AND a.city_id = c.id
                    " . $whereQuery . "
                ORDER BY a.id
                LIMIT ? OFFSET ?", array($rowperpage, $rowno));

        return $all_address->result_array();
    }

    // Select total records address
    public function getRecordCountAddress($params) {
        // process filter
        $whereQuery = '';
        if (!empty($params['address_id'])) {
            $whereQuery .= " AND a.address_id = " . pg_escape_string($params['address_id']) . " ";
        }

        if (!empty($params['city_id'])) {
            if (count($params['city_id']) > 1) {
                foreach ($params['city_id'] as $key => $value) {
                    if($key === 0) {
                        $whereQuery .= " AND ( a.city_id = " . pg_escape_string($value) . " ";
                    } else {
                        $whereQuery .= " OR a.city_id = " . pg_escape_string($value) . " ";
                    }
                }
                $whereQuery .= " ) ";
            } else {
                $whereQuery .= " AND a.city_id = " . pg_escape_string($params['city_id'][0]) . " ";
            }
        }

        if (!empty($params['latitude']) && !empty($params['longitude']) && !empty($params['radius'])) {
            $whereQuery = " AND ST_DWithin(b.geometry, ST_SetSRID(ST_MakePoint({$params['longitude']}, {$params['latitude']}), 4326), {$params['radius']})";
        }

        if (!empty($params['attraction'])) {
            $whereQuery .= " AND a.attraction  ILIKE '%" . pg_escape_string(trim($params['attraction'])) . "%' ";
        }

        $count = $this->read_replica->query(
                "SELECT count(a.id) AS COUNT
                FROM
                    tourist_attraction a, address b
                WHERE
                    b.id = a.address_id
                    AND b.country != '' " . $whereQuery);

        return $count->result_array()[0]["count"];
    }

    public function loadRecordAddress($rowno = 0) {
        $rowno = (int) $this->input->get('rowno');
        $params = $this->input->get();

        // Row per page
        $rowperpage = 20;
        $cur_page = $rowno;

        // Row position
        if ($rowno != 0) {
            $rowno = ($rowno - 1) * $rowperpage;
        }

        // All records count
        $allcount = $this->getRecordCountAddress($params);

        // Get records
        $users_record = $this->getDataAddress($rowno, $rowperpage, $params);

        // Pagination Configuration
        $config['base_url'] = '/tools/loadRecordAddress';
        $config['use_page_numbers'] = TRUE;
        $config['total_rows'] = $allcount;
        $config['per_page'] = $rowperpage;
        $config['cur_page'] = $cur_page;

        // Initialize
        $this->pagination->initialize($config);

        // Initialize $data Array
        $data['pagination'] = $this->pagination->create_links();
        $data['result'] = $users_record;
        $data['row'] = $rowno;

        echo json_encode($data);
    }

    public function gpstriggeraddress() {
        $data = [];
        $data["page_title"] = "GPS Tigger Address";

        if ($_POST) {
            $data['description'] = trim($this->input->post('description'));
            $data['address'] = trim($this->input->post('address'));
            $data['country'] = trim($this->input->post('location_country'));

            //Geocode the address if not avilable
            // call the backend with address_id , description
            $this->createOrUpdateGPSTriggerAddress();
        }

        $this->load->model('combo_model');
        $data['errMsg'] = null;
        $data['msg'] = null;
        $data['location_country'] = $this->combo_model->getCountryCombo('location_country', $this->input->get('location_country'));
        $this->renderToolsPage("view_gpstriggeraddress", $data);
    }

    public function gpstriggeraddresscreate() {
        $this->createOrUpdateGPSTriggerAddress(true);
    }

    public function gpstriggeraddressupdate() {
        $this->createOrUpdateGPSTriggerAddress(false);
    }

    public function gpstriggeraddressdestroy() {

        $this->load->helper(array('form', 'url'));
        $params = $this->getGPSTriggerAddressFormValue();

        $q = "DELETE FROM gps_trigger_location WHERE id='${params['gps_trigger_selected']}'";
        $r = $this->db->query($q);

        $this->viewData['msg'] = "GPS Trigger Address Deleted";

        $this->viewData['location_country'] = $this->combo_model->getCountryCombo('location_country', $this->input->get('location_country'));

        $this->renderToolsPage('view_gpstriggeraddress', $this->viewData);
    }

    public function handleGetDataFromOpenTripMap() {
        global $savvyext;
        $selectedCity = $this->input->post('city_id');
        $selectedCityName = $this->input->post('city_name');
        $city = $this->getCityInformationFromOpenTripMap($selectedCityName);
        $params = [
            "radius" => 1000,
            "lat" => $city['lat'],
            "lon" => $city['lon'],
            "format" => 'json'
        ];

        $places = $this->getLocalAttractionsFromThirdParty($params);

        echo json_encode($places);
    }

    public function getSingleLocalAttractionFromThirdParty() {
        global $savvyext;
        $xid = $this->input->post('xid');

        $baseUrl = $savvyext->cfgReadChar('opentripmap.api_url');
        $token = $savvyext->cfgReadChar('opentripmap.api_key');

        $url = $baseUrl . "/places/xid/$xid?apikey=" . $token;

        $api = curl_init();

        curl_setopt($api, CURLOPT_URL, $url);
        curl_setopt($api, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($api, CURLOPT_RETURNTRANSFER, true); // return the API response
        curl_setopt($api, CURLOPT_CUSTOMREQUEST, 'GET');
        curl_setopt($api, CURLOPT_TIMEOUT, 10);
        curl_setopt($api, CURLOPT_SSL_VERIFYPEER, false);

        $body = curl_exec($api);

        echo $body;
    }

    private function createOrUpdateGPSTriggerAddress($insert) {
        $this->load->helper(array('form', 'url'));
        $this->load->database();
        $this->load->library('form_validation');
        $this->setGPSTriggerAddressFormRule();

        $this->load->model('combo_model');
        $this->viewData['errMsg'] = null;
        $this->viewData['msg'] = null;
        $this->viewData['location_country'] = $this->combo_model->getCountryCombo('location_country', $this->input->get('location_country'));

        if ($this->form_validation->run() == false) {
            $this->viewData['errMsg'] = validation_errors();
            $this->renderToolsPage('view_gpstriggeraddress', $this->viewData);
            return;
        }

        $params = $this->getGPSTriggerAddressFormValue();

        $data = $this->geocode($params['address']);

        if (!$data['geocode']) {
            $this->viewData['errMsg'] = 'Address is invalid';
            $this->renderToolsPage('view_gpstriggeraddress', $this->viewData);
            return;
        }

        $params = array_merge($params, [
            'address_id' => $data['geocode']['id'],
            'country' => $data['geocode']['country'],
            'active' => true
        ]);

        // update the description of address
        //$this->updateAddress($params);

        $tourist_attraction_rs = $this->insertOrUpdateGPSTriggerAddress($params, $insert);
        $this->viewData['msg'] = "GPS Trigger Address ${tourist_attraction_rs}";

        $this->renderToolsPage('view_gpstriggeraddress', $this->viewData);
    }

    private function insertOrUpdateGPSTriggerAddress($params, $insert) {
        if ($insert) {
            $query = "INSERT INTO gps_trigger_location(
                description,
                status,
                country,
                address_id
              )
          VALUES
              (
                '" . $params['description'] . "',
                '" . $params['active'] . "',
                '" . $params['country'] . "',
                '" . $params['address_id'] . "'
              ) ON CONFLICT (description, address_id) DO UPDATE SET
              status = '" . $params['active'] . "'
              RETURNING *,
              CASE WHEN xmax::text::int > 0
              THEN 'Updated' else 'Inserted' END AS active";
        } else {
            $query = "UPDATE gps_trigger_location SET description='" . pg_escape_string($params['description']) . "'";
            $query .= ",country='" . pg_escape_string($params['country']) . "'";
            $query .= ",address_id='" . pg_escape_string($params['address_id']) . "'";
            $query .= " WHERE id=" . $params["gps_trigger_selected"] . " RETURNING 'Updated' AS active";
        }
        $rs = $this->db->query($query);
        return $rs->row_array()['active'];
    }

    private function geocode($data) {

        global $savvyext;
        $httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
        $encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
        $encryptionKey = $savvyext->cfgReadChar('encryption.key');
        $encryptionIV = $savvyext->cfgReadChar('encryption.iv');
        $api_url = $savvyext->cfgReadChar('system.api_url');

        $request_params = [
            'country' => $data['country'],
            'address' => $data['address']
        ];

        $url = $api_url
                . "/trips/api/geocode/?"
                . http_build_query($request_params);

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_VERBOSE, false);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
                'Content-Type: application/json',
                'Authorization: Server-Token ' . $httpAuthToken,
                "client_id: BackOffice"
            )
        );

        $body = curl_exec($ch);
        $result = json_decode($body, true);

        $payload = openssl_decrypt(
                hex2bin(
                        $result['payload']
                ), $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
        );
        return json_decode($payload, true);
    }

    public function loadRecordGPSTriggerAddress($rowno = 0) {
        $rowno = (int) $this->input->get('rowno');
        $params = $this->input->get();

        // Row per page
        $rowperpage = 20;
        $cur_page = $rowno;

        // Row position
        if ($rowno != 0) {
            $rowno = ($rowno - 1) * $rowperpage;
        }

        // process filter
        $whereQuery = '';
        if (!empty($params['address_id'])) {
            $whereQuery .= " AND a.address_id = " . pg_escape_string($params['address_id']) . " ";
        }

        if (!empty($params['city_id'])) {
            $whereQuery .= " AND b.city_id = " . pg_escape_string($params['city_id']) . " ";
        }

        if (!empty($params['latitude']) && !empty($params['longitude']) && !empty($params['radius'])) {
            $whereQuery = " AND ST_DWithin(geometry, ST_SetSRID(ST_MakePoint({$params['longitude']}, {$params['latitude']}), 4326), {$params['radius']})";
        }

        if (!empty($params['country'])) {
            $whereQuery .= " AND b.country = '" . pg_escape_string($params['country']) . "' ";
        }

        if (!empty($params['description'])) {
            $whereQuery .= " AND a.description  ILIKE '%" . pg_escape_string(trim($params['description'])) . "%' ";
        }

        // All records count
        $count = $this->read_replica->query(
            "SELECT count(a.id) AS COUNT
            FROM gps_trigger_location a, address b
            WHERE
                b.id=a.address_id
                AND b.country !='' " . $whereQuery);
        $allcount = $count->result_array()[0]["count"];

        // Get records
        $all_address = $this->read_replica->query(
                "SELECT a.id, a.address_id, b.country, a.description, b.address, b.latitude, b.longitude, b.timezone, b.postal
            FROM gps_trigger_location a, address b
            WHERE
                b.id=a.address_id
                AND b.country !=''
                " . $whereQuery . "
            ORDER BY a.id
            LIMIT ? OFFSET ?", array($rowperpage, $rowno));

        $users_record = $all_address->result_array();

        // Pagination Configuration
        $config['base_url'] = '/tools/loadRecordGPSTriggerAddress';
        $config['use_page_numbers'] = TRUE;
        $config['total_rows'] = $allcount;
        $config['per_page'] = $rowperpage;
        $config['cur_page'] = $cur_page;

        // Initialize
        $this->pagination->initialize($config);

        // Initialize $data Array
        $data['pagination'] = $this->pagination->create_links();
        $data['result'] = $users_record;
        $data['row'] = $rowno;

        echo json_encode($data);
    }

    private function setGPSTriggerAddressFormRule() {
        $this->form_validation->set_rules('address', 'Address', 'required|max_length[200]');
        $this->form_validation->set_rules('location_country', 'Country', 'required|max_length[2]');
        $this->form_validation->set_rules('description', 'Description', 'is_unique[address.description]');
    }

    private function getGPSTriggerAddressFormValue() {
        $location_country = $this->input->post('location_country');
        $address = $this->input->post('address');
        $description = $this->input->post('description');
        $gps_trigger_selected = $this->input->post('gps_trigger_selected');

        return [
            'location_country' => $location_country,
            'address' => $address,
            'description' => $description,
            'gps_trigger_selected' => $gps_trigger_selected
        ];
    }

    private function getCityInformationFromOpenTripMap($city_name) {
        // https://api.opentripmap.com/0.1/en/places/geoname?apikey=5ae2e3f221c38a28845f05b61de3e1c96cc4be1e55e26b5e8166170a&name=New%20York
        global $savvyext;
        $baseUrl = $savvyext->cfgReadChar('opentripmap.api_url');
        $token = $savvyext->cfgReadChar('opentripmap.api_key');

        $url = $baseUrl . '/places/geoname?name=' . urlencode($city_name);
        $url .= '&apikey=' . $token;
        $api = curl_init();

        curl_setopt($api, CURLOPT_URL, $url);
        curl_setopt($api, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($api, CURLOPT_RETURNTRANSFER, true); // return the API response
        curl_setopt($api, CURLOPT_CUSTOMREQUEST, 'GET');
        curl_setopt($api, CURLOPT_TIMEOUT, 10);
        curl_setopt($api, CURLOPT_SSL_VERIFYPEER, false);

        $body = curl_exec($api);

        return json_decode($body, true);
    }

    private function getLocalAttractionsFromThirdParty($params) {
        global $savvyext;

        $baseUrl = $savvyext->cfgReadChar('opentripmap.api_url');
        $token = $savvyext->cfgReadChar('opentripmap.api_key');

        'radius=1000&limit=5&offset=0&lon=-73.75623&lat=42.65258&rate=3&format=json';
        $url = $baseUrl . '/places/radius?apikey=' . $token;

        foreach($params as $key => $value) {
            $url .= "&$key=$value";
        }

        $api = curl_init();

        curl_setopt($api, CURLOPT_URL, $url);
        curl_setopt($api, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($api, CURLOPT_RETURNTRANSFER, true); // return the API response
        curl_setopt($api, CURLOPT_CUSTOMREQUEST, 'GET');
        curl_setopt($api, CURLOPT_TIMEOUT, 10);
        curl_setopt($api, CURLOPT_SSL_VERIFYPEER, false);

        $body = curl_exec($api);

        return json_decode($body, true);
    }

    private function checkCloneDataStatusForCity($city) {
        $query = 'SELECT is_fectched_data from geofence_area_city_settings WHERE geofence_area_city = ' . $city;
        $results = $this->read_replica->query($query);
        if ($results) {
            return [
                isset($results->result_array()[0]) ? $results->result_array()[0] : ['is_fectched_data' => false],
                NULL
            ];
        } else {
            $error = $this->read_replica->error();
            Logger::debug('Get city settings error:');
            Logger::debug($error);
            return [NULL, $error];
        }
    }

    private function updateFetchStatusOfCity($city) {
        $this->db->where('geofence_area_city', $city);
        $getResults = $this->db->get('geofence_area_city_settings');
        $citySettings = isset($getResults->result_array()[0]) ? $getResults->result_array()[0] : NULL;
        $this->db->where('geofence_area_city', $city);
        if (isset($citySettings)) {
            $data = array('is_fectched_data' => true);
            $this->db->update('geofence_area_city_settings', $data);
        } else {
            $data = array('is_fectched_data' => true, 'geofence_area_city' => $city, 'status' => 0);
            $this->db->insert('geofence_area_city_settings', $data);
        }
    }

  public function setFormRuleForPOI() {
    $this->form_validation->set_rules(
        'city_id[]',
        'city',
        'integer'
    );
    $this->form_validation->set_rules(
        'address_id',
        'address',
        'integer'
    );
    $this->form_validation->set_rules(
      'radius',
      'radius',
      'integer'
    );
    $this->form_validation->set_rules(
      'latitude',
      'latitude',
      'decimal'
    );
    $this->form_validation->set_rules(
      'longitude',
      'longitude',
      'decimal'
    );
  }

  public function getValueOfPOI() {
    return [
        'city_id' => $this->input->get('city_id'),
        'address_id' => $this->input->get('address_id'),
        'attraction' => $this->input->get('attraction'),
        'longitude' => $this->input->get('longitude'),
        'latitude' => $this->input->get('latitude'),
        'radius' => $this->input->get('radius'),
    ];
  }

  public function validateValueForPOI($params) {
      $this->load->library('form_validation');
      $this->form_validation->set_data($params);
      $this->setFormRuleForPOI();

      $errors = [];
      if ($this->form_validation->run() === FALSE) {
          $errors = $this->form_validation->error_array();
      }
      return $errors;
  }

  public function export() {
    global $savvyext;

    $this->load->model('Address_model');
    $this->load->helper('export_csv');
    $params = $this->getValueOfPOI();

    $errors = $this->validateValueForPOI($params);
    $params = array_filter($params, function($ele) {
        return $ele !== "" && $ele !== null;
    });
    $params = array_diff_key($params, $errors);
    $query = $this->Address_model->generate_query_select_address($params);

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

    if ($r->result_array()[0]['all_count'] === '0') {
      $this->renderToolsPage("view_pointsofinterest", $this->viewData);
      return;
    }

    $this->db->save_queries = false;
    $query = $this->Address_model->generate_query_select_address(
        $params,
        self::NONE_COUNT_RECORD
    );
    $r = $this->db->query($query);
    export($r);
  }

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

  public function import_csv() {
    $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);
        try {
            $poi_data = []; // Contains all data for POI - which will be used to inserted multiple data in one query.
            $existed_city_ids = []; // Contains existed city id that we found when importing.
            $existed_city_settings_id = []; // Contains existed city_settings id that we found when importing.
            while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
                $num = count($data);
                $f = [];
                for ($c=0; $c < $num; $c++) {
                    $f[self::CSV_FIELDS[$c]] = $data[$c];
                }
                if ($row>0) {
                    $importData = $f;
                    $geo_code_params = array(
                        'country' => $importData['gac_country'],
                        'address' => $importData['a_address']
                    );

                    // Call API geocode to generate the address for locaiton attraction.
                    // If this API fail. Continue to next record.
                    $response = $this->geocode($geo_code_params);
                    if (!$response['geocode']) {
                        log_message('DEBUG', "The address is not exists: {$importData['a_address']}");
                        log_message('DEBUG', json_encode($response));
                        continue;
                    }

                    // Check if the current city has been found
                    $city_params = array(
                        'country' => $importData['gac_country'],
                        'city' => $importData['gac_city']
                    );
                    if (empty($existed_city_ids[$city_params['city']]) || $existed_city_ids[$city_params['city']]['country'] != $city_params['country']) {
                        // If city has been found yet. Check it in database
                        // Check if the city is existed
                        $city = $this->check_existed_city($city_params);
                        if (!$city) {
                            // Create new city if the current city is not existed in our database
                            $created_city = $this->geofence_area_city_model->create([
                                'city' => $importData['gac_city'],
                                'country' => $importData['gac_country'],
                                'latitude' => $importData['gac_latitude'],
                                'longitude' => $importData['gac_longitude'],
                                'location' => $importData['gac_location'],
                                'radius' => !empty($importData['gac_radius']) ? $importData['gac_radius'] : 3000,
                                'status' => $importData['gac_status']
                            ]);
                            $cityID = $this->db->insert_id();
                        }

                        // Update cityID based on found city
                        $cityID = (int)(isset($city[0]) ? $city[0]['id'] : $cityID);
                        // Update found cities for next check
                        $existed_city_ids[$importData['gac_city']] = [
                            'country' => $importData['gac_country'],
                            'id' => $cityID
                        ];
                    } else {
                        // If city has been found already.
                        // Update cityID based on found city
                        $cityID = $existed_city_ids[$city_params['city']]['id'];
                    }

                    // Do nothing if import record doesn't have gacs_id
                    if (!empty($importData['gacs_id'])) {
                        if (empty($existed_city_settings_id[$cityID])) {
                            // create settings for city with id found in database or new city id
                            $citySetting = $this->check_existed_city_settings($cityID);
                            if (!$citySetting) {
                                $this->geofence_area_city_settings_model->create([
                                    'status' => $importData['gacs_status'],
                                    'geofence_area_city' => $cityID,
                                    'image_url' => $importData['gacs_image_url'],
                                    'is_fectched_data' => $importData['gacs_is_fectched_data']
                                ]);
                                $city_settings_id = $this->db->insert_id();
                            }

                            // Update found city settings
                            $existed_city_settings_id[$cityID] = $city_settings_id ?? $citySetting['id'];
                            if (!empty($citySetting)) {
                                // Check if city is active and existed settings is not active
                                $updateData = [];
                                if ($importData['gacs_status'] || $importData['gacs_status'] === '1') {
                                    // Update exsited settings to active for city if import data's status is active
                                    if (!$citySetting['status'] || $citySetting['status'] === '0' || $citySetting['status'] === 'f') {
                                        $updateData['status'] = 1;
                                    }

                                    if (isset($citySetting['image_url']) && $citySetting['image_url'] != $importData['gacs_image_url']) {
                                        $updateData['image_url'] = $importData['gacs_image_url'];
                                    }

                                    if (count($updateData) > 0) {
                                        $this->geofence_area_city_settings_model->update($citySetting['id'], $updateData);
                                    }
                                }
                            }
                        }
                    }

                    // add poi with city id found in database or new city id to the data list
                    $poi = [
                        'attraction'    => $importData['ta_attraction'],
                        'active'        => $importData['ta_active'] === 't' ? true : false,
                        'address_id'    => (int)$response['geocode']['id'],
                        'city_id'       => $cityID,
                        'feature_image' => $importData['ta_feature_image'],
                        'description'   => $importData['ta_description']
                    ];
                    $is_duplicate = $this->check_duplicate_record($poi_data, ['attraction' => $poi['attraction'], 'address_id' => $poi['address_id']]);
                    if (!$is_duplicate) {
                        $poi_data[] = $poi;
                    }
                }
                $row++;
            }
            // Create multiple POIs in tourist attraction
            log_message('DEBUG', 'import POI:');
            log_message('DEBUG', json_encode($poi_data));
            $this->tourist_attraction_model->create_multiple($poi_data);
        } catch (Exception $err) {
            log_message('DEBUG', $err->getMessage());
            exit();
        }
    }

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

    private function check_existed_city($params) {
        // find city by country code and city name
        // if not found create a new one
        $city = $this->geofence_area_city_model->getRecordByCityAndCountry($params);
        return $city ?? NULL;
    }

    private function check_existed_city_settings($city_id) {
        $citySetting = $this->geofence_area_city_settings_model->getCitySettingByCityID($city_id);
        return !empty($citySetting) ? $citySetting[0] : NULL;
    }
    
    /**
     * check_duplicate_record
     * Check condition is all the value are the same (and condition)
     *
     * @param  mixed $arr - array used to check
     * @param  mixed $check_value - checked value
     * @return bool
     */
    private function check_duplicate_record($arr, $check_value) {
        $keys = array_keys($check_value);
        $keys_length = count($keys);
        $is_duplicate = false;
        foreach($arr as $item) {
            for ($i = 0; $i < $keys_length; $i++) {
                $key = $keys[$i];
                if ($item[$key] != $check_value[$key]) {
                    break;
                }

                if ($i == $keys_length - 1) {
                    $is_duplicate = true;
                }
            }

            if ($is_duplicate) {
                break;
            }
        }

        return $is_duplicate;
    }

    private function setFormRuleForUpdatePOI() {
        $this->form_validation->set_rules('card_country', 'Country', 'required');
        $this->form_validation->set_rules('address', 'Address', 'required|max_length[200]');
        $this->form_validation->set_rules('loc_name', 'Location', 'required|max_length[200]');
        $this->form_validation->set_rules('city_id', 'City', 'required|integer');
        $this->form_validation->set_rules('feature_image', 'Image', 'max_length[300]');
    }

    public function updatePOI(){
        $postData = $this->input->post();
        $this->load->library('form_validation');
        $this->form_validation->set_data($postData);
        $this->setFormRuleForUpdatePOI();
        $errors = [];
        if ($this->form_validation->run() === FALSE) {
            $errors = $this->form_validation->error_array();
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['errors' => $errors]);
            return;
        }

        $data = $this->geocode(['address' => $postData['address'], 'country' => $postData['card_country'] ]);

        if (!$data['geocode']) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['errors' => 'Address is invalid']);
            return;
        }

        $poiData = array(
            'attraction' => $postData['loc_name'],
            'active' => $postData['status'] === '1' ? true : false,
            'address_id' => $data['geocode']['id'],
            'city_id' => $postData['city_id'],
            'description' => $postData['description'],
            'feature_image' => $postData['feature_image']
        );
        $this->tourist_attraction_model->update_by_id($postData['poiSelected'], $poiData);
        echo json_encode(['message' => 'Updated POI successfully!','data'=>$data['geocode']]);
    }
}