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

class Geofence_area_country_model extends CI_Model
{

    private $read_replica;

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

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

        $totalQueryString =
        'SELECT 
            geo.id, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name
        FROM 
            geofence_area_country as geo
                JOIN country ON country.code = geo.country
        ';

        $queryString = 
        'SELECT 
            geo.id, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name
        FROM 
            geofence_area_country as geo
                JOIN country ON country.code = geo.country
        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.country as country_code, geo.latitude, geo.longitude, geo.radius,
            country.country as country_name
        FROM 
            geofence_area_country 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_country', $inputData);

        $insertId= $this->db->insert_id();

        // update location field
        $this->db->query("UPDATE geofence_area_country 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_country', $inputData);

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

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