<?php
require_once ('Database.php');
require_once ('FacilityData.php');

class FacilityDataSet {
    protected $_dbHandle, $_dbInstance;

    public function __construct() {
        $this->_dbInstance = Database::getInstance();
        $this->_dbHandle = $this->_dbInstance->getDbConnection();
    }

    /**
     * @param $data
     * @return bool
     */
    public function addFacility($data) : bool {
        $sqlQuery = "
            INSERT INTO ecoFacilities 
                (title, 
                 category, 
                 description, 
                 houseNumber, 
                 streetName, 
                 county, 
                 town, 
                 postcode, 
                 lng, 
                 lat, 
                 contributor)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ;";
        $stmt = $this->_dbHandle->prepare($sqlQuery);
        // Ensures only one value is returned per column name
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);

        // Initialize index for binding
        $bindIndex = 1;

        // Bind other filters
        for ($i = 1; $i <= 8; $i++) { // Assuming 8 other filters
            $value = !empty($data[$i]) ? "%" . $data[$i] . "%" : "%";
            $stmt->bindValue($bindIndex++, $value, \PDO::PARAM_STR);
        }
        return !($stmt->rowCount());
    }

    public function deleteFacility($id) : bool {
        $sqlQuery = "DELETE FROM ecoFacilities WHERE id = ?";
        $stmt = $this->_dbHandle->prepare($sqlQuery);
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);
        $stmt->bindValue(1, $id, \PDO::PARAM_INT);
        $stmt->execute();
        return !($stmt->rowCount() == 0);
    }

    /**
     * @param $filterArray
     * @param $sortArray
     * @return array
     * Function to allow fetching of facility data. Data objects are created and held in an array
     * Count of rows for pagination returned alongside data objects.
     */
    public function fetchAll($filterArray, $sortArray): array
    {
        $direction = '';
        // Set direction, if not found in array, set to ascending.
        (in_array('desc', $sortArray)) ? $direction = 'DESC' : $direction = 'ASC';
        // Default to title
        // Note: I am very sorry, i am well aware this is horrible, im running out of time
        $sortBy = 1;
        switch (array_search('desc', $sortArray) ?? array_search('asc', $sortArray)) {
            case (0) :
                $sortBy = 'ecoFacilityStatus.statusComment';
                break;
            case (1) :
                $sortBy = 'ecoFacilities.title';
                break;
            case (2) :
                $sortBy = 'ecoCategories.name';
                break;
            case (3) :
                $sortBy = 'ecoFacilities.description';
                break;
            case (4) :
                $sortBy = 'ecoFacilities.streetName';
                break;
            case (5) :
                $sortBy = 'ecoFacilities.county';
                break;
            case (6) :
                $sortBy = 'ecoFacilities.town';
                break;
            case (7) :
                $sortBy = 'ecoFacilities.postcode';
                break;
            case (8) :
                $sortBy = 'ecoUser.username';
                break;
        }

        /**
         * COUNT(DISTINCT ecoFacilities.id) required due to multiple status comments possible.
         */
        $sqlCount = "SELECT COUNT(DISTINCT ecoFacilities.id) AS total FROM ecoFacilities";

        /**
         * DISTINCT used again for prior reasoning, although data is handled properly regardless later.
         * GROUP_CONCAT is used to handle multiple status comments under one facility. Without this, DISTINCT
         * drops the additional comment.
         */
        $sqlData = "SELECT DISTINCT ecoFacilities.id, 
                        title, 
                        GROUP_CONCAT(ecoFacilityStatus.statusComment, ', ') AS status, 
                        ecoCategories.name AS category, 
                        description, 
                        houseNumber, 
                        streetName, 
                        county, 
                        town, 
                        postcode, 
                        lng, 
                        lat, 
                        ecoUser.username AS contributor 
                    FROM ecoFacilities";

        /**
         * ? Parameters used here over named parameters so logic can be modular, more
         * columns can be added in the future
         */
        $sqlWhere = "
                    LEFT JOIN ecoCategories ON ecoCategories.id = ecoFacilities.category
                    LEFT JOIN ecoUser ON ecoUser.id = ecoFacilities.contributor
                    LEFT JOIN ecoFacilityStatus ON ecoFacilityStatus.facilityid = ecoFacilities.id
                    WHERE (ecoFacilityStatus.statusComment LIKE ? OR ? IS NULL)
                      AND ecoFacilities.title LIKE ?
                      AND ecoCategories.name LIKE ?
                      AND ecoFacilities.description LIKE ?
                      AND ecoFacilities.streetName LIKE ?
                      AND ecoFacilities.county LIKE ?
                      AND ecoFacilities.town LIKE ?
                      AND ecoFacilities.postcode LIKE ?
                      AND ecoUser.username LIKE ?
                    ";

        /**
         * GROUP BY required to ensure status comments are displayed under the same ID
         * Named parameters used here for prior reasoning, columns can be added above without
         * effecting the bindIndex.
         * I unfortunately HAVE to do the ORDER BY statement like this, since PDO doesn't allow
         * binding of column names to placeholders for some reason. I could have used the column
         * order and passed an integer, but its too much hassle and I have enough sanitisation,
         * this is fine.
         */
        $sqlLimits = "
                    GROUP BY ecoFacilities.id, ecoFacilities.title, ecoCategories.name, ecoFacilities.description, ecoFacilities.streetName,
                    ecoFacilities.county, ecoFacilities.town, ecoFacilities.postcode, ecoUser.username
                    ORDER BY {$sortBy} {$direction}
                    ;";

        // Concatenate query snippets for data and row count
        $dataQuery = $sqlData . $sqlWhere . $sqlLimits;
        $countQuery = $sqlCount . $sqlWhere . ";";

        // Prepare, bind and execute data query
        $stmt = $this->populateFields($dataQuery, $filterArray, $sortBy, $direction);
        $stmt->execute();

        // Create data objects
        $dataSet = [];
        while ($row = $stmt->fetch()) {
            $dataSet[] = new FacilityData($row);
        }

        // Prepare, bind then execute count query
        $stmt = $this->populateFields($countQuery, $filterArray, null, null);
        $stmt->execute();
        $totalCount = $stmt->fetch()['total'];

        return [
            'dataset' => $dataSet,
            'count' => $totalCount
            ];
    }

    /**
     * @param $sqlQuery
     * @param $filterArray
     * @param $sortBy
     * @param $direction
     * @return false|PDOStatement
     * Function for fetchAll() to de-dupe code. Performs binding on PDO statements to facilitate
     * filtering of facilities. Returns a bound PDO statement.
     */
    private function populateFields($sqlQuery, $filterArray, $sortBy, $direction)
    {
        $stmt = $this->_dbHandle->prepare($sqlQuery);
        // Ensures only one value is returned per column name
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);

        // Initialize index for binding
        $bindIndex = 1;

        // Bind statusComment filter, required due to comments not being so.
        $statusComment = !empty($filterArray[0]) ? "%" . $filterArray[0] . "%" : null;
        $stmt->bindValue($bindIndex++, $statusComment ?? "%", \PDO::PARAM_STR); // First ?
        $stmt->bindValue($bindIndex++, $statusComment, $statusComment === null ? \PDO::PARAM_NULL : \PDO::PARAM_STR); // Second ?
        // So i worked on trying to get this to work for 30 minutes and it turns out you
        // can never bind column name values to placeholders, and must use column orders
        // as integers..... what
//        if(isset($sortBy) && isset($direction)) {
//            $stmt->bindValue(':sortBy', $sortBy, \PDO::PARAM_STR);
//            $stmt->bindValue(':direction', $direction, \PDO::PARAM_STR);
//        }

        // Bind other filters
        for ($i = 1; $i <= 8; $i++) { // Assuming 8 other filters
            $value = !empty($filterArray[$i]) ? "%" . $filterArray[$i] . "%" : "%";
            print_r($i . " = " . $value . "|");
            $stmt->bindValue($bindIndex++, $value, \PDO::PARAM_STR);
        }
        return $stmt;
    }

    // UNUSED REPLACED
    public function setFilterUri($term, $category)
    {
        $uri = $_SERVER['REQUEST_URI'];
        $uriComp = parse_url($uri);
        $params = [];

        // Parse existing query parameters
        if (isset($uriComp['query'])) {
            parse_str($uriComp['query'], $params);
        } else {
            $params = array();
        }
        // Avoid unnecessary redirection if the filter is already correct
        if ((isset($params['category']) && $params['category'] === $category) && (isset($params['term']) && $params['term'] === $term)) {
            exit; // Do nothing if filter already applied
        }

        // Update the 'page' parameter
        $params['category'] = $category;
        $params['term'] = $term;

        // Rebuild the query string
        $newUri = http_build_query($params);
        var_dump($newUri);

        // Redirect to the updated URI
        // Use the current path or root
        return
            [
                'newUri' => $newUri,
                'path' => $uriComp['path'] ?? '/'
            ];    }

}