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

<?php

// Simple table creator using direct MySQL connection
// This bypasses CodeIgniter and creates tables directly

echo "🚀 Creating Desk-Moloni mapping table...\n";

// Database configuration - adjust these if needed
$host = 'localhost';
$dbname = 'desk_descomplicar_pt';
$username = 'desk_descomplicar_pt';
$password = 'your_password_here'; // You'll need to set this

// Try to read database config from Perfex CRM
$config_file = '/home/ealmeida/desk.descomplicar.pt/application/config/database.php';
if (file_exists($config_file)) {
    echo "📋 Reading database config...\n";
    include $config_file;
    if (isset($db['default'])) {
        $host = $db['default']['hostname'];
        $dbname = $db['default']['database'];
        $username = $db['default']['username'];
        $password = $db['default']['password'];
        echo "✅ Config loaded successfully!\n";
    }
}

try {
    // Connect to MySQL
    $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\n";
    
    // Create mapping table
    $sql = "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";
    
    $pdo->exec($sql);
    echo "✅ Table tbldeskmoloni_mapping created successfully!\n";
    
    // Verify table exists
    $check = $pdo->query("SHOW TABLES LIKE 'tbldeskmoloni_mapping'");
    if ($check->rowCount() > 0) {
        echo "✅ Table verified - exists in database!\n";
        
        // Show columns
        echo "\n📋 Table structure:\n";
        $columns = $pdo->query("DESCRIBE tbldeskmoloni_mapping");
        foreach ($columns as $column) {
            echo "  - {$column['Field']}: {$column['Type']}\n";
        }
    } else {
        echo "❌ Table verification failed!\n";
    }
    
} catch (PDOException $e) {
    echo "❌ Database error: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "❌ General error: " . $e->getMessage() . "\n";
}

echo "\n✨ Done!\n";