<?php if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Geofence_area_city_model extends CI_Model
{
    // city status
    const ACTIVE_STATUS = 1;
    const INACTIVE_STATUS = 0;

    private $read_replica;

    public function __construct()
    {
        parent::__construct();
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
    }

    public function getAllWithPagination($page = 1, $perPage = 10, $filterData = [])
    {
        $offset = ($page - 1) * $perPage;
        if ($offset < 0) {
            $offset = 0;
        }

        $whereQuery = 'WHERE 1=1';
        if (!empty($filterData)) {
            if (!empty($filterData['country_code'])) {
                $whereQuery .= '  AND geo.country = ' . $this->read_replica->escape(strtoupper($filterData['country_code'])) . ' ';
            }
            if (!empty($filterData['search_text'])) {
                $search_text = $this->read_replica->escape_like_str($filterData['search_text']);
                $whereQuery .= ' AND geo.city ILIKE \'%' . $search_text . '%\'';
            }
            if (isset($filterData['status']) && $filterData['status'] != "all") {
                $status = $this->read_replica->escape($filterData['status']);
                $whereQuery .= ' AND geo.status =' . $status;
            }
        }

        $totalQueryString =
            'SELECT
            geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name, geo.status
        FROM
            geofence_area_city as geo
                JOIN country ON country.code = geo.country
        ' . $whereQuery;
        //echo $totalQueryString;exit;
        $queryString =
        'SELECT
            geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name, geo.status
        FROM
            geofence_area_city as geo
                JOIN country ON country.code = geo.country
        ' . $whereQuery . '
        ORDER BY id DESC
        LIMIT ' . pg_escape_string($perPage) . ' OFFSET ' . pg_escape_string($offset);

        $totalQuery = $this->read_replica->query($totalQueryString);
        $query = $this->read_replica->query($queryString);

        return [
            'total' => count($totalQuery->result()),
            'data' => $query->result(),
            'page' => $page,
        ];
    }

    public function getItem($id)
    {
        $queryString =
            'SELECT
            geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name, geo.status
        FROM
            geofence_area_city as geo
                JOIN country ON country.code = geo.country
        WHERE
            geo.id = ?
        LIMIT 1
        ';

        $query = $this->read_replica->query($queryString, $id);
        return $query->row_array();
    }

    public function create($inputData)
    {
        $this->db->insert('geofence_area_city', $inputData);
        $insertId = $this->db->insert_id();

        // update location field
        $this->db->query("UPDATE geofence_area_city SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$insertId} ");
    }

    public function update($id, $inputData)
    {
        $this->db->where('id', $id);
        $this->db->update('geofence_area_city', $inputData);

        // update location field
        $this->db->query("UPDATE geofence_area_city SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$id} ");
        return $this->db->affected_rows();
    }

    public function delete($id)
    {
        $this->db->delete('geofence_area_city', ['id' => $id]);
    }

    public function getCities($condtions = '')
    {
        if ($condtions != '') {
            $q = "SELECT id, city  FROM geofence_area_city WHERE ".$condtions." ORDER BY id ASC";
            $r = pg_query($q);
            $query = $this->read_replica->query($q);
            return $query->result();
        }
        return null;
    }

    public function getDuplicatedCities()
    {
        $q = "SELECT city, country, count(*)
        FROM geofence_area_city
        GROUP BY city,
        country
        HAVING count(*)>1
        ORDER BY count(*) DESC";
        $query = $this->read_replica->query($q);
        return $query->result();
    }

    public function updateAddresses($condition,$data)
    {
        $this->db->where($condition);
        $this->db->update('address', $data);
        return $this->db->affected_rows();
    }

    public function deleteUnusedCities()
    {
        global $conn;
        $q = "DELETE
            FROM geofence_area_city
            WHERE id NOT IN (SELECT DISTINCT city_id FROM address);";
        $this->db->query($q);
        return $this->db->affected_rows();
    }

    public function getCityIDByName($city_name) {
      $q = $this->read_replica->select('id')
                    ->from('geofence_area_city')
                    ->where('lower(city)', strtolower($city_name))
                    ->get();

      return $q->result_array() ? $q->result_array()['id'] : NULL;
    }

    public function getCityByCoordindates($params) {
      $res = SQEAPI::get('geofencearea/api/geofencearea/cityApi', $params);

        if (isset($res['error'])) {
            return ['success' => false, 'error' => $res['error']];
        }

        $data = array();
        foreach ($res['data'] as $addr) {
            $data[] = (array)($addr);
        }

        return $data;
    }

    public function getCityWithFilter($params)
    {
        $page = $params['page'] ?? 1;
        $per_page = $params['per_page'] ?? 20;

        $offset = ($page - 1) * $per_page;
        if ($offset < 0) {
            $offset = 0;
        }

        $whereQuery = ' WHERE 1 = 1 ';
        if (!empty($params)) {
            if (!empty($params['city_name'])) {
                $search_text = $this->read_replica->escape_like_str($params['city_name']);
                $whereQuery .= ' AND city ILIKE \'%' . $search_text . '%\'';
            }
        }

        $totalQueryString = '
            SELECT id
            FROM geofence_area_city
            ' . $whereQuery;

        $queryString = '
            SELECT * 
            FROM geofence_area_city
            ' . $whereQuery . '
            ORDER BY city ASC
            LIMIT ' . pg_escape_string($per_page) . ' OFFSET ' . pg_escape_string($offset);

        $totalQuery = $this->read_replica->query($totalQueryString);
        $query = $this->read_replica->query($queryString);

        return [
            'total' => count($totalQuery->result()),
            'data' => $query->result(),
            'page' => $page,
        ];
    }

    public function getRecordByCityAndCountry($data) {
      $this->read_replica
        ->select('id')
        ->from('geofence_area_city')
        ->where('city', $data['city'])
        ->where('country', $data['country']);

      return $this->read_replica->get()->result_array();
    }

    public function getCityQueryWithFilter($filters = [], $count_record = true)
    {
        $numeric_array = [
            'status' => 'status',
        ];
  
        $string_array = [
          'search_text' => 'city',
          'country_code' => 'country'
        ];
  
        $count_record ?
        $this->read_replica->select(['count(*) AS all_count']) :
        $this->read_replica->select([
          'id',
          'city',
          'country',
          'latitude',
          'longitude',
          'location',
          'radius',
          'status',
        ]);
  
        $this->read_replica->from('geofence_area_city');
  
        foreach($filters as $key => $val) {
            if (array_key_exists($key, $numeric_array)) {  
                $this->read_replica->where($numeric_array[$key], $val);
            } else if (array_key_exists($key, $string_array)) {
                $this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
            }
        }

        if ($count_record === false) {
            $this->read_replica->order_by('id ASC');
        }
  
        return $this->read_replica->get_compiled_select();
    }

    public function getCountryByCode($data){
        $this->read_replica
          ->select('code')
          ->from('country')
          ->where('code', $data['country']);

        return $this->read_replica->get()->result_array();
    }
}