<?php

function runAutoArchive($conn) {
    $res   = $conn->query("SELECT setting_value FROM settings WHERE setting_key='archive_age_years'");
    $years = ($res && $r = $res->fetch_assoc()) ? max(1, (int)$r['setting_value']) : 2;

    foreach (['lost', 'found'] as $type) {
        $table  = $type === 'lost' ? 'lost_items' : 'found_items';
        $result = $conn->query("SELECT * FROM $table WHERE status='pending' AND created_at < DATE_SUB(NOW(), INTERVAL $years YEAR)");
        if ($result) while ($item = $result->fetch_assoc()) archiveItem($conn, $item['id'], $type, $item);
    }
}

function archiveItem($conn, $item_id, $item_type, $item_data = null) {
    $table = $item_type === 'lost' ? 'lost_items' : 'found_items';

    if (!$item_data) {
        $r = $conn->query("SELECT * FROM $table WHERE id=$item_id");
        if (!$r || $r->num_rows === 0) return false;
        $item_data = $r->fetch_assoc();
    }

    // Already archived (not restored)?
    $chk = $conn->query("SELECT id FROM archived_items WHERE source_item_id=$item_id AND source_type='$item_type' AND is_restored=FALSE");
    if ($chk && $chk->num_rows > 0) return false;

    $ur   = $conn->query("SELECT name, email, phone FROM users WHERE id={$item_data['user_id']}");
    $user = ($ur && $ur->num_rows) ? $ur->fetch_assoc() : ['name'=>'Unknown','email'=>'','phone'=>''];

    $name     = $conn->real_escape_string($item_data['item_name']);
    $desc     = $conn->real_escape_string($item_data['description'] ?? '');
    $cat      = $conn->real_escape_string($item_data['category'] ?? '');
    $loc      = $conn->real_escape_string($item_data['location'] ?? '');
    $reporter = $conn->real_escape_string($user['name']);
    $email    = $conn->real_escape_string($user['email']);
    $contact  = $conn->real_escape_string($item_data['contact_number'] ?? $user['phone'] ?? '');
    $img      = $conn->real_escape_string($item_data['image_path'] ?? '');
    $ostatus  = $conn->real_escape_string($item_data['status']);
    $date     = $item_type === 'lost' ? ($item_data['date_lost'] ?? '') : ($item_data['date_found'] ?? '');
    $reason   = 'Automatically Archived - Unclaimed for 2 Years';

    $ok = $conn->query("INSERT INTO archived_items
        (source_item_id, source_type, item_name, description, category, location, date_reported,
         image_path, reporter_name, reporter_email, contact_number, original_status, archive_reason)
        VALUES
        ($item_id, '$item_type', '$name', '$desc', '$cat', '$loc', '$date',
         '$img', '$reporter', '$email', '$contact', '$ostatus', '$reason')");

    if (!$ok) return false;

    // Ensure status enum includes 'archived'
    $sc = $conn->query("SHOW COLUMNS FROM $table LIKE 'status'")->fetch_assoc();
    if ($sc && strpos($sc['Type'], 'archived') === false)
        $conn->query("ALTER TABLE $table MODIFY status ENUM('pending','matched','returned','closed','archived') DEFAULT 'pending'");

    // Ensure archived_at / archive_reason columns exist
    foreach (['archived_at' => 'TIMESTAMP NULL', 'archive_reason' => 'VARCHAR(255)'] as $c => $d) {
        $cr = $conn->query("SHOW COLUMNS FROM $table LIKE '$c'");
        if ($cr && $cr->num_rows === 0) $conn->query("ALTER TABLE $table ADD COLUMN $c $d");
    }

    $conn->query("UPDATE $table SET status='archived', archived_at=NOW(), archive_reason='$reason' WHERE id=$item_id");
    return true;
}

function restoreArchivedItem($conn, $archive_id) {
    $r = $conn->query("SELECT * FROM archived_items WHERE id=$archive_id");
    if (!$r || $r->num_rows === 0) return false;
    $a     = $r->fetch_assoc();
    $table = $a['source_type'] === 'lost' ? 'lost_items' : 'found_items';

    $conn->query("UPDATE $table SET status='pending', archived_at=NULL, archive_reason=NULL WHERE id={$a['source_item_id']}");
    $conn->query("UPDATE archived_items SET is_restored=TRUE, restored_at=NOW() WHERE id=$archive_id");
    return true;
}
