-- AlterTable
ALTER TABLE `users` MODIFY `email` VARCHAR(191) NULL;

-- AlterTable
ALTER TABLE `users` CHANGE `password_hash` `pin_hash` VARCHAR(255) NOT NULL;

-- CreateTable
CREATE TABLE `auth_otps` (
    `id` CHAR(36) NOT NULL,
    `identifier_type` ENUM('EMAIL', 'MOBILE') NOT NULL,
    `identifier` VARCHAR(191) NOT NULL,
    `purpose` ENUM('LOGIN', 'REGISTER', 'RESET_PIN') NOT NULL,
    `code_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(191) NULL,
    `pin_hash` VARCHAR(255) NULL,
    `role` ENUM('PLANTER', 'DONOR', 'ADMIN') NULL,
    `user_id` CHAR(36) NULL,
    `attempts` INTEGER NOT NULL DEFAULT 0,
    `expires_at` DATETIME(3) NOT NULL,
    `consumed_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `auth_otps_identifier_type_identifier_purpose_idx`(`identifier_type`, `identifier`, `purpose`),
    INDEX `auth_otps_expires_at_idx`(`expires_at`),
    INDEX `auth_otps_user_id_idx`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_sessions` (
    `id` CHAR(36) NOT NULL,
    `user_id` CHAR(36) NOT NULL,
    `token_hash` VARCHAR(255) NOT NULL,
    `expires_at` DATETIME(3) NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `user_sessions_token_hash_key`(`token_hash`),
    INDEX `user_sessions_user_id_idx`(`user_id`),
    INDEX `user_sessions_expires_at_idx`(`expires_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

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