<?php

namespace coordParser;

use Dotenv\Dotenv;
use PDO;

class CommimAddress
{

    private PDO $dbh;
    private string $tableName = 'commim_addresses';
    public int $lastRequestTotalCount = 0;

    private function query(string $query)
    {
        return $this->dbh->query($query);
    }

    private function execute(string $query)
    {
        $sth = $this->dbh->prepare($query);
        $sth->execute();

        return $sth->fetchAll(PDO::FETCH_CLASS);
    }

    private function updateFloatField(string $field, int $id, float $value): void
    {
        $this->query('UPDATE ' . $this->tableName . ' SET ' . $field . ' = ' . $value . ' WHERE obj_id = ' . $id . ';');

    }

    private function createQuery(array $select = [], array $filters = [], ?string $groupBy = null, ?bool $withCoordinates = true, ?bool $withPolygon = null)
    {
        $query = 'SELECT ' . (empty($select) ? '*' : join(', ', $select)) . ' FROM ' . $this->tableName . ' ';

        if (is_null($withCoordinates)) {
            $where = [];
        } elseif ($withCoordinates) {
            $where = ['((ng_longitude IS NOT NULL AND ng_latitude IS NOT NULL AND ng_longitude != 0 AND ng_latitude != 0) OR (longitude IS NOT NULL AND latitude IS NOT NULL))'];
        } else {
            $where = ['longitude IS NULL', 'latitude IS NULL'];
        }

        if (isset($withPolygon)) {
            $where[] = ['coords IS ' . ($withPolygon ? 'NOT' : '') . ' NULL'];
        }

        $bindValues = [];
        if (!empty($filters)) {
            $bindValue = 1;

            $this->addFilter($filters, $bindValue, $bindValues, $where, 'type', 'obj_type_name');
            $this->addFilter($filters, $bindValue, $bindValues, $where, 'pravo');
            $this->addFilter($filters, $bindValue, $bindValues, $where, 'encumbrance_name');
            $this->addFilter($filters, $bindValue, $bindValues, $where, 'purpose_name');
            $this->addFilter($filters, $bindValue, $bindValues, $where, 'minArea', 'obj_area', '>=');
            $this->addFilter($filters, $bindValue, $bindValues, $where, 'maxArea', 'obj_area', '<=');
        }

        if (!empty($where)) {
            $query .= 'WHERE ' . join(' AND ', $where);
        }

        if (!empty($groupBy)) {
            $query .= ' GROUP BY ' . $groupBy;
        }

        $totalCountQuery = preg_replace('/SELECT .* FROM/', 'SELECT COUNT(*) AS count FROM', $query);
        $totalCountQuery .= ';';
        $sth = $this->dbh->prepare($totalCountQuery);
        foreach ($bindValues as $bindValue => $value) {
            $sth->bindValue($bindValue, $value);
        }
        $sth->execute();
        $this->lastRequestTotalCount = $sth->fetchAll(PDO::FETCH_COLUMN)[0];

        $query .= ' LIMIT 1000';

        $query .= ';';

        $sth = $this->dbh->prepare($query);
        foreach ($bindValues as $bindValue => $value) {
            $sth->bindValue($bindValue, $value);
        }
        $sth->execute();

        return $sth->fetchAll(PDO::FETCH_CLASS);
    }

    public function __construct()
    {
        $dotenv = Dotenv::createImmutable(dirname(__DIR__));
        $dotenv->load();

        $dotenv->required(['DB_NAME', 'DB_USER', 'DB_PASSWORD', 'DB_HOST']);
        define('DB_NAME', $_ENV['DB_NAME']);
        define('DB_USER', $_ENV['DB_USER']);
        define('DB_PASSWORD', $_ENV['DB_PASSWORD']);
        define('DB_HOST', $_ENV['DB_HOST']);

        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8;';

        $this->dbh = new PDO($dsn, DB_USER, DB_PASSWORD);
    }

    public function add(array $row)
    {
        $query = 'INSERT IGNORE INTO ' . $this->tableName . ' (' . join(', ', array_keys($row)) . ') ';
        $values = [];
        foreach (array_values($row) as $value) {
            if (is_string($value)) {
                $values[] = "'" . $value . "'";
            } elseif (is_bool($value)) {
                $values[] = (int) $value;
            } else {
                $values[] = $value;
            }
        }
        $query .= 'VALUES (' . join(', ', $values) . ');';

        return $this->query($query);
    }

    private function getFilter(string $field, array $filters = []): array
    {
        $result = $this->createQuery([$field], $filters, $field);

        return array_map(fn($item) => $item->$field, $result);
    }

    public function getFilters(array $filters = []): array
    {
        $types = $this->getFilter('obj_type_name', $filters);
        $pravo = $this->getFilter('pravo', $filters);
        $encumbranceName = $this->getFilter('encumbrance_name', $filters);
        $purposeName = $this->getFilter('purpose_name', $filters);

        $area = $this->createQuery(['MIN(obj_area) AS min', 'MAX(obj_area) AS max'], $filters);
        $areas = ['min' => (float) $area[0]->min, 'max' => (float) $area[0]->max];

        return compact('types', 'pravo', 'encumbranceName', 'purposeName', 'areas');
    }

    private function addFilter(array $filters, int &$bindValue, array &$bindValues, array &$where, string $field, ?string $tableField = null, string $operand = '='): void
    {
        if (!empty($filters[$field])) {
            $tableField ??= $field;
            $bindValues[$bindValue++] = $filters[$field];
            $where[] = $tableField . ' ' . $operand . ' ?';
        }
    }

    public function find(array $filters = []): array
    {
        return $this->createQuery(CommimAddressModel::$columns, $filters);
    }

    public function findWithOutCoordinates(array $filters = []): array
    {
        return $this->createQuery(CommimAddressModel::$columns, $filters, null, false);
    }

    public function findWithOutPolygon(array $filters = []): array
    {
        return $this->createQuery(CommimAddressModel::$columns, $filters, null, null, false);
    }

    /**
     * @param int $id
     *
     * @return CommimAddressModel
     */
    public function findOne(int $id)
    {
        $query = 'SELECT * FROM ' . $this->tableName . ' WHERE obj_id = ?;';
        $sth = $this->dbh->prepare($query);
        $sth->bindValue(1, $id);
        $sth->execute();

        return $sth->fetchObject();
    }

    public function updateChoice(int $id, bool $choice): void
    {
        $this->query('UPDATE ' . $this->tableName . ' SET choice = ' . intval($choice) . ' WHERE obj_id = ' . $id . ';');
    }

    public function updateCoordinates(int $id, float $longitude, float $latitude): void
    {
        $this->query('UPDATE ' . $this->tableName . ' SET longitude = ' . $longitude . ', latitude = ' . $latitude . ' WHERE obj_id = ' . $id . ';');
    }

    public function updateNextGisCoordinates(int $id, string $coords, float $longitude, float $latitude): void
    {
        $this->query('UPDATE ' . $this->tableName . ' SET coords = \'' . $coords . '\', ng_longitude = ' . $longitude . ', ng_latitude = ' . $latitude . ' WHERE obj_id = ' . $id . ';');
    }

    public function updateLongitude(int $id, float $value): void
    {
        $this->updateFloatField('longitude', $id, $value);
    }

    public function updateLatitude(int $id, float $value): void
    {
        $this->updateFloatField('latitude', $id, $value);
    }

}