<?php
// CoreFlux/core/Database/SchemaBuilder.php

namespace Core\Database;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Schema\Comparator;
use Exception;

class SchemaBuilder
{
    private Connection $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    /**
     * Checks if a table exists in the database.
     *
     * @param string $tableName The name of the table to check.
     * @return bool True if the table exists, false otherwise.
     */
    public function tableExists(string $tableName): bool
    {
        return $this->connection->getSchemaManager()->tablesExist([$tableName]);
    }

    /**
     * Creates a new table in the database based on a schema definition callback.
     *
     * @param string $tableName The name of the table to create (e.g., 'users').
     * @param \Closure $callback A closure that defines the table structure.
     * It receives (Schema $schema, Table $table).
     * @throws Exception If the table already exists or schema definition is invalid.
     */
    public function create(string $tableName, \Closure $callback): void
    {
        $schemaManager = $this->connection->getSchemaManager();

        if ($schemaManager->tablesExist([$tableName])) {
            echo "Info: Table '{$tableName}' already exists. Skipping creation.\n";
            return;
        }

        $schema = new Schema();
        $table = $schema->createTable($tableName); // Doctrine DBAL creates the table object

        // Execute the user's schema definition callback, passing the actual schema and table
        $callback($schema, $table);

        $sql = $schema->toSql($this->connection->getDatabasePlatform());
        foreach ($sql as $statement) {
            $this->connection->executeStatement($statement);
        }
        echo "Table '{$tableName}' created successfully.\n";
    }

    /**
     * Drops a table from the database.
     *
     * @param string $tableName The name of the table to drop.
     * @throws Exception If the table does not exist.
     */
    public function drop(string $tableName): void
    {
        $schemaManager = $this->connection->getSchemaManager();

        if (!$schemaManager->tablesExist([$tableName])) {
            throw new Exception("Table '{$tableName}' does not exist. Cannot drop.");
        }

        $schema = new Schema();
        $schema->dropTable($tableName); // Tell Doctrine DBAL schema object to prepare drop statement

        $sql = $schema->toSql($this->connection->getDatabasePlatform());
        foreach ($sql as $statement) {
            $this->connection->executeStatement($statement);
        }
        echo "Table '{$tableName}' dropped successfully.\n";
    }

    /**
     * Drops a table from the database only if it exists.
     * This is a safer alternative to `drop()` for migrations.
     *
     * @param string $tableName The name of the table to drop.
     */
    public function dropIfExists(string $tableName): void
    {
        $schemaManager = $this->connection->getSchemaManager();

        if ($schemaManager->tablesExist([$tableName])) {
            $schema = new Schema();
            $schema->dropTable($tableName);
            $sql = $schema->toSql($this->connection->getDatabasePlatform());
            foreach ($sql as $statement) {
                $this->connection->executeStatement($statement);
            }
            echo "Table '{$tableName}' dropped successfully (if it existed).\n";
        } else {
            echo "Info: Table '{$tableName}' does not exist. Skipping drop.\n";
        }
    }

    /**
     * Applies changes to an existing table (for alter migrations).
     *
     * @param string $tableName The name of the table to alter.
     * @param \Closure $callback The closure defining the alterations.
     * It receives (Table $table).
     * @throws Exception If the table does not exist or schema definition is invalid.
     */
    public function alter(string $tableName, \Closure $callback): void
    {
        $schemaManager = $this->connection->getSchemaManager();

        if (!$schemaManager->tablesExist([$tableName])) {
            throw new Exception("Table '{$tableName}' does not exist. Cannot alter.");
        }

        $fromSchema = $schemaManager->createSchema(); // Current state of the database schema
        $toSchema = clone $fromSchema; // Clone to represent the desired new state

        $table = $toSchema->getTable($tableName); // Get the table object to modify

        // Execute the callback on the table object to apply alterations
        $callback($table);

        $comparator = new Comparator(); // Use Doctrine's comparator
        $schemaDiff = $comparator->compare($fromSchema, $toSchema);

        $sql = $schemaDiff->toSql($this->connection->getDatabasePlatform());

        if (empty($sql)) {
            echo "Info: No changes detected for table '{$tableName}'.\n";
            return;
        }

        foreach ($sql as $statement) {
            $this->connection->executeStatement($statement);
        }
        echo "Table '{$tableName}' altered successfully.\n";
    }

    /**
     * Loads a schema definition closure from a PHP file.
     *
     * @param string $schemaName The base name of the schema file (e.g., 'users').
     * @return \Closure The schema definition closure.
     * @throws Exception If the file is not found or does not return a closure.
     */
    public function loadSchemaDefinitionCallback(string $schemaName): \Closure
    {
        // FS_APP_PATH is defined in bootstrap.php
        $schemaPath = FS_APP_PATH . DIRECTORY_SEPARATOR . 'schemas' . DIRECTORY_SEPARATOR . $schemaName . '.php';

        if (!file_exists($schemaPath)) {
            throw new Exception("Schema definition file not found at: {$schemaPath}");
        }

        $definitionCallback = require $schemaPath;

        if (!($definitionCallback instanceof \Closure)) {
            throw new Exception("Invalid schema definition in file: {$schemaPath}. Must return a Closure.");
        }

        return $definitionCallback;
    }

    /**
     * Extracts the base table name from a descriptive schema filename.
     * Useful for 'alter' migrations where the filename might be 'add_column_to_users_table'.
     *
     * @param string $schemaName The base name of the schema file (e.g., 'users', 'add_sku_to_products').
     * @return string The extracted table name (e.g., 'users', 'products').
     */
    public function getTableNameFromSchemaName(string $schemaName): string
    {
        // Remove common prefixes like 'add_', 'update_', 'drop_'
        $tableName = preg_replace('/^(add|update|drop)_/', '', $schemaName);
        // Remove '_table' suffix
        $tableName = preg_replace('/_table$/', '', $tableName);
        // If the schema name is like 'add_sku_to_products', we want 'products'
        if (preg_match('/_to_([a-zA-Z0-9_]+)$/', $tableName, $matches)) {
            return $matches[1];
        }
        // Fallback: assume the schemaName itself is the table name
        return $tableName;
    }
}