CREATE TABLE IF NOT EXISTS `reviews` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_id`   INT UNSIGNED NOT NULL,
    `client_id`    INT UNSIGNED NOT NULL,
    `therapist_id` INT UNSIGNED NOT NULL,
    `rating`       TINYINT UNSIGNED NOT NULL COMMENT '1–5',
    `comment`      TEXT NULL,
    `is_approved`  TINYINT(1) NOT NULL DEFAULT 1,
    `reply`        TEXT NULL COMMENT 'Therapist reply',
    `replied_at`   DATETIME NULL,
    `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `reviews_booking_unique` (`booking_id`),
    KEY `reviews_therapist_id_index` (`therapist_id`),
    KEY `reviews_client_id_index` (`client_id`),
    KEY `reviews_rating_index` (`rating`),
    CONSTRAINT `fk_rv_booking`   FOREIGN KEY (`booking_id`)   REFERENCES `bookings` (`id`),
    CONSTRAINT `fk_rv_client`    FOREIGN KEY (`client_id`)    REFERENCES `users` (`id`),
    CONSTRAINT `fk_rv_therapist` FOREIGN KEY (`therapist_id`) REFERENCES `therapist_profiles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
