-- CreateTable
CREATE TABLE `otp_challenges` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `userId` BIGINT NULL,
    `purpose` ENUM('LOGIN', 'REGISTRATION', 'VERIFY_MOBILE', 'VERIFY_EMAIL', 'RESET_PASSWORD', 'CHANGE_MOBILE', 'CHANGE_EMAIL', 'TRIP_START') NOT NULL,
    `channel` ENUM('SMS', 'EMAIL', 'WHATSAPP') NOT NULL,
    `status` ENUM('CREATED', 'SENT', 'VERIFIED', 'EXPIRED', 'FAILED', 'CANCELLED', 'LOCKED') NOT NULL DEFAULT 'CREATED',
    `destinationHash` VARCHAR(255) NOT NULL,
    `otpHash` VARCHAR(255) NOT NULL,
    `attemptCount` INTEGER NOT NULL DEFAULT 0,
    `maxAttempts` INTEGER NOT NULL DEFAULT 5,
    `expiresAt` DATETIME(3) NOT NULL,
    `verifiedAt` DATETIME(3) NULL,
    `sentAt` DATETIME(3) NULL,
    `requestIp` VARCHAR(64) NULL,
    `userAgent` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `otp_challenges_publicId_key`(`publicId`),
    INDEX `otp_challenges_userId_purpose_status_idx`(`userId`, `purpose`, `status`),
    INDEX `otp_challenges_destinationHash_purpose_status_idx`(`destinationHash`, `purpose`, `status`),
    INDEX `otp_challenges_expiresAt_idx`(`expiresAt`),
    INDEX `otp_challenges_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `auth_sessions` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `userId` BIGINT NOT NULL,
    `sessionTokenHash` VARCHAR(255) NOT NULL,
    `status` ENUM('ACTIVE', 'REVOKED', 'EXPIRED') NOT NULL DEFAULT 'ACTIVE',
    `ipAddress` VARCHAR(64) NULL,
    `userAgent` VARCHAR(500) NULL,
    `deviceId` VARCHAR(191) NULL,
    `deviceName` VARCHAR(191) NULL,
    `lastSeenAt` DATETIME(3) NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `revokedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `auth_sessions_publicId_key`(`publicId`),
    UNIQUE INDEX `auth_sessions_sessionTokenHash_key`(`sessionTokenHash`),
    INDEX `auth_sessions_userId_status_idx`(`userId`, `status`),
    INDEX `auth_sessions_expiresAt_idx`(`expiresAt`),
    INDEX `auth_sessions_deviceId_idx`(`deviceId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `refresh_tokens` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `sessionId` BIGINT NOT NULL,
    `tokenHash` VARCHAR(255) NOT NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `consumedAt` DATETIME(3) NULL,
    `revokedAt` DATETIME(3) NULL,
    `replacedById` BIGINT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `refresh_tokens_publicId_key`(`publicId`),
    UNIQUE INDEX `refresh_tokens_tokenHash_key`(`tokenHash`),
    INDEX `refresh_tokens_sessionId_expiresAt_idx`(`sessionId`, `expiresAt`),
    INDEX `refresh_tokens_revokedAt_idx`(`revokedAt`),
    INDEX `refresh_tokens_replacedById_idx`(`replacedById`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `login_attempts` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `publicId` VARCHAR(36) NOT NULL,
    `userId` BIGINT NULL,
    `identifierHash` VARCHAR(255) NOT NULL,
    `result` ENUM('SUCCESS', 'INVALID_CREDENTIALS', 'OTP_REQUIRED', 'OTP_FAILED', 'BLOCKED', 'RATE_LIMITED', 'USER_NOT_FOUND') NOT NULL,
    `ipAddress` VARCHAR(64) NULL,
    `userAgent` VARCHAR(500) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `login_attempts_publicId_key`(`publicId`),
    INDEX `login_attempts_userId_createdAt_idx`(`userId`, `createdAt`),
    INDEX `login_attempts_identifierHash_createdAt_idx`(`identifierHash`, `createdAt`),
    INDEX `login_attempts_ipAddress_createdAt_idx`(`ipAddress`, `createdAt`),
    INDEX `login_attempts_result_createdAt_idx`(`result`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `otp_challenges` ADD CONSTRAINT `otp_challenges_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `auth_sessions` ADD CONSTRAINT `auth_sessions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `refresh_tokens` ADD CONSTRAINT `refresh_tokens_sessionId_fkey` FOREIGN KEY (`sessionId`) REFERENCES `auth_sessions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `login_attempts` ADD CONSTRAINT `login_attempts_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

