<?php

class Migration_20240712000019_update_custom_roles_tbl
{
    public static function up(PDO $pdo)
    {
        $foreignKeyName = self::getForeignKeyConstraintName($pdo, 'custom_roles', 'created_by');
        if ($foreignKeyName) {
            self::dropForeignKey($pdo, 'custom_roles', $foreignKeyName);
        }

        $columnExists = self::columnExists($pdo, 'custom_roles', 'creator_type');
        if (!$columnExists) {
            $sql = "ALTER TABLE custom_roles ADD COLUMN creator_type ENUM('users', 'property_users') NOT NULL";
            $pdo->exec($sql);
        }

        $sql = "
            ALTER TABLE custom_roles
            MODIFY COLUMN name VARCHAR(50) NOT NULL,
            MODIFY COLUMN site_id INT NOT NULL,
            MODIFY COLUMN layer ENUM('super_admin', 'admin', 'dealer', 'organization', 'property', 'user', 'visitor') NOT NULL,
            MODIFY COLUMN created_by INT NOT NULL;
        ";

        $pdo->exec($sql);

        $sql = "
            ALTER TABLE custom_roles
            ADD CONSTRAINT FK_site_id FOREIGN KEY (site_id) REFERENCES properties(id);
        ";

        try {
            $pdo->exec($sql);
        } catch (PDOException $e) {
            if ($e->getCode() != '23000') { // 23000 is the SQLSTATE code for integrity constraint violation
                throw $e;
            }
        }
    }

    public static function down(PDO $pdo)
    {
        // Revert the changes made in the up method
        $sql = "
            ALTER TABLE custom_roles
            DROP FOREIGN KEY FK_site_id;
        ";

        $pdo->exec($sql);

        $sql = "
            ALTER TABLE custom_roles
            MODIFY COLUMN name VARCHAR(255) NULL,
            MODIFY COLUMN site_id INT NULL,
            MODIFY COLUMN layer ENUM('super_admin', 'admin', 'dealer', 'organization', 'property', 'user', 'visitor') NULL,
            MODIFY COLUMN created_by INT NULL;
        ";

        $pdo->exec($sql);

        $columnExists = self::columnExists($pdo, 'custom_roles', 'creator_type');
        if ($columnExists) {
            $sql = "ALTER TABLE custom_roles DROP COLUMN creator_type";
            $pdo->exec($sql);
        }
    }

    private static function getForeignKeyConstraintName(PDO $pdo, $tableName, $columnName)
    {
        $sql = "
            SELECT CONSTRAINT_NAME 
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_NAME = :table AND COLUMN_NAME = :column AND CONSTRAINT_SCHEMA = DATABASE()
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute(['table' => $tableName, 'column' => $columnName]);
        return $stmt->fetchColumn();
    }

    private static function dropForeignKey(PDO $pdo, $tableName, $foreignKeyName)
    {
        $sql = "ALTER TABLE $tableName DROP FOREIGN KEY $foreignKeyName";
        $pdo->exec($sql);
    }

    private static function columnExists(PDO $pdo, $tableName, $columnName)
    {
        $sql = "
            SELECT COLUMN_NAME 
            FROM information_schema.COLUMNS 
            WHERE TABLE_NAME = :table AND COLUMN_NAME = :column AND TABLE_SCHEMA = DATABASE()
        ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute(['table' => $tableName, 'column' => $columnName]);
        return (bool) $stmt->fetchColumn();
    }
}
