-- CreateTable
CREATE TABLE `user_activities` (
    `id` VARCHAR(191) NOT NULL,
    `user_id` VARCHAR(191) NOT NULL,
    `type` ENUM('ACCOUNT_CREATED', 'LOGIN', 'PROFILE_UPDATED', 'PASSWORD_CHANGED', 'ACCOUNT_BLOCKED', 'ACCOUNT_UNBLOCKED', 'USER_VERIFIED', 'USER_UNVERIFIED') NOT NULL,
    `title` VARCHAR(120) NOT NULL,
    `description` VARCHAR(500) NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `metadata` JSON NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `user_activities_user_id_created_at_idx`(`user_id`, `created_at`),
    INDEX `user_activities_user_id_type_idx`(`user_id`, `type`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `user_activities` ADD CONSTRAINT `user_activities_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Backfill account-created activity for existing users
INSERT INTO `user_activities` (`id`, `user_id`, `type`, `title`, `description`, `created_at`)
SELECT UUID(), `id`, 'ACCOUNT_CREATED', 'Account Created', CONCAT('User account created for ', `name`), `created_at`
FROM `users`
WHERE `role` = 'USER';
