<?php

require_once __DIR__ . '/../../api-user-check.php';
require_once __DIR__ . '/gb.nr/realm-locations.php'; /* anything specific to gb.nr, nothing currently.  Might remove. */

class LocationsDB
{
    const DB = 'sqlite:/opt/autopilot/database/live/locations.sqlite';

    static private $connection = null;

    static public function getConnection()
    {
        if (self::$connection === null) {
            self::$connection = new PDO(self::DB);
            if (!self::$connection) {
                die("Connection to sqlite db failed");
            }
        }
        return self::$connection;
    }
}

class Locations
{
    const GB_NR_CRS_CODE_LENGTH = 3;    /* CRS is 3 chars */
    const GB_NAPTAN_CODE_LENGTH = 8;    /* NaPTAN is 8 chars */
    const GB_NR_DEFAULT_CHANGE_TIME = 5;  /* 5 mins for NR, will change to include distance when more modes are supported */

    const DEFAULT_LANGUAGE = "en";

    private static $locationCache = [];

    public static function search(string $query, string $realm, int $maxResults = null, float $lat = null, float $long = null)
    {
        $matches = [];
        $db = LocationsDB::getConnection();
        if ($db) {
            // FIXME Simplify/hide these queries
            // FIXME LIMIT 25 for now, something more than would be requested but low enough not to cause performance problems
            if ($query) {
                $query = trim(urldecode($query));

                if (strlen($query) === self::GB_NAPTAN_CODE_LENGTH && (!isset($realm) || $realm === "all" || $realm === "gb.gov")) {
                    // A NaPTAN code match should be the only location shown
                    $sql = "SELECT loc.generic_id, locname.name || ', ' || loc.locality AS name, locname.language, loc.bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, 0 AS display_priority
                        FROM ap_secondary_location loc
                        JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                        WHERE loc.naptan_code = :query";
                    $stmt = $db->prepare($sql);
                    $stmt->bindParam(':query', $query);
                    if ($stmt->execute()) {
                        $row = $stmt->fetch();
                        if ($row) {
                            $matches[] = array(
                                'id' => $row['generic_id'],
                                'short_code' => $row['short_code'],
                                'name' => $row['name'],
                                'language' => $row['language'],
                                'bearing' => $row['bearing'],
                                'lat' => $row['latitude'],
                                'long' => $row['longitude'],
                                'change' => $row['min_interchange'],
                                'tz' => $row['loc_timezone'],
                                'icon' => $row['icon'] ?? false,
                                'show_code' => $row['show_code'],
                                'priority' => $row['display_priority']
                            );

                            return $matches;    // If NaPTAN matches, we only want one result
                        }
                    }
                } else if (strlen($query) === self::GB_NR_CRS_CODE_LENGTH && (!isset($realm) || $realm === "all" || $realm === "gb.nr")) {
                    // Put CRS match at the top if 3 characters supplied
                    $sql = "SELECT loc.generic_id, locname.name, locname.language, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, 0 AS display_priority
                        FROM ap_primary_location loc
                        JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                        WHERE loc.short_code = :query";
                    $stmt = $db->prepare($sql);
                    $stmt->bindParam(':query', $query);
                    if ($stmt->execute()) {
                        $row = $stmt->fetch();
                        if ($row) {
                            $matches[] = array(
                                'id' => $row['generic_id'],
                                'short_code' => $row['short_code'],
                                'name' => $row['name'],
                                'language' => $row['language'],
                                'lat' => $row['latitude'],
                                'long' => $row['longitude'],
                                'change' => $row['min_interchange'],
                                'tz' => $row['loc_timezone'],
                                'icon' => $row['icon'] ?? false,
                                'show_code' => $row['show_code'],
                                'priority' => $row['display_priority']
                            );
                        }
                    }
                }

                // Get substring matches
                // FIXME LIKE didn't work with a bound parameter. Use this until a proper fix is found
                $sql = "SELECT * FROM (SELECT loc.generic_id, locname.name, locname.language, NULL AS bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, loc.display_priority
                    FROM ap_primary_location loc
                    JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                    WHERE locname.name LIKE '%" . SQLite3::escapeString($query) . "%'";
                if (strlen($query) >= 4) {
                    $sql .= " UNION ALL 
                    SELECT loc.generic_id, name || ', ' || locality AS name, locname.language, loc.bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, loc.display_priority
                    FROM ap_secondary_location loc
                    JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                    WHERE locname.name LIKE '%" . SQLite3::escapeString($query) . "%'"; // OR soundex_name = SOUNDEX('" . $query . "')
                }
                $sql .= ") ORDER BY display_priority, ((:lat - latitude) * (:lat - latitude)) + ((:long - longitude) * (:long - longitude)) ASC LIMIT 25";
                $stmt = $db->prepare($sql);
                #$likeQuery = '%' . $query . '%';
                #$stmt->bindParam(':query', $query);
                #$stmt->bindParam(':likeQuery', $likeQuery, SQLITE3_TEXT);
                if ($lat && $long) {
                    $stmt->bindParam(':lat', $lat);
                    $stmt->bindParam(':long', $long);
                } else {
                    $stmt->bindValue(':lat', NULL);
                    $stmt->bindValue(':long', NULL);
                }
                if ($stmt->execute()) {
                    foreach ($stmt->fetchAll() as $row) {
                        if (!in_array($row['generic_id'], array_column($matches, 'id')) && (!isset($realm) || $realm === "all" || strpos($row['generic_id'], $realm) === 0)) {
                            $matches[] = array(
                                'id' => $row['generic_id'],
                                'short_code' => $row['short_code'],
                                'name' => $row['name'],
                                'language' => $row['language'],
                                'bearing' => $row['bearing'],
                                'lat' => $row['latitude'],
                                'long' => $row['longitude'],
                                'change' => $row['min_interchange'],
                                'tz' => $row['loc_timezone'],
                                'icon' => $row['icon'] ?? false,
                                'show_code' => $row['show_code'],
                                'priority' => $row['display_priority']
                            );
                        }
                    }
                }
            } else if ($lat && $long) {
                // Get locations witin a box around the current lat/long.  0.1 degrees at 50N = +/- 7 km.
                $sql = "SELECT * FROM (SELECT loc.generic_id, locname.name, locname.language,NULL AS bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, loc.display_priority
                	FROM ap_primary_location loc
                    JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                	WHERE loc.latitude BETWEEN (:lat - 0.1) AND (:lat + 0.1)
                    AND loc.longitude BETWEEN (:long - 0.1) AND (:long + 0.1)
                    ORDER BY ((:lat - loc.latitude) * (:lat - loc.latitude)) + ((:long - loc.longitude) * (:long - loc.longitude)) ASC
                    LIMIT 5)
                        UNION ALL
                        SELECT * FROM (SELECT loc.generic_id, locname.name || ', ' || loc.locality AS name, locname.language, loc.bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, loc.icon, loc.show_code, loc.display_priority
                            FROM ap_secondary_location loc
                            JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                            WHERE loc.latitude BETWEEN (:lat - 0.1) AND (:lat + 0.1)
                            AND loc.longitude BETWEEN (:long - 0.1) AND (:long + 0.1)
                    ORDER BY loc.display_priority, ((:lat - loc.latitude) * (:lat - loc.latitude)) + ((:long - loc.longitude) * (:long - loc.longitude)) ASC
                    LIMIT 20)";
                $stmt = $db->prepare($sql);
                $stmt->bindParam(':lat', $lat);
                $stmt->bindParam(':long', $long);
                if ($stmt->execute()) {
                    foreach ($stmt->fetchAll() as $row) {
                        if (!isset($realm) || $realm === "all" || strpos($row['generic_id'], $realm) === 0) {
                            $matches[] = array(
                                'id' => $row['generic_id'],
                                'short_code' => $row['short_code'],
                                'name' => $row['name'],
                                'language' => $row['language'],
                                'bearing' => $row['bearing'],
                                'lat' => $row['latitude'],
                                'long' => $row['longitude'],
                                'change' => $row['min_interchange'],
                                'tz' => $row['loc_timezone'],
                                'icon' => $row['icon'] ?? false,
                                'show_code' => $row['show_code'],
                                'priority' => $row['display_priority']
                            );
                        }
                    }
                }
            }

            // Sort by proximity if provided
            if (count($matches) > 1 && $lat && $long) {

                uasort(
                    $matches,
                    function ($stop1, $stop2) {
                        global $lat, $long;

                        if (!isset($lat) || !isset($long) || !isset($stop1['lat']) || !isset($stop1['long']) || !isset($stop2['lat']) || !isset($stop2['long'])) {
                            return 1;  // Fall towards the bottom of the list
                        }

                        if ($stop1['priority'] != $stop2['priority']) {
                            return $stop1['priority'] <=> $stop2['priority'];
                        }

                        $dist1 = sqrt((($stop1['lat'] - $lat) ** 2) + (($stop1['long'] - $long) ** 2));
                        $dist2 = sqrt((($stop2['lat'] - $lat) ** 2) + (($stop2['long'] - $long) ** 2));

                        return $dist1 <=> $dist2;
                    }
                );
            }

            // And trim the results
            if (isset($maxResults)) {
                $matches = array_slice($matches, 0, $maxResults);
            }
        }

        return $matches;
    }

    public static function get_location(string $loc, string $language = self::DEFAULT_LANGUAGE)
    {
        if (isset(self::$locationCache[$loc . "|" . $language]))
            return self::$locationCache[$loc . "|" . $language];

        $db = LocationsDB::getConnection();
        if ($db) {
            $sql = "SELECT loc.generic_id, locname.name, locname.language, NULL AS bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, '' AS nearby_locs, loc.icon, loc.show_code
                FROM ap_primary_location loc
                JOIN ap_location_name locname ON locname.generic_id = loc.generic_id 
                WHERE loc.generic_id = :loc
                UNION SELECT loc.generic_id, locname.name || ', ' || loc.locality AS name, locname.language, loc.bearing, loc.short_code, loc.latitude, loc.longitude, loc.min_interchange, loc.loc_timezone, '' AS nearby_locs, loc.icon, loc.show_code
                FROM ap_secondary_location loc
                JOIN ap_location_name locname ON locname.generic_id = loc.generic_id
                WHERE loc.generic_id = :loc";
            $stmt = $db->prepare($sql);
            $stmt->bindParam(':loc', $loc);
            if ($stmt->execute()) {
                $locInfo = [];
                foreach ($stmt->fetchAll() as $row) {
                    $locInfo[$row['language'] ?? "none"] = array(
                        'id' => $row['generic_id'],
                        'short_code' => $row['short_code'],
                        'name' => $row['name'],
                        'language' => $row['language'],
                        'bearing' => $row['bearing'],
                        'lat' => $row['latitude'],
                        'long' => $row['longitude'],
                        'change' => $row['min_interchange'],
                        'tz' => $row['loc_timezone'],
                        'nearby_locs' => $row['nearby_locs'],
                        'icon' => $row['icon'] ?? false,
                        'show_code' => $row['show_code']
                    );
                }

                $result = $locInfo[$language] ?? $locInfo["none"] ?? $locInfo[self::DEFAULT_LANGUAGE] ?? false;
                self::$locationCache[$loc . "|" . $language] = $result;
                return $result;
            } else {
                return false;
            }
        }
        return false;
    }

    public static function GetLocationName(string $loc)
    {
        return self::get_location($loc)['name'] ?? $loc;
    }

    public static function GetLocationShortName(string $loc)
    {
        $locInfo = self::get_location($loc);
        if (!$locInfo) return $loc;

        if (strlen($locInfo['short_code']) === self::GB_NR_CRS_CODE_LENGTH)
            return $locInfo['short_code'];
        else {  // TODO Consider getting a good short name and storing in db.  At the moment locality and stop number/letters are also piled into name
            $words = explode(' ', $locInfo['name']);
            $shortName = '';
            foreach ($words as $word) {
                $shortName .= substr($word, 0, 1);
            }
            return $shortName;
        }
    }

    public static function get_associated_locations(string $loc)
    {
        $locInfo = self::get_location($loc);
        $assocLocs = $locInfo['nearby_locs'] ?? false;
        if ($assocLocs) {
            $assocLocs = explode(';', $assocLocs);
            foreach ($assocLocs as $assocLoc) {
                $assocLocsWithDistance[] = explode('|', $assocLoc);
            }
            return $assocLocsWithDistance;
        } else {
            return false;
        }
    }

    public static function get_peer_locations(string $loc)
    {
        $nearbyLocs = [];
        $db = LocationsDB::getConnection();
        if ($db) {
            $sql = "SELECT peer_id FROM ap_nearby_location WHERE generic_id = :loc";
            $stmt = $db->prepare($sql);
            $stmt->bindParam(':loc', $loc);
            if ($stmt->execute()) {
                foreach ($stmt->fetchAll() as $row) {
                    $nearbyLocs[] = $row['peer_id'];
                }
            }
        }
        return $nearbyLocs;
    }

    public static function get_timezone_for_loc(string $genericId)
    {
        global $GB_TIMEZONE;
        global $GB_TIMEZONE_NAME;

        $tz = $GB_TIMEZONE;

        $locInfo = self::get_location($genericId);
        if ($locInfo === false) return $tz;

        if ($locInfo['tz'] !== $GB_TIMEZONE_NAME) {
            $tz = new DateTimeZone($locInfo['tz']);
        }

        return $tz;
    }

    public static function get_min_change_time_for_loc(string $genericId)
    {
        $locInfo = self::get_location($genericId);
        return $locInfo['change'] ?? self::GB_NR_DEFAULT_CHANGE_TIME;   // Default, although this shouldn't happen
    }

    public static function get_distance(string $genericLoc1Id, string $genericLoc2Id)
    {
        $loc1Info = self::get_location($genericLoc1Id);
        $loc2Info = self::get_location($genericLoc2Id);
        if (isset($loc1Info['lat'], $loc2Info['lat'], $loc1Info['long'], $loc2Info['long'])) {
            return sqrt((($loc1Info['lat'] - $loc2Info['lat']) ** 2) + (($loc1Info['long'] - $loc2Info['long']) ** 2));
        };

        return 0;   // Default, although this shouldn't happen.  Perhaps return large default distance?
    }

    public static function get_walk_duration_minutes(string $genericLoc1Id, string $genericLoc2Id)
    {
        return self::get_walk_duration_minutes_from_distance(self::get_distance($genericLoc1Id, $genericLoc2Id));
    }

    public static function get_walk_duration_minutes_from_distance($distance)
    {
        // 3 m/s, in minutes
        return (int) (($distance / 3) / 60);
    }
}