<?php
/**
 * Migration Script: حذف NOT NULL constraint از owner_didar_id
 * 
 * این اسکریپت constraint NOT NULL را از owner_didar_id در جداول deals و activities حذف می‌کند
 */

$dbFile = __DIR__ . '/crm_database (3).db';

if (!file_exists($dbFile)) {
    // Try default database
    $dbFile = __DIR__ . '/crm_database.db';
    if (!file_exists($dbFile)) {
        die("دیتابیس یافت نشد.\n");
    }
}

try {
    $pdo = new PDO('sqlite:' . $dbFile);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "=== Migration: حذف NOT NULL constraint از owner_didar_id ===\n\n";
    
    // SQLite doesn't support ALTER TABLE DROP CONSTRAINT directly
    // We need to recreate the tables
    
    // 1. Migrate deals table
    echo "1. اصلاح جدول deals...\n";
    try {
        // Create backup
        $pdo->exec("CREATE TABLE IF NOT EXISTS deals_backup AS SELECT * FROM deals");
        echo "   ✅ Backup ایجاد شد\n";
        
        // Drop old table
        $pdo->exec("DROP TABLE IF EXISTS deals");
        echo "   ✅ جدول قدیمی حذف شد\n";
        
        // Create new table without NOT NULL on owner_didar_id
        // Include all columns that might have been added via ALTER TABLE
        $pdo->exec("CREATE TABLE deals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            didar_deal_id TEXT UNIQUE NOT NULL,
            owner_didar_id TEXT,
            contact_didar_id TEXT,
            title TEXT,
            code TEXT,
            status TEXT CHECK(status IN ('Pending', 'Won', 'Lost')),
            pipeline_stage_id TEXT,
            pipeline_stage_title TEXT,
            estimated_price REAL,
            final_price REAL,
            probability INTEGER,
            is_paid INTEGER DEFAULT 0,
            payment_short_link TEXT,
            register_time DATETIME,
            last_update_time DATETIME,
            won_time DATETIME,
            lost_time DATETIME,
            visibility_type TEXT,
            last_sync DATETIME,
            raw_json TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            service_name TEXT,
            service_cost REAL,
            price_list_code TEXT,
            has_discount INTEGER DEFAULT 0,
            discount_type TEXT,
            discount_occasion TEXT,
            discount_amount REAL DEFAULT 0,
            payable_amount REAL,
            payment_conditions TEXT,
            payment_amount REAL DEFAULT 0,
            payment_method TEXT,
            payment_description TEXT,
            payments TEXT
        )");
        echo "   ✅ جدول جدید ایجاد شد\n";
        
        // Copy data back - specify columns explicitly to handle missing columns gracefully
        // First, get list of columns from backup
        $backupColumns = $pdo->query("PRAGMA table_info(deals_backup)")->fetchAll(PDO::FETCH_ASSOC);
        $backupColumnNames = array_column($backupColumns, 'name');
        
        // Get list of columns from new table
        $newColumns = $pdo->query("PRAGMA table_info(deals)")->fetchAll(PDO::FETCH_ASSOC);
        $newColumnNames = array_column($newColumns, 'name');
        
        // Find common columns (exclude id as it's auto-increment)
        $commonColumns = array_intersect($backupColumnNames, $newColumnNames);
        $commonColumns = array_filter($commonColumns, function($col) { return $col !== 'id'; });
        
        if (count($commonColumns) > 0) {
            $columnList = implode(', ', $commonColumns);
            $pdo->exec("INSERT INTO deals ($columnList) SELECT $columnList FROM deals_backup");
            echo "   ✅ داده‌ها کپی شدند (" . count($commonColumns) . " ستون)\n";
        } else {
            throw new Exception("هیچ ستون مشترکی بین backup و جدول جدید یافت نشد");
        }
        
        // Drop backup
        $pdo->exec("DROP TABLE deals_backup");
        echo "   ✅ Backup حذف شد\n";
        
        echo "   ✅ جدول deals با موفقیت اصلاح شد\n\n";
    } catch (Exception $e) {
        echo "   ❌ خطا در اصلاح deals: " . $e->getMessage() . "\n";
        // Try to restore from backup if exists
        if ($pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='deals_backup'")->fetch()) {
            $pdo->exec("DROP TABLE IF EXISTS deals");
            $pdo->exec("ALTER TABLE deals_backup RENAME TO deals");
            echo "   ⚠️  تغییرات برگشت داده شد\n";
        }
        throw $e;
    }
    
    // 2. Migrate activities table
    echo "2. اصلاح جدول activities...\n";
    try {
        // Check if table exists
        $tableExists = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='activities'")->fetch();
        if ($tableExists) {
            // Create backup
            $pdo->exec("CREATE TABLE IF NOT EXISTS activities_backup AS SELECT * FROM activities");
            echo "   ✅ Backup ایجاد شد\n";
            
            // Drop old table
            $pdo->exec("DROP TABLE IF EXISTS activities");
            echo "   ✅ جدول قدیمی حذف شد\n";
            
            // Create new table without NOT NULL on owner_didar_id
            $pdo->exec("CREATE TABLE activities (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                didar_activity_id TEXT UNIQUE NOT NULL,
                activity_type_id TEXT NOT NULL,
                activity_type_title TEXT,
                owner_didar_id TEXT,
                deal_didar_id TEXT,
                contact_didar_id TEXT,
                title TEXT,
                note TEXT,
                result_note TEXT,
                is_done INTEGER DEFAULT 0,
                due_date DATETIME,
                done_date DATETIME,
                duration INTEGER,
                activity_category TEXT,
                direction TEXT CHECK(direction IN ('incoming', 'outgoing')),
                stage TEXT,
                register_date DATETIME,
                last_update_time DATETIME,
                last_sync DATETIME,
                raw_json TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");
            echo "   ✅ جدول جدید ایجاد شد\n";
            
            // Copy data back - specify columns explicitly to handle missing columns gracefully
            // First, get list of columns from backup
            $backupColumns = $pdo->query("PRAGMA table_info(activities_backup)")->fetchAll(PDO::FETCH_ASSOC);
            $backupColumnNames = array_column($backupColumns, 'name');
            
            // Get list of columns from new table
            $newColumns = $pdo->query("PRAGMA table_info(activities)")->fetchAll(PDO::FETCH_ASSOC);
            $newColumnNames = array_column($newColumns, 'name');
            
            // Find common columns (exclude id as it's auto-increment)
            $commonColumns = array_intersect($backupColumnNames, $newColumnNames);
            $commonColumns = array_filter($commonColumns, function($col) { return $col !== 'id'; });
            
            if (count($commonColumns) > 0) {
                $columnList = implode(', ', $commonColumns);
                $pdo->exec("INSERT INTO activities ($columnList) SELECT $columnList FROM activities_backup");
                echo "   ✅ داده‌ها کپی شدند (" . count($commonColumns) . " ستون)\n";
            } else {
                echo "   ⚠️  هیچ ستون مشترکی بین backup و جدول جدید یافت نشد\n";
            }
            
            // Drop backup
            $pdo->exec("DROP TABLE activities_backup");
            echo "   ✅ Backup حذف شد\n";
            
            echo "   ✅ جدول activities با موفقیت اصلاح شد\n\n";
        } else {
            echo "   ⚠️  جدول activities وجود ندارد، رد شد\n\n";
        }
    } catch (Exception $e) {
        echo "   ❌ خطا در اصلاح activities: " . $e->getMessage() . "\n";
        // Try to restore from backup if exists
        if ($pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='activities_backup'")->fetch()) {
            $pdo->exec("DROP TABLE IF EXISTS activities");
            $pdo->exec("ALTER TABLE activities_backup RENAME TO activities");
            echo "   ⚠️  تغییرات برگشت داده شد\n";
        }
        // Don't throw, just log the error
    }
    
    echo "=== Migration با موفقیت انجام شد ===\n";
    echo "\n✅ حالا می‌توانید اسکریپت fix_existing_owners.php را دوباره اجرا کنید.\n";
    
} catch (Exception $e) {
    echo "\n❌ خطای کلی: " . $e->getMessage() . "\n";
    exit(1);
}

