<?php
// Auto-detect BASE_URL from the actual file location relative to DOCUMENT_ROOT
// This works correctly on both local Laragon and cPanel shared hosting
if (!defined('BASE_URL')) {
    $projectRoot = str_replace('\\', '/', dirname(__DIR__));
    $docRoot     = str_replace('\\', '/', rtrim($_SERVER['DOCUMENT_ROOT'] ?? '', '/\\'));
    $subPath     = str_replace($docRoot, '', $projectRoot);
    $subPath     = '/' . ltrim($subPath, '/');
    $subPath     = rtrim($subPath, '/');
    define('BASE_URL', $subPath === '/' ? '' : $subPath);
}

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);
