CREATE TABLE IF NOT EXISTS `{{prefix}}user_role_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `assigned_by` BIGINT UNSIGNED NULL,
  `assignment_type` ENUM('primary','secondary') NOT NULL DEFAULT 'secondary',
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `note` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_role_assignment` (`user_id`,`role_id`),
  KEY `idx_user_role_assignments_user_status` (`user_id`,`status`),
  KEY `idx_user_role_assignments_role_status` (`role_id`,`status`),
  KEY `idx_user_role_assignments_assigned_by` (`assigned_by`),
  CONSTRAINT `{{prefix}}fk_user_role_assignments_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_user_role_assignments_role` FOREIGN KEY (`role_id`) REFERENCES `{{prefix}}roles` (`id`),
  CONSTRAINT `{{prefix}}fk_user_role_assignments_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}user_role_assignments` (`user_id`,`role_id`,`assignment_type`,`status`,`created_at`)
SELECT u.id,u.role_id,'primary','active',NOW()
FROM `{{prefix}}users` u
ON DUPLICATE KEY UPDATE `assignment_type`='primary',`status`='active',`updated_at`=NOW();
