<?php

namespace CliqThemes\ORM\Schema;

use CliqThemes\ORM\Database\Connection;

/**
 * Declarative builder used by {@see SchemaManager} to generate table schemas.
 */
class TableBlueprint
{
    protected Connection $connection;

    protected string $table;

    protected array $columns = [];

    protected array $indexes = [];

    protected string $charsetCollate = '';

    protected string $engine = 'InnoDB';

    /**
     * Create a new table blueprint for the given table name.
     */
    public function __construct(Connection $connection, string $table)
    {
        $this->connection = $connection;
        $this->table = $table;
        $this->charsetCollate = $this->inferCharsetCollate();
    }

    /**
     * Add an auto-incrementing big integer primary key column.
     */
    public function increments(string $column = 'id'): self
    {
        return $this->addColumn('bigint', $column, 20, [
            'unsigned' => true,
            'auto_increment' => true,
            'primary' => true,
        ]);
    }

    /**
     * Alias for {@see increments()}.
     */
    public function id(): self
    {
        return $this->increments();
    }

    /**
     * Add a VARCHAR column to the blueprint.
     */
    public function string(string $column, int $length = 255, array $options = []): self
    {
        return $this->addColumn('varchar', $column, $length, $options);
    }

    /**
     * Add a TEXT column to the blueprint.
     */
    public function text(string $column, array $options = []): self
    {
        return $this->addColumn('text', $column, null, $options);
    }

    /**
     * Add a LONGTEXT column to the blueprint.
     */
    public function longText(string $column, array $options = []): self
    {
        return $this->addColumn('longtext', $column, null, $options);
    }

    /**
     * Add an integer column with the given length.
     */
    public function integer(string $column, int $length = 11, array $options = []): self
    {
        return $this->addColumn('int', $column, $length, $options);
    }

    /**
     * Add a big integer column with the given length.
     */
    public function bigInteger(string $column, int $length = 20, array $options = []): self
    {
        return $this->addColumn('bigint', $column, $length, $options);
    }

    /**
     * Add a decimal column with custom precision and scale.
     */
    public function decimal(string $column, int $precision = 10, int $scale = 0, array $options = []): self
    {
        $options['precision'] = $precision;
        $options['scale'] = $scale;

        return $this->addColumn('decimal', $column, null, $options);
    }

    /**
     * Add a boolean column using a tinyint under the hood.
     */
    public function boolean(string $column, array $options = []): self
    {
        $options['default'] = $options['default'] ?? 0;

        return $this->addColumn('tinyint', $column, 1, $options);
    }

    /**
     * Add a spatial POINT column. SPATIAL indexes require the column to be
     * NOT NULL, so this column is non-nullable unless explicitly overridden.
     */
    public function point(string $column, array $options = []): self
    {
        return $this->addColumn('point', $column, null, $options);
    }

    /**
     * Add a STORED generated POINT column derived from existing latitude and
     * longitude columns (SRID 4326). Because the value is computed by the
     * database it works transparently with ORM inserts and is NOT NULL, so a
     * SPATIAL index can be declared over it. The referenced columns must be
     * declared earlier in the blueprint.
     */
    public function pointGeneratedFrom(string $column, string $latitudeColumn = 'latitude', string $longitudeColumn = 'longitude'): self
    {
        $expression = sprintf('ST_SRID(POINT(`%s`, `%s`), 4326)', $longitudeColumn, $latitudeColumn);

        return $this->addColumn('point_generated', $column, null, ['expression' => $expression]);
    }

    /**
     * Add a DATETIME column to the blueprint.
     */
    public function datetime(string $column, array $options = []): self
    {
        return $this->addColumn('datetime', $column, null, $options);
    }

    /**
     * Add a TIMESTAMP column to the blueprint.
     */
    public function timestamp(string $column, array $options = []): self
    {
        return $this->addColumn('timestamp', $column, null, $options);
    }

    /**
     * Add created_at and updated_at datetime columns.
     */
    public function timestamps(): self
    {
        $this->datetime('created_at', ['nullable' => false]);
        $this->datetime('updated_at', ['nullable' => false]);

        return $this;
    }

    /**
     * Add a nullable deleted_at column to support soft deletes.
     */
    public function softDeletes(): self
    {
        $this->datetime('deleted_at', ['nullable' => true]);

        return $this;
    }

    /**
     * Declare a primary key constraint for the given columns.
     *
     * @param string[] $columns
     */
    public function primary(array $columns, ?string $name = null): self
    {
        $this->indexes[] = [
            'type' => 'primary',
            'name' => $name,
            'columns' => (array) $columns,
        ];

        return $this;
    }

    /**
     * Declare a unique index for the given columns.
     *
     * @param string[] $columns
     */
    public function unique(array $columns, ?string $name = null): self
    {
        $this->indexes[] = [
            'type' => 'unique',
            'name' => $name,
            'columns' => (array) $columns,
        ];

        return $this;
    }

    /**
     * Declare a non-unique index for the given columns.
     *
     * @param string[] $columns
     */
    public function index(array $columns, ?string $name = null): self
    {
        $this->indexes[] = [
            'type' => 'index',
            'name' => $name,
            'columns' => (array) $columns,
        ];

        return $this;
    }

    /**
     * Declare a SPATIAL index for the given POINT/geometry columns.
     *
     * @param string[]|string $columns
     */
    public function spatial(array|string $columns, ?string $name = null): self
    {
        $this->indexes[] = [
            'type' => 'spatial',
            'name' => $name,
            'columns' => (array) $columns,
        ];

        return $this;
    }

    /**
     * Whether this blueprint declares POINT columns or SPATIAL indexes, which
     * cannot be created reliably through dbDelta and require a direct CREATE.
     */
    public function hasSpatialFeatures(): bool
    {
        foreach ($this->columns as $column) {
            if (in_array($column['type'] ?? '', ['point', 'point_generated'], true)) {
                return true;
            }
        }

        foreach ($this->indexes as $index) {
            if (($index['type'] ?? '') === 'spatial') {
                return true;
            }
        }

        return false;
    }

    /**
     * Specify the storage engine to use for the table.
     */
    public function engine(string $engine): self
    {
        $this->engine = $engine;

        return $this;
    }

    /**
     * Get the un-prefixed table name the blueprint targets.
     */
    public function getTable(): string
    {
        return $this->table;
    }

    /**
     * Compile the blueprint into a SQL statement consumable by dbDelta.
     */
    public function toSql(bool $ifNotExists = false): string
    {
        $columnsSql = array_map([$this, 'compileColumn'], $this->columns);
        $indexesSql = array_map([$this, 'compileIndex'], $this->indexes);

        $columns = array_filter(array_merge($columnsSql, $indexesSql));

        $table = $this->connection->table($this->table);

        $create = $ifNotExists ? 'CREATE TABLE IF NOT EXISTS' : 'CREATE TABLE';
        $sql = "{$create} {$table} (" . PHP_EOL . '  ' . implode(',' . PHP_EOL . '  ', $columns) . PHP_EOL . ')';

        if ($this->engine) {
            $sql .= " ENGINE={$this->engine}";
        }

        if ($this->charsetCollate) {
            $sql .= ' ' . $this->charsetCollate;
        }

        $sql .= ';';

        return $sql;
    }

    /**
     * Register a column definition with the blueprint.
     *
     * @param array<string, mixed> $options
     */
    protected function addColumn(string $type, string $column, ?int $length = null, array $options = []): self
    {
        $this->columns[] = compact('type', 'column', 'length', 'options');

        if (($options['primary'] ?? false) === true) {
            $this->primary([$column]);
        }

        return $this;
    }

    /**
     * Convert a column definition into a SQL fragment.
     *
     * @param array{type: string, column: string, length: int|null, options: array<string, mixed>} $column
     */
    protected function compileColumn(array $column): string
    {
        if ($column['type'] === 'point_generated') {
            return sprintf(
                '`%s` POINT GENERATED ALWAYS AS (%s) STORED NOT NULL',
                $column['column'],
                $column['options']['expression']
            );
        }

        $sql = '`' . $column['column'] . '` ' . strtoupper($column['type']);

        if ($column['type'] === 'decimal') {
            $precision = $column['options']['precision'] ?? 10;
            $scale = $column['options']['scale'] ?? 0;
            $sql .= "({$precision},{$scale})";
        } elseif ($column['length']) {
            $sql .= '(' . $column['length'] . ')';
        }

        if (!empty($column['options']['unsigned'])) {
            $sql .= ' UNSIGNED';
        }

        $sql .= empty($column['options']['nullable']) ? ' NOT NULL' : ' NULL';

        if (array_key_exists('default', $column['options'])) {
            $default = $column['options']['default'];

            if (is_numeric($default)) {
                $sql .= ' DEFAULT ' . $default;
            } elseif (is_string($default) && strtoupper($default) === 'CURRENT_TIMESTAMP') {
                $sql .= ' DEFAULT CURRENT_TIMESTAMP';
            } else {
                $sql .= " DEFAULT '{$default}'";
            }
        }

        if (!empty($column['options']['auto_increment'])) {
            $sql .= ' AUTO_INCREMENT';
        }

        return $sql;
    }

    /**
     * Convert an index definition into a SQL fragment.
     *
     * @param array{type: string, name: string|null, columns: string[]} $index
     */
    protected function compileIndex(array $index): string
    {
        $columns = implode('`, `', $index['columns']);

        return match ($index['type']) {
            'primary' => "PRIMARY KEY (`{$columns}`)",
            'unique' => sprintf('UNIQUE KEY `%s` (`%s`)', $index['name'] ?? $this->indexName('unique', $index['columns']), $columns),
            'spatial' => sprintf('SPATIAL KEY `%s` (`%s`)', $index['name'] ?? $this->indexName('spatial', $index['columns']), $columns),
            default => sprintf('KEY `%s` (`%s`)', $index['name'] ?? $this->indexName('index', $index['columns']), $columns),
        };
    }

    /**
     * Generate an index name based on its type and columns.
     *
     * @param string[] $columns
     */
    protected function indexName(string $type, array $columns): string
    {
        return implode('_', array_merge([$type], $columns));
    }

    /**
     * Infer the charset and collation SQL suffix from the connection settings.
     */
    protected function inferCharsetCollate(): string
    {
        $wpdb = $this->connection->getWpdb();
        $charset = $wpdb->charset ? "DEFAULT CHARSET={$wpdb->charset}" : '';
        $collate = $wpdb->collate ? "COLLATE {$wpdb->collate}" : '';

        return trim($charset . ' ' . $collate);
    }
}

