ALTER TABLE `users`
    ADD COLUMN IF NOT EXISTS `role` ENUM('admin','user') NOT NULL DEFAULT 'user' AFTER `username`,
    ADD COLUMN IF NOT EXISTS `email` varchar(255) DEFAULT NULL AFTER `role`,
    ADD COLUMN IF NOT EXISTS `is_active` tinyint(1) NOT NULL DEFAULT 1 AFTER `email`,
    ADD COLUMN IF NOT EXISTS `last_login` datetime DEFAULT NULL AFTER `is_active`;

ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

UPDATE `users` SET `role` = 'admin' WHERE `username` = 'admin';

CREATE TABLE IF NOT EXISTS `user_groups` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `group_id` int(11) NOT NULL,
    `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_group_unique` (`user_id`, `group_id`),
    KEY `fk_ug_user` (`user_id`),
    KEY `fk_ug_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `vms`
    ADD COLUMN IF NOT EXISTS `earnapp_running` tinyint(1) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS `earnapp_autostart` tinyint(1) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS `earnapp_paused` tinyint(1) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS `duplicate_of` int(11) DEFAULT NULL COMMENT 'vm_id this was bridged from';

ALTER TABLE `vms` MODIFY COLUMN IF EXISTS `status` varchar(50) DEFAULT 'Offline';

INSERT IGNORE INTO `user_groups` (`user_id`, `group_id`)
SELECT u.id, g.id FROM `users` u, `groups` g WHERE u.role = 'admin';
