<?php
/**
 * Verify Store Categories Script
 * Check if store categories are correctly assigned
 */

// Suppress deprecation warnings from third-party libraries
error_reporting(E_ALL & ~E_DEPRECATED & ~E_NOTICE);
ini_set('display_errors', 1);

// Include required files
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/functions.php';

// Establish database connection
$connect = connect();

if (!$connect) {
    die("ERROR: Could not connect to database.\n");
}

echo "=== Store Category Verification ===\n\n";

// Check specific stores that were fixed
$stores_to_check = [
    6 => ['name' => 'Blackberrys', 'expected_category' => 2, 'expected_name' => 'Fashion'],
    21 => ['name' => 'Go Colors', 'expected_category' => 2, 'expected_name' => 'Fashion'],
    40 => ['name' => 'Montecarlo', 'expected_category' => 2, 'expected_name' => 'Fashion'],
    106 => ['name' => 'House of Masaba', 'expected_category' => 2, 'expected_name' => 'Fashion'],
    99 => ['name' => 'Boat', 'expected_category' => 7, 'expected_name' => 'Electronics'],
    100 => ['name' => 'Innisfree', 'expected_category' => 1, 'expected_name' => 'Beauty & Health'],
];

$all_correct = true;

foreach ($stores_to_check as $store_id => $expected) {
    // Check stores table
    $stmt = $connect->prepare("SELECT s.store_title, s.store_category, c.category_title 
                               FROM stores s 
                               LEFT JOIN categories c ON s.store_category = c.category_id 
                               WHERE s.store_id = :store_id");
    $stmt->execute([':store_id' => $store_id]);
    $store = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$store) {
        echo "✗ Store ID $store_id not found\n";
        $all_correct = false;
        continue;
    }
    
    // Check store_categories junction table
    $junction_stmt = $connect->prepare("SELECT category_id FROM store_categories WHERE store_id = :store_id");
    $junction_stmt->execute([':store_id' => $store_id]);
    $junction_categories = $junction_stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $is_correct = ($store['store_category'] == $expected['expected_category']);
    $junction_correct = in_array($expected['expected_category'], $junction_categories);
    
    if ($is_correct && $junction_correct) {
        echo "✓ {$expected['name']}: Correctly assigned to {$store['category_title']}\n";
    } else {
        echo "✗ {$expected['name']}: ";
        if (!$is_correct) {
            echo "Legacy field is {$store['category_title']} (expected {$expected['expected_name']}) ";
        }
        if (!$junction_correct) {
            echo "Junction table missing category {$expected['expected_category']}";
        }
        echo "\n";
        $all_correct = false;
    }
}

echo "\n=== Category Distribution ===\n";

// Count stores per category
$category_query = $connect->query("
    SELECT c.category_title, COUNT(DISTINCT s.store_id) as store_count
    FROM categories c
    LEFT JOIN stores s ON c.category_id = s.store_category AND s.store_status = 1
    WHERE c.category_status = 1
    GROUP BY c.category_id, c.category_title
    ORDER BY store_count DESC
    LIMIT 10
");

$categories = $category_query->fetchAll(PDO::FETCH_ASSOC);

foreach ($categories as $cat) {
    echo "{$cat['category_title']}: {$cat['store_count']} stores\n";
}

echo "\n=== Summary ===\n";
if ($all_correct) {
    echo "✓ All checked stores are correctly assigned!\n";
    echo "Your store categories are now properly organized.\n";
} else {
    echo "⚠ Some stores still have incorrect categories.\n";
    echo "Run the fix script again or check manually in admin panel.\n";
}

?>
