-- Script to update report_responses table structure in production
-- This script checks if columns exist before adding them to avoid errors

-- Check if user_id column exists and add it if it doesn't
SET @columnExists = 0;
SELECT COUNT(*) INTO @columnExists FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND COLUMN_NAME = 'user_id';

SET @addUserIdColumn = CONCAT('ALTER TABLE `report_responses` ADD COLUMN `user_id` int(11) NOT NULL AFTER `report_id`');

SET @addUserIdFK = CONCAT('ALTER TABLE `report_responses` ADD CONSTRAINT `report_responses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)');

PREPARE stmt FROM @addUserIdColumn;
PREPARE stmt2 FROM @addUserIdFK;

-- Only execute if the column doesn't exist
SET @executeStmt = IF(@columnExists = 0, 'EXECUTE stmt', 'SELECT "user_id column already exists"');
SET @executeStmt2 = IF(@columnExists = 0, 'EXECUTE stmt2', 'SELECT "FK constraint not needed"');

PREPARE execStmt FROM @executeStmt;
PREPARE execStmt2 FROM @executeStmt2;

EXECUTE execStmt;
EXECUTE execStmt2;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE execStmt;
DEALLOCATE PREPARE execStmt2;

-- Check if response_text column exists and add it if it doesn't
SET @columnExists = 0;
SELECT COUNT(*) INTO @columnExists FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND COLUMN_NAME = 'response_text';

SET @addColumn = CONCAT('ALTER TABLE `report_responses` ADD COLUMN `response_text` text NOT NULL AFTER `user_id`');

PREPARE stmt FROM @addColumn;

-- Only execute if the column doesn't exist
SET @executeStmt = IF(@columnExists = 0, 'EXECUTE stmt', 'SELECT "response_text column already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;

-- Check if attachment column exists and add it if it doesn't
SET @columnExists = 0;
SELECT COUNT(*) INTO @columnExists FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND COLUMN_NAME = 'attachment';

SET @addColumn = CONCAT('ALTER TABLE `report_responses` ADD COLUMN `attachment` varchar(255) DEFAULT NULL AFTER `response_text`');

PREPARE stmt FROM @addColumn;

-- Only execute if the column doesn't exist
SET @executeStmt = IF(@columnExists = 0, 'EXECUTE stmt', 'SELECT "attachment column already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;

-- Check if created_at column exists and add it if it doesn't
SET @columnExists = 0;
SELECT COUNT(*) INTO @columnExists FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND COLUMN_NAME = 'created_at';

SET @addColumn = CONCAT('ALTER TABLE `report_responses` ADD COLUMN `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `attachment`');

PREPARE stmt FROM @addColumn;

-- Only execute if the column doesn't exist
SET @executeStmt = IF(@columnExists = 0, 'EXECUTE stmt', 'SELECT "created_at column already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;

-- Add indexes if they don't exist
SET @indexExists = 0;
SELECT COUNT(*) INTO @indexExists FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND INDEX_NAME = 'report_id';

SET @addIndex = CONCAT('ALTER TABLE `report_responses` ADD INDEX `report_id` (`report_id`)');

PREPARE stmt FROM @addIndex;

-- Only execute if the index doesn't exist
SET @executeStmt = IF(@indexExists = 0, 'EXECUTE stmt', 'SELECT "report_id index already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;

-- Add user_id index if it doesn't exist
SET @indexExists = 0;
SELECT COUNT(*) INTO @indexExists FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND INDEX_NAME = 'user_id';

SET @addIndex = CONCAT('ALTER TABLE `report_responses` ADD INDEX `user_id` (`user_id`)');

PREPARE stmt FROM @addIndex;

-- Only execute if the index doesn't exist
SET @executeStmt = IF(@indexExists = 0, 'EXECUTE stmt', 'SELECT "user_id index already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;

-- Add foreign key constraint for report_id if it doesn't exist
SET @fkExists = 0;
SELECT COUNT(*) INTO @fkExists FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'report_responses' AND CONSTRAINT_NAME = 'report_responses_ibfk_1';

SET @addFK = CONCAT('ALTER TABLE `report_responses` ADD CONSTRAINT `report_responses_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `reports` (`id`) ON DELETE CASCADE');

PREPARE stmt FROM @addFK;

-- Only execute if the FK doesn't exist
SET @executeStmt = IF(@fkExists = 0, 'EXECUTE stmt', 'SELECT "report_id FK already exists"');

PREPARE execStmt FROM @executeStmt;
EXECUTE execStmt;

DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE execStmt;
