

CREATE TABLE IF NOT EXISTS reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_code VARCHAR(20) NOT NULL,
    agreement_no VARCHAR(60) NULL,
    rental_date DATE NULL,
    employee_name VARCHAR(120) NULL,
    wait_time VARCHAR(30) NOT NULL,
    process_time VARCHAR(30) NOT NULL,
    office_find_rating TINYINT UNSIGNED NOT NULL,
    pickup_speed_rating TINYINT UNSIGNED NOT NULL,
    staff_rating TINYINT UNSIGNED NOT NULL,
    professionalism_rating TINYINT UNSIGNED NOT NULL,
    terms_rating TINYINT UNSIGNED NOT NULL,
    vehicle_condition_rating TINYINT UNSIGNED NOT NULL,
    cleanliness_rating TINYINT UNSIGNED NOT NULL,
    overall_rating TINYINT UNSIGNED NOT NULL,
    rent_again TINYINT(1) NOT NULL,
    comment TEXT NULL,
    ip_hash CHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_review_agreement (agreement_no),
    INDEX idx_review_branch_date (branch_code, created_at),
    INDEX idx_review_employee_date (employee_name, created_at),
    INDEX idx_review_overall (overall_rating)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
