<?php
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 === '-')
        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)
{
    $s = $raw === null ? null : trim((string) $raw);
    if ($s === null || $s === '' || $s === '-')
        return null;
    $u = strtoupper($s);
    $map = [
        'FIRST' => 1,
        '1ST' => 1,
        '1' => 1,
        'SECOND' => 2,
        '2ND' => 2,
        '2' => 2,
        'THIRD' => 3,
        '3RD' => 3,
        '3' => 3,
        'FOURTH' => 4,
        '4TH' => 4,
        '4' => 4,
        'FIFTH' => 5,
        '5TH' => 5,
        '5' => 5,
        'SIXTH' => 6,
        '6TH' => 6,
        '6' => 6,
        'SEVENTH' => 7,
        '7TH' => 7,
        '7' => 7,
        'EIGHTH' => 8,
        '8TH' => 8,
        '8' => 8,
        'NINTH' => 9,
        '9TH' => 9,
        '9' => 9,
        'TENTH' => 10,
        '10TH' => 10,
        '10' => 10,
        'ELEVENTH' => 11,
        '11TH' => 11,
        '11' => 11,
        'TWELFTH' => 12,
        '12TH' => 12,
        '12' => 12
    ];
    $u2 = preg_replace('/\s*EDITION$/i', '', $u);
    $u2 = preg_replace('/[^A-Z0-9 ]/', '', $u2);
    $u2 = trim($u2);
    if (isset($map[$u2]))
        return $map[$u2];
    if (preg_match('/^(\d{1,2})\b/', $u2, $m))
        return (int) $m[1];
    return $raw;
}

function normalizeVolume($raw)
{
    $s = $raw === null ? null : trim((string) $raw);
    if ($s === null || $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)
{
    $a = valOrNull($authorName);
    if (!$a)
        return null;
    $st = $dbh->prepare("SELECT id FROM tblauthors WHERE AuthorName = :n LIMIT 1");
    $st->execute([':n' => $a]);
    $r = $st->fetch(PDO::FETCH_ASSOC);
    if ($r)
        return (int) $r['id'];

    $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);
    $ins = $dbh->prepare("INSERT INTO tblauthors (AuthorID, AuthorName, creationDate) VALUES (:aid, :name, :cd)");
    $ins->execute([':aid' => $newAid, ':name' => $a, ':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_no = :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_no) 
                          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();
}

// function accessionExists(PDO $dbh, $acc)
// {
//     if (!$acc) return false;
//     $st = $dbh->prepare("SELECT id FROM tblbookcopies WHERE accession_no = :a LIMIT 1");
//     $st->execute([':a' => $acc]);
//     return (bool)$st->fetch(PDO::FETCH_ASSOC);
// }

/* ---------- 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;

        $accNumber = valOrNull($row['A'] ?? null);
        $bookName = valOrNull($row['B'] ?? null);
        $author1Raw = $row['C'] ?? null;
        $author2Raw = $row['D'] ?? null;
        $editionNorm = normalizeEdition(valOrNull($row['E'] ?? null));
        $volumeNorm = normalizeVolume(valOrNull($row['F'] ?? null));
        $publisherName = valOrNull($row['G'] ?? null);
        $pubYear = toIntSafe(valOrNull($row['H'] ?? null));
        $price = toFloatSafe($row['I'] ?? null);
        $rack_no = valOrNull($row['J'] ?? null);
        $shelf_no = valOrNull($row['K'] ?? null);
        $floor_no = valOrNull($row['L'] ?? null);

        $rack_id = getOrCreateRackId($dbh, $rack_no, $shelf_no, $floor_no);


        // Split authors
        $authorNames = [];
        if ($author1Raw) {
            $splitAuthors = array_map('trim', explode(',', $author1Raw));
            foreach ($splitAuthors as $a)
                if ($a !== '')
                    $authorNames[] = $a;
        }
        if ($author2Raw)
            $authorNames[] = trim($author2Raw);

        $authorIdsArr = [];
        foreach ($authorNames as $aName) {
            $aid = ensureAuthor($dbh, $aName);
            if ($aid)
                $authorIdsArr[] = $aid;
        }
        $authorIdsCsv = !empty($authorIdsArr) ? implode(',', $authorIdsArr) : null;
        $publisherId = ensurePublisher($dbh, $publisherName);

        // Check duplicate book
        $dupId = null;
        $st = $dbh->prepare("SELECT id, AuthorIds, Edition, Volume, Publisher, PublicationYear 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);
            $dbAuthors = array_filter(explode(',', $r['AuthorIds'] ?? ''));
            sort($dbAuthors);
            $curAuthors = $authorIdsArr;
            sort($curAuthors);
            if ($sameEdition && $sameVolume && $samePublisher && $sameYear && ($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, Volume, Publisher, PublicationYear, BookPrice, total_count, current_count, Status, CatId)
            //                      VALUES (:BookName, :AuthorIds, :Edition, :Volume, :Publisher, :PublicationYear, :BookPrice, 0, 0, 1, 7)");
            $st = $dbh->prepare("INSERT INTO tblbooks (
    BookName, AuthorIds, Edition, Volume, Publisher, PublicationYear,
    BookPrice, total_count, current_count, Status, 
    department_id, CatId, degree_id
) VALUES (
    :BookName, :AuthorIds, :Edition, :Volume, :Publisher, :PublicationYear,
    :BookPrice, 0, 0, 1,
    :department, :category, :degree
)");

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

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

        // // Skip if accession already exists
        // if ($accNumber && accessionExists($dbh, $accNumber)) {
        //     $skipped[] = $bookName ?: 'Unknown';
        //     continue;
        // }

        // Insert book copy
        $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, 1, :qr)");

        $stc->execute([
            ':bid' => $bookId,
            ':acc' => $accNumber,
            ':shift' => $shift,
            ':rack_id' => $rack_id,
            ':bno' => $book_no,
            ':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
    header("Location: import-books.php");
    exit;

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