<?php

class GeofenceAreaModel
{
    public static function getCountryListWithPagination($db, $page = 1, $perPage = 10)
    {
        $offset = ($page - 1) * $perPage;
        if ($offset < 0) {
            $offset = 0;
        }

        $query_total = "SELECT count(*) FROM geofence_area_country";

        $query = "SELECT
                    id, country as country_code, latitude, longitude, location, radius
                FROM
                    geofence_area_country
                ORDER BY
                    id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";

        $rs = pg_query($db, $query);
        $rs_total = pg_query($db, $query_total);

        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : [],
            'total' => pg_fetch_result($rs_total, 'count'),
            'page' => $page,
        ];
    }

    public static function getCountryListWithoutPagination($db)
    {
        $query = "SELECT
                    id, country as country_code, latitude, longitude, location, radius
                FROM
                    geofence_area_country
                ORDER BY
                    id desc";

        $rs = pg_query($db, $query);
        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : []
        ];
    }

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

        $query_total = "SELECT count(*) FROM geofence_area_city";

        $query = "SELECT
                    id, city, country as country_code, latitude, longitude, location, radius
                FROM
                    geofence_area_city
                ORDER BY
                    id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";

        $rs = pg_query($db, $query);
        $rs_total = pg_query($db, $query_total);

        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : [],
            'total' => pg_fetch_result($rs_total, 'count'),
            'page' => $page,
        ];
    }

    public static function getCityListWithoutPagination($db, $search = [])
    {
        $_self = new self;

        $query = "SELECT
                    id, city, country as country_code, 
                    latitude, longitude, location, 
                    radius
                  FROM
                    geofence_area_city";

        $whereQuery = '';
        $order = " ORDER BY id desc";

        if (!empty($search["country_code"])) {
            $condition = " LOWER(country) = '" . pg_escape_string(strtolower($search["country_code"])) . "'";
            $whereQuery = $_self->checkCondition($condition, $whereQuery);

            $query .= $whereQuery;

        }
        if (!empty($search["latitude"]) &&
            !empty($search["longitude"]) &&
            !empty($search["radius"])) {
            
            $earth_radius = 6371;
            $km_per_degree_lat = 111.2;
            $pi = 3.14/180;
            $latitude = $search["latitude"];
            $longitude = $search["longitude"];
            $distance_in_km = $search["radius"];

            $query =  " SELECT id
                              , city
                              , country as country_code
                              , latitude
                              , longitude
                              , location
                              , radius
                            FROM (
                              SELECT (
                                $earth_radius * acos(
                                    cos( radians( {$latitude}) )
                                  * cos( radians( latitude  ) )
                                  * cos( radians( longitude ) - radians( {$longitude} ) )
                                  + sin( radians( {$latitude}) )
                                  * sin( radians( latitude  ) ))
                                ) AS distance
                                , id
                                , city
                                , country
                                , latitude
                                , longitude
                                , location
                                , radius
                              FROM geofence_area_city
                              WHERE 
                                latitude BETWEEN " . ($latitude - ($distance_in_km / $km_per_degree_lat)) .
                              " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

            $delta = round($distance_in_km  / ($km_per_degree_lat  * COS(deg2rad($longitude))), 10);

            // Bounding box for speed - latitude within range (longtitude to km not linear)
            if(cos($longitude * $pi) > 0) {

                $from =  $longitude - $delta;
                $to = $longitude + $delta;

                $query .= 
                  " AND longitude" .
                  " BETWEEN " . $from .
                  " AND " . $to;

            } else {

                $from =  $longitude + $delta;
                $to = $longitude - $delta;

                $query .= 
                  " AND longitude" .
                  " BETWEEN " . $from .
                  " AND " . $to;

            }

            // distance limit for circle
            $query .= " ) geofence_area_city
                        WHERE distance < " . $distance_in_km;
        }

        $query .= $order;

        $rs = pg_query($db, $query);
        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : []
        ];
    }

    public static function getAreaListWithPagination($db, $page = 1, $perPage = 10, $search = [])
    {
        $_self = new self;

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

        $whereQuery = '';
        if (isset($search["city_id"])) {
            $condition = " geofence_area_city.id = '" . pg_escape_string($search["city_id"]) . "'";
            $whereQuery = $_self->checkCondition($condition, $whereQuery);
        }

        $query_total = "SELECT count(*)
                        FROM
                            geofence_area
                                JOIN geofence_area_city ON geofence_area_city.id = geofence_area.city_id " . $whereQuery;

        $query = "SELECT
                    geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.country as country_code, 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
                " . $whereQuery . "
                ORDER BY
                    id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";

        $rs = pg_query($db, $query);
        $rs_total = pg_query($db, $query_total);

        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : [],
            'total' => pg_fetch_result($rs_total, 'count'),
            'page' => $page,
        ];
    }

    public static function getAreaListWithoutPagination($db, $search = [])
    {
        $_self = new self;
        $whereQuery = '';

        if (!empty($search["city_id"])) {
            $condition = " geofence_area_city.id = '" . pg_escape_string($search["city_id"]) . "'";
            $whereQuery = $_self->checkCondition($condition, $whereQuery);
        }

        if (!empty($search["country_code"])) {
            $condition = " LOWER(geofence_area.country) = '" . pg_escape_string(strtolower($search["country_code"])) . "'";
            $whereQuery = $_self->checkCondition($condition, $whereQuery);
        }

        $query = "SELECT
                    geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.country as country_code, 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
                " . $whereQuery . "
                ORDER BY
                    id desc";

        $rs = pg_query($db, $query);
        $data = pg_fetch_all($rs);

        return [
            'success' => true,
            'data' => !empty($data) ? $data : []
        ];
    }

    public static function getTripListBetweenArea($db, $startAreaId, $endAreaId, $search = [])
    {
        $_self = new self;
        $whereQuery = '';
        if (!empty($search["transport_provider_id"])) {
            $condition = " parsedemail_item.transport_provider_id = '" . pg_escape_string($search["transport_provider_id"]) . "'";
            $whereQuery = $_self->checkCondition($condition, $whereQuery);
        }

        // 1) Define geofenced areas, get list of areas
        $startArea = $_self->getArea($db, $startAreaId);
        $endArea = $_self->getArea($db, $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
        $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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->checkCondition($condition, $whereQuery);
            } else {
                return [
                    'success' => false,
                    'message' => 'Missing polygon in boundaries field.'
                ];
            }
        }

        // only get trips within last year
        $numberOfDays = 365;
        $condition = " parsedemail_item.travel_date BETWEEN NOW() - INTERVAL '" . $numberOfDays . " days' AND NOW() ";
        $whereQuery = $_self->checkCondition($condition, $whereQuery);
        
        $tripQuery = 
            "SELECT
                parsedemail_item.id as trip_id, parsedemail_item.transport_provider_id, startAddr.address as start_address, endAddr.address as end_address
            FROM
                parsedemail_item
                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;

        $data = pg_fetch_all(pg_query($db, $tripQuery));

        $result = [
            'from_area' => $startArea,
            'to_area' => $endArea,
            'trip_list' => $data ?: []
        ];

        return [
            'success' => true,
            'data' => $result ?: []
        ];
    }

    public static function priceComparison($db, $startAreaId, $endAreaId)
    {
        $_self = new self;

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

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

        $tripQuery = "SELECT ROUND(SUM(average_total)/SUM(average_count), 2) as avg_price, transport_provider_id";
        $tripQuery.= " FROM geofence_area_average_quotes ";
        $tripQuery.= " WHERE area_start_id=${startAreaId} AND area_end_id=${endAreaId}";
        $tripQuery.= " GROUP BY transport_provider_id";

        $data = pg_fetch_all(pg_query($db, $tripQuery));
        error_log($tripQuery);

        $result = [
            'from_area' => $startArea,
            'to_area' => $endArea,
            'price_comparison' => $data ?: []
        ];

        return $result;
    }

    public static function priceComparisonOld($db, $startAreaId, $endAreaId)
    {
        $_self = new self;

        // 1) Define geofenced areas, get list of areas
        $startArea = $_self->getArea($db, $startAreaId);
        $endArea = $_self->getArea($db, $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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->checkCondition($condition, $whereQuery);

        $tripQuery = 
            "SELECT
                ROUND(AVG(parsedemail_item.cost), 2) as avg_price, parsedemail_item.transport_provider_id
            FROM
                parsedemail_item
                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
            ";

            

        $data = pg_fetch_all(pg_query($db, $tripQuery));

        $result = [
            'from_area' => $startArea,
            'to_area' => $endArea,
            'price_comparison' => $data ?: []
        ];

        return $result;
    }

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

    public static function getArea($db, $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
        ";

        $rs = pg_query($db, $query);
        $data = pg_fetch_assoc($rs);

        return $data;
    }
}