

-- V3 uses one permanent public survey URL. Token tables are no longer required.
-- Existing V2 reviews are preserved where possible.

SET @fk_name := (
    SELECT CONSTRAINT_NAME
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'reviews'
      AND COLUMN_NAME = 'survey_link_id'
      AND REFERENCED_TABLE_NAME IS NOT NULL
    LIMIT 1
);
SET @sql := IF(@fk_name IS NOT NULL, CONCAT('ALTER TABLE reviews DROP FOREIGN KEY `', @fk_name, '`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reviews' AND INDEX_NAME = 'uq_review_link'
);
SET @sql := IF(@idx_exists > 0, 'ALTER TABLE reviews DROP INDEX uq_review_link', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reviews' AND COLUMN_NAME = 'survey_link_id'
);
SET @sql := IF(@col_exists > 0, 'ALTER TABLE reviews DROP COLUMN survey_link_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE reviews
    MODIFY agreement_no VARCHAR(60) NULL,
    MODIFY rental_date DATE NULL,
    MODIFY employee_name VARCHAR(120) NULL;

SET @prof_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reviews' AND COLUMN_NAME = 'professionalism_rating'
);
SET @sql := IF(@prof_exists = 0,
    'ALTER TABLE reviews ADD COLUMN professionalism_rating TINYINT UNSIGNED NULL AFTER staff_rating',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Old rows did not contain this question; use staff rating as the historical fallback.
UPDATE reviews SET professionalism_rating = staff_rating WHERE professionalism_rating IS NULL;
ALTER TABLE reviews MODIFY professionalism_rating TINYINT UNSIGNED NOT NULL;

SET @agreement_index_exists := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'reviews' AND INDEX_NAME = 'uq_review_agreement'
);
SET @agreement_duplicates := (
    SELECT COUNT(*) FROM (
        SELECT agreement_no FROM reviews
        WHERE agreement_no IS NOT NULL AND agreement_no <> ''
        GROUP BY agreement_no HAVING COUNT(*) > 1
    ) d
);
SET @sql := IF(@agreement_index_exists = 0 AND @agreement_duplicates = 0,
    'ALTER TABLE reviews ADD UNIQUE KEY uq_review_agreement (agreement_no)',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

DROP TABLE IF EXISTS survey_links;
