CREATE TABLE `seller_reviews` (
    `id` VARCHAR(191) NOT NULL,
    `seller_id` VARCHAR(191) NOT NULL,
    `reviewer_id` VARCHAR(191) NOT NULL,
    `listing_id` VARCHAR(191) NOT NULL,
    `rating` INTEGER NOT NULL,
    `comment` TEXT NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `seller_reviews_seller_id_reviewer_id_key`(`seller_id`, `reviewer_id`),
    INDEX `seller_reviews_seller_id_created_at_idx`(`seller_id`, `created_at`),
    INDEX `seller_reviews_listing_id_created_at_idx`(`listing_id`, `created_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `seller_reviews` ADD CONSTRAINT `seller_reviews_seller_id_fkey` FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `seller_reviews` ADD CONSTRAINT `seller_reviews_reviewer_id_fkey` FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `seller_reviews` ADD CONSTRAINT `seller_reviews_listing_id_fkey` FOREIGN KEY (`listing_id`) REFERENCES `car_listings`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
