-- ============================================================================
-- ServerManager Server Access Control
-- Adds ownership + team-based permissions per server
-- ============================================================================

ALTER TABLE `servers`
    ADD COLUMN `created_by` INT UNSIGNED DEFAULT NULL AFTER `group_name`,
    ADD KEY `idx_created_by` (`created_by`);

CREATE TABLE IF NOT EXISTS `server_user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `server_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `role` ENUM('viewer', 'manager') NOT NULL DEFAULT 'viewer',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_server_user` (`server_id`, `user_id`),
    KEY `idx_server_id` (`server_id`),
    KEY `idx_user_id` (`user_id`),
    CONSTRAINT `fk_server_user_server` FOREIGN KEY (`server_id`)
        REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_server_user_user` FOREIGN KEY (`user_id`)
        REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;