prepare("UPDATE api_usage SET count = 0 WHERE date = ?")->execute([$today]);
header("Location: index.php");
exit;
}
// --- CALCUL BADGE-URI (Notificari WhatsApp Style - CE E NOU) ---
$views = $pdo->query("SELECT * FROM report_views")->fetchAll(PDO::FETCH_KEY_PAIR);
function getLastView($key, $views) { return isset($views[$key]) ? $views[$key] : '2000-01-01'; }
// Datele pentru "Last Viewed"
$z_date = getLastView('zombie', $views);
$d_date = getLastView('discovered', $views);
$dusty_date = getLastView('dusty', $views);
$h_date = getLastView('hot', $views);
$t_date = getLastView('tech', $views);
// --- CALCUL TOTALURI & BADGE-URI ---
// Avem nevoie de doua seturi:
// 1. $badge_... (Doar cele noi, nevizualizate) -> Cerculet Rosu
// 2. $count_... (Totalul existent in categorie) -> Text (X pag.)
// Zombie
$count_zombie = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND coverage_state LIKE '%CRAWLED%' AND is_ignored=0")->fetchColumn();
$badge_zombie = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND coverage_state LIKE '%CRAWLED%' AND is_ignored=0 AND last_checked_at > '$z_date'")->fetchColumn();
// Discovered
$count_discovered = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND coverage_state LIKE '%DISCOVERED%' AND is_ignored=0")->fetchColumn();
$badge_discovered = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND coverage_state LIKE '%DISCOVERED%' AND is_ignored=0 AND last_checked_at > '$d_date'")->fetchColumn();
// Dusty
$count_dusty = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE last_crawl < DATE_SUB(NOW(), INTERVAL 6 MONTH) AND is_ignored=0 AND last_crawl IS NOT NULL")->fetchColumn();
$badge_dusty = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE last_crawl < DATE_SUB(NOW(), INTERVAL 6 MONTH) AND is_ignored=0 AND last_checked_at > '$dusty_date'")->fetchColumn();
// Hot
$count_hot = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE last_crawl >= DATE_SUB(NOW(), INTERVAL 5 DAY) AND is_ignored=0")->fetchColumn();
$badge_hot = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE last_crawl >= DATE_SUB(NOW(), INTERVAL 5 DAY) AND is_ignored=0 AND last_checked_at > '$h_date'")->fetchColumn();
// Tech
$count_tech = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND is_ignored=0 AND coverage_state NOT LIKE '%CRAWLED%' AND coverage_state NOT LIKE '%DISCOVERED%'")->fetchColumn();
$badge_tech = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed=0 AND is_ignored=0 AND coverage_state NOT LIKE '%CRAWLED%' AND coverage_state NOT LIKE '%DISCOVERED%' AND last_checked_at > '$t_date'")->fetchColumn();
// Instabile (Calcul mai complex, facem count pe subquery)
$sql_unstable_count = "SELECT COUNT(*) FROM (SELECT t1.id FROM seo_audit t1 JOIN seo_history t2 ON t1.id = t2.url_id WHERE t1.is_ignored = 0 GROUP BY t1.id HAVING COUNT(DISTINCT t2.is_indexed) > 1) as sub";
$count_unstable = $pdo->query($sql_unstable_count)->fetchColumn();
$badge_unstable = 0; // Instabile nu au badge de "nou" momentan, e complex de urmarit istoric diff
// --- LOGICA STANDARD ---
$sitemapList = ['https://www.infocontact.ro/post-sitemap.xml', 'https://www.infocontact.ro/post-sitemap2.xml', 'https://www.infocontact.ro/post-sitemap3.xml', 'https://www.infocontact.ro/page-sitemap.xml', 'https://www.infocontact.ro/category-sitemap.xml'];
$import_message = '';
if (isset($_POST['sync_sitemap'])) {
set_time_limit(300);
// 1. Functia de extragere (ramane la fel)
if (!function_exists('getUrlsFromSitemap')) {
function getUrlsFromSitemap($url) {
$urls = [];
$xmlContent = @file_get_contents($url);
if (!$xmlContent) return [];
$xml = simplexml_load_string($xmlContent);
if (!$xml) return [];
if (isset($xml->sitemap)) {
foreach ($xml->sitemap as $sitemap) {
$urls = array_merge($urls, getUrlsFromSitemap((string)$sitemap->loc));
}
} elseif (isset($xml->url)) {
foreach ($xml->url as $urlEntry) {
$urls[] = trim((string)$urlEntry->loc);
}
}
return $urls;
}
}
// 2. Colectam URL-urile din Sitemap (LIVE)
$sitemapUrls = [];
foreach ($sitemapList as $sm) {
$sitemapUrls = array_merge($sitemapUrls, getUrlsFromSitemap($sm));
}
$sitemapUrls = array_unique($sitemapUrls); // Lista curata din XML
// PROTECTIE: Daca sitemap-ul e gol sau da eroare, NU stergem baza de date!
if (count($sitemapUrls) > 0) {
// A. Preluam URL-urile existente in DB
$dbUrls = $pdo->query("SELECT url FROM seo_audit")->fetchAll(PDO::FETCH_COLUMN);
// B. Calculam diferentele
$urlsToAdd = array_diff($sitemapUrls, $dbUrls); // Ce e nou
$urlsToDelete = array_diff($dbUrls, $sitemapUrls); // Ce a disparut (cele 70 articole)
// C. ADAUGARE (INSERT)
$added = 0;
if (!empty($urlsToAdd)) {
$stmtInsert = $pdo->prepare("INSERT IGNORE INTO seo_audit (url) VALUES (?)");
foreach ($urlsToAdd as $url) {
$stmtInsert->execute([$url]);
$added++;
}
}
// D. STERGERE (DELETE)
$deleted = 0;
if (!empty($urlsToDelete)) {
// Stergem in pachete pentru a nu bloca SQL daca sunt multe
$chunks = array_chunk($urlsToDelete, 100);
foreach ($chunks as $chunk) {
$placeholders = implode(',', array_fill(0, count($chunk), '?'));
$stmtDelete = $pdo->prepare("DELETE FROM seo_audit WHERE url IN ($placeholders)");
$stmtDelete->execute($chunk);
// Optional: Stergem si istoricul pentru ele ca sa nu ramana orfani
// Dar baza ta are probabil ON DELETE CASCADE setat pe FK?
// Daca nu, ar fi bine sa cureti si seo_history, dar nu e critic acum.
$deleted += count($chunk);
}
}
$import_message = "Sincronizare Reușită: ➕ Adăugate: $added 🗑️ Șterse (eliminate): $deleted";
} else {
$import_message = "❌ Eroare: Nu am putut citi sitemap-ul sau este gol. Nu s-a făcut nicio modificare pentru siguranță.";
}
}
if (isset($_POST['reset_all'])) { $pdo->query("UPDATE seo_audit SET status = 'pending' WHERE is_ignored = 0"); header("Location: index.php"); exit; }
if (isset($_POST['ignore_urls'])) {
$raw = explode("\n", $_POST['ignore_text']);
$upd = $pdo->prepare("UPDATE seo_audit SET is_ignored=1, status='success', verdict='IGNORED' WHERE url=?");
$ins = $pdo->prepare("INSERT IGNORE INTO seo_audit (url, is_ignored, status, verdict) VALUES (?, 1, 'success', 'IGNORED')");
foreach($raw as $l) { $u=trim($l); if($u){ $upd->execute([$u]); if($upd->rowCount()==0) $ins->execute([$u]); } }
}
$today = date('Y-m-d');
$usage_count = $pdo->query("SELECT count FROM api_usage WHERE date = '$today'")->fetchColumn() ?: 0;
$limit_daily = 2000;
$percent_usage = ($usage_count / $limit_daily) * 100;
$limit_color = ($percent_usage > 90) ? 'bg-danger' : (($percent_usage > 50) ? 'bg-warning' : 'bg-success');
$total = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_ignored = 0")->fetchColumn();
$pending = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE status = 'pending' AND is_ignored = 0")->fetchColumn();
$indexed = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE is_indexed = 1 AND is_ignored = 0")->fetchColumn();
$not_indexed = $pdo->query("SELECT COUNT(*) FROM seo_audit WHERE status='success' AND is_indexed = 0 AND is_ignored = 0")->fetchColumn();
// Tabel Principal
$valid_columns = ['url', 'is_indexed', 'status', 'last_crawl', 'last_checked_at'];
$sort_col = isset($_GET['sort']) && in_array($_GET['sort'], $valid_columns) ? $_GET['sort'] : 'id';
$sort_order = isset($_GET['order']) && $_GET['order'] === 'DESC' ? 'DESC' : 'ASC';
$next_order = ($sort_order === 'ASC') ? 'DESC' : 'ASC';
function getSortLink($column, $label, $current_col, $current_order, $next_order) {
$icon = ($column === $current_col) ? (($current_order === 'ASC') ? ' ▲' : ' ▼') : '';
return '' . $label . $icon . '';
}
$sql = "SELECT a.*, (SELECT GROUP_CONCAT(is_indexed ORDER BY checked_at ASC SEPARATOR ',') FROM (SELECT * FROM seo_history ORDER BY checked_at DESC LIMIT 10) as sub WHERE sub.url_id = a.id) as history_spark FROM seo_audit a ORDER BY $sort_col $sort_order";
$existing_results = $pdo->query($sql)->fetchAll();
?>
SEO Monitor Dashboard