<?php

/**
 * Migration Script: Add Creator Fields to Tables
 *
 * Adds created_by_didar_id, created_by_name fields to activities, deals, persons tables
 * Adds lead_type field to deals table
 * Adds is_duplicate_lead field to persons table
 */

echo "Starting migration: Add Creator Fields\n";

// Use SQLite database
$dbPath = __DIR__ . '/../../data/databases/crm_database.db';

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

try {
    $pdo = new PDO("sqlite:$dbPath");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbType = 'sqlite';
    echo "✓ SQLite connection established\n";
} catch (Exception $e) {
    die("✗ SQLite connection failed: " . $e->getMessage() . "\n");
}

// Migration queries
$migrationQueries = [
    // Activities table
    "ALTER TABLE activities ADD COLUMN created_by_didar_id TEXT",
    "ALTER TABLE activities ADD COLUMN created_by_name TEXT",

    // Deals table
    "ALTER TABLE deals ADD COLUMN created_by_didar_id TEXT",
    "ALTER TABLE deals ADD COLUMN created_by_name TEXT",
    "ALTER TABLE deals ADD COLUMN lead_type TEXT DEFAULT 'new'",

    // Persons table
    "ALTER TABLE persons ADD COLUMN created_by_didar_id TEXT",
    "ALTER TABLE persons ADD COLUMN created_by_name TEXT",
    "ALTER TABLE persons ADD COLUMN is_duplicate_lead INTEGER DEFAULT 0"
];

// Execute migration queries
foreach ($migrationQueries as $query) {
    try {
        echo "Executing: $query\n";
        $pdo->exec($query);
        echo "✓ Success\n";
    } catch (Exception $e) {
        // Check if column already exists (ignore duplicate column errors)
        if (strpos($e->getMessage(), 'duplicate column name') !== false ||
            strpos($e->getMessage(), 'already exists') !== false) {
            echo "⚠ Column already exists, skipping\n";
            continue;
        }
        echo "✗ Error: " . $e->getMessage() . "\n";
        // Continue with other queries
    }
}

// Populate existing data with current user info where possible
echo "\nPopulating existing data...\n";

try {
    // Update activities - set creator to owner if not set
    $pdo->exec("
        UPDATE activities
        SET created_by_didar_id = owner_didar_id,
            created_by_name = (
                SELECT display_name FROM users WHERE users.didar_user_id = activities.owner_didar_id LIMIT 1
            )
        WHERE created_by_didar_id IS NULL AND owner_didar_id IS NOT NULL
    ");
    echo "✓ Updated activities creator info\n";
} catch (Exception $e) {
    echo "⚠ Could not update activities: " . $e->getMessage() . "\n";
}

try {
    // Update deals - set creator to owner if not set
    $pdo->exec("
        UPDATE deals
        SET created_by_didar_id = owner_didar_id,
            created_by_name = (
                SELECT display_name FROM users WHERE users.didar_user_id = deals.owner_didar_id LIMIT 1
            )
        WHERE created_by_didar_id IS NULL AND owner_didar_id IS NOT NULL
    ");
    echo "✓ Updated deals creator info\n";
} catch (Exception $e) {
    echo "⚠ Could not update deals: " . $e->getMessage() . "\n";
}

try {
    // Update persons - set creator to owner if not set
    $pdo->exec("
        UPDATE persons
        SET created_by_didar_id = owner_didar_id,
            created_by_name = (
                SELECT display_name FROM users WHERE users.didar_user_id = persons.owner_didar_id LIMIT 1
            )
        WHERE created_by_didar_id IS NULL AND owner_didar_id IS NOT NULL
    ");
    echo "✓ Updated persons creator info\n";
} catch (Exception $e) {
    echo "⚠ Could not update persons: " . $e->getMessage() . "\n";
}

try {
    // Determine lead types for existing deals
    $pdo->exec("
        UPDATE deals d
        INNER JOIN persons p ON d.contact_didar_id = p.didar_contact_id
        SET d.lead_type = CASE WHEN p.is_duplicate_lead = 1 THEN 'duplicate' ELSE 'new' END
        WHERE d.lead_type = 'new'
    ");
    echo "✓ Updated deal lead types\n";
} catch (Exception $e) {
    echo "⚠ Could not update deal lead types: " . $e->getMessage() . "\n";
}

echo "\nMigration completed successfully!\n";
echo "New fields added:\n";
echo "- activities: created_by_didar_id, created_by_name\n";
echo "- deals: created_by_didar_id, created_by_name, lead_type\n";
echo "- persons: created_by_didar_id, created_by_name, is_duplicate_lead\n";
