<?php
// University asset inventory and custody workflow.
declare(strict_types=1);

require_once __DIR__ . '/assets_common.php';

try {
    $user = assets_user($pdo);
} catch (Throwable $error) {
    error_log('Asset API authentication database error: ' . $error->getMessage());
    assets_json(['detail' => 'The asset API could not read the signed-in account. Check the PostgreSQL connection and user-table permissions.'], 500);
}
$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;
$action = strtolower(assets_clean($_GET['action'] ?? '', 40));

function asset_text_field(array $data, string $key, int $max, bool $required = false): ?string {
    $value = $data[$key] ?? '';
    if (!is_scalar($value)) assets_json(['detail' => 'Check the asset information and try again.'], 422);
    $value = assets_clean($value, $max);
    if ($required && $value === '') assets_json(['detail' => 'Complete all required asset fields.'], 422);
    return $value === '' ? null : $value;
}

function asset_condition(mixed $value): string {
    $condition = strtolower(assets_clean($value ?: 'good', 20));
    if (!in_array($condition, ['new','good','fair','poor','damaged'], true)) {
        assets_json(['detail' => 'Choose a valid asset condition.'], 422);
    }
    return $condition;
}

function asset_require_admin(array $user): void {
    assets_admin($user);
}

try {
    if ($method === 'GET') {
        if ($action === 'setup') {
            asset_require_admin($user);
            $campusStmt = $pdo->query("SELECT DISTINCT TRIM(campus) AS campus FROM users
                WHERE campus IS NOT NULL AND TRIM(campus)<>'' ORDER BY campus");
            $staffStmt = $pdo->query("SELECT id AS user_id,full_name,campus
                FROM users WHERE role='staff' AND LOWER(COALESCE(account_status,'active'))='active'
                ORDER BY full_name");
            assets_json([
                'current_user' => $user,
                'campuses' => $campusStmt->fetchAll(PDO::FETCH_ASSOC),
                'staff' => $staffStmt->fetchAll(PDO::FETCH_ASSOC),
                'conditions' => ['new','good','fair','poor','damaged'],
                'statuses' => ['available','assigned','maintenance','retired','lost'],
            ]);
        }

        if ($id > 0) {
            $asset = assets_record($pdo, $id);
            if (!$asset) assets_json(['detail' => 'Asset not found.'], 404);
            if ($user['role'] === 'staff' &&
                ((int)($asset['assigned_to_user_id'] ?? 0) !== (int)$user['id'] || $asset['status'] !== 'assigned')) {
                assets_json(['detail' => 'This asset is not currently assigned to your account.'], 403);
            }
            if (!in_array($user['role'], ['admin','staff'], true)) {
                assets_json(['detail' => 'You are not authorized to view asset records.'], 403);
            }
            assets_json(['data' => $asset]);
        }

        if ($user['role'] === 'admin') {
            $where = [];
            $params = [];
            $search = assets_clean($_GET['q'] ?? '', 120);
            $status = strtolower(assets_clean($_GET['status'] ?? '', 30));
            $campus = assets_clean($_GET['campus'] ?? '', 180);
            if ($search !== '') {
                $where[] = '(a.asset_tag ILIKE ? OR a.asset_name ILIKE ? OR a.category ILIKE ? OR COALESCE(a.serial_number,\'\') ILIKE ? OR a.campus ILIKE ? OR COALESCE(a.location,\'\') ILIKE ? OR COALESCE(staff.full_name,\'\') ILIKE ?)';
                for ($i = 0; $i < 7; $i++) $params[] = '%' . $search . '%';
            }
            if (in_array($status, ['available','assigned','maintenance','retired','lost'], true)) {
                $where[] = 'a.status = ?';
                $params[] = $status;
            }
            if ($campus !== '') {
                $where[] = 'a.campus = ?';
                $params[] = $campus;
            }
            $filter = $where ? ' WHERE ' . implode(' AND ', $where) : '';
            $stmt = $pdo->prepare("SELECT a.*,aa.id AS assignment_id,aa.staff_user_id AS assigned_to_user_id,
                staff.full_name AS assigned_to_name,aa.assigned_at,aa.due_back_date,
                (SELECT COUNT(*) FROM asset_maintenance m WHERE m.asset_id=a.id AND m.closed_at IS NULL) AS open_maintenance
                FROM assets a LEFT JOIN asset_assignments aa ON aa.asset_id=a.id AND aa.returned_at IS NULL
                LEFT JOIN users staff ON staff.id=aa.staff_user_id $filter ORDER BY a.updated_at DESC,a.id DESC");
            $stmt->execute($params);
            $rows = array_map('assets_public_record', $stmt->fetchAll(PDO::FETCH_ASSOC));
            $counts = $pdo->query("SELECT COUNT(*) AS total,
                COUNT(*) FILTER (WHERE status='available') AS available,
                COUNT(*) FILTER (WHERE status='assigned') AS assigned,
                COUNT(*) FILTER (WHERE status='maintenance') AS maintenance,
                COUNT(*) FILTER (WHERE status='retired') AS retired,
                COUNT(*) FILTER (WHERE status='lost') AS lost FROM assets")->fetch(PDO::FETCH_ASSOC);
            assets_json(['data' => $rows, 'count' => count($rows), 'counts' => $counts]);
        }

        if ($user['role'] === 'staff') {
            $stmt = $pdo->prepare("SELECT a.*,aa.id AS assignment_id,aa.staff_user_id AS assigned_to_user_id,
                aa.assigned_at,aa.due_back_date,aa.assignment_note
                FROM asset_assignments aa JOIN assets a ON a.id=aa.asset_id
                WHERE aa.staff_user_id=? AND aa.returned_at IS NULL AND a.status='assigned'
                ORDER BY aa.assigned_at DESC");
            $stmt->execute([$user['id']]);
            $rows = array_map('assets_public_record', $stmt->fetchAll(PDO::FETCH_ASSOC));
            assets_json(['data' => $rows, 'count' => count($rows)]);
        }

        assets_json(['detail' => 'You are not authorized to view asset records.'], 403);
    }

    if ($method !== 'POST') {
        header('Allow: GET, POST');
        assets_json(['detail' => 'Request method not allowed.'], 405);
    }
    asset_require_admin($user);
    $input = assets_input();
    $operation = $action !== '' ? $action : strtolower(assets_clean($input['action'] ?? '', 40));

    // Register a new item or update its descriptive and purchase details.
    if ($operation === 'create' || $operation === 'update') {
        $assetId = $operation === 'update' ? assets_positive_id($id ?: ($input['id'] ?? null)) : 0;
        $tag = asset_text_field($input, 'asset_tag', 50, true);
        $name = asset_text_field($input, 'asset_name', 180, true);
        $category = asset_text_field($input, 'category', 100, true);
        $campus = asset_text_field($input, 'campus', 180, true);
        $location = asset_text_field($input, 'location', 240);
        $serial = asset_text_field($input, 'serial_number', 140);
        $purchaseDate = assets_date($input['purchase_date'] ?? null, 'purchase date');
        $purchaseCost = assets_money($input['purchase_cost'] ?? null, 'purchase cost');
        $condition = asset_condition($input['condition_status'] ?? 'good');
        $campusCheck = $pdo->prepare("SELECT 1 FROM users WHERE campus IS NOT NULL AND LOWER(TRIM(campus))=LOWER(TRIM(?)) LIMIT 1");
        $campusCheck->execute([$campus]);
        if (!$campusCheck->fetchColumn()) assets_json(['detail'=>'Choose a campus already used in the university directory.'],422);

        if ($operation === 'create') {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare("INSERT INTO assets(asset_tag,asset_name,category,campus,location,serial_number,
                purchase_date,purchase_cost,condition_status,created_by_user_id)
                VALUES(?,?,?,?,?,?,?,?,?,?) RETURNING id");
            $stmt->execute([$tag,$name,$category,$campus,$location,$serial,$purchaseDate,$purchaseCost,$condition,$user['id']]);
            $assetId = (int)$stmt->fetchColumn();
            assets_event($pdo,$assetId,$user,'created','Registered asset ' . $tag);
            $pdo->commit();
        } else {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare('SELECT campus,status FROM assets WHERE id=? FOR UPDATE');
            $stmt->execute([$assetId]);
            $old = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$old) { $pdo->rollBack(); assets_json(['detail'=>'Asset not found.'],404); }
            if ($old['status'] === 'assigned' && $old['campus'] !== $campus) {
                $pdo->rollBack();
                assets_json(['detail'=>'Return this item before changing its campus.'],422);
            }
            $stmt = $pdo->prepare("UPDATE assets SET asset_tag=?,asset_name=?,category=?,campus=?,location=?,
                serial_number=?,purchase_date=?,purchase_cost=?,condition_status=?,updated_at=CURRENT_TIMESTAMP WHERE id=?");
            $stmt->execute([$tag,$name,$category,$campus,$location,$serial,$purchaseDate,$purchaseCost,$condition,$assetId]);
            assets_event($pdo,$assetId,$user,'updated','Updated asset details for ' . $tag);
            $pdo->commit();
        }
        assets_json(['message'=>'Asset details saved.','id'=>$assetId,'data'=>assets_record($pdo,$assetId)],$operation==='create'?201:200);
    }

    $assetId = assets_positive_id($id ?: ($input['asset_id'] ?? null));
    $pdo->beginTransaction();
    $stmt = $pdo->prepare('SELECT * FROM assets WHERE id=? FOR UPDATE');
    $stmt->execute([$assetId]);
    $asset = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$asset) { $pdo->rollBack(); assets_json(['detail'=>'Asset not found.'],404); }

    if ($operation === 'assign') {
        if ($asset['status'] !== 'available') {
            $pdo->rollBack(); assets_json(['detail'=>'Only available assets can be assigned.'],422);
        }
        $staffId = assets_positive_id($input['staff_user_id'] ?? null);
        $stmt = $pdo->prepare("SELECT id,full_name,campus FROM users
            WHERE id=? AND role='staff' AND LOWER(COALESCE(account_status,'active'))='active'");
        $stmt->execute([$staffId]);
        $staff = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$staff) { $pdo->rollBack(); assets_json(['detail'=>'Choose an active staff account.'],422); }
        if (mb_strtolower(trim((string)$staff['campus'])) !== mb_strtolower(trim((string)$asset['campus']))) {
            $pdo->rollBack(); assets_json(['detail'=>'The staff member and asset must belong to the same campus.'],422);
        }
        $due = assets_date($input['due_back_date'] ?? null, 'due-back date');
        $note = asset_text_field($input,'assignment_note',2000);
        $stmt = $pdo->prepare('INSERT INTO asset_assignments(asset_id,staff_user_id,assigned_by_user_id,due_back_date,assignment_note) VALUES(?,?,?,?,?)');
        $stmt->execute([$assetId,$staffId,$user['id'],$due,$note]);
        $pdo->prepare("UPDATE assets SET status='assigned',updated_at=CURRENT_TIMESTAMP WHERE id=?")->execute([$assetId]);
        assets_event($pdo,$assetId,$user,'assigned','Assigned to ' . ($staff['full_name'] ?: 'staff member'),['staff_user_id'=>$staffId,'due_back_date'=>$due]);
        $pdo->commit();
        assets_json(['message'=>'Asset assigned to staff member.','data'=>assets_record($pdo,$assetId)]);
    }

    if ($operation === 'return') {
        $condition = asset_condition($input['condition_on_return'] ?? 'good');
        $note = asset_text_field($input,'return_note',2000);
        $stmt = $pdo->prepare('SELECT id,staff_user_id FROM asset_assignments WHERE asset_id=? AND returned_at IS NULL FOR UPDATE');
        $stmt->execute([$assetId]);
        $assignment = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$assignment) { $pdo->rollBack(); assets_json(['detail'=>'This asset has no current staff assignment.'],422); }
        $stmt = $pdo->prepare('UPDATE asset_assignments SET returned_at=CURRENT_TIMESTAMP,returned_by_user_id=?,condition_on_return=?,return_note=? WHERE id=?');
        $stmt->execute([$user['id'],$condition,$note,$assignment['id']]);
        $nextStatus = $condition === 'damaged' ? 'maintenance' : 'available';
        $pdo->prepare('UPDATE assets SET status=?,condition_status=?,updated_at=CURRENT_TIMESTAMP WHERE id=?')->execute([$nextStatus,$condition,$assetId]);
        assets_event($pdo,$assetId,$user,'returned','Recorded the return of the assigned asset',['condition'=>$condition,'staff_user_id'=>(int)$assignment['staff_user_id']]);
        $pdo->commit();
        assets_json(['message'=>'Asset return recorded.','data'=>assets_record($pdo,$assetId)]);
    }

    if ($operation === 'open_maintenance') {
        if ($asset['status'] === 'assigned' || $asset['status'] === 'retired' || $asset['status'] === 'lost') {
            $pdo->rollBack(); assets_json(['detail'=>'Return the asset and resolve its status before opening maintenance.'],422);
        }
        $issue = asset_text_field($input,'issue',4000,true);
        $cost = assets_money($input['cost'] ?? null,'maintenance cost');
        $stmt = $pdo->prepare('INSERT INTO asset_maintenance(asset_id,reported_by_user_id,issue,cost) VALUES(?,?,?,?) RETURNING id');
        $stmt->execute([$assetId,$user['id'],$issue,$cost]);
        $maintenanceId = (int)$stmt->fetchColumn();
        $pdo->prepare("UPDATE assets SET status='maintenance',updated_at=CURRENT_TIMESTAMP WHERE id=?")->execute([$assetId]);
        assets_event($pdo,$assetId,$user,'maintenance_opened','Opened a maintenance record',['maintenance_id'=>$maintenanceId,'issue'=>$issue]);
        $pdo->commit();
        assets_json(['message'=>'Maintenance record opened.','data'=>assets_record($pdo,$assetId)]);
    }

    if ($operation === 'close_maintenance') {
        $work = asset_text_field($input,'work_done',5000,true);
        $condition = asset_condition($input['condition_status'] ?? 'good');
        $cost = assets_money($input['cost'] ?? null,'maintenance cost');
        $stmt = $pdo->prepare('SELECT id FROM asset_maintenance WHERE asset_id=? AND closed_at IS NULL ORDER BY id DESC LIMIT 1 FOR UPDATE');
        $stmt->execute([$assetId]);
        $maintenance = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$maintenance) { $pdo->rollBack(); assets_json(['detail'=>'There is no open maintenance record for this asset.'],422); }
        $stmt = $pdo->prepare('UPDATE asset_maintenance SET closed_at=CURRENT_TIMESTAMP,work_done=?,cost=COALESCE(?,cost),condition_after=? WHERE id=?');
        $stmt->execute([$work,$cost,$condition,$maintenance['id']]);
        $nextStatus = $condition === 'damaged' ? 'maintenance' : 'available';
        $pdo->prepare('UPDATE assets SET status=?,condition_status=?,updated_at=CURRENT_TIMESTAMP WHERE id=?')->execute([$nextStatus,$condition,$assetId]);
        assets_event($pdo,$assetId,$user,'maintenance_closed','Closed maintenance record',['maintenance_id'=>(int)$maintenance['id'],'condition'=>$condition]);
        $pdo->commit();
        $message = $condition === 'damaged' ? 'Maintenance entry closed; the asset remains in maintenance because it is still damaged.' : 'Maintenance completed and asset is available.';
        assets_json(['message'=>$message,'data'=>assets_record($pdo,$assetId)]);
    }

    if ($operation === 'retire') {
        if (in_array($asset['status'], ['assigned','maintenance'], true)) {
            $pdo->rollBack(); assets_json(['detail'=>'Return the asset and close maintenance before retiring it.'],422);
        }
        $pdo->prepare("UPDATE assets SET status='retired',updated_at=CURRENT_TIMESTAMP WHERE id=?")->execute([$assetId]);
        assets_event($pdo,$assetId,$user,'retired','Retired asset ' . $asset['asset_tag']);
        $pdo->commit();
        assets_json(['message'=>'Asset retired. Its history has been preserved.']);
    }

    if ($operation === 'mark_lost' || $operation === 'recover') {
        if ($operation === 'mark_lost') {
            if (!in_array($asset['status'], ['available','assigned'], true)) {
                $pdo->rollBack(); assets_json(['detail'=>'Return the asset and resolve its current status first.'],422);
            }
            if ($asset['status'] === 'assigned') {
                $stmt = $pdo->prepare('SELECT id,staff_user_id FROM asset_assignments WHERE asset_id=? AND returned_at IS NULL FOR UPDATE');
                $stmt->execute([$assetId]);
                $assignment = $stmt->fetch(PDO::FETCH_ASSOC);
                if ($assignment) {
                    $pdo->prepare("UPDATE asset_assignments SET returned_at=CURRENT_TIMESTAMP,returned_by_user_id=?,condition_on_return='damaged',return_note='Asset marked as lost' WHERE id=?")
                        ->execute([$user['id'],$assignment['id']]);
                }
            }
            $pdo->prepare("UPDATE assets SET status='lost',updated_at=CURRENT_TIMESTAMP WHERE id=?")->execute([$assetId]);
            assets_event($pdo,$assetId,$user,'marked_lost','Marked asset ' . $asset['asset_tag'] . ' as lost');
        } else {
            if ($asset['status'] !== 'lost') { $pdo->rollBack(); assets_json(['detail'=>'Only a lost asset can be recovered.'],422); }
            $pdo->prepare("UPDATE assets SET status='available',condition_status='fair',updated_at=CURRENT_TIMESTAMP WHERE id=?")->execute([$assetId]);
            assets_event($pdo,$assetId,$user,'recovered','Recovered asset ' . $asset['asset_tag']);
        }
        $pdo->commit();
        assets_json(['message'=>$operation==='recover'?'Asset recovered and returned to available inventory.':'Asset marked as lost.']);
    }

    $pdo->rollBack();
    assets_json(['detail'=>'Choose a supported asset operation.'],422);
} catch (PDOException $error) {
    if ($pdo->inTransaction()) $pdo->rollBack();
    if ((string)$error->getCode() === '23505') {
        $detail = str_contains($error->getMessage(), 'uq_assets_asset_tag')
            ? 'That asset tag already exists.'
            : 'This asset already has a current assignment or open maintenance record.';
        assets_json(['detail'=>$detail],409);
    }
    error_log('ASSET API database error: ' . $error->getMessage());
    $sqlState = (string)($error->errorInfo[0] ?? $error->getCode());
    if ($sqlState === '42P01') {
        assets_json(['detail'=>'Asset tables are missing. In pgAdmin 4, run api/assets_schema.sql against the application database.'],500);
    }
    if ($sqlState === '42501') {
        assets_json(['detail'=>'The PHP database role lacks permission for the asset tables. Grant it SELECT, INSERT and UPDATE access, plus sequence usage.'],500);
    }
    if ($sqlState === '42703') {
        assets_json(['detail'=>'A required database column is missing. Confirm the API is connected to the correct PostgreSQL database and that its users table has id, full_name, role, campus and account_status.'],500);
    }
    assets_json(['detail'=>'The PostgreSQL request failed. Check the PHP error log for the exact database error.'],500);
} catch (Throwable $error) {
    if ($pdo->inTransaction()) $pdo->rollBack();
    error_log('ASSET API error: ' . $error->getMessage());
    assets_json(['detail'=>'The asset request could not be completed.'],500);
}
