from ..services.mysql_service import get_db_connection

class Role:
    @staticmethod
    def create_tables_if_not_exists():
        """
        Ensures the roles and staff_roles tables exist and populates them with
        default roles and initial staff assignments.
        """
        conn = get_db_connection()
        if not conn:
            raise Exception("Could not connect to the database to set up roles.")

        try:
            cursor = conn.cursor()

            # 1. Create 'roles' table
            create_roles_table = """
            CREATE TABLE IF NOT EXISTS `roles` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `name` VARCHAR(255) NOT NULL UNIQUE COMMENT 'The name of the role, e.g., Super Admin'
            ) ENGINE=InnoDB;
            """
            cursor.execute(create_roles_table)

            # 2. Create 'staff_roles' table
            create_staff_roles_table = """
            CREATE TABLE IF NOT EXISTS `staff_roles` (
                `pid` INT NOT NULL PRIMARY KEY COMMENT 'The staff member PID, acting as the unique identifier.',
                `role_id` INT NOT NULL,
                FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB;
            """
            cursor.execute(create_staff_roles_table)

            # 3. Insert default roles
            insert_roles = """
            INSERT INTO `roles` (`id`, `name`) VALUES
                (1, 'Super Admin'),
                (2, 'Admin'),
                (3, 'HR')
            ON DUPLICATE KEY UPDATE name=VALUES(name);
            """
            cursor.execute(insert_roles)

            # 4. Insert Super Admin PIDs
            super_admin_pids = [2695, 332, 786, 145852]
            insert_super_admins = "INSERT IGNORE INTO `staff_roles` (`pid`, `role_id`) VALUES (%s, 1)"
            for pid in super_admin_pids:
                cursor.execute(insert_super_admins, (pid,))

            # 5. Insert Admin PIDs
            admin_pids = [145870, 420]
            insert_admins = "INSERT IGNORE INTO `staff_roles` (`pid`, `role_id`) VALUES (%s, 2)"
            for pid in admin_pids:
                cursor.execute(insert_admins, (pid,))

            conn.commit()

        except Exception as e:
            print(f"Error setting up roles tables: {e}")
            conn.rollback()
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()