<?php
require_once __DIR__ . '/../database/Database.php';
$db = Database::getInstance();

echo "=== ALL STORES ===\n";
$stores = $db->query('SELECT s.*, u.username, u.email FROM stores s LEFT JOIN users u ON s.user_id = u.id ORDER BY s.id ASC')->fetchAll(PDO::FETCH_ASSOC);
foreach ($stores as $st) {
    echo sprintf(
        "Store ID: %2d | User ID: %2d | Store: %-30s | Slug: %-25s | Owner: %-25s (%s) | Status: %s | Verified: %s\n",
        $st['id'],
        $st['user_id'],
        $st['store_name'],
        $st['store_slug'],
        $st['username'] ?? 'NULL',
        $st['email'] ?? 'NULL',
        $st['status'],
        $st['verified_at'] ? 'YES' : 'NO'
    );
}

echo "\n=== ALL PRODUCTS ===\n";
$products = $db->query('SELECT p.*, s.store_name FROM products p LEFT JOIN stores s ON p.store_id = s.id ORDER BY p.id ASC')->fetchAll(PDO::FETCH_ASSOC);
foreach ($products as $pr) {
    echo sprintf(
        "Product ID: %2d | Store ID: %2d (%-20s) | Name: %-45s | Status: %-8s | Created: %s\n",
        $pr['id'],
        $pr['store_id'],
        $pr['store_name'] ?? 'NULL',
        $pr['name'],
        $pr['status'] ?? 'NULL',
        $pr['created_at']
    );
}

echo "\n=== FOREIGN KEYS ON STORES & PRODUCTS ===\n";
$fks = $db->query("
    SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = 'cargoo_shop' AND (REFERENCED_TABLE_NAME IN ('stores', 'products', 'product_skus', 'product_variations') OR TABLE_NAME IN ('stores', 'products'))
")->fetchAll(PDO::FETCH_ASSOC);
foreach ($fks as $fk) {
    echo sprintf(
        "Table: %-25s | Column: %-20s -> Ref: %-20s (%s)\n",
        $fk['TABLE_NAME'],
        $fk['COLUMN_NAME'],
        $fk['REFERENCED_TABLE_NAME'] ?? '',
        $fk['REFERENCED_COLUMN_NAME'] ?? ''
    );
}

echo "\n=== ORDER ASSOCIATIONS BY STORE ===\n";
$orderStores = $db->query("
    SELECT o.store_id, s.store_name, COUNT(o.id) as order_count, SUM(o.total_amount) as total_amount
    FROM orders o
    LEFT JOIN stores s ON o.store_id = s.id
    GROUP BY o.store_id
")->fetchAll(PDO::FETCH_ASSOC);
foreach ($orderStores as $os) {
    echo sprintf("Store ID: %2s (%-25s) | Orders: %2d | Total: %10.2f\n", $os['store_id'] ?? 'NULL', $os['store_name'] ?? 'NULL', $os['order_count'], $os['total_amount']);
}

echo "\n=== SETTLEMENT ASSOCIATIONS BY STORE ===\n";
$settleStores = $db->query("
    SELECT se.store_id, s.store_name, COUNT(se.id) as settle_count
    FROM settlements se
    LEFT JOIN stores s ON se.store_id = s.id
    GROUP BY se.store_id
")->fetchAll(PDO::FETCH_ASSOC);
foreach ($settleStores as $ss) {
    echo sprintf("Store ID: %2s (%-25s) | Settlements: %2d\n", $ss['store_id'] ?? 'NULL', $ss['store_name'] ?? 'NULL', $ss['settle_count']);
}

echo "\n=== ALL ORDERS ===\n";
$orders = $db->query('SELECT id, order_no, customer_id, store_id, total_amount, order_status, payment_status, created_at FROM orders ORDER BY id ASC')->fetchAll(PDO::FETCH_ASSOC);
foreach ($orders as $o) {
    echo sprintf(
        "Order ID: %2d | OrderNo: %-25s | Cust ID: %2d | Store ID: %2d | Status: %-16s | PayStatus: %-16s | Created: %s\n",
        $o['id'],
        $o['order_no'],
        $o['customer_id'],
        $o['store_id'],
        $o['order_status'],
        $o['payment_status'],
        $o['created_at']
    );
}