-- CreateTable
CREATE TABLE `users` (
    `id` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(191) NOT NULL,
    `firstName` VARCHAR(191) NOT NULL,
    `lastName` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `organizationName` VARCHAR(191) NULL,
    `role` ENUM('READER', 'AUTHOR', 'ADMIN', 'PUBLISHER', 'EDITOR', 'REVIEWER', 'AFFILIATE') NOT NULL DEFAULT 'AUTHOR',
    `phoneNumber` VARCHAR(191) NULL,
    `bio` TEXT NULL,
    `website` VARCHAR(191) NULL,
    `address` VARCHAR(191) NULL,
    `profilePicture` TEXT NULL,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `emailVerified` BOOLEAN NOT NULL DEFAULT false,
    `lastLogin` DATETIME(3) NULL,
    `resetToken` VARCHAR(191) NULL,
    `resetTokenExpiry` DATETIME(3) NULL,
    `affiliateCode` VARCHAR(191) NULL,
    `isAffiliate` BOOLEAN NOT NULL DEFAULT false,
    `affiliateEarnings` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `totalEarnings` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `paidEarnings` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `pendingPayout` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `paypalEmail` VARCHAR(191) NULL,
    `bankDetails` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `authorId` VARCHAR(191) NULL,
    `userType` ENUM('INDIVIDUAL', 'ORGANIZATION') NULL DEFAULT 'INDIVIDUAL',
    `signupIp` VARCHAR(191) NULL,
    `signupUserAgent` TEXT NULL,
    `signupReferrer` TEXT NULL,
    `signupCountry` VARCHAR(2) NULL,
    `signupRegion` VARCHAR(191) NULL,
    `signupCity` VARCHAR(191) NULL,
    `signupChannel` ENUM('CREDENTIALS', 'GOOGLE', 'OTP', 'MAGIC_LINK', 'ADMIN_CREATED') NULL,
    `signupLandingPath` TEXT NULL,
    `utmSource` VARCHAR(191) NULL,
    `utmMedium` VARCHAR(191) NULL,
    `utmCampaign` VARCHAR(191) NULL,
    `utmTerm` VARCHAR(191) NULL,
    `utmContent` VARCHAR(191) NULL,

    UNIQUE INDEX `users_email_key`(`email`),
    UNIQUE INDEX `users_slug_key`(`slug`),
    UNIQUE INDEX `users_affiliateCode_key`(`affiliateCode`),
    UNIQUE INDEX `users_authorId_key`(`authorId`),
    INDEX `users_emailVerified_idx`(`emailVerified`),
    INDEX `users_isActive_idx`(`isActive`),
    INDEX `users_affiliateCode_idx`(`affiliateCode`),
    INDEX `users_signupCountry_idx`(`signupCountry`),
    INDEX `users_signupChannel_idx`(`signupChannel`),
    INDEX `users_utmSource_idx`(`utmSource`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `books` (
    `id` VARCHAR(191) NOT NULL,
    `title` VARCHAR(191) NOT NULL,
    `subtitle` VARCHAR(191) NULL,
    `slug` VARCHAR(191) NOT NULL,
    `description` TEXT NOT NULL,
    `metaKeywords` TEXT NULL,
    `metaDescription` TEXT NULL,
    `isbn` VARCHAR(191) NULL,
    `language` VARCHAR(191) NOT NULL,
    `bookType` ENUM('FICTION', 'NON_FICTION', 'ACADEMIC', 'CHILDREN', 'BIOGRAPHY', 'SELF_HELP', 'OTHER', 'EBOOK', 'PAPERBACK', 'HARDCOVER', 'AUDIOBOOK', 'ARTICLE') NOT NULL,
    `format` ENUM('PAPERBACK', 'HARDCOVER', 'EBOOK', 'AUDIOBOOK') NOT NULL,
    `numberOfPages` INTEGER NOT NULL,
    `duration` INTEGER NULL,
    `originalPrice` DECIMAL(10, 2) NOT NULL,
    `sellingPrice` DECIMAL(10, 2) NOT NULL,
    `productionCost` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `royaltyRate` DOUBLE NOT NULL DEFAULT 0.5,
    `isExclusive` BOOLEAN NOT NULL DEFAULT false,
    `exclusiveRate` DOUBLE NULL,
    `hasAdultContent` BOOLEAN NOT NULL DEFAULT false,
    `copyright` BOOLEAN NOT NULL DEFAULT true,
    `interiorColor` ENUM('BLACK_WHITE', 'COLOR', 'MIXED', 'BW') NOT NULL,
    `laminationType` ENUM('GLOSS', 'MATTE', 'NONE', 'GLOSSY') NOT NULL,
    `heightIn` DECIMAL(5, 2) NOT NULL,
    `widthIn` DECIMAL(5, 2) NOT NULL,
    `trimSize` VARCHAR(191) NOT NULL,
    `bindingType` VARCHAR(191) NULL,
    `manuscript` VARCHAR(191) NOT NULL,
    `coverImage` VARCHAR(191) NOT NULL,
    `coverSpread` VARCHAR(191) NULL,
    `sampleFile` VARCHAR(191) NULL,
    `isPublished` BOOLEAN NOT NULL DEFAULT false,
    `publishedAt` DATETIME(3) NULL,
    `authorId` VARCHAR(191) NULL,
    `viewsCount` INTEGER NOT NULL DEFAULT 0,
    `averageRating` DOUBLE NOT NULL DEFAULT 0,
    `ratingsCount` INTEGER NOT NULL DEFAULT 0,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `categoryId` VARCHAR(191) NOT NULL,
    `paperTypeId` VARCHAR(191) NOT NULL,
    `authorFirstName` VARCHAR(191) NOT NULL,
    `authorLastName` VARCHAR(191) NOT NULL,
    `isBestseller` BOOLEAN NOT NULL DEFAULT false,
    `isFeatured` BOOLEAN NOT NULL DEFAULT false,
    `stockQuantity` INTEGER NOT NULL DEFAULT 0,
    `rejectionReason` TEXT NULL,
    `status` ENUM('DRAFT', 'PENDING', 'APPROVED', 'REJECTED', 'PUBLISHED') NOT NULL DEFAULT 'PENDING',

    UNIQUE INDEX `books_slug_key`(`slug`),
    UNIQUE INDEX `books_isbn_key`(`isbn`),
    INDEX `books_authorId_idx`(`authorId`),
    INDEX `books_categoryId_idx`(`categoryId`),
    INDEX `books_paperTypeId_idx`(`paperTypeId`),
    INDEX `books_isPublished_idx`(`isPublished`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_roles` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `role` ENUM('READER', 'AUTHOR', 'ADMIN', 'AFFILIATE', 'PUBLISHER', 'EDITOR', 'REVIEWER') NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `user_roles_userId_idx`(`userId`),
    INDEX `user_roles_role_idx`(`role`),
    UNIQUE INDEX `user_roles_userId_role_key`(`userId`, `role`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `authors` (
    `id` VARCHAR(191) NOT NULL,
    `firstName` VARCHAR(191) NOT NULL,
    `lastName` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `bio` TEXT NULL,
    `userId` VARCHAR(191) NULL,
    `walletId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `authors_email_key`(`email`),
    UNIQUE INDEX `authors_userId_key`(`userId`),
    UNIQUE INDEX `authors_walletId_key`(`walletId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `categories` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `description` VARCHAR(191) NULL,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `categories_name_key`(`name`),
    UNIQUE INDEX `categories_slug_key`(`slug`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `paper_types` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `description` VARCHAR(191) NULL,
    `pricePerPageBw` DOUBLE NOT NULL DEFAULT 0.02,
    `pricePerPageColor` DOUBLE NOT NULL DEFAULT 0.05,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `paper_types_name_key`(`name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `carts` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `carts_userId_key`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `cart_items` (
    `id` VARCHAR(191) NOT NULL,
    `cartId` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `quantity` INTEGER NOT NULL DEFAULT 1,
    `price` DECIMAL(10, 2) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `cart_items_bookId_idx`(`bookId`),
    UNIQUE INDEX `cart_items_cartId_bookId_key`(`cartId`, `bookId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `orders` (
    `id` VARCHAR(191) NOT NULL,
    `orderNumber` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NULL,
    `affiliateId` VARCHAR(191) NULL,
    `status` ENUM('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED', 'REFUNDED', 'PARTIALLY_REFUNDED', 'ON_HOLD') NOT NULL DEFAULT 'PENDING',
    `subTotal` DECIMAL(10, 2) NOT NULL,
    `taxAmount` DECIMAL(10, 2) NOT NULL,
    `shippingAmount` DECIMAL(10, 2) NOT NULL,
    `discountAmount` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `totalAmount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `paymentMethod` VARCHAR(191) NULL,
    `paymentStatus` ENUM('PENDING', 'AUTHORIZED', 'PAID', 'PARTIALLY_REFUNDED', 'REFUNDED', 'VOIDED', 'FAILED') NOT NULL DEFAULT 'PENDING',
    `paymentId` VARCHAR(191) NULL,
    `paymentDetails` LONGTEXT NULL,
    `shippingAddress` LONGTEXT NOT NULL,
    `shippingMethod` VARCHAR(191) NULL,
    `trackingNumber` VARCHAR(191) NULL,
    `trackingUrl` VARCHAR(191) NULL,
    `shippedAt` DATETIME(3) NULL,
    `deliveredAt` DATETIME(3) NULL,
    `customerEmail` VARCHAR(191) NOT NULL,
    `customerPhone` VARCHAR(191) NOT NULL,
    `customerNote` VARCHAR(191) NULL,
    `ipAddress` VARCHAR(191) NOT NULL,
    `userAgent` VARCHAR(191) NULL,
    `referrer` VARCHAR(191) NULL,
    `utmSource` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `expiresAt` DATETIME(3) NULL,

    UNIQUE INDEX `orders_orderNumber_key`(`orderNumber`),
    INDEX `orders_userId_idx`(`userId`),
    INDEX `orders_affiliateId_idx`(`affiliateId`),
    INDEX `orders_orderNumber_idx`(`orderNumber`),
    INDEX `orders_status_idx`(`status`),
    INDEX `orders_paymentStatus_idx`(`paymentStatus`),
    INDEX `orders_createdAt_idx`(`createdAt`),
    INDEX `orders_customerEmail_idx`(`customerEmail`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `order_items` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `quantity` INTEGER NOT NULL,
    `unitPrice` DECIMAL(10, 2) NOT NULL,
    `taxAmount` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `discountAmount` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `totalPrice` DECIMAL(10, 2) NOT NULL,
    `authorId` VARCHAR(191) NOT NULL,
    `authorRoyaltyRate` DOUBLE NOT NULL,
    `authorEarnings` DECIMAL(10, 2) NOT NULL,
    `affiliateEarnings` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `platformEarnings` DECIMAL(10, 2) NOT NULL,
    `productionCost` DECIMAL(10, 2) NOT NULL,
    `processingFee` DECIMAL(10, 2) NOT NULL,
    `netProfit` DECIMAL(10, 2) NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `bookSnapshot` LONGTEXT NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `order_items_orderId_idx`(`orderId`),
    INDEX `order_items_bookId_idx`(`bookId`),
    INDEX `order_items_authorId_idx`(`authorId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `order_status_history` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `metadata` LONGTEXT NULL,
    `createdBy` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `order_status_history_orderId_idx`(`orderId`),
    INDEX `order_status_history_status_idx`(`status`),
    INDEX `order_status_history_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `order_transactions` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `type` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL,
    `processor` VARCHAR(191) NOT NULL,
    `processorId` VARCHAR(191) NULL,
    `processorData` LONGTEXT NULL,
    `failureReason` VARCHAR(191) NULL,
    `metadata` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `order_transactions_orderId_idx`(`orderId`),
    INDEX `order_transactions_processorId_idx`(`processorId`),
    INDEX `order_transactions_status_idx`(`status`),
    INDEX `order_transactions_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `affiliates` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `status` ENUM('PENDING', 'APPROVED', 'REJECTED', 'SUSPENDED') NOT NULL DEFAULT 'PENDING',
    `referralCode` VARCHAR(191) NOT NULL,
    `website` VARCHAR(191) NULL,
    `promotionMethods` TEXT NULL,
    `notes` TEXT NULL,
    `commissionRate` DOUBLE NOT NULL DEFAULT 10,
    `totalEarnings` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `pendingPayout` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `totalPaid` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `paypalEmail` VARCHAR(191) NULL,
    `bankDetails` TEXT NULL,
    `appliedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `approvedAt` DATETIME(3) NULL,
    `rejectedAt` DATETIME(3) NULL,
    `rejectionReason` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `affiliates_userId_key`(`userId`),
    UNIQUE INDEX `affiliates_referralCode_key`(`referralCode`),
    INDEX `affiliates_referralCode_idx`(`referralCode`),
    INDEX `affiliates_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `payouts` (
    `id` VARCHAR(191) NOT NULL,
    `affiliateId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    `paymentMethod` ENUM('PAYPAL', 'BANK_TRANSFER', 'MPESA', 'CRYPTO', 'OTHER') NOT NULL,
    `paymentDetails` TEXT NULL,
    `notes` TEXT NULL,
    `processedAt` DATETIME(3) NULL,
    `processedBy` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `payouts_affiliateId_idx`(`affiliateId`),
    INDEX `payouts_status_idx`(`status`),
    INDEX `payouts_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `referrals` (
    `id` VARCHAR(191) NOT NULL,
    `affiliateId` VARCHAR(191) NOT NULL,
    `referredUserId` VARCHAR(191) NULL,
    `referralCode` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'PENDING',
    `commissionEarned` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    `orderId` VARCHAR(191) NULL,
    `ipAddress` VARCHAR(191) NULL,
    `userAgent` TEXT NULL,
    `metadata` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `referrals_orderId_key`(`orderId`),
    INDEX `referrals_affiliateId_idx`(`affiliateId`),
    INDEX `referrals_referredUserId_idx`(`referredUserId`),
    INDEX `referrals_orderId_idx`(`orderId`),
    INDEX `referrals_referralCode_idx`(`referralCode`),
    INDEX `referrals_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `wishlists` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `isDefault` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `wishlists_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `wishlist_items` (
    `id` VARCHAR(191) NOT NULL,
    `wishlistId` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `notes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `wishlist_items_bookId_idx`(`bookId`),
    UNIQUE INDEX `wishlist_items_wishlistId_bookId_key`(`wishlistId`, `bookId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `reviews` (
    `id` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `rating` INTEGER NOT NULL DEFAULT 5,
    `title` VARCHAR(191) NULL,
    `comment` TEXT NULL,
    `isApproved` BOOLEAN NOT NULL DEFAULT false,
    `approvedAt` DATETIME(3) NULL,
    `approvedBy` VARCHAR(191) NULL,
    `helpfulVotes` INTEGER NOT NULL DEFAULT 0,
    `totalVotes` INTEGER NOT NULL DEFAULT 0,
    `metadata` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `reviews_bookId_idx`(`bookId`),
    INDEX `reviews_userId_idx`(`userId`),
    INDEX `reviews_rating_idx`(`rating`),
    INDEX `reviews_isApproved_idx`(`isApproved`),
    UNIQUE INDEX `reviews_bookId_userId_key`(`bookId`, `userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `refunds` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `reason` VARCHAR(191) NULL,
    `status` VARCHAR(191) NOT NULL,
    `processorRefundId` VARCHAR(191) NULL,
    `processedAt` DATETIME(3) NULL,
    `processedBy` VARCHAR(191) NULL,
    `notes` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `refunds_orderId_key`(`orderId`),
    INDEX `refunds_orderId_idx`(`orderId`),
    INDEX `refunds_status_idx`(`status`),
    INDEX `refunds_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `refund_items` (
    `id` VARCHAR(191) NOT NULL,
    `refundId` VARCHAR(191) NOT NULL,
    `orderItemId` VARCHAR(191) NOT NULL,
    `quantity` INTEGER NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `reason` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `refund_items_orderItemId_idx`(`orderItemId`),
    INDEX `refund_items_refundId_idx`(`refundId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `tax_rates` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `rate` DOUBLE NOT NULL,
    `country` VARCHAR(191) NOT NULL,
    `region` VARCHAR(191) NULL,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `isInclusive` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `tax_rates_isActive_idx`(`isActive`),
    UNIQUE INDEX `tax_rates_country_region_name_key`(`country`, `region`, `name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_follows` (
    `id` VARCHAR(191) NOT NULL,
    `followerId` VARCHAR(191) NOT NULL,
    `followingId` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `user_follows_followingId_fkey`(`followingId`),
    UNIQUE INDEX `user_follows_followerId_followingId_key`(`followerId`, `followingId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `wallets` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `walletType` ENUM('AUTHOR', 'AFFILIATE', 'PUBLISHER') NOT NULL DEFAULT 'AUTHOR',
    `balance` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `wallets_userId_key`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `transactions` (
    `id` VARCHAR(191) NOT NULL,
    `walletId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(12, 2) NOT NULL,
    `type` ENUM('DEPOSIT', 'WITHDRAWAL', 'REFUND', 'AFFILIATE_EARNING', 'ROYALTY', 'OTHER') NOT NULL,
    `status` ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED', 'PROCESSING') NOT NULL DEFAULT 'PENDING',
    `reference` VARCHAR(191) NULL,
    `orderId` VARCHAR(191) NULL,
    `description` VARCHAR(191) NULL,
    `metadata` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `transactions_reference_key`(`reference`),
    INDEX `transactions_walletId_idx`(`walletId`),
    INDEX `transactions_reference_idx`(`reference`),
    INDEX `transactions_orderId_idx`(`orderId`),
    INDEX `transactions_type_idx`(`type`),
    INDEX `transactions_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `notifications` (
    `id` VARCHAR(191) NOT NULL,
    `type` ENUM('SYSTEM', 'ORDER', 'PAYMENT', 'AFFILIATE', 'FOLLOW', 'COMMENT', 'LIKE', 'MESSAGE', 'ANNOUNCEMENT', 'BOOK_PUBLISHED', 'BOOK_UNPUBLISHED', 'BOOK_REJECTED', 'COAUTHOR_INVITATION', 'COAUTHOR_ACCEPTED', 'COAUTHOR_REJECTED') NOT NULL,
    `title` VARCHAR(191) NOT NULL,
    `message` TEXT NOT NULL,
    `status` ENUM('UNREAD', 'READ', 'ARCHIVED') NOT NULL DEFAULT 'UNREAD',
    `metadata` LONGTEXT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `relatedId` VARCHAR(191) NULL,
    `readAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `notifications_userId_status_idx`(`userId`, `status`),
    INDEX `notifications_createdAt_idx`(`createdAt`),
    INDEX `notifications_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `bookauthor` (
    `id` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `role` VARCHAR(191) NOT NULL,
    `joinedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `BookAuthor_bookId_fkey`(`bookId`),
    INDEX `BookAuthor_userId_fkey`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `bookauthorinvitation` (
    `id` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `inviterId` VARCHAR(191) NOT NULL,
    `inviteeId` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL,
    `message` VARCHAR(191) NULL,
    `expiresAt` DATETIME(3) NULL,
    `respondedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `BookAuthorInvitation_bookId_fkey`(`bookId`),
    INDEX `BookAuthorInvitation_inviteeId_fkey`(`inviteeId`),
    INDEX `BookAuthorInvitation_inviterId_fkey`(`inviterId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `mpesatransaction` (
    `id` VARCHAR(191) NOT NULL,
    `paymentId` VARCHAR(191) NOT NULL,
    `merchantRequestId` VARCHAR(191) NOT NULL,
    `checkoutRequestId` VARCHAR(191) NOT NULL,
    `resultCode` VARCHAR(191) NULL,
    `resultDesc` VARCHAR(191) NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `mpesaReceipt` VARCHAR(191) NULL,
    `transactionDate` DATETIME(3) NULL,
    `phoneNumber` VARCHAR(191) NULL,
    `status` ENUM('REQUEST_SENT', 'WAITING_FOR_PAYMENT', 'PAYMENT_RECEIVED', 'PAYMENT_FAILED', 'CANCELLED', 'TIMEOUT') NOT NULL DEFAULT 'REQUEST_SENT',
    `rawResponse` LONGTEXT NULL,
    `callbackMetadata` LONGTEXT NULL,
    `retryCount` INTEGER NOT NULL DEFAULT 0,
    `lastRetryAt` DATETIME(3) NULL,
    `nextRetryAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `MpesaTransaction_paymentId_key`(`paymentId`),
    UNIQUE INDEX `MpesaTransaction_merchantRequestId_key`(`merchantRequestId`),
    UNIQUE INDEX `MpesaTransaction_checkoutRequestId_key`(`checkoutRequestId`),
    UNIQUE INDEX `MpesaTransaction_mpesaReceipt_key`(`mpesaReceipt`),
    INDEX `MpesaTransaction_checkoutRequestId_idx`(`checkoutRequestId`),
    INDEX `MpesaTransaction_createdAt_idx`(`createdAt`),
    INDEX `MpesaTransaction_merchantRequestId_idx`(`merchantRequestId`),
    INDEX `MpesaTransaction_mpesaReceipt_idx`(`mpesaReceipt`),
    INDEX `MpesaTransaction_paymentId_idx`(`paymentId`),
    INDEX `MpesaTransaction_status_idx`(`status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `payment` (
    `id` VARCHAR(191) NOT NULL,
    `orderId` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `paymentMethod` ENUM('MPESA', 'CARD', 'CASH_ON_DELIVERY', 'BANK_TRANSFER', 'PAYPAL', 'OTHER') NOT NULL,
    `status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'PARTIALLY_REFUNDED', 'CANCELLED', 'EXPIRED') NOT NULL DEFAULT 'PENDING',
    `reference` VARCHAR(191) NOT NULL,
    `idempotencyKey` VARCHAR(191) NULL,
    `mpesaReceipt` VARCHAR(191) NULL,
    `phoneNumber` VARCHAR(191) NULL,
    `paymentDate` DATETIME(3) NULL,
    `paymentDetails` LONGTEXT NULL,
    `refundedAmount` DECIMAL(10, 2) NULL,
    `refundReason` VARCHAR(191) NULL,
    `metadata` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `Payment_reference_key`(`reference`),
    UNIQUE INDEX `Payment_idempotencyKey_key`(`idempotencyKey`),
    INDEX `Payment_orderId_idx`(`orderId`),
    INDEX `Payment_paymentMethod_idx`(`paymentMethod`),
    INDEX `Payment_reference_idx`(`reference`),
    INDEX `Payment_status_idx`(`status`),
    INDEX `Payment_idempotencyKey_idx`(`idempotencyKey`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `notification_preferences` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `emailSystem` BOOLEAN NOT NULL DEFAULT true,
    `emailOrder` BOOLEAN NOT NULL DEFAULT true,
    `emailPayment` BOOLEAN NOT NULL DEFAULT true,
    `emailAffiliate` BOOLEAN NOT NULL DEFAULT true,
    `emailFollow` BOOLEAN NOT NULL DEFAULT true,
    `emailComment` BOOLEAN NOT NULL DEFAULT true,
    `emailLike` BOOLEAN NOT NULL DEFAULT true,
    `emailMessage` BOOLEAN NOT NULL DEFAULT true,
    `emailAnnouncement` BOOLEAN NOT NULL DEFAULT true,
    `emailBookPublished` BOOLEAN NOT NULL DEFAULT true,
    `emailBookUnpublished` BOOLEAN NOT NULL DEFAULT true,
    `emailBookRejected` BOOLEAN NOT NULL DEFAULT true,
    `emailCoauthorInvitation` BOOLEAN NOT NULL DEFAULT true,
    `emailCoauthorAccepted` BOOLEAN NOT NULL DEFAULT true,
    `emailCoauthorRejected` BOOLEAN NOT NULL DEFAULT true,
    `smsSystem` BOOLEAN NOT NULL DEFAULT false,
    `smsOrder` BOOLEAN NOT NULL DEFAULT true,
    `smsPayment` BOOLEAN NOT NULL DEFAULT true,
    `smsAffiliate` BOOLEAN NOT NULL DEFAULT true,
    `smsFollow` BOOLEAN NOT NULL DEFAULT false,
    `smsComment` BOOLEAN NOT NULL DEFAULT false,
    `smsLike` BOOLEAN NOT NULL DEFAULT false,
    `smsMessage` BOOLEAN NOT NULL DEFAULT false,
    `smsAnnouncement` BOOLEAN NOT NULL DEFAULT true,
    `smsBookPublished` BOOLEAN NOT NULL DEFAULT false,
    `smsBookUnpublished` BOOLEAN NOT NULL DEFAULT false,
    `smsBookRejected` BOOLEAN NOT NULL DEFAULT false,
    `smsCoauthorInvitation` BOOLEAN NOT NULL DEFAULT false,
    `smsCoauthorAccepted` BOOLEAN NOT NULL DEFAULT false,
    `smsCoauthorRejected` BOOLEAN NOT NULL DEFAULT false,
    `enabledSystem` BOOLEAN NOT NULL DEFAULT true,
    `enabledOrder` BOOLEAN NOT NULL DEFAULT true,
    `enabledPayment` BOOLEAN NOT NULL DEFAULT true,
    `enabledAffiliate` BOOLEAN NOT NULL DEFAULT true,
    `enabledFollow` BOOLEAN NOT NULL DEFAULT true,
    `enabledComment` BOOLEAN NOT NULL DEFAULT true,
    `enabledLike` BOOLEAN NOT NULL DEFAULT true,
    `enabledMessage` BOOLEAN NOT NULL DEFAULT true,
    `enabledAnnouncement` BOOLEAN NOT NULL DEFAULT true,
    `enabledBookPublished` BOOLEAN NOT NULL DEFAULT true,
    `enabledBookUnpublished` BOOLEAN NOT NULL DEFAULT true,
    `enabledBookRejected` BOOLEAN NOT NULL DEFAULT true,
    `enabledCoauthorInvitation` BOOLEAN NOT NULL DEFAULT true,
    `enabledCoauthorAccepted` BOOLEAN NOT NULL DEFAULT true,
    `enabledCoauthorRejected` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `notification_preferences_userId_key`(`userId`),
    INDEX `notification_preferences_userId_idx`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `contact_forms` (
    `id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `subject` VARCHAR(191) NULL,
    `message` TEXT NOT NULL,
    `status` VARCHAR(191) NOT NULL DEFAULT 'NEW',
    `ip_address` VARCHAR(191) NULL,
    `user_agent` TEXT NULL,
    `metadata` LONGTEXT NULL,
    `resolved_at` DATETIME(3) NULL,
    `resolved_by` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `contact_forms_email_idx`(`email`),
    INDEX `contact_forms_status_idx`(`status`),
    INDEX `contact_forms_created_at_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `webhook_queue` (
    `id` VARCHAR(191) NOT NULL,
    `processor` VARCHAR(191) NOT NULL,
    `eventType` VARCHAR(191) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `processingAttempts` INTEGER NOT NULL DEFAULT 0,
    `maxAttempts` INTEGER NOT NULL DEFAULT 3,
    `status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'RETRY_LATER') NOT NULL DEFAULT 'PENDING',
    `processedAt` DATETIME(3) NULL,
    `error` TEXT NULL,
    `priority` INTEGER NOT NULL DEFAULT 5,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `scheduledFor` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `webhook_queue_status_idx`(`status`),
    INDEX `webhook_queue_processor_idx`(`processor`),
    INDEX `webhook_queue_scheduledFor_idx`(`scheduledFor`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `reading_progress` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `progress` DOUBLE NOT NULL DEFAULT 0,
    `currentPage` INTEGER NOT NULL DEFAULT 1,
    `totalPages` INTEGER NOT NULL DEFAULT 1,
    `lastPosition` LONGTEXT NULL,
    `completedAt` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `reading_progress_userId_idx`(`userId`),
    INDEX `reading_progress_bookId_idx`(`bookId`),
    INDEX `reading_progress_updatedAt_idx`(`updatedAt`),
    UNIQUE INDEX `reading_progress_userId_bookId_key`(`userId`, `bookId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `payment_reconciliations` (
    `id` VARCHAR(191) NOT NULL,
    `transactionId` VARCHAR(191) NOT NULL,
    `processor` VARCHAR(191) NOT NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(191) NOT NULL DEFAULT 'KES',
    `phoneNumber` VARCHAR(191) NULL,
    `matchedPaymentId` VARCHAR(191) NULL,
    `matchedAt` DATETIME(3) NULL,
    `status` ENUM('PENDING', 'MATCHED', 'UNMATCHED', 'IGNORED') NOT NULL DEFAULT 'PENDING',
    `notes` TEXT NULL,
    `rawData` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    INDEX `payment_reconciliations_transactionId_idx`(`transactionId`),
    INDEX `payment_reconciliations_matchedPaymentId_idx`(`matchedPaymentId`),
    INDEX `payment_reconciliations_status_idx`(`status`),
    UNIQUE INDEX `payment_reconciliations_transactionId_processor_key`(`transactionId`, `processor`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `book_views` (
    `id` VARCHAR(191) NOT NULL,
    `bookId` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NULL,
    `viewerKey` VARCHAR(191) NOT NULL,
    `hourBucket` DATETIME(3) NOT NULL,
    `sessionId` VARCHAR(191) NULL,
    `ipAddress` VARCHAR(191) NULL,
    `userAgent` TEXT NULL,
    `referrer` TEXT NULL,
    `country` VARCHAR(2) NULL,
    `region` VARCHAR(191) NULL,
    `city` VARCHAR(191) NULL,
    `utmSource` VARCHAR(191) NULL,
    `utmMedium` VARCHAR(191) NULL,
    `utmCampaign` VARCHAR(191) NULL,
    `isBot` BOOLEAN NOT NULL DEFAULT false,
    `isCounted` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `book_views_bookId_idx`(`bookId`),
    INDEX `book_views_userId_idx`(`userId`),
    INDEX `book_views_createdAt_idx`(`createdAt`),
    INDEX `book_views_country_idx`(`country`),
    INDEX `book_views_utmSource_idx`(`utmSource`),
    UNIQUE INDEX `book_views_bookId_viewerKey_hourBucket_key`(`bookId`, `viewerKey`, `hourBucket`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `_userfollows` (
    `A` VARCHAR(191) NOT NULL,
    `B` VARCHAR(191) NOT NULL,

    UNIQUE INDEX `_userfollows_AB_unique`(`A`, `B`),
    INDEX `_userfollows_B_index`(`B`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `books` ADD CONSTRAINT `books_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `authors`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `books` ADD CONSTRAINT `books_categoryId_fkey` FOREIGN KEY (`categoryId`) REFERENCES `categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `books` ADD CONSTRAINT `books_paperTypeId_fkey` FOREIGN KEY (`paperTypeId`) REFERENCES `paper_types`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

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

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

-- AddForeignKey
ALTER TABLE `cart_items` ADD CONSTRAINT `cart_items_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `cart_items` ADD CONSTRAINT `cart_items_cartId_fkey` FOREIGN KEY (`cartId`) REFERENCES `carts`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `orders` ADD CONSTRAINT `orders_affiliateId_fkey` FOREIGN KEY (`affiliateId`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `order_status_history` ADD CONSTRAINT `order_status_history_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `order_transactions` ADD CONSTRAINT `order_transactions_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `payouts` ADD CONSTRAINT `payouts_affiliateId_fkey` FOREIGN KEY (`affiliateId`) REFERENCES `affiliates`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `referrals` ADD CONSTRAINT `referrals_affiliateId_fkey` FOREIGN KEY (`affiliateId`) REFERENCES `affiliates`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `referrals` ADD CONSTRAINT `referrals_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `referrals` ADD CONSTRAINT `referrals_referredUserId_fkey` FOREIGN KEY (`referredUserId`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `wishlist_items` ADD CONSTRAINT `wishlist_items_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `wishlist_items` ADD CONSTRAINT `wishlist_items_wishlistId_fkey` FOREIGN KEY (`wishlistId`) REFERENCES `wishlists`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `reviews` ADD CONSTRAINT `reviews_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `refunds` ADD CONSTRAINT `refunds_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `refund_items` ADD CONSTRAINT `refund_items_orderItemId_fkey` FOREIGN KEY (`orderItemId`) REFERENCES `order_items`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `refund_items` ADD CONSTRAINT `refund_items_refundId_fkey` FOREIGN KEY (`refundId`) REFERENCES `refunds`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_follows` ADD CONSTRAINT `user_follows_followerId_fkey` FOREIGN KEY (`followerId`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_follows` ADD CONSTRAINT `user_follows_followingId_fkey` FOREIGN KEY (`followingId`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `transactions` ADD CONSTRAINT `transactions_walletId_fkey` FOREIGN KEY (`walletId`) REFERENCES `wallets`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `bookauthor` ADD CONSTRAINT `BookAuthor_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `bookauthorinvitation` ADD CONSTRAINT `BookAuthorInvitation_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookauthorinvitation` ADD CONSTRAINT `BookAuthorInvitation_inviteeId_fkey` FOREIGN KEY (`inviteeId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `bookauthorinvitation` ADD CONSTRAINT `BookAuthorInvitation_inviterId_fkey` FOREIGN KEY (`inviterId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `mpesatransaction` ADD CONSTRAINT `MpesaTransaction_paymentId_fkey` FOREIGN KEY (`paymentId`) REFERENCES `payment`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `payment` ADD CONSTRAINT `Payment_orderId_fkey` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `reading_progress` ADD CONSTRAINT `reading_progress_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `payment_reconciliations` ADD CONSTRAINT `payment_reconciliations_matchedPaymentId_fkey` FOREIGN KEY (`matchedPaymentId`) REFERENCES `payment`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `book_views` ADD CONSTRAINT `book_views_bookId_fkey` FOREIGN KEY (`bookId`) REFERENCES `books`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE `_userfollows` ADD CONSTRAINT `_userfollows_A_fkey` FOREIGN KEY (`A`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `_userfollows` ADD CONSTRAINT `_userfollows_B_fkey` FOREIGN KEY (`B`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;