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

class Geofence_area_model extends CI_Model
{
    const GEOFENCING_AREA_TYPES = [
        'postal',
        'radius',
        'polygon'
    ];

    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 = '';
        if (!empty($filterData)) {
            if (!empty($filterData['city_id'])) {
                $whereQuery .= ' WHERE geo_city.id = ' . $this->read_replica->escape($filterData['city_id']) . ' ';
            }
        }

        $totalQueryString = 
        'SELECT 
            geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
            geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
            country.country as country_name
        FROM 
            geofence_area as geo
                JOIN country ON country.code = geo.country
                JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
        ' . $whereQuery;

        $queryString = 
        'SELECT 
            geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
            geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
            country.country as country_name
        FROM 
            geofence_area as geo
                JOIN country ON country.code = geo.country
                JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
        ' . $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 getAllAreas()
    {
        $query = "SELECT
                    geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.latitude, geofence_area.longitude,
                    geofence_area.location, geofence_area.type, geofence_area.boundaries
                FROM
                    geofence_area
                        JOIN geofence_area_city ON geofence_area_city.id = geofence_area.city_id
        ";

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

        return $query->result();
    }

    public function getItem($id)
    {
        $queryString = 
        'SELECT 
            geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
            geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
            country.country as country_name
        FROM 
            geofence_area as geo
                JOIN country ON country.code = geo.country
                JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
        WHERE
            geo.id = ?
        LIMIT 1
        ';

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

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

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

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

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

    public function priceComparison($startAreaId, $endAreaId)
    {
        // validate id
        if (!is_numeric($startAreaId) || !is_numeric($endAreaId)) {
            return false;
        }

        // 1) Define geofenced areas, get list of areas
        $startArea = $this->getArea($startAreaId);
        $endArea = $this->getArea($endAreaId);

        if (!$startArea || !$endArea) {
            return [
                'success' => false,
                'message' => 'Can not find area.'
            ];
        }

        // 2. Select trips where "from" falls into origin area and "to" falls into destination area
        $whereQuery = '';
        $startBoundaries = json_decode($startArea['boundaries'], true);
        $endBoundaries = json_decode($endArea['boundaries'], true);
        if ($startArea['type'] === 'radius') {
            if (isset($startBoundaries['radius'])) {
                $radius = $startBoundaries['radius'];
                $condition = " ST_DWithin('" . $startArea['location'] . "', startAddr.geometry, " . $radius . ") ";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing radius in boundaries field.'
                ];
            }
        } else if ($startArea['type'] === 'postal') {
            if (is_array($startBoundaries) && array_key_exists("postal_code",$startBoundaries) && is_array($startBoundaries["postal_code"]) && count($startBoundaries["postal_code"]) > 0) {
                $condition = "";
                foreach ($startBoundaries['postal_code'] as $code) {
                    if ($condition != "") {
                        $condition .= " OR ";
                    }
                    $condition .= " LEFT(startAddr.postal, " . strlen($code) . ") = '${code}' ";
                }

                $condition = "(${condition})";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing postal_code in boundaries field.'
                ];
            }
        } else if ($startArea['type'] === 'polygon') {
            if (isset($startBoundaries['polygon'])) {
                $polygon = $startBoundaries['polygon'];

                $polygonCoords = [];
                foreach ($polygon as $coord) {
                    $polygonCoords[] = "$coord[0] $coord[1]";
                }
                $polygonCoordsString = implode(",", $polygonCoords);

                $condition = " ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString}))', 4326), startAddr.geometry::geometry) ";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing polygon in boundaries field.'
                ];
            }
        }

        if ($endArea['type'] === 'radius') {
            if (isset($endBoundaries['radius'])) {
                $radius = $endBoundaries['radius'];
                $condition = " ST_DWithin('" . $endArea['location'] . "', endAddr.geometry, " . $radius . ") ";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing radius in boundaries field.'
                ];
            }
        } else if ($endArea['type'] === 'postal') {
            if (is_array($endBoundaries) && array_key_exists("postal_code",$endBoundaries) && is_array($endBoundaries["postal_code"]) && count($endBoundaries["postal_code"]) > 0) {
                $condition = "";
                foreach ($endBoundaries['postal_code'] as $code) {
                    if ($condition != "") {
                        $condition .= " OR ";
                    }
                    $condition .= " LEFT(endAddr.postal, " . strlen($code) . ") = '${code}' ";
                }

                $condition = "(${condition})";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing postal_code in boundaries field.'
                ];
            }
        } else if ($endArea['type'] === 'polygon') {
            if (isset($endBoundaries['polygon'])) {
                $polygon = $endBoundaries['polygon'];

                $polygonCoords = [];
                foreach ($polygon as $coord) {
                    $polygonCoords[] = "$coord[0] $coord[1]";
                }
                $polygonCoordsString = implode(",", $polygonCoords);

                $condition = " ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString}))', 4326), endAddr.geometry::geometry) ";
                $whereQuery = $this->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing polygon in boundaries field.'
                ];
            }
        }

        // 3. Filter pricing by transport_provider_id and calculate average within last year

        // only get trips within last year
        $numberOfDays = 365;
        $condition = " parsedemail_item.travel_date BETWEEN NOW() - INTERVAL '" . $numberOfDays . " days' AND NOW() ";
        $whereQuery = $this->checkCondition($condition, $whereQuery);

        $tripQuery = 
            "SELECT
                ROUND(AVG(parsedemail_item.cost), 2) as avg_price, parsedemail_item.transport_provider_id,
                transport_providers.name as transport_provider_name
            FROM
                parsedemail_item
                JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
                JOIN address AS startAddr ON (startAddr.id = parsedemail_item.location_start_id AND transport_provider_id IS NOT NULL 
                                            AND LOWER(startAddr.country) = '" . strtolower($startArea['country_code']) . "')
                JOIN address AS endAddr ON (endAddr.id = parsedemail_item.location_end_id 
                                            AND LOWER(endAddr.country) = '" . strtolower($endArea['country_code']) . "')
            " . $whereQuery . "
            GROUP BY parsedemail_item.transport_provider_id, transport_providers.name
            ";

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

        $result = [
            'success' => true,
            'from_area' => $startArea,
            'to_area' => $endArea,
            'price_comparison' => $query->result_array() ?: []
        ];

        return $result;
    }

    private function checkCondition($condition, $query)
    {
        if (strpos($query, 'WHERE') === false) {
            $query .= " WHERE $condition";
        } else {
            $query .= " AND $condition";
        }
        return $query;
    }

    public function getArea($id)
    {
        $query = "SELECT
                    geofence_area.id, geofence_area.name, geofence_area.country as country_code, geofence_area.latitude, geofence_area.longitude,
                    geofence_area.location, geofence_area.type, geofence_area.boundaries
                FROM
                    geofence_area
                WHERE 
                    geofence_area.id = $id
                LIMIT 1
        ";

        $query = $this->read_replica->query($query);
        $data = $query->result_array();

        return !empty($data) ? $data[0] : null;
    }

    public function getLocationByID($id) {
      $this->read_replica->select('*');
      $this->read_replica->from('geofence_area');
      $this->read_replica->where('id', $id);
      
      $query = $this->read_replica->get();
  
      return $query->result_array();
    }

    public function getLocationByCityID($city_id) {
      $this->read_replica->select('ga.id AS id, ga.name AS geo_area_name');
      $this->read_replica->from('geofence_area ga');
      $this->read_replica->join('geofence_area_city gac', 'gac.id = ga.city_id');
      $this->read_replica->where('gac.id', $city_id);
      $this->read_replica->order_by('geo_area_name');

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