<?php
/**
 * Database Setup Script - Report Responses Table
 * GHAMECC Choir Management Platform
 * 
 * Run this script to ensure the report_responses table exists
 * Usage: php setup_report_responses.php
 */

// Include database functions
require_once '../config/database.php';
require_once '../config/logger.php';

echo "Setting up report_responses table...\n";

// Check if table exists
$tableExistsQuery = "SHOW TABLES LIKE 'report_responses'";
$tableExists = $conn->query($tableExistsQuery);
$tableExistsBefore = $tableExists && $tableExists->num_rows > 0;

echo "Table exists before setup: " . ($tableExistsBefore ? "YES" : "NO") . "\n";

if ($tableExistsBefore) {
    echo "Table already exists. Checking structure...\n";
    
    // Check table structure
    $describeQuery = "DESCRIBE report_responses";
    $result = $conn->query($describeQuery);
    
    if ($result) {
        echo "Current table structure:\n";
        while ($row = $result->fetch_assoc()) {
            echo "  - {$row['Field']}: {$row['Type']}\n";
        }
    }
    
    echo "Setup complete - table already exists.\n";
    exit(0);
}

// Create the table
$createTableSQL = "CREATE TABLE `report_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `response_text` text NOT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_report_id` (`report_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

echo "Creating report_responses table...\n";

try {
    $createResult = $conn->query($createTableSQL);
    
    if ($createResult) {
        echo "✓ Table created successfully\n";
        
        // Verify table was created
        $verifyExists = $conn->query($tableExistsQuery);
        if ($verifyExists && $verifyExists->num_rows > 0) {
            echo "✓ Table creation verified\n";
        } else {
            echo "✗ Table creation verification failed\n";
            exit(1);
        }
        
    } else {
        echo "✗ Table creation failed: " . $conn->error . "\n";
        exit(1);
    }
    
} catch (Exception $e) {
    echo "✗ Exception during table creation: " . $e->getMessage() . "\n";
    exit(1);
}

// Add foreign key constraints
echo "Adding foreign key constraints...\n";

$constraints = [
    "fk_report_responses_report_id" => "ALTER TABLE `report_responses` ADD CONSTRAINT `fk_report_responses_report_id` FOREIGN KEY (`report_id`) REFERENCES `reports` (`id`) ON DELETE CASCADE",
    "fk_report_responses_user_id" => "ALTER TABLE `report_responses` ADD CONSTRAINT `fk_report_responses_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE"
];

foreach ($constraints as $constraintName => $constraintSQL) {
    try {
        echo "  Adding constraint: {$constraintName}...\n";
        $constraintResult = $conn->query($constraintSQL);
        
        if ($constraintResult) {
            echo "  ✓ Constraint {$constraintName} added successfully\n";
        } else {
            // Check if constraint already exists
            if (strpos($conn->error, 'Duplicate key name') !== false || 
                strpos($conn->error, 'already exists') !== false) {
                echo "  ℹ Constraint {$constraintName} already exists\n";
            } else {
                echo "  ⚠ Constraint {$constraintName} failed: " . $conn->error . "\n";
            }
        }
        
    } catch (Exception $e) {
        echo "  ⚠ Exception adding constraint {$constraintName}: " . $e->getMessage() . "\n";
    }
}

// Final verification
echo "Final verification...\n";

// Check table structure
$describeQuery = "DESCRIBE report_responses";
$result = $conn->query($describeQuery);

if ($result) {
    echo "Final table structure:\n";
    while ($row = $result->fetch_assoc()) {
        echo "  - {$row['Field']}: {$row['Type']}\n";
    }
} else {
    echo "✗ Could not describe table: " . $conn->error . "\n";
    exit(1);
}

// Test insert (and rollback)
echo "Testing table functionality...\n";

try {
    $conn->begin_transaction();
    
    $testSQL = "INSERT INTO report_responses (report_id, user_id, response_text, created_at) VALUES (999999, 999999, 'Test response', NOW())";
    $testResult = $conn->query($testSQL);
    
    if ($testResult) {
        echo "✓ Test insert successful\n";
        $conn->rollback(); // Rollback the test data
        echo "✓ Test data rolled back\n";
    } else {
        echo "✗ Test insert failed: " . $conn->error . "\n";
        $conn->rollback();
        exit(1);
    }
    
} catch (Exception $e) {
    echo "✗ Test insert exception: " . $e->getMessage() . "\n";
    $conn->rollback();
    exit(1);
}

$conn->close();

echo "\n✓ Setup completed successfully!\n";
echo "The report_responses table is now ready for use.\n";
?>
