<?php
require __DIR__ . '/../config.php';
require __DIR__ . '/../libraries/medoo/medoo.php';

$mdb = new medoo([
  'database_type' => 'mysql',
  'database_name' => $database['name'],
  'server'        => $database['host'],
  'username'      => $database['user'],
  'password'      => $database['password'],
  'charset'       => 'utf8'
]);

$client_id = 5;

$rows = [];
$rows['products_total'] = (int)$mdb->query("SELECT COUNT(*) FROM products WHERE client_id = {$client_id}")->fetchColumn();
$rows['products_not_deleted'] = (int)$mdb->query("SELECT COUNT(*) FROM products WHERE client_id = {$client_id} AND (deleted = 0 OR deleted IS NULL)")->fetchColumn();
$rows['products_with_offer'] = (int)$mdb->query("SELECT COUNT(*) FROM products WHERE client_id = {$client_id} AND (deleted = 0 OR deleted IS NULL) AND TRIM(COALESCE(offer_id,'')) <> ''")->fetchColumn();
$rows['pd_rows_for_client_products'] = (int)$mdb->query("SELECT COUNT(*) FROM products_data pd JOIN products p ON p.id = pd.product_id WHERE p.client_id = {$client_id}")->fetchColumn();
$rows['pd_distinct_products'] = (int)$mdb->query("SELECT COUNT(DISTINCT pd.product_id) FROM products_data pd JOIN products p ON p.id = pd.product_id WHERE p.client_id = {$client_id}")->fetchColumn();
$rows['products_with_real_url_any_pd_row'] = (int)$mdb->query("SELECT COUNT(DISTINCT p.id) FROM products p JOIN products_data pd ON pd.product_id = p.id WHERE p.client_id = {$client_id} AND (p.deleted = 0 OR p.deleted IS NULL) AND TRIM(COALESCE(p.offer_id,'')) <> '' AND TRIM(COALESCE(pd.product_url,'')) <> '' AND LOWER(TRIM(pd.product_url)) NOT IN ('0','-','null')")->fetchColumn();
$rows['products_without_real_url'] = (int)$mdb->query("SELECT COUNT(*) FROM products p LEFT JOIN ( SELECT product_id, MAX(CASE WHEN TRIM(COALESCE(product_url,'')) = '' THEN 0 WHEN LOWER(TRIM(product_url)) IN ('0','-','null') THEN 0 ELSE 1 END) AS has_real_url FROM products_data GROUP BY product_id ) pd ON pd.product_id = p.id WHERE p.client_id = {$client_id} AND (p.deleted = 0 OR p.deleted IS NULL) AND TRIM(COALESCE(p.offer_id,'')) <> '' AND COALESCE(pd.has_real_url,0) = 0")->fetchColumn();

$dup = $mdb->query("SELECT COUNT(*) FROM (SELECT pd.product_id, COUNT(*) c FROM products_data pd JOIN products p ON p.id = pd.product_id WHERE p.client_id = {$client_id} GROUP BY pd.product_id HAVING COUNT(*) > 1) t")->fetchColumn();
$rows['products_with_duplicate_pd_rows'] = (int)$dup;

$sample = $mdb->query("SELECT p.id, p.offer_id, pd.product_url FROM products p LEFT JOIN products_data pd ON pd.product_id=p.id WHERE p.client_id={$client_id} ORDER BY p.id DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);

echo json_encode(['stats'=>$rows,'sample'=>$sample], JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT);