<!-- A ACC.NO
B TITLE OF THE BOOK
C AUTHOR(S)
D AUTHOR(S)
E AUTHOR(S)
F AUTHOR(S)
G PUBLISHER
H PUB . YEAR
I EDITION
J ISBN NO
K PRICE
L CUPBOARD
M RACK
N BOOK NO
O REMARKS -->
<?php
ob_start();

ini_set('max_execution_time', 300); // 5 minutes

session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Asia/Kolkata');

include('includes/config.php');
require __DIR__ . '/../vendor/autoload.php';
require_once('../includes/phpqrcode/qrlib.php');

use PhpOffice\PhpSpreadsheet\IOFactory;

/* ---------- Helpers ---------- */

function valOrNull($v)
{
    if ($v === null)
        return null;
    $v = trim((string) $v);
    if ($v === '' || $v === '-' || $v === '_') // treat underscore as empty too
        return null;
    return $v;
}

function toFloatSafe($v)
{
    if ($v === null || $v === '')
        return 0.0;
    if (is_numeric($v))
        return (float) $v;
    $clean = preg_replace('/[^\d.]/', '', (string) $v);
    return $clean === '' ? 0.0 : (float) $clean;
}

function toIntSafe($v)
{
    if ($v === null || $v === '')
        return null;
    if (is_numeric($v))
        return (int) $v;
    $clean = preg_replace('/\D+/', '', (string) $v);
    return $clean === '' ? null : (int) $clean;
}

function normalizeEdition($raw)
{
    if ($raw === null)
        return null;

    $s = trim((string) $raw);

    // Treat empty, dash, _, etc. as null
    if ($s === '' || $s === '-' || $s === '_' || $s === '--')
        return null;

    // Remove accidental characters like ` ' "
    $s = preg_replace('/[^0-9a-zA-Z]/', '', $s);

    if ($s === '')
        return null;

    $s = strtoupper($s);

    // Extract first number (handles 3TH, 21TH, 35TH, etc)
    if (preg_match('/^(\d{1,3})/', $s, $m)) {
        return (int) $m[1];
    }

    return null;
}


function normalizeVolume($raw)
{
    $s = $raw === null ? null : trim((string) $raw);
    if ($s === null || $s === '' || $s === '-' || $s === '_')
        return null;
    $u = strtoupper($s);
    if (preg_match('/\bVOL(?:UME)?\.?\s*([0-9IVXLC]+)\b/i', $u, $m))
        return 'Vol ' . $m[1];
    if (preg_match('/\bPART\.?\s*([0-9IVXLC]+)\b/i', $u, $m))
        return 'Part ' . $m[1];
    if (preg_match('/^\s*([0-9]+)\s*$/', $u, $m))
        return 'Vol ' . $m[1];
    return $raw;
}

function ensureAuthor(PDO $dbh, $authorName)
{
    $clean = normalizeAuthorName($authorName);
    if (!$clean)
        return null;

    // Check if normalized author already exists
    $st = $dbh->prepare("SELECT id FROM tblauthors WHERE LOWER(TRIM(AuthorName)) = LOWER(TRIM(:n)) LIMIT 1");
    $st->execute([':n' => $clean]);
    $r = $st->fetch(PDO::FETCH_ASSOC);

    if ($r)
        return (int) $r['id'];

    // Create new AuthorID like AUTH000001
    $st2 = $dbh->prepare("
        SELECT MAX(CAST(SUBSTRING(AuthorID,5) AS UNSIGNED)) AS maxid 
        FROM tblauthors WHERE AuthorID REGEXP '^AUTH[0-9]+'
    ");
    $st2->execute();
    $row2 = $st2->fetch(PDO::FETCH_ASSOC);

    $next = ($row2 && $row2['maxid']) ? ((int) $row2['maxid'] + 1) : 1;
    $newAid = 'AUTH' . str_pad((string) $next, 6, '0', STR_PAD_LEFT);

    // Insert normalized name
    $ins = $dbh->prepare("INSERT INTO tblauthors (AuthorID, AuthorName, creationDate)
                          VALUES (:aid, :name, :cd)");
    $ins->execute([
        ':aid' => $newAid,
        ':name' => $clean,
        ':cd' => date('Y-m-d H:i:s')
    ]);

    return (int) $dbh->lastInsertId();
}


function getOrCreateRackId(PDO $dbh, $rack_no, $shelf_no, $floor_no)
{
    if (!$rack_no && !$shelf_no && !$floor_no)
        return null;

    $st = $dbh->prepare("SELECT id FROM tbl_racks 
                         WHERE rack_no = :r AND shelf_no = :s AND floor = :f 
                         LIMIT 1");
    $st->execute([
        ':r' => $rack_no,
        ':s' => $shelf_no,
        ':f' => $floor_no
    ]);
    $found = $st->fetch(PDO::FETCH_ASSOC);
    if ($found)
        return (int) $found['id'];

    $ins = $dbh->prepare("INSERT INTO tbl_racks (rack_no, shelf_no, floor) 
                          VALUES (:r, :s, :f)");
    $ins->execute([
        ':r' => $rack_no,
        ':s' => $shelf_no,
        ':f' => $floor_no
    ]);

    return (int) $dbh->lastInsertId();
}

function ensurePublisher(PDO $dbh, $publisherName)
{
    $p = valOrNull($publisherName);
    if (!$p)
        return null;
    $st = $dbh->prepare("SELECT id FROM tblpublishers WHERE PublisherName = :n LIMIT 1");
    $st->execute([':n' => $p]);
    $r = $st->fetch(PDO::FETCH_ASSOC);
    if ($r)
        return (int) $r['id'];
    $ins = $dbh->prepare("INSERT INTO tblpublishers (PublisherName, creationDate) VALUES (:n, :cd)");
    $ins->execute([':n' => $p, ':cd' => date('Y-m-d H:i:s')]);
    return (int) $dbh->lastInsertId();
}
if (!function_exists('cleanPrice')) {
    function cleanPrice($price)
    {
        if ($price === null || $price === '') {
            return 0; // or return null; depending on what you want
        }

        // Remove RS, Rs, dots
        $clean = preg_replace('/rs|\./i', '', (string) $price);

        // Remove spaces
        $clean = trim(str_replace(' ', '', $clean));

        // Remove any remaining non-digits
        return (float) preg_replace('/\D/', '', $clean);
    }
}



function normalizeAuthorName($name)
{
    if (!$name)
        return null;

    // Remove unwanted prefixes
    $name = preg_replace('/\b(Dr|Dr\.|Mr|Mr\.|Mrs|Mrs\.|Ms|Ms\.|Prof|Prof\.)\b/i', '', $name);

    // Remove numbering: "1)", "2." , "3 -", etc.
    $name = preg_replace('/^\d+\s*[\)\.\:\-]\s*/', '', $name);
    $name = preg_replace('/^\d+\s+/', '', $name);

    // Remove dots in initials: "T.S." → "TS"
    $name = str_replace('.', ' ', $name);

    // Remove commas and weird separators
    $name = preg_replace('/[,\/&]/', ' ', $name);

    // Reduce multiple spaces
    $name = preg_replace('/\s+/', ' ', $name);

    $name = trim($name);
    if ($name === '')
        return null;

    // Convert to proper case: "T S REDDY" → "T S Reddy"
    $name = ucwords(strtolower($name));

    return $name;
}


/* ---------- Main ---------- */

if ($_SERVER['REQUEST_METHOD'] !== 'POST' || empty($_FILES['excel_file'])) {
    $_SESSION['error'] = "No file uploaded.";
    header("Location: import-books.php");
    exit;
}
$department = isset($_POST['department']) && $_POST['department'] !== ''
    ? (int) $_POST['department']
    : null;

$category = isset($_POST['category']) && $_POST['category'] !== ''
    ? (int) $_POST['category']
    : null;

$degree = isset($_POST['degree']) && $_POST['degree'] !== ''
    ? (int) $_POST['degree']
    : null;

$shift = isset($_POST['shift']) && $_POST['shift'] !== ''
    ? (int) $_POST['shift']
    : null;


$uploadDir = 'uploads/excel/';
if (!file_exists($uploadDir))
    mkdir($uploadDir, 0755, true);
$fileName = time() . '_' . basename($_FILES['excel_file']['name']);
$filePath = $uploadDir . $fileName;
if (!move_uploaded_file($_FILES['excel_file']['tmp_name'], $filePath)) {
    $_SESSION['error'] = "Upload failed.";
    header("Location: import-books.php");
    exit;
}

try {
    $spreadsheet = IOFactory::load($filePath);
    $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
    $qrDir = '../uploads/qrcodes/';
    if (!file_exists($qrDir))
        mkdir($qrDir, 0755, true);

    $isHeader = true;
    $imported = 0;
    $skipped = [];

    foreach ($sheetData as $rIdx => $row) {
        if ($isHeader) {
            $isHeader = false;
            continue;
        }

        // Skip row only if all columns are empty
        $allEmpty = true;
        foreach ($row as $v) {
            if (valOrNull($v) !== null) {
                $allEmpty = false;
                break;
            }
        }
        if ($allEmpty)
            continue;

        // Column mapping for NEW FORMAT:
//
// A: accession_no
// B: BookName
// C-F: Authors (multiple columns + each may contain multiple authors separated by &)
// G: Publisher
// H: Publication Year
// I: Edition
// J: ISBN
// K: Price
// L: Cupboard No
// M: Rack No
// N: Book No (Accession/Copy number input manually?)
// O: Remarks / Status

        $accNumber = valOrNull($row['A'] ?? null);
        $bookName = valOrNull($row['B'] ?? null);

        // ---- AUTHORS (C, D, E, F) ----
        $authorCells = [
            $row['C'] ?? null,
            $row['D'] ?? null,
            $row['E'] ?? null,
            $row['F'] ?? null
        ];

        $authorNames = [];

        foreach ($authorCells as $cell) {
            if ($cell) {
                // Split by '&' first
                $spl = preg_split('/\s*(?:\/|&|,)\s*/', $cell);

                foreach ($spl as $a) {
                    $a = normalizeAuthorName($a);
                    if ($a === '')
                        continue;

                    // Remove numbering like:
                    // 1) Author
                    // 2. Author
                    // 3 : Author
                    // 4 - Author
                    // 5)Dr. Author
                    $a = preg_replace('/^\d+\s*[\)\.\:\-]\s*/', '', $a);

                    // Remove numbering without punctuation
                    // Example: "1 Author" -> "Author"
                    $a = preg_replace('/^\d+\s+/', '', $a);

                    $a = trim($a);

                    if ($a !== '') {
                        $authorNames[] = $a;
                    }
                }
            }
        }

        // Remove duplicates
        $authorNames = array_unique($authorNames);


        // ---- Publisher, Year, Edition, ISBN, Price ----
        $publisherName = valOrNull($row['G'] ?? null);
        $pubYear = toIntSafe(valOrNull($row['H'] ?? null));
        $editionNorm = normalizeEdition($row['I'] ?? null);
        $isbn = valOrNull($row['J'] ?? null);


        // use:
        $price = cleanPrice($row['K'] ?? null);

        $cupboard = valOrNull($row['L'] ?? null);
        $rack_no = valOrNull($row['M'] ?? null);
        $book_no_excel = valOrNull($row['N'] ?? null); // Manual book no from excel
        $remarks = valOrNull($row['O'] ?? null);
        if ($remarks === null) {
            $bookStatus = 'available';
        } elseif ($remarks === 'NA') {
            $bookStatus = 'NA';
        }
        // New rack_id → cupboard + rack only
        $rack_id = getOrCreateRackId($dbh, $cupboard, $rack_no, $book_no_excel);

        // ---- AUTHOR IDs ----
        $authorIdsArr = [];
        foreach ($authorNames as $aName) {
            $aid = ensureAuthor($dbh, $aName);
            if ($aid)
                $authorIdsArr[] = $aid;
        }
        $authorIdsCsv = !empty($authorIdsArr) ? implode(',', $authorIdsArr) : null;

        // ---- PUBLISHER ----
        $publisherId = ensurePublisher($dbh, $publisherName);

        // Check duplicate book (treat as same book if: BookName, Authors, Publisher, PublicationYear, Edition, ISBN, Price match)
        $dupId = null;
        $st = $dbh->prepare("SELECT id, AuthorIds, Edition, Publisher, PublicationYear, ISBNNumber, BookPrice FROM tblbooks WHERE LOWER(TRIM(BookName)) = LOWER(TRIM(:bn))");
        $st->execute([':bn' => $bookName]);
        while ($r = $st->fetch(PDO::FETCH_ASSOC)) {
            $sameEdition = ($r['Edition'] == $editionNorm);
            // $sameVolume = ($r['Volume'] == $volumeNorm);
            $samePublisher = ((int) $r['Publisher'] === (int) $publisherId);
            $sameYear = ((int) $r['PublicationYear'] === (int) $pubYear);
            $sameIsbn = (valOrNull($r['ISBNNumber']) == valOrNull($isbn));
            $samePrice = ((float) $r['BookPrice'] == (float) $price);

            $dbAuthors = array_filter(explode(',', $r['AuthorIds'] ?? ''));
            sort($dbAuthors, SORT_NUMERIC);
            $curAuthors = $authorIdsArr;
            sort($curAuthors, SORT_NUMERIC);

            if (($sameIsbn) || ($sameEdition && $samePublisher && $sameYear && $samePrice && ($dbAuthors == $curAuthors))) {
                $dupId = (int) $r['id'];
                break;
            }
        }

        // Insert new book if not duplicate
        if ($dupId) {
            $bookId = $dupId;
        } else {
            $dbh->beginTransaction();
            $st = $dbh->prepare("INSERT INTO tblbooks (
    BookName, AuthorIds, Edition, Publisher, PublicationYear,
    ISBNNumber, BookPrice, total_count, current_count, Status,
    department_id, CatId, degree_id
) VALUES (
    :BookName, :AuthorIds, :Edition, :Publisher, :PublicationYear,
    :ISBNNumber, :BookPrice, 0, 0, 1,
    :department, :category, :degree
)");

            $st->execute([
                ':BookName' => $bookName,
                ':AuthorIds' => $authorIdsCsv,
                ':Edition' => $editionNorm,
                // ':Volume' => $volumeNorm,
                ':Publisher' => $publisherId,
                ':PublicationYear' => $pubYear,
                ':ISBNNumber' => $isbn,
                ':BookPrice' => $price,
                ':department' => $department,
                ':category' => $category,
                ':degree' => $degree
            ]);

            $bookId = (int) $dbh->lastInsertId();
            $dbh->commit();
        }

        // Insert book copy with accession_no, rack_id and book_status as required
        $stNext = $dbh->prepare("SELECT COUNT(*) AS cnt FROM tblbookcopies WHERE book_id = :bid");
        $stNext->execute([':bid' => $bookId]);
        $rowNext = $stNext->fetch(PDO::FETCH_ASSOC);
        $nextCopy = ($rowNext && $rowNext['cnt']) ? ((int) $rowNext['cnt'] + 1) : 1;

        $book_no = "BOOK-" . str_pad($bookId, 5, "0", STR_PAD_LEFT) . "-" . str_pad($nextCopy, 3, "0", STR_PAD_LEFT);
        $qrFileName = "bookcopy_{$bookId}_{$nextCopy}.png";
        $qrFullPath = $qrDir . $qrFileName;
        $qrRelativePath = "/uploads/qrcodes/" . $qrFileName;
        $copyUrl = "http://localhost/library_software/admin/view-bookcopy.php?bookcopy=" . urlencode($book_no);
        QRcode::png($copyUrl, $qrFullPath, QR_ECLEVEL_L, 4);

        $stc = $dbh->prepare("INSERT INTO tblbookcopies 
    (book_id, accession_no, shift, rack_id, book_no, book_status, QRCode)
VALUES 
    (:bid, :acc, :shift, :rack_id, :bno, :bstatus, :qr)");

        $stc->execute([
            ':bid' => $bookId,
            ':acc' => $accNumber,            // A column
            ':shift' => $shift,
            ':rack_id' => $rack_id,
            ':bno' => $book_no,              // Auto generated unique book copy id
            ':bstatus' => $bookStatus,
            ':qr' => $qrRelativePath
        ]);


        // Update counts
        $dbh->prepare("UPDATE tblbooks 
                       SET total_count = (SELECT COUNT(*) FROM tblbookcopies WHERE book_id = :bid),
                           current_count = (SELECT COUNT(*) FROM tblbookcopies WHERE book_id = :bid AND book_status = 1)
                       WHERE id = :bid")->execute([':bid' => $bookId]);

        $imported++;
    }

    $_SESSION['msg'] = "Imported: {$imported} | Skipped: " . count($skipped);
    $_SESSION['skipped_details'] = $skipped; // only book names array
    ob_end_clean();

    header("Location: import-books.php");
    exit;

} catch (Exception $e) {
    $_SESSION['error'] = "Import failed: " . $e->getMessage();
    header("Location: import-books.php");
    exit;
}
?>