<?php
namespace FleekDash\Database\Core;

defined('ABSPATH') || exit;

/**
 * Table Builder
 *
 * Fluent API for building database tables with WordPress dbDelta
 */
class TableBuilder
{
    private $table;
    private $columns = [];
    private $indexes = [];
    private $primary = null;
    private $last_column = null;

    public function __construct(string $table)
    {
        global $wpdb;
        $this->table = $wpdb->prefix . $table;
    }

    // ===== Column Types =====

    public function id(string $name = 'id'): self
    {
        $this->primary = $name;
        $this->addColumn("{$name} bigint(20) unsigned NOT NULL AUTO_INCREMENT");
        return $this;
    }

    public function string(string $name, int $length = 255): self
    {
        $this->addColumn("{$name} varchar({$length}) NOT NULL");
        return $this;
    }

    public function text(string $name): self
    {
        $this->addColumn("{$name} text NOT NULL");
        return $this;
    }

    public function longText(string $name): self
    {
        $this->addColumn("{$name} longtext NOT NULL");
        return $this;
    }

    public function integer(string $name): self
    {
        $this->addColumn("{$name} int(11) NOT NULL");
        return $this;
    }

    public function bigInteger(string $name): self
    {
        $this->addColumn("{$name} bigint(20) NOT NULL");
        return $this;
    }

    public function unsignedBigInteger(string $name): self
    {
        $this->addColumn("{$name} bigint(20) unsigned NOT NULL");
        return $this;
    }

    public function decimal(string $name, int $precision = 10, int $scale = 2): self
    {
        $this->addColumn("{$name} decimal({$precision},{$scale}) NOT NULL");
        return $this;
    }

    public function boolean(string $name): self
    {
        $this->addColumn("{$name} tinyint(1) NOT NULL DEFAULT 0");
        return $this;
    }

    public function datetime(string $name): self
    {
        $this->addColumn("{$name} datetime NOT NULL");
        return $this;
    }

    public function date(string $name): self
    {
        $this->addColumn("{$name} date NOT NULL");
        return $this;
    }

    public function timestamp(string $name): self
    {
        $this->addColumn("{$name} timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
        return $this;
    }

    public function enum(string $name, array $values): self
    {
        $enumValues = "'" . implode("','", $values) . "'";
        $this->addColumn("{$name} enum({$enumValues}) NOT NULL");
        return $this;
    }

    public function json(string $name): self
    {
        $this->addColumn("{$name} longtext NOT NULL");
        return $this;
    }

    // ===== Column Modifiers =====

    public function nullable(): self
    {
        $this->columns[$this->last_column] = str_replace(
            ' NOT NULL',
            ' DEFAULT NULL',
            $this->columns[$this->last_column]
        );
        return $this;
    }

    public function default($value): self
    {
        if (is_string($value)) {
            $value = "'{$value}'";
        } elseif (is_bool($value)) {
            $value = $value ? '1' : '0';
        } elseif (is_null($value)) {
            $value = 'NULL';
        }

        $this->columns[$this->last_column] .= " DEFAULT {$value}";
        return $this;
    }

    public function unsigned(): self
    {
        $this->columns[$this->last_column] = str_replace(
            ' NOT NULL',
            ' unsigned NOT NULL',
            $this->columns[$this->last_column]
        );
        return $this;
    }

    public function comment(string $comment): self
    {
        $comment = str_replace("'", "\\'", $comment);
        $this->columns[$this->last_column] .= " COMMENT '{$comment}'";
        return $this;
    }

    // ===== Special Methods =====

    public function timestamps(): self
    {
        $this->addColumn("created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP");
        $this->addColumn("updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        return $this;
    }

    // ===== Indexes =====

    public function index($columns, ?string $name = null): self
    {
        $columns = is_array($columns) ? $columns : [$columns];
        $name = $name ?: 'idx_' . implode('_', $columns);
        $columnList = implode(', ', $columns);

        $this->indexes[] = "KEY {$name} ({$columnList})";
        return $this;
    }

    public function unique($columns, ?string $name = null): self
    {
        $columns = is_array($columns) ? $columns : [$columns];
        $name = $name ?: 'uniq_' . implode('_', $columns);
        $columnList = implode(', ', $columns);

        $this->indexes[] = "UNIQUE KEY {$name} ({$columnList})";
        return $this;
    }

    // ===== Internal Methods =====

    private function addColumn(string $definition): void
    {
        $this->last_column = count($this->columns);
        $this->columns[] = $definition;
    }

    public function toSql(): string
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$this->table} (\n";
        $sql .= "  " . implode(",\n  ", $this->columns);

        if ($this->primary) {
            $sql .= ",\n  PRIMARY KEY ({$this->primary})";
        }

        if (!empty($this->indexes)) {
            $sql .= ",\n  " . implode(",\n  ", $this->indexes);
        }

        $sql .= "\n) {$charset_collate};";

        return $sql;
    }
}
