#!/usr/bin/php -c/var/www/scopserv/config/php.ini
<?php

/**
 * Migrate CDR and CEL tables to InnoDB engine
 *
 * This script checks all cdr* and cel* tables (including archived tables
 * like cdr_2025, cdr_2025_12, cel_2025, etc.) and converts them to InnoDB
 * if they are not already using that engine.
 *
 * Usage: php migrate_database.php
 */

@define('AUTH_HANDLER', true);
ini_set('include_path', dirname(__FILE__) . '/../../framework');
@define('ASTERISK_BASE', dirname(__FILE__) . '/..');
require_once ASTERISK_BASE . '/lib/base.php';

require_once 'Horde/CLI.php';

$cli = new Horde_CLI();

if (!$cli->runningFromCLI()) {
    $cli->fatal("This script must be run from the command line.");
}

$cli->init();

print "==============================================\n";
print "CDR/CEL Table InnoDB Migration Script\n";
print "==============================================\n\n";

// Get all CDR and CEL tables
$tables = getTablesWithPattern(array('cdr%', 'cel%'));

if (empty($tables)) {
    print "No CDR or CEL tables found.\n";
    exit(0);
}

print "Found " . count($tables) . " table(s) to check.\n\n";

$migrated = 0;
$skipped = 0;
$errors = 0;

// Migrate each table to InnoDB
foreach ($tables as $table) {
    $result = migrateToInnoDB($table);
    if ($result === true) {
        $migrated++;
    } elseif ($result === false) {
        $errors++;
    } else {
        $skipped++;
    }
}

print "\n==============================================\n";
print "Migration Complete\n";
print "==============================================\n";
print "Tables migrated: $migrated\n";
print "Tables skipped (already InnoDB): $skipped\n";
print "Errors: $errors\n";

/**
 * Get all tables matching the given patterns
 *
 * @param array $patterns Array of SQL LIKE patterns
 * @return array Array of table info with name and engine
 */
function getTablesWithPattern($patterns)
{
    global $asterisk;
    $tables = array();

    foreach ($patterns as $pattern) {
        $sql = "SHOW TABLE STATUS WHERE Name LIKE '$pattern'";
        $res = $asterisk->_db_report_write->query($sql);

        if (is_a($res, 'PEAR_Error')) {
            print "Error querying tables for pattern '$pattern': {$res->getMessage()}\n";
            continue;
        }

        while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            $tables[] = array(
                'name' => $row['Name'],
                'engine' => $row['Engine']
            );
        }
    }

    return $tables;
}

/**
 * Migrate a single table to InnoDB if needed
 *
 * @param array $table Table info with name and engine keys
 * @return mixed true if migrated, false if error, null if skipped
 */
function migrateToInnoDB($table)
{
    global $asterisk;

    $name = $table['name'];
    $engine = $table['engine'];

    if ($engine === 'InnoDB') {
        print "Table '$name' is already using InnoDB - skipping\n";
        return null;
    }

    print "Migrating table '$name' from $engine to InnoDB...\n";
    flush();

    $sql = "ALTER TABLE `$name` ENGINE=InnoDB";
    $res = $asterisk->_db_report_write->query($sql);

    if (is_a($res, 'PEAR_Error')) {
        print " [ERROR] {$res->getMessage()}\n";
        return false;
    }

    print " [SUCCESS] Table '$name' converted to InnoDB\n";
    return true;
}