<?php
/**
 * Analyze database structure to understand the schema
 */

$dbPath = 'crm_database_new.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);
    
    echo "=" . str_repeat("=", 79) . "\n";
    echo "DATABASE STRUCTURE ANALYSIS\n";
    echo "=" . str_repeat("=", 79) . "\n";
    
    // 1. Get all tables
    echo "\n1. TABLES:\n";
    echo str_repeat("-", 80) . "\n";
    $tables = $pdo->query("SELECT name FROM sqlite_master WHERE type='table'")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        echo "  - $table\n";
    }
    
    // 2. Analyze users table
    echo "\n2. USERS TABLE STRUCTURE:\n";
    echo str_repeat("-", 80) . "\n";
    $columns = $pdo->query("PRAGMA table_info(users)")->fetchAll(PDO::FETCH_ASSOC);
    echo "  Total columns: " . count($columns) . "\n";
    echo "\n  Columns:\n";
    $hasDisplayName = false;
    foreach ($columns as $col) {
        $nullable = $col['notnull'] == 0 ? 'YES' : 'NO';
        $default = $col['dflt_value'] ?? 'NULL';
        echo "    - {$col['name']} ({$col['type']}) - Nullable: $nullable, Default: $default\n";
        if ($col['name'] === 'display_name') {
            $hasDisplayName = true;
        }
    }
    
    echo "\n  Has 'display_name' column: " . ($hasDisplayName ? 'YES' : 'NO') . "\n";
    
    // 3. Sample data
    echo "\n3. SAMPLE DATA FROM USERS TABLE:\n";
    echo str_repeat("-", 80) . "\n";
    $sample = $pdo->query("SELECT * FROM users LIMIT 3")->fetchAll(PDO::FETCH_ASSOC);
    if ($sample) {
        foreach ($sample as $i => $row) {
            echo "\n  Row " . ($i + 1) . ":\n";
            foreach ($row as $key => $value) {
                if ($value === null) {
                    $value = "NULL";
                } elseif (is_string($value) && strlen($value) > 50) {
                    $value = substr($value, 0, 50) . "...";
                }
                echo "    $key: $value\n";
            }
        }
    } else {
        echo "  No data in users table\n";
    }
    
    $count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
    echo "\n  Total users: $count\n";
    
    // 4. Check persons table
    echo "\n4. PERSONS TABLE - OWNER REFERENCES:\n";
    echo str_repeat("-", 80) . "\n";
    $personsColumns = $pdo->query("PRAGMA table_info(persons)")->fetchAll(PDO::FETCH_ASSOC);
    $hasOwner = false;
    foreach ($personsColumns as $col) {
        if ($col['name'] === 'owner_didar_id') {
            $hasOwner = true;
            break;
        }
    }
    echo "  Has 'owner_didar_id' column: " . ($hasOwner ? 'YES' : 'NO') . "\n";
    
    if ($hasOwner) {
        $withOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NOT NULL")->fetchColumn();
        $total = $pdo->query("SELECT COUNT(*) FROM persons")->fetchColumn();
        echo "  Persons with owner: $withOwner / $total\n";
        
        $sampleOwners = $pdo->query("SELECT DISTINCT owner_didar_id FROM persons WHERE owner_didar_id IS NOT NULL LIMIT 5")->fetchAll(PDO::FETCH_COLUMN);
        echo "\n  Sample owner_didar_id values:\n";
        foreach ($sampleOwners as $owner) {
            echo "    - $owner\n";
        }
    }
    
    // 5. Check sample users with names
    echo "\n5. SAMPLE USERS (first_name + last_name):\n";
    echo str_repeat("-", 80) . "\n";
    $usersWithNames = $pdo->query("
        SELECT didar_user_id, first_name, last_name 
        FROM users 
        WHERE first_name IS NOT NULL OR last_name IS NOT NULL 
        LIMIT 5
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($usersWithNames as $user) {
        $fullName = trim(($user['first_name'] ?? '') . ' ' . ($user['last_name'] ?? ''));
        echo "    - {$user['didar_user_id']}: '$fullName'\n";
    }
    
    echo "\n" . str_repeat("=", 80) . "\n";
    echo "ANALYSIS COMPLETE\n";
    echo str_repeat("=", 80) . "\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
    echo $e->getTraceAsString() . "\n";
}




