<?php
include '_common.php';

### 가중치 변수 설정
$CPU_WEIGHT = 0.85;
$GPU_WEIGHT = 0.15;

$result = sql_query("SELECT it_id, it_spec_cpu, it_spec_gpu FROM g5_shop_item WHERE it_spec_cpu != '' AND it_spec_gpu != '' ");

for ($i=0; $row = sql_fetch_array($result); $i++) {
    echo "<br><br>######## [{$row['it_id']}] {$row['it_spec_cpu']} / {$row['it_spec_gpu']} <br>";
    $cpu_name = $row['it_spec_cpu'];
    $gpu_name = $row['it_spec_gpu'];

    $cpu_data = get_rank('api___price_cpu_single', 'cpu_name', $cpu_name);
    $gpu_data = get_rank('api___price_gpu', 'videocard_name', $gpu_name);

    $cpu_rank = ($cpu_data && $cpu_data['rank'] > 0) ? ($cpu_data['rank'] * $CPU_WEIGHT) : 0;
    $gpu_rank = ($gpu_data && $gpu_data['rank'] > 0) ? ($gpu_data['rank'] * $GPU_WEIGHT) : 0;

    $total_rank = round($cpu_rank + $gpu_rank);
    $rank_of_all_db = $total_rank; // rank_of_all_db = '{$rank_of_all_db}'

    $cpu_price = ($cpu_data && $cpu_data['price'] > 0) ? $cpu_data['price'] : -1;
    $gpu_price = ($gpu_data && $gpu_data['price'] > 0) ? $gpu_data['price'] : -2;
    echo "[{$row['it_id']}] cpu_price: {$cpu_price} , gpu_price: {$gpu_price}<br>";
    //print_r($cpu_data);

    if ($cpu_price > 0 && $gpu_price > 0) {
        $estimated_price_of_item = round($cpu_price + $gpu_price);
    } else {
        $estimated_price_of_item = $cpu_price + $gpu_price;
    }


    $sql_all_cpu = "SELECT CAST(REPLACE(REPLACE(rank, ',', ''), ' ', '') AS UNSIGNED) AS rank_num FROM api___price_cpu_single ORDER BY rank_num ASC";
    $sql_all_gpu = "SELECT CAST(REPLACE(REPLACE(rank, ',', ''), ' ', '') AS UNSIGNED) AS rank_num FROM api___price_gpu ORDER BY rank_num ASC";

    $cpu_ranks = [];
    $gpu_ranks = [];

    $result_all_cpu = $g5['connect_db']->query($sql_all_cpu);
    $result_all_gpu = $g5['connect_db']->query($sql_all_gpu);

    if ($result_all_cpu->num_rows > 0) {
        while ($row2 = $result_all_cpu->fetch_assoc()) {
            $cpu_ranks[] = $row2['rank_num'];
        }
    }

    if ($result_all_gpu->num_rows > 0) {
        while ($row3 = $result_all_gpu->fetch_assoc()) {
            $gpu_ranks[] = $row3['rank_num'];
        }
    }

    $cpu_percent = ($cpu_data && $cpu_data['rank'] > 0) ? round(getRankPercentage($cpu_data['rank'], $cpu_ranks), 0) : 0;
    $gpu_percent = ($gpu_data && $gpu_data['rank'] > 0) ? round(getRankPercentage($gpu_data['rank'], $gpu_ranks), 0) : 0;

    $total_percent_rank = $cpu_rank + $gpu_rank;

    $cpu_percent = ($cpu_data && $cpu_data['rank'] > 0) ? round(getRankPercentage($cpu_data['rank'], $cpu_ranks), 0) : 0;
    $gpu_percent = ($gpu_data && $gpu_data['rank'] > 0) ? round(getRankPercentage($gpu_data['rank'], $gpu_ranks), 0) : 0;

    $missing_parts = [];
    if ($cpu_data === null) $missing_parts[] = "CPU: $cpu_name";
    if ($gpu_data === null) $missing_parts[] = "GPU: $gpu_name";

    if (!empty($missing_parts)) {
        echo "[{$row['it_id']}] The following parts were not found in the database:<br>";
        foreach ($missing_parts as $part) {
            echo "[{$row['it_id']}] " . $part . "<br>";
        }
    } else {
        // echo "[{$row['it_id']}] CPU Rank: {$cpu_data['rank']} (유사도: {$cpu_data['similarity']}%) 상위 $cpu_percent% [{$cpu_name}]<br>";
        // echo "[{$row['it_id']}] GPU Rank: {$gpu_data['rank']} (유사도: {$gpu_data['similarity']}%) 상위 $gpu_percent% [{$gpu_name}]<br>";
        // //echo "[{$row['it_id']}] PRICE: {$estimated_price_of_item} (유사도: {$cpu_data['similarity']}%) 상위 $cpu_percent% [{$cpu_name}]<br>";
        // echo "[{$row['it_id']}] Total Rank (가중치적용후): " . $total_rank . "위<br>";

        echo "[{$row['it_id']}] Detailed Similarities:<br>";
        foreach ($cpu_data['details'] as $detail) {
            echo "[{$row['it_id']}] CPU: {$detail['name']} - Rank: {$detail['rank']}, Similarity: {$detail['similarity']}%<br>";
        }
        foreach ($gpu_data['details'] as $detail) {
            echo "[{$row['it_id']}] GPU: {$detail['name']} - Rank: {$detail['rank']}, Similarity: {$detail['similarity']}%<br>";
        }
    }


    sql_query("UPDATE g5_shop_item SET rank_of_item_tbl = '', rank_of_item_tbl_percent = '', rank_of_all_db = '{$rank_of_all_db}', estimated_price_of_item = '{$estimated_price_of_item}' WHERE it_id = '{$row['it_id']}'");
}


// $weighted_cpu_percent = $cpu_percent * $CPU_WEIGHT;
// $weighted_gpu_percent = $gpu_percent * $GPU_WEIGHT;
// $weight_item_percent = $weighted_cpu_percent + $weighted_gpu_percent; // rank_of_item_tbl_percent = '{$weight_item_percent}'

// Retrieve all rows sorted by rank_of_all_db in descending order
$sql = "SELECT it_id, rank_of_all_db FROM g5_shop_item ORDER BY rank_of_all_db DESC";
$result = $g5['connect_db']->query($sql);

if ($result->num_rows > 0) {
    $total_rows = $result->num_rows;
    $rank_of_item_tbl = 1;
    $previous_rank_of_all_db = null;
    $previous_rank = 1;
    $same_rank_count = 0;

    while ($row = $result->fetch_assoc()) {
        $it_id = $row['it_id'];
        $rank_of_all_db = $row['rank_of_all_db'];

        // Check if the current rank_of_all_db is the same as the previous one
        if ($rank_of_all_db === $previous_rank_of_all_db) {
            $rank_of_item_tbl = $previous_rank;
            $same_rank_count++;
        } else {
            $rank_of_item_tbl += $same_rank_count;
            $previous_rank = $rank_of_item_tbl;
            $same_rank_count = 1;
        }

        // Calculate the percentage
        $rank_of_item_tbl_percent = ($total_rows - $rank_of_item_tbl + 1) / $total_rows * 100;

        // Update the rank_of_item_tbl and rank_of_item_tbl_percent columns
        $update_sql = "UPDATE g5_shop_item SET rank_of_item_tbl = ?, rank_of_item_tbl_percent = ? WHERE it_id = ?";
        $stmt = $g5['connect_db']->prepare($update_sql);
        $stmt->bind_param("idi", $rank_of_item_tbl, $rank_of_item_tbl_percent, $it_id);
        $stmt->execute();

        $previous_rank_of_all_db = $rank_of_all_db;
    }
}


$g5['connect_db']->close();

















function isIntegratedGPU($gpuName) {
    // Define a list of common prefixes for integrated graphics card names
    $integratedPrefixes = [
        'Intel HD',    // Intel HD Graphics
        'Intel UHD',   // Intel UHD Graphics
        'Intel Iris',  // Intel Iris Graphics
        'AMD Radeon Vega', // AMD Radeon Vega Graphics
        'AMD Radeon Graphics', // Newer AMD APUs with integrated graphics
        'Apple M1',    // Apple M1 integrated graphics
        'Apple M2',     // Apple M2 integrated graphics
        'Apple M3',     // Apple M3 integrated graphics
        'Apple M4'     // Apple M4 integrated graphics
    ];

    // Check if the given GPU name starts with any of the known integrated GPU prefixes
    foreach ($integratedPrefixes as $prefix) {
        if (stripos($gpuName, $prefix) === 0) {
            return true; // It's an integrated GPU
        }
    }

    return false; // Not an integrated GPU
}


function normalize_name_for_cpu($name) {
    $name = preg_replace('/\b(cpu|gpu|intel|amd|nvidia|geforce|radeon|ryzen|core|xeon|threadripper|pro|@|cores|r|gb)\b/i', '', $name);
    return strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $name));
}

function normalize_name_for_gpu($name) {
    //$name = preg_replace('/\b(cpu|gpu|intel|amd|nvidia|geforce|radeon|ryzen|core|xeon|threadripper|pro|@|cores|r|gb)\b/i', '', $name);
    return strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $name));
}

function get_rank($table, $column, $name) {
    global $g5;

    $is_cpu_table = ($table == "api___price_cpu_single") ? true: false;

    if ($table === 'api___price_cpu_single') {
        $name = preg_replace('/\[dual cpu\]/i', '', $name);
        $name = preg_replace('/cpu|\(r\)|\(tm\)|processor|with radeon graphics|with radeon vega graphics/i', '', $name);
        $name = preg_replace('/- desktop|- server|- laptop|- mobile\/embedded/i', '', $name);
        $name = preg_replace('/\b\w+-Core\b/i', '', $name);
        $name = preg_replace('/\(\d+ Cores\)/i', '', $name);
        $name = preg_replace('/\d+ Cores$/i', '', $name);
        $name = trim(preg_replace('/\s+/', ' ', $name));
        echo "Normalized CPU Name: $name<br>";
    } else if ($table === 'api___price_gpu') {
        $name = preg_replace('/\(r\)|\(tm\)|graphics/i', '', $name);
        $name = preg_replace('/\(\d+(\.\d+)?GB\)$/i', '', $name);
        $name = trim(preg_replace('/\s+/', ' ', $name));
        echo "Normalized GPU Name: $name<br>";
    }

    $name_parts = preg_split('/[\s,]+/', $name);
    $like_clauses = [];

    foreach ($name_parts as $part) {
        $part = $g5['connect_db']->real_escape_string($part);
        $like_clauses[] = "$column LIKE '%$part%'";
    }

    $like_query = implode(' AND ', $like_clauses);

    // Debugging: Print the like_query
    echo "LIKE Query: $like_query<br>";

    $sql = "SELECT $column, CAST(REPLACE(REPLACE(rank, ',', ''), ' ', '') AS UNSIGNED) AS rank_num, price
            FROM $table
            WHERE $like_query";

    // Debugging: Print the SQL query
    echo "SQL: $sql<br>";

    $result = $g5['connect_db']->query($sql);

    if (!$result) {
        // Debugging: Print the error if the query fails
        echo "Query Error: " . $g5['connect_db']->error . "<br>";
        return null;
    }

    $rank_list = [];
    $similarities = [];

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $rank_list[] = $row;
            $similarity = 0;
            if ($is_cpu_table) {
                similar_text(normalize_name_for_cpu($name), normalize_name_for_cpu($row[$column]), $similarity);
            } else {
                similar_text(normalize_name_for_gpu($name), normalize_name_for_gpu($row[$column]), $similarity);
            }
            $similarities[] = ['rank' => $row['rank_num'], 'similarity' => $similarity, 'name' => $row[$column], 'price' => preg_replace('/[^\d.]/', '', $row['price'])];
        }

        usort($similarities, function($a, $b) {
            return $b['similarity'] <=> $a['similarity'];
        });

        $highest_similarity = $similarities[0]['similarity'];
        $filtered_similarities = array_filter($similarities, function($item) use ($highest_similarity) {
            return $item['similarity'] > 50;
        });

        if (empty($filtered_similarities)) {
            echo "No similarities found with similarity > 50<br>";
            return null;
        }

        $highest_similarity = $filtered_similarities[0]['similarity'];
        $filtered_similarities = array_filter($filtered_similarities, function($item) use ($highest_similarity) {
            return $item['similarity'] == $highest_similarity;
        });

        if (count($filtered_similarities) > 10) {
            $filtered_similarities = array_slice($filtered_similarities, 0, 10);
        }

        $average_rank_cnt = 0;
        $average_price_cnt = 0;
        $average_rank = 0;
        $average_price = 0;
        foreach ($filtered_similarities as $item) {
            if ((float)$item['rank'] > 0) {
                $average_rank += $item['rank'];
                $average_rank_cnt++;
            }

            if ((float)$item['price'] > 0 && $item['price'] != "") {
                $average_price += $item['price'];
                $average_price_cnt++;
            }
        }

        // Avoid division by zero
        if ($average_rank_cnt > 0) {
            $average_rank = round($average_rank / $average_rank_cnt);
        } else {
            $average_rank = 0;
        }

        if ($average_price_cnt > 0) {
            $average_price = round($average_price / $average_price_cnt);
        } else {
            $average_price = 0;
        }

        echo "[B:{$average_rank_cnt}]Average Rank: $average_rank<br>";
        echo "[B:{$average_price_cnt}]Average Price: $average_price<br>";

        return ['rank' => $average_rank, 'similarity' => $highest_similarity, 'details' => $filtered_similarities, 'price' => $average_price];
    } else {
        return null;
    }
}


function getRankPercentage($rank, $all_ranks) {
    if (empty($all_ranks)) {
        return null;
    }

    sort($all_ranks);
    $position = array_search($rank, $all_ranks) + 1;
    $total = count($all_ranks);
    if ($total == 0 || $position == 0) {
        return null;
    }

    $percent = ($position / $total) * 100;
    return $percent + 1;
}
?>