desk-moloni/create_tables_standalone.php

/**
 * Descomplicar® Crescimento Digital
 * https://descomplicar.pt
 */

<?php

// Standalone table creator for Desk-Moloni
// This creates tables without depending on Perfex CRM

echo "🚀 Creating Desk-Moloni tables...\n";

try {
    // Try to read database config from wp-config or similar
    $host = 'localhost';
    $dbname = 'desk_descomplicar_pt';  
    $username = 'desk_descomplicar_pt';
    
    // Common password guesses (replace with actual)
    $possible_passwords = [
        'desk_descomplicar_pt',
        'password',
        'admin',
        'root',
        '123456'
    ];
    
    $pdo = null;
    foreach ($possible_passwords as $password) {
        try {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "✅ Connected to database: $dbname (password found)\n";
            break;
        } catch (PDOException $e) {
            continue; // Try next password
        }
    }
    
    if (!$pdo) {
        echo "❌ Could not connect to database with any common password\n";
        exit(1);
    }
    
    // Create mapping table
    echo "📋 Creating mapping table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS `tbldeskmoloni_mapping` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `entity_type` enum('client','product','invoice','estimate','credit_note') NOT NULL,
        `perfex_id` int(11) NOT NULL,
        `moloni_id` int(11) NOT NULL,
        `sync_direction` enum('perfex_to_moloni','moloni_to_perfex','bidirectional') NOT NULL DEFAULT 'bidirectional',
        `last_sync_at` timestamp NULL DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_perfex_mapping` (`entity_type`, `perfex_id`),
        UNIQUE KEY `unique_moloni_mapping` (`entity_type`, `moloni_id`),
        KEY `idx_entity_perfex` (`entity_type`, `perfex_id`),
        KEY `idx_entity_moloni` (`entity_type`, `moloni_id`),
        KEY `idx_last_sync` (`last_sync_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✅ Mapping table created\n";
    
    // Create sync queue table  
    echo "📋 Creating sync queue table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS `tbldeskmoloni_sync_queue` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `entity_type` varchar(50) NOT NULL,
        `entity_id` int(11) NOT NULL,
        `perfex_id` int(11) DEFAULT NULL,
        `moloni_id` int(11) DEFAULT NULL,
        `action` enum('create','update','delete','sync') NOT NULL DEFAULT 'sync',
        `direction` enum('perfex_to_moloni','moloni_to_perfex','bidirectional') NOT NULL DEFAULT 'bidirectional',
        `priority` enum('low','normal','high','critical') NOT NULL DEFAULT 'normal',
        `status` enum('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
        `attempts` int(11) NOT NULL DEFAULT 0,
        `max_attempts` int(11) NOT NULL DEFAULT 3,
        `data` longtext DEFAULT NULL COMMENT 'JSON data for sync',
        `error_message` text DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `scheduled_at` timestamp NULL DEFAULT NULL,
        `started_at` timestamp NULL DEFAULT NULL,
        `completed_at` timestamp NULL DEFAULT NULL,
        `created_by` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_entity_type_id` (`entity_type`, `entity_id`),
        KEY `idx_status_priority` (`status`, `priority`),
        KEY `idx_scheduled_at` (`scheduled_at`),
        KEY `idx_perfex_id` (`perfex_id`),
        KEY `idx_moloni_id` (`moloni_id`),
        KEY `idx_created_by` (`created_by`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✅ Queue table created\n";
    
    // Create sync log table
    echo "📋 Creating sync log table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS `tbldeskmoloni_sync_log` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `operation_type` enum('create','update','delete','status_change') NOT NULL,
        `entity_type` enum('client','product','invoice','estimate','credit_note') NOT NULL,
        `perfex_id` int(11) DEFAULT NULL,
        `moloni_id` int(11) DEFAULT NULL,
        `direction` enum('perfex_to_moloni','moloni_to_perfex') NOT NULL,
        `status` enum('success','error','warning') NOT NULL,
        `request_data` longtext DEFAULT NULL COMMENT 'JSON request data',
        `response_data` longtext DEFAULT NULL COMMENT 'JSON response data',
        `error_message` text DEFAULT NULL,
        `execution_time_ms` int(11) DEFAULT NULL COMMENT 'Execution time in milliseconds',
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_entity_status` (`entity_type`, `status`),
        KEY `idx_perfex_entity` (`perfex_id`, `entity_type`),
        KEY `idx_moloni_entity` (`moloni_id`, `entity_type`),
        KEY `idx_created_at` (`created_at`),
        KEY `idx_status_direction` (`status`, `direction`),
        KEY `idx_log_analytics` (`entity_type`, `operation_type`, `status`, `created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "✅ Log table created\n";
    
    // Verify tables
    echo "\n📊 Verifying tables...\n";
    $stmt = $pdo->query("SHOW TABLES LIKE 'tbldeskmoloni_%'");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    foreach ($tables as $table) {
        echo "  ✅ $table exists\n";
    }
    
    echo "\n🎉 All tables created successfully!\n";
    echo "Total Desk-Moloni tables: " . count($tables) . "\n";
    
} catch (PDOException $e) {
    echo "❌ Database error: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "❌ General error: " . $e->getMessage() . "\n";
}

echo "\n✨ Done!\n";