<?php
/**
 * Migration Script: Create CRM Support Logs Table (SQLite)
 * Run this script to create the crm_support_logs table in SQLite
 */

// Database path
$dbPath = __DIR__ . '/../../data/databases/crm_database.db';

if (!file_exists($dbPath)) {
    echo "✗ Database file not found: $dbPath\n";
    exit(1);
}

try {
    $pdo = new PDO("sqlite:$dbPath");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Starting migration: create_crm_support_logs...\n";
    echo "Database: $dbPath\n\n";
    
    // Read SQL migration file
    $sql = file_get_contents(__DIR__ . '/../../migrations/create_crm_support_logs.sql');
    
    if ($sql === false) {
        throw new Exception("Could not read migration file");
    }
    
    // Execute the migration
    $pdo->exec($sql);
    
    echo "✓ Migration completed successfully!\n";
    echo "✓ Table crm_support_logs created\n";
    echo "✓ Indexes created\n";
    
    // Verify table exists
    $stmt = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='crm_support_logs'");
    $table = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($table) {
        echo "✓ Table verified: crm_support_logs exists\n";
        
        // Show table structure
        $stmt = $pdo->query("PRAGMA table_info(crm_support_logs)");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "\nTable structure:\n";
        echo str_repeat("-", 60) . "\n";
        foreach ($columns as $col) {
            echo sprintf("  %-20s %-15s %s\n", 
                $col['name'], 
                $col['type'], 
                $col['notnull'] ? 'NOT NULL' : 'NULL'
            );
        }
        echo str_repeat("-", 60) . "\n";
    } else {
        throw new Exception("Table verification failed");
    }
    
    echo "\n✓ All done!\n";
    
} catch (Exception $e) {
    echo "✗ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}

