<?php

class Migration_20250202051908_alter_roles_roleresources_tbls
{
    public static function up(PDO $pdo)
    {
        // Disable foreign key checks
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 0;");

        // Drop dependent tables first
        $pdo->exec("DROP TABLE IF EXISTS user_roles;");
        $pdo->exec("DROP TABLE IF EXISTS role_resources;");
        $pdo->exec("DROP TABLE IF EXISTS role_desc;");
        $pdo->exec("DROP TABLE IF EXISTS roles;");

        // Re-enable foreign key checks
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1;");

        // Create the roles table
        $sql = "
            CREATE TABLE IF NOT EXISTS roles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                created_by INT NOT NULL,
                FOREIGN KEY(created_by) REFERENCES users(id)
            );
        ";
        $pdo->exec($sql);

        // Insert predefined roles
        $sql = "
            INSERT INTO roles (name, created_by) VALUES 
            ('super_admin', 1), 
            ('admin', 1), 
            ('dealer', 1), 
            ('sub_dealer', 1), 
            ('organization admin', 1), 
            ('property admin', 1), 
            ('user', 1), 
            ('visitor', 1);
        ";
        $pdo->exec($sql);

        // Create role descriptions table
        $sql = "
            CREATE TABLE IF NOT EXISTS role_desc (
                id INT NOT NULL AUTO_INCREMENT,
                role_id INT NOT NULL,
                `desc` VARCHAR(255) NOT NULL,
                PRIMARY KEY (id),
                FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
            );

        ";
        $pdo->exec($sql);

        // Insert role descriptions
        $sql = "
            INSERT INTO role_desc (role_id, `desc`) VALUES 
            (1, 'Full Control'),
            (2, 'Administrator Control. User can create/modify/delete all users or operators at Dealers level and below'),
            (3, 'Dealers Control. User can create/modify/delete all users or operators at Organization level and below'),
            (4, 'Sub Dealers Control. User can create/modify/delete all users or operators at Organization level and below'),
            (5, 'Organization Admins Control. User can create/modify/delete all users or operators at Property Admin level and below'),
            (6, 'Property Admin Control. User can create/modify/delete all users or operators at Property level and below'),
            (7, 'Users Control. User can create/modify/delete all Visitors associated to them'),
            (8, 'Visitor Control. Visitors have no website permissions');
        ";
        $pdo->exec($sql);

        // Create role_resources table
        $sql = "
            CREATE TABLE IF NOT EXISTS role_resources (
                role_id INT NOT NULL,
                resource_id INT NOT NULL,
                action_id INT NOT NULL,
                PRIMARY KEY(role_id, resource_id, action_id),
                FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
                FOREIGN KEY(resource_id) REFERENCES resources(id) ON DELETE CASCADE,
                FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE
            );
        ";
        $pdo->exec($sql);

        // Create user_roles table
        $sql = "
            CREATE TABLE IF NOT EXISTS user_roles (
                user_id INT NOT NULL,
                role_id INT NOT NULL,
                PRIMARY KEY(user_id, role_id),
                FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
            );

            INSERT INTO user_roles (user_id, role_id) VALUES 
            ('1', '1'),
            ('2', '2'),
            ('3', '3');
        ";
        $pdo->exec($sql);

        // Assign resources and actions to roles (ensuring it works for predefined roles)
        $roles = [
            'super_admin' => ['admin_panel', 'admin_setup', 'dashboard', 'my_iis', 'my_properties', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'admin' => ['dealer_setup', 'dashboard', 'my_iis', 'my_properties', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'dealer' => ['dashboard', 'my_iis', 'my_properties', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'sub_dealer' => ['dashboard', 'my_iis', 'my_properties', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'organization admin' => ['dashboard', 'my_iis', 'my_properties', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'property admin' => ['dashboard', 'my_iis', 'property_builder', 'integrations', 'my_roles', 'users', 'visitors', 'command_center', 'events', 'settings'],
            'user' => ['dashboard', 'visitors', 'command_center', 'events']
        ];
        $actions = ['read', 'write'];
        $sql = '';

        foreach ($roles as $role => $resources) {
            foreach ($resources as $resource) {
                foreach ($actions as $action) {
                    $sql .= "
                        INSERT INTO role_resources (role_id, resource_id, action_id)
                        VALUES (
                            (SELECT id FROM roles WHERE name = '$role'),
                            (SELECT id FROM resources WHERE name = '$resource'),
                            (SELECT id FROM actions WHERE name = '$action')
                        );\n";
                }
            }
        }
        $pdo->exec($sql);
    }

    public static function down(PDO $pdo)
    {
        // Disable foreign key checks
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 0;");

        // Drop tables in reverse order to prevent foreign key conflicts
        $pdo->exec("DROP TABLE IF EXISTS user_roles;");
        $pdo->exec("DROP TABLE IF EXISTS role_resources;");
        $pdo->exec("DROP TABLE IF EXISTS role_desc;");
        $pdo->exec("DROP TABLE IF EXISTS roles;");

        // Re-enable foreign key checks
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1;");
    }
}
