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

namespace Core\Database;

use Exception;
use PDO;

class Schema
{
    /**
     * Executes a raw SQL statement against the database.
     * This private helper uses the static Connection::execute() method.
     * @param string $sql The SQL statement to execute.
     * @param array $params The parameters for a prepared statement.
     * @return int The number of affected rows.
     * @throws Exception If the database connection is not initialized or the SQL is invalid.
     */
    private static function execute(string $sql, array $params = []): int
    {
        try {
            return Connection::execute($sql, $params);
        } catch (Exception $e) {
            throw new Exception("Schema error: Failed to execute SQL statement: " . $sql . "\n" . $e->getMessage());
        }
    }

    /**
     * Checks if a table exists in the database.
     *
     * @param string $table The table name.
     * @return bool True if the table exists, false otherwise.
     */
    public static function tableExists(string $table): bool
    {
        try {
            $dbName = Connection::getDatabaseName(); // adjust depending on your Connection class
            $sql = "SELECT COUNT(*) AS cnt 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
            $result = Connection::fetch($sql, [$dbName, $table]);
            return !empty($result) && $result['cnt'] > 0;
        } catch (Exception $e) {
            return false;
        }
    }


    public static function createTable(string $table, array $columns): void
    {
        // Add a check to prevent creating an existing table
        if (self::tableExists($table)) {
            echo "Info: Table '{$table}' already exists. Skipping creation.\n";
            return;
        }
        
        $columnsSql = implode(", ", array_map(
            fn($name, $type) => "`$name` $type",
            array_keys($columns),
            $columns
        ));
        $sql = "CREATE TABLE `$table` ($columnsSql)";
        self::execute($sql);
        echo "Table '{$table}' created successfully.\n";
    }

    public static function dropTable(string $table): void
    {
        $sql = "DROP TABLE IF EXISTS `$table`";
        self::execute($sql);
        echo "Table '{$table}' dropped successfully.\n";
    }

    public static function renameTable(string $old, string $new): void
    {
        $sql = "RENAME TABLE `$old` TO `$new`";
        self::execute($sql);
        echo "Table '{$old}' renamed to '{$new}' successfully.\n";
    }

    public static function addColumn(string $table, string $column, string $type): void
    {
        $sql = "ALTER TABLE `$table` ADD COLUMN `$column` $type";
        self::execute($sql);
        echo "Column '{$column}' added to table '{$table}' successfully.\n";
    }

    public static function renameColumn(string $table, string $old, string $new, string $type): void
    {
        // MySQL-specific syntax
        $sql = "ALTER TABLE `$table` CHANGE COLUMN `$old` `$new` $type";
        self::execute($sql);
        echo "Column '{$old}' in table '{$table}' renamed to '{$new}' successfully.\n";
    }

    public static function modifyColumn(string $table, string $column, string $type): void
    {
        // MySQL-specific syntax
        $sql = "ALTER TABLE `$table` MODIFY COLUMN `$column` $type";
        self::execute($sql);
        echo "Column '{$column}' in table '{$table}' modified successfully.\n";
    }

    public static function dropColumn(string $table, string $column): void
    {
        $sql = "ALTER TABLE `$table` DROP COLUMN `$column`";
        self::execute($sql);
        echo "Column '{$column}' dropped from table '{$table}' successfully.\n";
    }

    public static function addIndex(string $table, string $indexName, array $columns): void
    {
        $columnsSql = implode(", ", array_map(fn($col) => "`$col`", $columns));
        $sql = "CREATE INDEX `$indexName` ON `$table` ($columnsSql)";
        self::execute($sql);
        echo "Index '{$indexName}' added to table '{$table}' successfully.\n";
    }

    public static function dropIndex(string $table, string $indexName): void
    {
        $sql = "DROP INDEX `$indexName` ON `$table`";
        self::execute($sql);
        echo "Index '{$indexName}' dropped from table '{$table}' successfully.\n";
    }

    public static function addForeignKey(
        string $table,
        string $column,
        string $refTable,
        string $refColumn,
        string $onDelete = "CASCADE"
    ): void {
        $sql = "ALTER TABLE `$table` ADD CONSTRAINT fk_{$table}_{$column} 
                FOREIGN KEY (`$column`) REFERENCES `$refTable`(`$refColumn`) ON DELETE $onDelete";
        self::execute($sql);
        echo "Foreign key added to table '{$table}' successfully.\n";
    }

    public static function dropForeignKey(string $table, string $keyName): void
    {
        $sql = "ALTER TABLE `$table` DROP FOREIGN KEY `$keyName`";
        self::execute($sql);
        echo "Foreign key '{$keyName}' dropped from table '{$table}' successfully.\n";
    }
}