<?php
/**
 * Checks, after the import of the Black Friday special prices, that every row of the file is on the site:
 * special_price = "Pret BF", special price active during the campaign, price index updated.
 * Read only (SELECT queries only), uses the database of app/etc/env.php.
 *
 * Usage (from the Magento root): php verify_bf_prices.php <file.csv> [<report.csv>]
 * Columns used: sku, "Pret BF"; optional: price, special_price (the old one), "Discount aplicat". Separator , or ; (detected).
 */
const TOLERANCE = 0.01;

$root = __DIR__;
$in = $argv[1] ?? '';
if ($in === '' || !is_readable($in)) {
    fwrite(STDERR, "Folosire: php verify_bf_prices.php <fisier.csv> [<raport.csv>]\n");
    exit(2);
}
$reportFile = $argv[2] ?? preg_replace('/\.csv$/i', '', $in) . '_verificare.csv';

// ---- database (read only)
$env = include $root . '/app/etc/env.php';
$c = $env['db']['connection']['default'];
$prefix = (string)($env['db']['table_prefix'] ?? '');
$host = (string)$c['host'];
if ($host !== '' && $host[0] === '/') {
    $dsn = "mysql:unix_socket=$host";
} elseif (preg_match('/^(.+):(\d+)$/', $host, $m)) {
    $dsn = "mysql:host={$m[1]};port={$m[2]}";
} elseif (preg_match('/^(.+):(\/.+)$/', $host, $m)) {
    $dsn = "mysql:host={$m[1]};unix_socket={$m[2]}";
} else {
    $dsn = "mysql:host=$host";
}
$db = new PDO("$dsn;dbname={$c['dbname']};charset=utf8mb4", $c['username'], $c['password'] ?? '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$t = fn (string $table) => $prefix . $table;
$config = function (string $path) use ($db, $t): ?string {
    $value = $db->query("SELECT value FROM {$t('core_config_data')} WHERE scope = 'default' AND path = " . $db->quote($path))->fetchColumn();
    return $value === false ? null : $value;
};

/** "809.1", "809,1", "1.234,56", "1,234.56", "809 lei" -> float; empty / NULL -> null */
function money(?string $value): ?float
{
    $value = str_ireplace(['lei', 'ron', ' ', "\xC2\xA0"], '', trim((string)$value));
    if ($value === '' || strtoupper($value) === 'NULL') {
        return null;
    }
    $dot = strrpos($value, '.');
    $comma = strrpos($value, ',');
    if ($dot !== false && $comma !== false) {
        $decimal = $dot > $comma ? '.' : ',';
        $value = str_replace($decimal === '.' ? ',' : '.', '', $value);
    }
    $value = str_replace(',', '.', $value);
    return is_numeric($value) ? (float)$value : null;
}

/** Discount label of the site: (int)(100 - round(final / regular * 100)) */
function percent(float $regular, float $final): int
{
    return $regular > 0 && $final < $regular ? (int)(100 - round($final / $regular * 100)) : 0;
}

function same(?float $a, ?float $b): bool
{
    return $a !== null && $b !== null && abs($a - $b) < TOLERANCE;
}

function fmt(?float $value): string
{
    return $value === null ? '-' : rtrim(rtrim(number_format($value, 2, '.', ''), '0'), '.');
}

// ---- file
$fh = fopen($in, 'r');
$first = (string)fgets($fh);
$sep = substr_count($first, ';') > substr_count($first, ',') ? ';' : ',';
rewind($fh);
$header = fgetcsv($fh, 0, $sep, '"', '');
$header[0] = preg_replace('/^\xEF\xBB\xBF/', '', (string)$header[0]);
$col = [];
foreach ($header as $i => $name) {
    $col[strtolower(str_replace([' ', '_'], '', trim((string)$name)))] = $i;
}
foreach (['sku' => 'sku', 'pretbf' => 'Pret BF'] as $key => $label) {
    if (!isset($col[$key])) {
        fwrite(STDERR, "Coloana \"$label\" lipseste din $in (coloane: " . implode(', ', $header) . ")\n");
        exit(2);
    }
}
$rows = [];
$line = 1;
while (($row = fgetcsv($fh, 0, $sep, '"', '')) !== false) {
    $line++;
    if ($row === [null] || trim(implode('', $row)) === '') {
        continue;
    }
    $rows[] = [
        'line' => $line,
        'sku' => trim((string)($row[$col['sku']] ?? '')),
        'bf' => money($row[$col['pretbf']] ?? null),
        'bfRaw' => trim((string)($row[$col['pretbf']] ?? '')),
        'price' => isset($col['price']) ? money($row[$col['price']] ?? null) : null,
        'discount' => isset($col['discountaplicat']) ? money($row[$col['discountaplicat']] ?? null) : null,
        'old' => isset($col['specialprice']) ? money($row[$col['specialprice']] ?? null) : null,
    ];
}
fclose($fh);

// ---- catalog values of the products in the file
$attr = [];
foreach ($db->query("SELECT ea.attribute_code, ea.attribute_id, ea.backend_type, cea.is_global FROM {$t('eav_attribute')} ea
        JOIN {$t('catalog_eav_attribute')} cea ON cea.attribute_id = ea.attribute_id
        WHERE ea.entity_type_id = 4 AND ea.attribute_code IN ('price', 'special_price', 'special_from_date', 'special_to_date', 'status')") as $a) {
    $attr[$a['attribute_code']] = $a;
}
$storeId = (int)$db->query("SELECT store_id FROM {$t('store')} WHERE store_id > 0 ORDER BY store_id LIMIT 1")->fetchColumn();
$websiteId = (int)$db->query("SELECT website_id FROM {$t('store')} WHERE store_id = $storeId")->fetchColumn();
$tz = new DateTimeZone($config('general/locale/timezone') ?: 'Europe/Bucharest');
$today = (new DateTime('now', $tz))->format('Y-m-d');
$start = $config('autogedal_blackfriday/general/start_at');
$end = $config('autogedal_blackfriday/general/end_at');
$startDay = $start ? substr($start, 0, 10) : null;
$endDay = $end ? substr($end, 0, 10) : null;

$mode = $config('indexer/catalog_product_price/dimensions_mode') ?: 'none';
$indexTable = $t('catalog_product_index_price') . match ($mode) {
    'website' => "_ws$websiteId",
    'customer_group' => '_cg0',
    'website_and_customer_group' => "_ws{$websiteId}_cg0",
    default => '',
};
$mview = $db->query("SELECT mode, version_id FROM {$t('mview_state')} WHERE view_id = 'catalog_product_price'")->fetch() ?: null;
$indexer = $db->query("SELECT status FROM {$t('indexer_state')} WHERE indexer_id = 'catalog_product_price'")->fetchColumn() ?: '?';

$products = [];
$skus = array_values(array_unique(array_filter(array_column($rows, 'sku'), 'strlen')));
foreach (array_chunk($skus, 1000) as $chunk) {
    $list = implode(',', array_map([$db, 'quote'], $chunk));
    foreach ($db->query("SELECT entity_id, sku, type_id FROM {$t('catalog_product_entity')} WHERE sku IN ($list)") as $p) {
        $products[strtoupper($p['sku'])] = $p + ['values' => []];
    }
}
$byId = [];
foreach ($products as $key => $p) {
    $byId[(int)$p['entity_id']] = $key;
}
foreach (array_chunk(array_keys($byId), 1000) as $ids) {
    $idList = implode(',', $ids);
    foreach ($attr as $code => $a) {
        // global attributes: default value only; website ones: the store value wins over the default
        $stores = (int)$a['is_global'] === 1 ? '0' : "0, $storeId";
        foreach ($db->query("SELECT entity_id, store_id, value FROM {$t('catalog_product_entity_' . $a['backend_type'])}
                WHERE attribute_id = {$a['attribute_id']} AND store_id IN ($stores) AND entity_id IN ($idList) ORDER BY store_id") as $v) {
            $products[$byId[(int)$v['entity_id']]]['values'][$code] = $v['value'];
        }
    }
    foreach ($db->query("SELECT entity_id, price, final_price FROM $indexTable
            WHERE customer_group_id = 0 AND website_id = $websiteId AND entity_id IN ($idList)") as $v) {
        $products[$byId[(int)$v['entity_id']]]['index'] = $v;
    }
    if ($mview && $mview['mode'] === 'enabled') {
        foreach ($db->query("SELECT DISTINCT entity_id FROM {$t('catalog_product_price_cl')}
                WHERE version_id > " . (int)$mview['version_id'] . " AND entity_id IN ($idList)") as $v) {
            $products[$byId[(int)$v['entity_id']]]['queued'] = true;
        }
    }
}

// ---- checks
$problems = [];   // [line, sku, level, problem, details]
$add = function (array $r, string $level, string $problem, string $details = '') use (&$problems) {
    $problems[] = [$r['line'], $r['sku'], $level, $problem, $details];
};
$seen = [];
$okRows = 0;
$activeNow = 0;
$fromDays = [];
foreach ($rows as $r) {
    $before = count($problems);
    if ($r['sku'] === '') {
        $add($r, 'EROARE', 'sku gol in fisier');
        continue;
    }
    if ($r['bf'] === null) {
        $add($r, 'EROARE', 'Pret BF lipsa sau invalid in fisier', "valoare: '{$r['bfRaw']}'");
        continue;
    }
    $key = strtoupper($r['sku']);
    if (isset($seen[$key])) {
        $other = $seen[$key];
        $add($r, same($other['bf'], $r['bf']) ? 'ATENTIE' : 'EROARE', 'sku de mai multe ori in fisier',
            "si pe linia {$other['line']}" . (same($other['bf'], $r['bf']) ? '' : " cu alt Pret BF (" . fmt($other['bf']) . ")"));
    }
    $seen[$key] = $r;
    $p = $products[$key] ?? null;
    if (!$p) {
        $add($r, 'EROARE', 'produsul nu exista pe site', 'niciun produs cu acest sku');
        continue;
    }
    $v = $p['values'];
    $price = isset($v['price']) ? (float)$v['price'] : null;
    $special = isset($v['special_price']) ? (float)$v['special_price'] : null;
    $from = isset($v['special_from_date']) ? substr($v['special_from_date'], 0, 10) : null;
    $to = isset($v['special_to_date']) ? substr($v['special_to_date'], 0, 10) : null;
    $enabled = (int)($v['status'] ?? 1) === 1;

    if ($p['type_id'] === 'bundle') {
        $add($r, 'EROARE', 'produs bundle: special_price e procent la bundle, nu pret',
            'pe site special_price = ' . fmt($special) . '%, in fisier Pret BF = ' . fmt($r['bf']));
    } elseif ($special === null) {
        $add($r, 'EROARE', 'pretul special nu s-a importat', 'special_price gol pe site, Pret BF = ' . fmt($r['bf']));
    } elseif (!same($special, $r['bf'])) {
        $add($r, 'EROARE', 'pret special diferit', 'site ' . fmt($special) . ', fisier ' . fmt($r['bf'])
            . (same($special, $r['old']) ? ' (a ramas pretul special vechi)' : ''));
    } else {
        if ($price !== null && $special >= $price) {
            $add($r, 'EROARE', 'pretul special nu e mai mic decat pretul', 'price ' . fmt($price) . ', special ' . fmt($special) . ' - nu apare reducere');
        }
        if ($startDay && $endDay) {
            if ($to !== null && $to < $endDay) {
                $add($r, 'EROARE', 'pretul special expira inainte de sfarsitul campaniei',
                    "special_to_date $to, campania tine pana pe $endDay" . ($to < $startDay ? ' (nu se aplica deloc in campanie)' : ''));
            }
            if ($from !== null && $from > $startDay) {
                $add($r, 'EROARE', 'pretul special incepe dupa startul campaniei', "special_from_date $from, campania incepe pe $startDay");
            }
        }
        if ($price !== null && $r['price'] !== null && !same($price, $r['price'])) {
            $add($r, 'ATENTIE', 'pretul de baza difera de fisier', 'site ' . fmt($price) . ', fisier ' . fmt($r['price'])
                . ' - reducerea afisata devine ' . percent($price, $special) . '%');
        } elseif ($price !== null && $r['discount'] !== null && percent($price, $special) !== (int)round($r['discount'])) {
            // price and special price are the ones of the file here: the file itself does not add up
            $add($r, 'ATENTIE', 'in fisier, Pret BF nu corespunde cu Discount aplicat', 'pe site apare -' . percent($price, $special)
                . '% (' . fmt($price) . ' -> ' . fmt($special) . '), in fisier Discount aplicat ' . fmt($r['discount']) . '%');
        }
    }
    if (!$enabled) {
        $add($r, 'ATENTIE', 'produs dezactivat pe site');
    }
    if ($p['type_id'] === 'configurable') {
        $add($r, 'ATENTIE', 'produs configurabil: pretul il dau variantele, nu parintele');
    }

    // price index (what the site shows): today's final price, for guests
    if ($enabled && $price !== null && $p['type_id'] === 'simple') {
        $active = $special !== null && ($from === null || $from <= $today) && ($to === null || $to >= $today);
        $expected = $active ? min($price, $special) : $price;
        $index = $p['index'] ?? null;
        if (!$index) {
            $add($r, 'ATENTIE', 'produsul lipseste din indexul de preturi', !empty($p['queued']) ? 'e in coada de reindexare (cron)' : 'reindex catalog_product_price');
        } elseif (!same((float)$index['final_price'], $expected) || !same((float)$index['price'], $price)) {
            $add($r, 'ATENTIE', 'indexul de preturi nu e actualizat', 'pe site se vede ' . fmt((float)$index['final_price'])
                . ' (pret ' . fmt((float)$index['price']) . '), ar trebui ' . fmt($expected) . ($active ? '' : ' (pretul special nu e activ azi)')
                . (!empty($p['queued']) ? '; e in coada de reindexare (cron)' : ''));
        }
    }

    if ($special !== null && same($special, $r['bf'])) {
        if ($from === null || $from <= $today) {
            $activeNow++;
        } else {
            $fromDays[$from] = ($fromDays[$from] ?? 0) + 1;
        }
    }
    $okRows += count($problems) === $before;
}

// ---- report
$r = fopen($reportFile, 'w');
fputcsv($r, ['linie', 'sku', 'nivel', 'problema', 'detalii'], ',', '"', '');
foreach ($problems as $problem) {
    fputcsv($r, $problem, ',', '"', '');
}
fclose($r);

$total = count($rows);
$errorLines = count(array_unique(array_column(array_filter($problems, fn ($x) => $x[2] === 'EROARE'), 0)));
$warnLines = count(array_unique(array_column(array_filter($problems, fn ($x) => $x[2] === 'ATENTIE'), 0)));
echo "Fisier: $in ($total randuri)\n";
echo "Baza: {$c['dbname']} | campania: " . ($start ?: '?') . ' - ' . ($end ?: '?') . " | azi: $today\n";
echo "Index preturi: $indexer" . ($mview ? ', update by schedule' . ($mview['mode'] === 'enabled' ? '' : ' oprit') : '') . "\n\n";
echo "OK:              $okRows randuri\n";
echo "Cu erori:        $errorLines randuri\n";
echo "Cu atentionari:  $warnLines randuri\n\n";

if ($problems) {
    $byType = [];
    foreach ($problems as $problem) {
        $byType[$problem[2] . ' | ' . $problem[3]][] = $problem;
    }
    uksort($byType, fn ($a, $b) => [strncmp($a, 'EROARE', 6) !== 0, $a] <=> [strncmp($b, 'EROARE', 6) !== 0, $b]);
    echo "Probleme:\n";
    foreach ($byType as $type => $list) {
        printf("  %5d  %s\n", count($list), $type);
        foreach (array_slice($list, 0, 3) as $x) {
            echo "           linia {$x[0]}, {$x[1]}" . ($x[4] !== '' ? ": {$x[4]}" : '') . "\n";
        }
    }
    echo "\n";
}
if ($activeNow || $fromDays) {
    ksort($fromDays);
    echo "Pretul special BF (randurile unde s-a importat corect) e activ:\n";
    if ($activeNow) {
        printf("  %5d  deja, de azi%s\n", $activeNow, $startDay && $today < $startDay ? " (inainte de startul campaniei, $startDay)" : '');
    }
    foreach ($fromDays as $day => $n) {
        printf("  %5d  de pe %s\n", $n, $day);
    }
    echo "\n";
}
echo "Raport complet (fiecare problema, pe linie): $reportFile\n";
exit($errorLines ? 1 : 0);