<?php

class Migration_20241216132245_alter_resources_table
{
    public static function up(PDO $pdo)
    {
        try {
            // Add the 'display_order' column to the 'resources' table
            $addColumnSql = "ALTER TABLE resources ADD COLUMN display_order INT";
            $pdo->exec($addColumnSql);

            // Update the 'slug' field for the row where 'name' is 'Settings'
            $updateSlugSql = "UPDATE resources SET slug = '#' WHERE name = 'Settings'";
            $pdo->exec($updateSlugSql);

            // Delete dependent rows in 'role_resources' where 'resource_id' matches the 'id' of the 'property' resource
            $deleteRoleResourcesSql = "
                DELETE FROM role_resources
                WHERE resource_id = (
                    SELECT id FROM resources WHERE name = 'property'
                )
            ";
            $pdo->exec($deleteRoleResourcesSql);

            // Delete the row in 'resources' where 'name' is 'property'
            $deleteResourceSql = "DELETE FROM resources WHERE name = 'property'";
            $pdo->exec($deleteResourceSql);

            // Update the row in 'resources' where 'name' is 'property_builder'
            $updateBuilderSql = "
                UPDATE resources
                SET isChild = '', parentId = NULL
                WHERE name = 'property_builder'
            ";
            $pdo->exec($updateBuilderSql);

            // Assign display_order values to existing rows
            // Example: Set 'my_properties' to 1 and 'property_builder' to 2
            $updateDisplayOrderSql = "
                UPDATE resources
                SET display_order = CASE
                    WHEN name = 'dashboard' THEN 1
                    WHEN name = 'my_iis' THEN 2
                    WHEN name = 'my_properties' THEN 3
                    WHEN name = 'property_builder' THEN 4
                    WHEN name = 'integrations' THEN 5
                    WHEN name = 'my_roles' THEN 6
                    WHEN name = 'users' THEN 7
                    WHEN name = 'visitors' THEN 8
                    WHEN name = 'command_center' THEN 9
                    WHEN name = 'events' THEN 10
                    WHEN name = 'settings' THEN 11
                    WHEN name = 'admin_panel' THEN 12
                    WHEN name = 'admin_setup' THEN 13
                    WHEN name = 'dealer_setup' THEN 14
                    ELSE display_order
                END
            ";
            $pdo->exec($updateDisplayOrderSql);
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function down(PDO $pdo)
    {
        // Begin transaction
        $pdo->beginTransaction();

        try {
            // Revert the 'display_order' column addition
            $dropColumnSql = "ALTER TABLE resources DROP COLUMN display_order";
            $pdo->exec($dropColumnSql);

            // Revert the 'slug' field update
            $revertSlugSql = "UPDATE resources SET slug = 'previous_value' WHERE name = 'Settings'";
            $pdo->exec($revertSlugSql);

            // Reinsert the deleted 'property' resource
            $reinsertResourceSql = "
                INSERT INTO resources (name, slug, isChild, parentId)
                VALUES ('property', 'property_slug', 'isChild_value', parentId_value)
            ";
            $pdo->exec($reinsertResourceSql);

            // Reinsert the deleted 'role_resources' entries
            $reinsertRoleResourcesSql = "
                INSERT INTO role_resources (role_id, resource_id)
                VALUES (role_id_value, (SELECT id FROM resources WHERE name = 'property'))
            ";
            $pdo->exec($reinsertRoleResourcesSql);

            // Revert the 'property_builder' row update
            $revertBuilderSql = "
                UPDATE resources
                SET isChild = 'previous_value', parentId = previous_parentId
                WHERE name = 'property_builder'
            ";
            $pdo->exec($revertBuilderSql);

            // Commit transaction
            $pdo->commit();
        } catch (Exception $e) {
            // Roll back transaction in case of error
            $pdo->rollBack();
            throw $e;
        }
    }
}
