<?php
/* ============================================================================
 *  admin/cleanup_attendance_photos.php
 *  Deletes attendance selfies older than 90 days (3 months) to save space,
 *  and clears their DB references. Safe to run repeatedly.
 *  Run manually: /seven-credit-system/admin/cleanup_attendance_photos.php
 *  Or schedule it to run daily.
 * ==========================================================================*/
ini_set('display_errors', 1);
error_reporting(E_ALL);

session_start();
require_once '../config/database.php';
date_default_timezone_set('Asia/Vientiane');

/* Allow admin in browser, or CLI/cron */
$isCli = (php_sapi_name() === 'cli');
if (!$isCli && (!isset($_SESSION['user_id']) || ($_SESSION['role'] ?? '') !== 'admin')) {
    http_response_code(403);
    die('Admin only. Please log in as an administrator.');
}
header('Content-Type: text/plain; charset=utf-8');

$KEEP_DAYS = 90;
$cutoff = date('Y-m-d', strtotime("-{$KEEP_DAYS} days"));
echo "Cleanup selfies older than {$KEEP_DAYS} days (before {$cutoff}).\n\n";

$photoCols = ['am_in_photo','am_out_photo','pm_in_photo','pm_out_photo','check_in_photo','check_out_photo'];
$base = __DIR__ . '/../';
$filesDeleted = 0; $rowsCleared = 0;

try {
    /* find existing photo columns */
    $cols = [];
    foreach ($photoCols as $c) {
        $st = $pdo->prepare("SHOW COLUMNS FROM hr_attendance LIKE ?");
        $st->execute([$c]);
        if ($st->fetch()) $cols[] = $c;
    }
    if (!$cols) { echo "No photo columns found. Nothing to do.\n"; exit; }

    $select = implode(',', $cols);
    $rows = $pdo->prepare("SELECT id, $select FROM hr_attendance WHERE work_date < ?");
    $rows->execute([$cutoff]);
    $rows = $rows->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $r) {
        $clear = false;
        foreach ($cols as $c) {
            $path = $r[$c] ?? null;
            if (!$path) continue;
            $abs = $base . ltrim($path, '/');
            if (is_file($abs) && @unlink($abs)) $filesDeleted++;
            $clear = true;
        }
        if ($clear) {
            $set = implode('=NULL, ', $cols) . '=NULL';
            $pdo->prepare("UPDATE hr_attendance SET $set WHERE id=?")->execute([$r['id']]);
            $rowsCleared++;
        }
    }
    echo "Files deleted: {$filesDeleted}\n";
    echo "Rows cleared:  {$rowsCleared}\n";

    /* sweep orphan files left on disk older than cutoff */
    $orphans = 0;
    $dir = $base . 'uploads/attendance';
    if (is_dir($dir)) {
        $it = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($dir, FilesystemIterator::SKIP_DOTS));
        foreach ($it as $f) {
            if ($f->isFile() && $f->getMTime() < strtotime("-{$KEEP_DAYS} days")) {
                if (@unlink($f->getPathname())) $orphans++;
            }
        }
    }
    echo "Orphan files swept: {$orphans}\n";
    echo "\nDone.\n";
} catch (Throwable $e) {
    echo "Error: " . $e->getMessage() . "\n";
}