<?php
include('includes/config.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

$sql = "SELECT id, cbrb_no FROM tblbookcopies";
$query = $dbh->prepare($sql);
$query->execute();
$copies = $query->fetchAll(PDO::FETCH_OBJ);

foreach ($copies as $copy) {

    $raw = trim($copy->cbrb_no ?? '');

    // damaged
    if (preg_match('/damag/i', $raw)) {
        $up = $dbh->prepare("UPDATE tblbookcopies SET book_status='Damaged', rack_id=NULL WHERE id=:id");
        $up->bindParam(':id', $copy->id);
        $up->execute();
        continue;
    }

    // NA
    if (strtoupper($raw) == "NA") {
        $up = $dbh->prepare("UPDATE tblbookcopies SET book_status='NA', rack_id=NULL WHERE id=:id");
        $up->bindParam(':id', $copy->id);
        $up->execute();
        continue;
    }

    // NEW regex → supports floor values like 21A, 03B, 10 etc.
    if (preg_match('/^(\d+)-(\d+)-([0-9A-Za-z]+)$/', $raw, $m)) {

        $rack_no  = $m[1];
        $shelf_no = $m[2];
        $floor    = $m[3];

        // check rack record
        $chk = $dbh->prepare(
            "SELECT id FROM tbl_racks WHERE rack_no=:rack AND shelf_no=:shelf AND floor=:floor LIMIT 1"
        );
        $chk->bindParam(':rack', $rack_no);
        $chk->bindParam(':shelf', $shelf_no);
        $chk->bindParam(':floor', $floor);
        $chk->execute();

        if ($chk->rowCount() == 0) {
            $ins = $dbh->prepare("INSERT INTO tbl_racks (rack_no, shelf_no, floor) 
                                  VALUES (:rack, :shelf, :floor)");
            $ins->bindParam(':rack', $rack_no);
            $ins->bindParam(':shelf', $shelf_no);
            $ins->bindParam(':floor', $floor);
            $ins->execute();
            $rack_id = $dbh->lastInsertId();
        } else {
            $rack_id = $chk->fetchColumn();
        }

        // update book copy
        $up = $dbh->prepare("UPDATE tblbookcopies SET rack_id=:rid, book_status=NULL WHERE id=:id");
        $up->bindParam(':rid', $rack_id);
        $up->bindParam(':id', $copy->id);
        $up->execute();

    } else {
        // anything else → NA
        $up = $dbh->prepare("UPDATE tblbookcopies SET book_status='NA', rack_id=NULL WHERE id=:id");
        $up->bindParam(':id', $copy->id);
        $up->execute();
    }
}

echo "Update completed successfully.";
?>
