whatsapp/scripts/migrate_add_conversation_fields.php

<?php
/**
 * Migration: Add conversation fields and indexes
 * - Creates a backup SQL file for `conversations` (CREATE TABLE + INSERTs)
 * - Adds columns if missing
 * - Adds indexes if missing
 *
 * Usage (CLI): php scripts/migrate_add_conversation_fields.php --yes
 * If --yes is not passed the script will prompt for confirmation.
 */

require_once __DIR__ . '/../config/config.php';

$options = getopt('', ['yes']);
$force = isset($options['yes']);

$dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . (defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4');
try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (Throwable $t) {
    echo "Error connecting to DB: " . $t->getMessage() . PHP_EOL;
    exit(1);
}

echo "This script will backup table `conversations` and apply schema changes.\n";
if (!$force) {
    fwrite(STDOUT, "Proceed? (yes/no): ");
    $answer = trim(fgets(STDIN));
    if (strtolower($answer) !== 'yes') {
        echo "Aborted by user.\n";
        exit(0);
    }
}

// 1) Backup: get CREATE TABLE and all rows
$timestamp = date('Ymd_His');
$backupDir = __DIR__ . '/../backups';
if (!is_dir($backupDir)) mkdir($backupDir, 0755, true);
$backupFile = $backupDir . "/conversations_backup_{$timestamp}.sql";

try {
    // Show create
    $row = $pdo->query("SHOW CREATE TABLE `conversations`")->fetch(PDO::FETCH_ASSOC);
    $createSql = $row['Create Table'] ?? null;
    if (!$createSql) throw new Exception('Could not obtain CREATE TABLE for conversations');

    $fh = fopen($backupFile, 'w');
    fwrite($fh, "-- Backup of table `conversations` generated at {$timestamp}\n\n");
    fwrite($fh, "DROP TABLE IF EXISTS `conversations`;\n");
    fwrite($fh, $createSql . ";\n\n");

    // Dump rows as INSERTs in batches
    $stmt = $pdo->query("SELECT * FROM `conversations`");
    $cols = [];
    $insertCount = 0;
    $batchSize = 200;
    $rows = [];
    while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
        if (empty($cols)) $cols = array_keys($r);
        $values = array_map(function($v) use ($pdo) {
            if (is_null($v)) return 'NULL';
            // Ensure non-binary scalars are properly quoted as strings
            return $pdo->quote((string)$v);
        }, array_values($r));
        $rows[] = '(' . implode(',', $values) . ')';
        if (count($rows) >= $batchSize) {
            $line = "INSERT INTO `conversations` (`" . implode('`,`', $cols) . "`) VALUES\n" . implode(",\n", $rows) . ";\n";
            fwrite($fh, $line);
            $insertCount += count($rows);
            $rows = [];
        }
    }
    if (count($rows)) {
        $line = "INSERT INTO `conversations` (`" . implode('`,`', $cols) . "`) VALUES\n" . implode(",\n", $rows) . ";\n";
        fwrite($fh, $line);
        $insertCount += count($rows);
    }
    fclose($fh);
    echo "Backup written to: {$backupFile} (rows: {$insertCount})\n";
} catch (Throwable $t) {
    echo "Backup failed: " . $t->getMessage() . PHP_EOL;
    exit(1);
}

// 2) Prepare ALTER statements (only add if missing)
$columnsToAdd = [
    'message_id' => "VARCHAR(255) DEFAULT NULL",
    'message_type' => "VARCHAR(32) DEFAULT 'text'",
    'media_url' => "TEXT DEFAULT NULL",
    'local_file' => "VARCHAR(255) DEFAULT NULL",
    'local_thumb' => "VARCHAR(255) DEFAULT NULL",
    'filename' => "VARCHAR(255) DEFAULT NULL",
    'mime_type' => "VARCHAR(100) DEFAULT NULL",
    'reply_to_message_id' => "VARCHAR(255) DEFAULT NULL",
    'reaction_emoji' => "VARCHAR(64) DEFAULT NULL",
    'reaction_to_message_id' => "VARCHAR(255) DEFAULT NULL",
    'status' => "VARCHAR(32) DEFAULT 'received'"
];

// Gather existing columns and their types
$existingColsStmt = $pdo->query("SHOW COLUMNS FROM `conversations`");
$existingCols = [];
$existingTypes = []; // field -> Type
foreach ($existingColsStmt->fetchAll(PDO::FETCH_ASSOC) as $c) {
    $existingCols[] = $c['Field'];
    $existingTypes[$c['Field']] = strtolower($c['Type']);
}

$alters = [];
foreach ($columnsToAdd as $col => $type) {
    if (!in_array($col, $existingCols)) {
        $alters[] = "ADD COLUMN `{$col}` {$type}";
    } else {
        // If the column exists but its type doesn't match the desired one, schedule a MODIFY
        $desiredType = strtolower(preg_replace('/\s+default\s+.*/i', '', $type)); // e.g. 'varchar(255)'
        if (!str_starts_with($existingTypes[$col], $desiredType)) {
            $alters[] = "MODIFY COLUMN `{$col}` {$type}";
            echo "Will modify column type for {$col}: found {$existingTypes[$col]} -> desired {$desiredType}\n";
        }
    }
}

try {
    if (count($alters)) {
        $sql = "ALTER TABLE `conversations` " . implode(', ', $alters) . ";";
        $pdo->exec($sql);
        echo "Applied ALTER TABLE to add columns: " . implode(', ', array_keys($columnsToAdd)) . "\n";
    } else {
        echo "No new columns to add.\n";
    }
} catch (Throwable $t) {
    echo "ALTER TABLE failed: " . $t->getMessage() . PHP_EOL;
    echo "You can manually inspect {$backupFile} and run the appropriate ALTERs.\n";
    exit(1);
}

// 3) Add indexes if missing
$indexes = [
    'idx_message_id' => ['cols' => ['message_id'], 'unique' => false],
    'idx_reply' => ['cols' => ['reply_to_message_id'], 'unique' => false],
];

$existingIndexes = [];
$rs = $pdo->query("SHOW INDEX FROM `conversations`");
foreach ($rs->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $existingIndexes[$row['Key_name']][] = $row['Column_name'];
}

foreach ($indexes as $name => $info) {
    if (!isset($existingIndexes[$name])) {
        $colsSql = implode('`,`', $info['cols']);
        $sql = "CREATE INDEX `{$name}` ON `conversations` (`{$colsSql}`);";
        try {
            $pdo->exec($sql);
            echo "Added index {$name} (" . implode(',', $info['cols']) . ")\n";
        } catch (Throwable $t) {
            echo "Failed to add index {$name}: " . $t->getMessage() . PHP_EOL;
        }
    } else {
        echo "Index {$name} already exists.\n";
    }
}

echo "Migration complete. Backup file: {$backupFile}\n";

return 0;