<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'lostfound_db');

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
$conn->set_charset("utf8mb4");

function sanitize($conn, $data) {
    return $conn->real_escape_string(trim($data));
}

// ── Archived items migration (runs on every request, safe & idempotent) ──────
$conn->query("CREATE TABLE IF NOT EXISTS archived_items (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    source_item_id  INT NOT NULL,
    source_type     ENUM('lost','found') NOT NULL,
    item_name       VARCHAR(150) NOT NULL,
    description     TEXT,
    category        VARCHAR(100),
    location        VARCHAR(150),
    date_reported   DATE,
    image_path      VARCHAR(255),
    reporter_name   VARCHAR(100),
    reporter_email  VARCHAR(100),
    contact_number  VARCHAR(20),
    original_status VARCHAR(50),
    archive_reason  VARCHAR(255),
    extra_data      JSON,
    archived_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    restored_at     TIMESTAMP NULL,
    is_restored     BOOLEAN DEFAULT FALSE,
    UNIQUE KEY unique_archive (source_item_id, source_type)
)");

// Add any columns that may be missing on older installs
foreach ([
    'is_restored'  => 'BOOLEAN DEFAULT FALSE',
    'restored_at'  => 'TIMESTAMP NULL',
    'extra_data'   => 'JSON',
] as $__col => $__def) {
    $__r = $conn->query("SHOW COLUMNS FROM archived_items LIKE '$__col'");
    if ($__r && $__r->num_rows === 0)
        $conn->query("ALTER TABLE archived_items ADD COLUMN $__col $__def");
}
unset($__col, $__def, $__r);

// Drop broken FK if it exists (migration SQL wrongly referenced only lost_items)
$__fk = $conn->query("SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = 'archived_items'
      AND CONSTRAINT_NAME = 'archived_items_ibfk_1'");
if ($__fk && $__fk->num_rows > 0)
    $conn->query("ALTER TABLE archived_items DROP FOREIGN KEY archived_items_ibfk_1");
unset($__fk);
