<?php
// This file is an AJAX endpoint that fetches fields from a source table
// and suggests mappings with a destination table

// Include database connection
require_once '../layouts/dbconn.php';

// Initialize response array
$response = [
    'success' => false,
    'message' => '',
    'sourceFields' => [],
    'destinationFields' => [],
    'suggestedMappings' => []
];

// Check if all required parameters are provided
if (!isset($_POST['connection']) || !isset($_POST['sourceTable']) || !isset($_POST['destinationTable'])) {
    $response['message'] = 'Missing required parameters';
    echo json_encode($response);
    exit;
}

// Get parameters
$connection = $_POST['connection'];
$sourceTable = $_POST['sourceTable'];
$destinationTable = $_POST['destinationTable'];

// Decode connection details
$connection = json_decode($connection, true);
if (!$connection || !isset($connection['host']) || !isset($connection['username']) || !isset($connection['database'])) {
    $response['message'] = 'Invalid connection details';
    echo json_encode($response);
    exit;
}

try {
    // Connect to source database
    $sourceConn = new mysqli(
        $connection['host'],
        $connection['username'],
        $connection['password'] ?? '',
        $connection['database']
    );
    
    if ($sourceConn->connect_error) {
        throw new Exception("Source connection failed: " . $sourceConn->connect_error);
    }
    
    // Get source fields
    $sourceFieldsQuery = "SHOW COLUMNS FROM `$sourceTable`";
    $sourceFieldsResult = $sourceConn->query($sourceFieldsQuery);
    
    if (!$sourceFieldsResult) {
        throw new Exception("Error fetching source fields: " . $sourceConn->error);
    }
    
    $sourceFields = [];
    while ($row = $sourceFieldsResult->fetch_assoc()) {
        $sourceFields[] = [
            'name' => $row['Field'],
            'type' => $row['Type'],
            'null' => $row['Null'],
            'key' => $row['Key'],
            'default' => $row['Default'],
            'extra' => $row['Extra']
        ];
    }
    
    // Get destination fields
    $destFieldsQuery = "SHOW COLUMNS FROM `$destinationTable`";
    $destFieldsResult = $conn->query($destFieldsQuery);
    
    if (!$destFieldsResult) {
        throw new Exception("Error fetching destination fields: " . $conn->error);
    }
    
    $destinationFields = [];
    while ($row = $destFieldsResult->fetch_assoc()) {
        $destinationFields[] = [
            'name' => $row['Field'],
            'type' => $row['Type'],
            'null' => $row['Null'],
            'key' => $row['Key'],
            'default' => $row['Default'],
            'extra' => $row['Extra']
        ];
    }
    
    // Suggest field mappings based on name similarity
    $suggestedMappings = [];
    foreach ($destinationFields as $destField) {
        $mapped = false;
        
        // First try exact match
        foreach ($sourceFields as $sourceField) {
            if (strtolower($destField['name']) === strtolower($sourceField['name'])) {
                $suggestedMappings[$destField['name']] = [
                    'sourceField' => $sourceField['name'],
                    'matchType' => 'exact'
                ];
                $mapped = true;
                break;
            }
        }
        
        // If no exact match, try partial match
        if (!$mapped) {
            foreach ($sourceFields as $sourceField) {
                if (strpos(strtolower($sourceField['name']), strtolower($destField['name'])) !== false ||
                    strpos(strtolower($destField['name']), strtolower($sourceField['name'])) !== false) {
                    $suggestedMappings[$destField['name']] = [
                        'sourceField' => $sourceField['name'],
                        'matchType' => 'partial'
                    ];
                    $mapped = true;
                    break;
                }
            }
        }
        
        // If still no match, suggest based on field type
        if (!$mapped) {
            foreach ($sourceFields as $sourceField) {
                if (strpos(strtolower($sourceField['type']), strtolower($destField['type'])) !== false ||
                    strpos(strtolower($destField['type']), strtolower($sourceField['type'])) !== false) {
                    // If type matches, suggest it
                    $suggestedMappings[$destField['name']] = [
                        'sourceField' => $sourceField['name'],
                        'matchType' => 'type'
                    ];
                    $mapped = true;
                    break;
                }
            }
        }
        
        // If still no match, leave it empty
        if (!$mapped) {
            $suggestedMappings[$destField['name']] = [
                'sourceField' => '',
                'matchType' => 'none'
            ];
        }
    }
    
    // Close source connection
    $sourceConn->close();
    
    // Set success response
    $response['success'] = true;
    $response['message'] = 'Fields fetched successfully';
    $response['sourceFields'] = $sourceFields;
    $response['destinationFields'] = $destinationFields;
    $response['suggestedMappings'] = $suggestedMappings;
    
} catch (Exception $e) {
    $response['message'] = $e->getMessage();
}

// Return response as JSON
header('Content-Type: application/json');
echo json_encode($response);
?>