<?php
declare(strict_types=1);

namespace mystic\forum;

use DateTime;
use DateTimeImmutable;
use mystic\forum\attributes\Column;
use mystic\forum\attributes\DefaultValue;
use mystic\forum\attributes\NotNull;
use mystic\forum\attributes\PrimaryKey;
use mystic\forum\attributes\References;
use mystic\forum\attributes\Table;
use mystic\forum\attributes\Unique;
use mystic\forum\exceptions\DatabaseConnectionException;
use mystic\forum\orm\Entity;
use mystic\forum\utils\ArrayUtils;
use mystic\forum\utils\StringUtils;
use PgSql\Connection;
use PgSql\Result;
use ReflectionClass;
use ReflectionNamedType;
use ReflectionProperty;

class Database {
    private Connection $connection;
    private int $queryCount = 0;

    private const PRIMARY_KEY = 0b0000_0001;
    private const NOT_NULL    = 0b0000_0010;
    private const REFERENCES  = 0b0000_0100;
    private const UNIQUE      = 0b0000_1000;
    private const DEFAULT     = 0b0001_0000;

    protected static function encodeBinary(string $bin): string {
        return "\\x" . bin2hex($bin);
    }

    protected static function decodeBinary(string $enc): string {
        return hex2bin(substr($enc, 2));
    }

    public function getQueryCount(): int {
        return $this->queryCount;
    }

    protected function queryParams(string $query, array $params): Result|false {
        ++$this->queryCount;
        return \pg_query_params  ($this->connection, $query, $params);
    }

    protected function query(string $query): Result|false {
        ++$this->queryCount;
        return \pg_query  ($this->connection, $query);
    }

    public static function generateId(int $length = 64): string {
        static $charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_";
        static $charsetLength = strlen($charset);
        $buf = "";
        for ($i = 0; $i < $length; ++$i)
            $buf .= $charset[random_int(0, $charsetLength - 1)];
        return $buf;
    }

    private static function exception_error_handler($errno, $errstr, $errfile, $errline ) {
        throw new \ErrorException(html_entity_decode($errstr), $errno, 0, $errfile, $errline);
    }

    public function __construct(string $connectionString) {
        try {
            set_error_handler(self::exception_error_handler(...));
            $conn = \pg_connect($connectionString);
            if ($conn !== false)
                $this->connection = $conn;
        } catch (\ErrorException $ex) {
            throw new DatabaseConnectionException($ex->getMessage(), $ex->getCode(), $ex);
        } finally {
            restore_error_handler();
        }
    }

    public static function getConnectionString(string $host, string $user, string $password, string $dbname, int $port = 5432): string {
        return self::buildConnectionString([
            "host" => $host,
            "dbname" => $dbname,
            "user" => $user,
            "password" => $password,
            "port" => strval($port),
        ]);
    }

    public static function buildConnectionString(array $params): string {
        $items = [];
        foreach ($params as $key => $value) {
            $items []= $key . "='" . self::escapeConnectionStringValue($value) . "'";
        }
        return implode(" ", $items);
    }

    private static function escapeConnectionStringValue(string $value): string {
        return str_replace([
            '\\',
            "'",
        ], [
            '\\\\',
            "\\'",
        ], $value);
    }

    private static function getTableName(string $entityClassName): string {
        $reflClass = new ReflectionClass($entityClassName);
        $attrs = $reflClass->getAttributes(Table::class);
        foreach ($attrs as $attr)
            return $attr->newInstance()->tableName;
        return "public." . StringUtils::camelToSnake($reflClass->getShortName());
    }

    private static function getColumnName(ReflectionProperty &$prop): string {
        $attrs = $prop->getAttributes(Column::class);
        foreach ($attrs as $attr) {
            $name = $attr->newInstance()->columnName;
            if ($name !== null)
                return $name;
        }
        return StringUtils::camelToSnake($prop->getName());
    }

    private static function getColumnType(ReflectionProperty &$prop): string {
        $attrs = $prop->getAttributes(Column::class);
        foreach ($attrs as $attr) {
            $type = $attr->newInstance()->columnType;
            if ($type !== null)
                return $type;
        }
        $type = $prop->getType();
        if (!($type instanceof ReflectionNamedType))
            throw new \RuntimeException("Union types or intersection types cannot be converted to a "
                . "database type. Please specify one manually using the #[Column] attribute!");
        $typeName = $type->getName();
        if (!$type->isBuiltin() && $typeName !== \DateTime::class && $typeName !== \DateTimeImmutable::class)
            throw new \RuntimeException("User-defined types cannot be converted to a "
                . "database type. Please specify one manually using the #[Column] attribute!");
        switch ($typeName) {
            case \DateTime::class:
            case \DateTimeImmutable::class:
                return "timestamp with time zone";
            case "true":
            case "false":
            case "bool":
                return "boolean";
            case "float":
                return "double precision";
            case "int":
                return "bigint";
            case "string":
            case "null":
                return "text";
            case "object":
            case "array":
                return "jsonb";
            case "iterable":
            case "callable":
            case "mixed":
            case "never":
            case "void":
            default:
                throw new \RuntimeException("The type \"$typeName\" cannot be stored in the database");
        }
    }

    private static function getColumnDefinitions(string $entityClassName): array {
        $statements = [];

        // TODO Foreign keys

        $rflEntity = new \ReflectionClass($entityClassName);
        $cols = self::getColumns($rflEntity);
        foreach ($cols as $colName => $colInfo) {
            $rflProp = new \ReflectionProperty($entityClassName, $colInfo["propertyName"]);
            $colType = self::getColumnType($rflProp);
            $statement = "$colName $colType";

            if (($colInfo["flags"] & self::DEFAULT) !== 0) {
                $statement .= " DEFAULT " . $colInfo["defaultValue"];
            }

            if (($colInfo["flags"] & self::UNIQUE) !== 0) {
                $statement .= " UNIQUE";
            }

            if (($colInfo["flags"] & self::NOT_NULL) !== 0) {
                $statement .= " NOT NULL";
            }

            if (($colInfo["flags"] & self::PRIMARY_KEY) !== 0) {
                $statement .= " PRIMARY KEY";
            }

            if (($colInfo["flags"] & self::REFERENCES) !== 0) {
                $statement .= " REFERENCES " . $colInfo["reference"];
            }

            $statements []= $statement;
        }

        return $statements;
    }

    private static function getColumns(ReflectionClass &$rflEntity): array {
        return ArrayUtils::assocFromPairs(array_map(function(ReflectionProperty $prop): array {
            $flags = 0;
            $attrs = $prop->getAttributes();
            $reference = "";
            $defaultValue = null;
            foreach ($attrs as $attr) {
                $attrName = $attr->getName();
                if ($attrName === PrimaryKey::class) {
                    $flags |= self::PRIMARY_KEY;
                } elseif ($attrName === NotNull::class) {
                    $flags |= self::NOT_NULL;
                } elseif ($attrName === Unique::class) {
                    $flags |= self::UNIQUE;
                } elseif ($attrName === References::class) {
                    $flags |= self::REFERENCES;
                    $reference = $attr->newInstance()->__toString();
                } elseif ($attrName === DefaultValue::class) {
                    $flags |= self::DEFAULT;
                    $defaultValue = $attr->newInstance()->defaultValue;
                }
            }

            return [self::getColumnName($prop), [
                "propertyName" => $prop->getName(),
                "flags" => $flags,
                "reference" => $reference,
                "defaultValue" => $defaultValue,
                "columnType" => self::getColumnType($prop),
            ]];
        }, $rflEntity->getProperties(ReflectionProperty::IS_PUBLIC)));
    }

    private static function getPrimaryKeyColumn(array &$cols): ?string {
        foreach ($cols as $col => ["flags" => $flags])
            if (($flags & self::PRIMARY_KEY) !== 0)
                return $col;
        return null;
    }

    private static function stringifyValue(mixed $value, string $columnType): ?string {
        if ($columnType === "bytea" && is_string($value))
            return self::encodeBinary($value);
        elseif (is_null($value))
            return null;
        elseif (is_bool($value))
            return $value ? "true" : "false";
        elseif (is_scalar($value))
            return strval($value);
        elseif (is_a($value, \DateTimeInterface::class))
            return $value->format("c");
        elseif (is_array($value) || is_object($value))
            return json_encode($value, JSON_UNESCAPED_SLASHES);
        else
            throw new \RuntimeException("Don't know how to stringify " . ((is_object($value) && !is_array($value)) ? get_class($value) : gettype($value)));
    }

    private static function assignValue(Entity &$entity, array $colProps, ?string $value): void {
        $propName = $colProps["propertyName"];
        $colType = $colProps["columnType"];
        $prop = new \ReflectionProperty($entity, $propName);

        $type = $prop->getType();
        $typedValue = null;
        if (!($type instanceof ReflectionNamedType))
            throw new \RuntimeException("Union types or intersection types cannot be converted to from a database type.");
        $typeName = $type->getName();
        if (!$type->isBuiltin() && $typeName !== \DateTime::class && $typeName !== \DateTimeImmutable::class)
            throw new \RuntimeException("User-defined types cannot be converted to from a database type.");
        if ($value !== null)
            switch ($typeName) {
                case \DateTime::class:
                    $typedValue = new \DateTime($value);
                    break;
                case \DateTimeImmutable::class:
                    $typedValue = new \DateTimeImmutable($value);
                    break;
                case "true":
                case "false":
                case "bool":
                    $typedValue = in_array(strtolower($value), ["true","t","yes","on","1"]) ? true : (in_array(strtolower($value), ["false","f","no","off","0"]) ? false : null);
                    break;
                case "float":
                    $typedValue = floatval($value);
                    break;
                case "int":
                    $typedValue = intval($value);
                    break;
                case "string":
                case "null":
                    if ($colType === "bytea") {
                        $typedValue = self::decodeBinary(strval($value));
                    } else {
                        $typedValue = strval($value);
                    }
                    break;
                case "object":
                    $typedValue = json_decode($value);
                    break;
                case "array":
                    $typedValue = json_decode($value, true);
                    break;
                case "iterable":
                case "callable":
                case "mixed":
                case "never":
                case "void":
                default:
                    throw new \RuntimeException("The type \"$typeName\" cannot be restored from the database");
            }
        $entity->{$propName} = $typedValue;
    }

    private static function getColumnValues(Entity &$entity, array &$cols): array {
        $values = [];

        foreach ($cols as $colInfo) {
            $values []= self::stringifyValue($entity->{$colInfo["propertyName"]} ?? null, $colInfo["columnType"]);
        }

        return $values;
    }

    public function insert(Entity &$entity): void {
        $entityClassName = get_class($entity);
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        $values = ArrayUtils::fill(fn($i) => null, count($cols));
        foreach (self::getColumnValues($entity, $cols) as $i => $value)
            $values[$i] = $value;
        $query = "INSERT INTO $tableName VALUES (" . implode(",", ArrayUtils::fill(fn($i) => "$" . ($i + 1), count($cols))) . ");";
        $result = $this->queryParams($query, $values);
        if ($result === false)
            throw new \RuntimeException("Insert failed: " . \pg_last_error($this->connection));
        \pg_free_result($result);
    }

    public function fetch(Entity &$entity): bool {
        $entityClassName = get_class($entity);
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        $primaryCol = self::getPrimaryKeyColumn($cols);
        if ($primaryCol === null)
            throw new \RuntimeException("Fetching an entity requires a primary key column to be specified");
        $query = "SELECT * FROM $tableName WHERE $primaryCol = \$1 LIMIT 1;";
        $result = $this->queryParams($query, [ $entity->{$cols[$primaryCol]["propertyName"]} ]);
        if ($result === false)
            throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection));
        $row = \pg_fetch_assoc($result);
        \pg_free_result($result);
        if ($row === false)
            return false;
        foreach ($cols as $colName => $colProps)
            self::assignValue($entity, $colProps, $row[$colName]);
        return true;
    }

    public function fetchWhere(Entity &$entity, string|array $columnNames): bool {
        $entityClassName = get_class($entity);
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        if (!is_array($columnNames)) {
            $columnNames = [ $columnNames ];
        }

        $whereClause = [];
        $columnValues = [];
        $count = 0;
        foreach ($columnNames as $columnName) {
            ++$count;
            if (!isset($cols[$columnName]))
                throw new \RuntimeException("Column $columnName does not exist!");
            $whereClause []= "$columnName = \$$count";
            $columnValues []= self::stringifyValue($entity->{$cols[$columnName]["propertyName"]}, $cols[$columnName]["columnType"]);
        }
        $whereClause = implode(" AND ", $whereClause);
        $query = "SELECT * FROM $tableName WHERE $whereClause LIMIT 1;";
        $result = $this->queryParams($query, $columnValues);
        if ($result === false)
            throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection));
        $row = \pg_fetch_assoc($result);
        \pg_free_result($result);
        if ($row === false)
            return false;
        foreach ($cols as $colName => $colProps)
            self::assignValue($entity, $colProps, $row[$colName]);
        return true;
    }

    public function fetchAll(string $entityClassName): array {
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        $query = "SELECT * FROM $tableName;";
        $result = $this->query($query);
        if ($result === false)
            throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection));
        $items = [];
        while (($row = \pg_fetch_assoc($result)) !== false) {
            $entity = new $entityClassName();
            foreach ($cols as $colName => $colProps)
                self::assignValue($entity, $colProps, $row[$colName]);
            $items []= $entity;
        }
        \pg_free_result($result);
        return $items;
    }

    public function fetchCustom(string $entityClassName, string $customQuery, ?array $customQueryParams = null): array {
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        $query = "SELECT * FROM $tableName $customQuery;";
        if ($customQueryParams === null)
            $result = $this->query($query);
        else
            $result = $this->queryParams($query, $customQueryParams);
        if ($result === false)
            throw new \RuntimeException("Fetch failed: " . \pg_last_error($this->connection));
        $items = [];
        while (($row = \pg_fetch_assoc($result)) !== false) {
            $entity = new $entityClassName();
            foreach ($cols as $colName => $colProps)
                self::assignValue($entity, $colProps, $row[$colName]);
            $items []= $entity;
        }
        \pg_free_result($result);
        return $items;
    }

    public function execCustomQuery(string $query, ?array $customQueryParams = null, ?string $entityClassName = null): array {
        if ($customQueryParams === null)
            $result = $this->query($query);
        else
            $result = $this->queryParams($query, $customQueryParams);
        if ($result === false)
            throw new \RuntimeException("Query failed: " . \pg_last_error($this->connection));
        $cols = null;
        if ($entityClassName !== null) {
            $reflClass = new ReflectionClass($entityClassName);
            $cols = self::getColumns($reflClass);
        }
        $rowsOrItems = [];
        while (($row = \pg_fetch_assoc($result)) !== false) {
            if ($entityClassName !== null) {
                $entity = new $entityClassName();
                foreach ($cols as $colName => $colProps)
                    self::assignValue($entity, $colProps, $row[$colName]);
                $rowsOrItems []= $entity;
            } else {
                $rowsOrItems []= $row;
            }
        }
        \pg_free_result($result);
        return $rowsOrItems;
    }

    public function delete(Entity &$entity): bool {
        $entityClassName = get_class($entity);
        $tableName = self::getTableName($entityClassName);
        $reflClass = new ReflectionClass($entityClassName);
        $cols = self::getColumns($reflClass);
        $primaryCol = self::getPrimaryKeyColumn($cols);
        if ($primaryCol === null)
            throw new \RuntimeException("Deleting an entity requires a primary key column to be specified");
        $query = "DELETE FROM $tableName WHERE $primaryCol = \$1;";
        $result = $this->queryParams($query, [ $entity->{$cols[$primaryCol]["propertyName"]} ]);
        if ($result === false)
            throw new \RuntimeException("Deletion failed: " . \pg_last_error($this->connection));
        $num_affected_rows = \pg_affected_rows($result);
        \pg_free_result($result);
        return $num_affected_rows >= 1;
    }

    public function update(Entity &$entity): bool {
        $tableName = self::getTableName(get_class($entity));
        $reflClass = new ReflectionClass($entity);
        $cols = self::getColumns($reflClass);
        $primaryCol = self::getPrimaryKeyColumn($cols);
        if ($primaryCol === null)
            throw new \RuntimeException("Updating an entity requires a primary key column to be specified");

        $filteredCols = array_values(array_filter(ArrayUtils::asPairs($cols), function($pair) {
            return ($pair[1]["flags"] & self::PRIMARY_KEY) === 0;
        }));
        // echo "<pre>";
        // var_dump($filteredCols);
        // exit;
        $qCols = [];
        for ($i = 0; $i < count($filteredCols); ++$i) {
            $qCols []= implode("=", [ $filteredCols[$i][0], '$' . ($i + 2) ]);
        }
        $colDef = implode(", ", $qCols);
        $query = "UPDATE $tableName SET $colDef WHERE $primaryCol = \$1;";
        //$theCols = ArrayUtils::assocFromPairs($filteredCols);
        $result = $this->queryParams($query, self::getColumnValues($entity, $cols));
        if ($result === false)
            throw new \RuntimeException("Update failed: " . \pg_last_error($this->connection));
        $num_affected_rows = \pg_affected_rows($result);
        \pg_free_result($result);
        return $num_affected_rows >= 1;
    }

    public function ensureTable(string $entityClassName): void {
        $tableName = self::getTableName($entityClassName);
        $colDefs = self::getColumnDefinitions($entityClassName);
        $query = "CREATE TABLE IF NOT EXISTS $tableName ();";
        foreach ($colDefs as $colDef)
            $query .= "ALTER TABLE $tableName ADD COLUMN IF NOT EXISTS $colDef;";
        $result = $this->query($query);
        if ($result === false)
            throw new \RuntimeException("Table creation failed: " . \pg_last_error($this->connection));
        \pg_free_result($result);
    }
}