<?php

require_once 'Logger.php';
require_once 'Tracking.php';
require_once 'Notifications.php';

class Shipment {
    private $conn;
    private $logger;
    private $tracking;
    private $notifications;

    public function __construct($conn, $logger = null) {
        $this->conn = $conn;
        $this->logger = $logger;
        $this->tracking = new Tracking($conn, $logger);
        $this->notifications = new Notifications($conn, $logger);
    }

    /**
     * Create a new shipment from one branch to another
     * 
     * @param int $fromBranchId The source branch ID (can be null for headquarters)
     * @param int $toBranchId The destination branch ID
     * @param int $createdBy The user ID who created the shipment
     * @param array $items Array of item IDs to be shipped
     * @return int|bool The new shipment ID on success, false on failure
     */
    public function createShipment($fromBranchId, $toBranchId, $createdBy, $items) {
        if (empty($items) || !is_array($items)) {
            return false;
        }

        $this->conn->beginTransaction();

        try {
            // Create the shipment record
            $sql = "INSERT INTO shipments (from_branch_id, to_branch_id, created_by, status) 
                    VALUES (:from_branch_id, :to_branch_id, :created_by, 'pending')";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':from_branch_id', $fromBranchId);
            $stmt->bindParam(':to_branch_id', $toBranchId);
            $stmt->bindParam(':created_by', $createdBy);
            $stmt->execute();

            $shipmentId = $this->conn->lastInsertId();

            // Prepare statement for checking item's current location
            $checkItemSql = "SELECT current_location_id FROM items WHERE item_id = :item_id FOR UPDATE";
            $checkStmt = $this->conn->prepare($checkItemSql);

            // Prepare statement for updating item's current location
            $updateLocationSql = "UPDATE items SET current_location_id = NULL WHERE item_id = :item_id AND current_location_id = :current_location_id";
            $updateStmt = $this->conn->prepare($updateLocationSql);

            // Prepare statement for inserting shipment items
            $insertItemSql = "INSERT INTO shipment_items (shipment_id, item_id) 
                              VALUES (:shipment_id, :item_id)";
            $insertStmt = $this->conn->prepare($insertItemSql);

            foreach ($items as $item_id) {
                // Handle array structure from import_excel.php
                if (is_array($item_id)) {
                    $item_id = $item_id['item_id'];
                }
                
                if (!$item_id) {
                    throw new Exception("Invalid item ID");
                }

                

                // Check if item is in the source branch
                $checkStmt->bindParam(':item_id', $item_id);
                $checkStmt->execute();
                $currentLocationId = $checkStmt->fetchColumn();

                if ($currentLocationId != $fromBranchId) {
                    throw new Exception("Item ID " . $item_id . " is not in the source branch.");
                }

                // Update item's current location to NULL (in transit)
                $updateStmt->bindParam(':item_id', $item_id);
                $updateStmt->bindParam(':current_location_id', $fromBranchId);
                if (!$updateStmt->execute()) {
                    throw new Exception("Failed to update location for item ID " . $item_id);
                }

                // Add item to the shipment
                $insertStmt->bindParam(':shipment_id', $shipmentId);
                $insertStmt->bindParam(':item_id', $item_id);
                $insertStmt->execute();

                // Get unique_code for logging
                $uniqueCodeSql = "SELECT unique_code FROM items WHERE item_id = :item_id";
                $uniqueCodeStmt = $this->conn->prepare($uniqueCodeSql);
                $uniqueCodeStmt->bindParam(':item_id', $item_id);
                $uniqueCodeStmt->execute();
                $uniqueCode = $uniqueCodeStmt->fetchColumn();

                error_log("Shipment: Logging shipped event for item_id: $item_id, unique_code: $uniqueCode, shipment_id: $shipmentId");

                // Log item tracking event - shipped
                $result = $this->tracking->logEvent(
                    $item_id,
                    $uniqueCode,
                    'shipped',
                    $fromBranchId, // from_branch
                    $shipmentId,
                    $createdBy,
                    [
                        'to_branch_id' => $toBranchId,
                        'shipment_date' => date('Y-m-d H:i:s')
                    ]
                );

                error_log("Shipment: Tracking log result: " . ($result ? 'SUCCESS' : 'FAILED'));
            }

            $this->conn->commit();

            // Log shipment creation
            if ($this->logger) {
                $shipmentData = [
                    'from_branch_id' => $fromBranchId,
                    'to_branch_id' => $toBranchId,
                    'created_by' => $createdBy,
                    'status' => 'pending',
                    'items_count' => count($items)
                ];
                $this->logger->logShipment('CREATE', $shipmentId, $shipmentData);
            }

            $message = "Shipment ".$shipmentId. " has been received from warehouse please verify and accept.";
            $this->notifications->createNotification($toBranchId, $message, 'branch');

            $warehouseBranchId = 7;
            if ((int)$toBranchId === $warehouseBranchId && (int)$fromBranchId !== $warehouseBranchId) {
                $adminMessage = "Shipment #".$shipmentId." has been created from branch #".$fromBranchId." to warehouse and is pending review.";
                $this->notifications->createNotification($fromBranchId, $adminMessage, 'Admin');
            }

            return $shipmentId;
        } catch (Exception $e) {
            $this->conn->rollBack();
            // Log the error message for debugging
            $errorMessage = "Shipment creation failed: " . $e->getMessage();
            error_log($errorMessage);
            // Return the error message instead of just false for better debugging
            return $errorMessage; 
        }
    }

    /**
     * Update shipment status
     * 
     * @param int $shipmentId The shipment ID
     * @param string $status The new status ('pending', 'shipped', 'verified', 'completed', 'discrepancy', 'rejected')
     * @return bool True on success, false on failure
     */
    public function updateShipmentStatus($shipmentId, $status) {
        $validStatuses = ['pending', 'shipped', 'verified', 'completed', 'discrepancy', 'rejected'];
        if (!in_array($status, $validStatuses)) {
            return false;
        }

        // Get old status for logging
        $oldData = null;
        if ($this->logger) {
            $shipment = $this->getShipmentById($shipmentId);
            if ($shipment) {
                $oldData = ['status' => $shipment['status']];
            }
        }

        $sql = "UPDATE shipments SET status = :status WHERE shipment_id = :shipment_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':status', $status);
        $stmt->bindParam(':shipment_id', $shipmentId);
        $result = $stmt->execute();

        // Log status update
        if ($result && $this->logger && $oldData) {
            $newData = ['status' => $status];
            $this->logger->logShipment('UPDATE_STATUS', $shipmentId, $newData, $oldData);
        }

        return $result;
    }

    /**
     * Delete a pending shipment and return its items to the origin branch.
     *
     * @param int $shipmentId The shipment ID to delete
     * @param int $deletedBy User ID performing the deletion
     * @return bool|string True on success, error message on failure
     */
    public function deletePendingShipment($shipmentId, $deletedBy) {
        $this->conn->beginTransaction();

        try {
            $shipmentSql = "SELECT shipment_id, from_branch_id, to_branch_id, status
                            FROM shipments
                            WHERE shipment_id = :shipment_id
                            FOR UPDATE";
            $shipmentStmt = $this->conn->prepare($shipmentSql);
            $shipmentStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $shipmentStmt->execute();
            $shipment = $shipmentStmt->fetch(PDO::FETCH_ASSOC);

            if (!$shipment) {
                throw new Exception('Shipment not found.');
            }

            if ($shipment['status'] !== 'pending') {
                throw new Exception('Only pending shipments can be deleted.');
            }

            if (empty($shipment['from_branch_id'])) {
                throw new Exception('Shipment origin branch is missing.');
            }

            $itemsSql = "SELECT item_id FROM shipment_items WHERE shipment_id = :shipment_id";
            $itemsStmt = $this->conn->prepare($itemsSql);
            $itemsStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $itemsStmt->execute();
            $itemIds = $itemsStmt->fetchAll(PDO::FETCH_COLUMN);

            if (!empty($itemIds)) {
                $restoreSql = "UPDATE items
                               SET current_location_id = :from_branch_id
                               WHERE item_id = :item_id";
                $restoreStmt = $this->conn->prepare($restoreSql);

                foreach ($itemIds as $itemId) {
                    $restoreStmt->bindValue(':from_branch_id', $shipment['from_branch_id'], PDO::PARAM_INT);
                    $restoreStmt->bindValue(':item_id', $itemId, PDO::PARAM_INT);
                    $restoreStmt->execute();
                }
            }

            $trackingSql = "UPDATE item_tracking
                            SET shipment_id = NULL
                            WHERE shipment_id = :shipment_id";
            $trackingStmt = $this->conn->prepare($trackingSql);
            $trackingStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $trackingStmt->execute();

            $deleteItemsSql = "DELETE FROM shipment_items WHERE shipment_id = :shipment_id";
            $deleteItemsStmt = $this->conn->prepare($deleteItemsSql);
            $deleteItemsStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $deleteItemsStmt->execute();

            $deleteShipmentSql = "DELETE FROM shipments WHERE shipment_id = :shipment_id";
            $deleteShipmentStmt = $this->conn->prepare($deleteShipmentSql);
            $deleteShipmentStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $deleteShipmentStmt->execute();

            $this->conn->commit();

            if ($this->logger) {
                $this->logger->logShipment('DELETE_PENDING', $shipmentId, [
                    'from_branch_id' => $shipment['from_branch_id'],
                    'to_branch_id' => $shipment['to_branch_id'],
                    'deleted_by' => $deletedBy,
                    'items_returned' => count($itemIds)
                ]);
            }

            return true;
        } catch (Exception $e) {
            $this->conn->rollBack();
            error_log("Pending shipment deletion failed: " . $e->getMessage());
            return $e->getMessage();
        }
    }

    /**
     * Get shipments for a branch (either as source or destination)
     * 
     * @param int $branch_id The branch ID
     * @param string $type 'incoming', 'outgoing', or 'all'
     * @param string|null $start_date Start date filter (YYYY-MM-DD)
     * @param string|null $end_date End date filter (YYYY-MM-DD)
     * @return array The shipments
     */
    public function getBranchShipments($branch_id, $type = 'all', $start_date = null, $end_date = null) {
        try {
            $query = "SELECT s.*, 
                             b_from.branch_name AS from_branch_name, 
                             b_to.branch_name AS to_branch_name, 
                             u.username AS created_by_name
                      FROM shipments s
                      LEFT JOIN branches b_from ON s.from_branch_id = b_from.branch_id
                      LEFT JOIN branches b_to ON s.to_branch_id = b_to.branch_id
                      LEFT JOIN users u ON s.created_by = u.user_id
                      WHERE s.to_branch_id = :branch_id OR s.from_branch_id = :branch_id";
    
            // Add type filter
            if ($type === 'incoming') {
                $query .= " AND s.to_branch_id = :branch_id";
            } elseif ($type === 'outgoing') {
                $query .= " AND s.from_branch_id = :branch_id";
            }
    
            // Add date filters if provided
            if (!empty($start_date)) {
                $query .= " AND DATE(s.shipment_date) >= :start_date";
            }
            if (!empty($end_date)) {
                $query .= " AND DATE(s.shipment_date) <= :end_date";
            }
    
            $query .= " ORDER BY s.shipment_date DESC";
    
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':branch_id', $branch_id, PDO::PARAM_INT);
    
            // Bind date parameters if provided
            if (!empty($start_date)) {
                $stmt->bindParam(':start_date', $start_date);
            }
            if (!empty($end_date)) {
                $stmt->bindParam(':end_date', $end_date);
            }
    
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error fetching branch shipments: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get shipment by ID
     *
     * @param int $shipmentId The shipment ID
     * @return array|null The shipment details or null if not found
     */
    public function getShipmentById($shipmentId) {
        try {
            $sql = "SELECT s.*,
                           fb.branch_name as from_branch_name,
                           tb.branch_name as to_branch_name,
                           u.username as created_by_name
                    FROM shipments s
                    LEFT JOIN branches fb ON s.from_branch_id = fb.branch_id
                    JOIN branches tb ON s.to_branch_id = tb.branch_id
                    JOIN users u ON s.created_by = u.user_id
                    WHERE s.shipment_id = :shipment_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $stmt->execute();
            $shipment = $stmt->fetch(PDO::FETCH_ASSOC);
            return $shipment ?: null;
        } catch (Exception $e) {
            error_log("Error fetching shipment by ID {$shipmentId}: " . $e->getMessage());
            return null;
        }
    }

    /**
     * Get shipment details with items in flat structure
     *
     * @param int $shipment_id The shipment ID
     * @return array|false Array of shipment-item details or false on failure
     */
    public function getShipmentDetailsFlat($shipment_id) {
        $sql = "SELECT
                    s.shipment_id, s.from_branch_id, s.to_branch_id, s.status, s.created_at,
                    ob.branch_name AS from_branch_name,
                    db.branch_name AS to_branch_name,
                    u.username AS created_by_username,
                    si.quantity,
                    i.item_id, i.item_name, i.item_details, i.unique_code, i.unit_price,
                    i.condition_status, i.serial_number, i.warranty_months, i.technical_specs,
                    i.brand, i.model, i.description
                FROM shipments s
                JOIN branches ob ON s.from_branch_id = ob.branch_id
                JOIN branches db ON s.to_branch_id = db.branch_id
                JOIN users u ON s.created_by = u.user_id
                JOIN shipment_items si ON s.shipment_id = si.shipment_id
                JOIN items i ON si.item_id = i.item_id
                WHERE s.shipment_id = ?";

        try {
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(1, $shipment_id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return $result ?: false;
        } catch (Exception $e) {
            error_log("Error getting shipment details: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get items for a specific shipment
     * 
     * @param int $shipmentId The shipment ID
     * @return array Array of shipment items
     */
    public function getShipmentItems($shipmentId) {
        try {
            $sql = "SELECT si.*, i.item_name, i.item_details, i.unique_code, i.unit_price, i.condition_status,
                           i.serial_number, i.warranty_months, i.technical_specs,
                           u.username as verified_by_name
                    FROM shipment_items si
                    JOIN items i ON si.item_id = i.item_id
                    LEFT JOIN users u ON si.verified_by = u.user_id
                    WHERE si.shipment_id = :shipment_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error fetching shipment items for shipment ID {$shipmentId}: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get shipment details
     * 
     * @param int $shipmentId The shipment ID
     * @return array|null The shipment details including items, or null if not found
     */
    public function getShipmentDetails($shipmentId) {
        // Get shipment info
        $sql = "SELECT s.*,
                    fb.branch_name as from_branch_name,
                    tb.branch_name as to_branch_name,
                    u.username as created_by_name,
                    au.username as approved_by_name
                FROM shipments s
                LEFT JOIN branches fb ON s.from_branch_id = fb.branch_id
                JOIN branches tb ON s.to_branch_id = tb.branch_id
                JOIN users u ON s.created_by = u.user_id
                LEFT JOIN users au ON s.approved_by = au.user_id
                WHERE s.shipment_id = :shipment_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':shipment_id', $shipmentId);
        $stmt->execute();
        $shipment = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$shipment) {
            return null;
        }

        // Get shipment items
        $sql = "SELECT si.*, i.item_name, i.item_details, i.unique_code, i.unit_price, i.condition_status, i.serial_number, i.warranty_months, i.technical_specs, i.brand, i.model, i.description,
                    u.username as verified_by_name
                FROM shipment_items si
                JOIN items i ON si.item_id = i.item_id
                LEFT JOIN users u ON si.verified_by = u.user_id
                WHERE si.shipment_id = :shipment_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':shipment_id', $shipmentId);
        $stmt->execute();
        $shipment['items'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $shipment;
    }

    /**
     * Verify a shipment item (or report discrepancy)
     *
     * @param int $shipmentItemId The shipment item ID
     * @param int $verifiedBy The user ID who verified the item
     * @param string $notes Verification notes (optional)
     * @param bool $reportDiscrepancy Whether a discrepancy is being reported
     * @param string $discrepancyNotes Details of the discrepancy (if any)
     * @return bool True on success (throws Exception on failure)
     * @throws Exception If any database operation fails
     */
    public function verifyShipmentItem($shipmentItemId, $verifiedBy, $notes, $reportDiscrepancy, $discrepancyNotes) {
        try {
            // 1. Item-Level Update: Update the specific shipment_item row
            $isVerified = $reportDiscrepancy ? 0 : 1;
            $sql = "UPDATE shipment_items
                    SET is_verified = :is_verified,
                        verification_date = NOW(),
                        verified_by = :verified_by,
                        verification_notes = :notes,
                        discrepancy_reported = :discrepancy_reported,
                        discrepancy_notes = :discrepancy_notes
                    WHERE shipment_item_id = :shipment_item_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':is_verified', $isVerified, PDO::PARAM_INT);
            $stmt->bindParam(':verified_by', $verifiedBy, PDO::PARAM_INT);
            $stmt->bindParam(':notes', $notes);
            $stmt->bindParam(':discrepancy_reported', $reportDiscrepancy, PDO::PARAM_BOOL);
            $stmt->bindParam(':discrepancy_notes', $discrepancyNotes);
            $stmt->bindParam(':shipment_item_id', $shipmentItemId, PDO::PARAM_INT);

            if (!$stmt->execute()) {
                 $errorInfo = $stmt->errorInfo();
                 throw new Exception("Failed to update shipment_items verification details for ID " . $shipmentItemId . ": [" . $errorInfo[0] . "] " . $errorInfo[2]);
            }

            // Get shipment, item, and destination branch details
            $sql = "SELECT si.item_id, si.shipment_id, s.from_branch_id, s.to_branch_id,
                           i.item_name, i.item_details, i.unique_code
                    FROM shipment_items si
                    JOIN shipments s ON si.shipment_id = s.shipment_id
                    JOIN items i ON si.item_id = i.item_id
                    WHERE si.shipment_item_id = :shipment_item_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':shipment_item_id', $shipmentItemId, PDO::PARAM_INT);

            if (!$stmt->execute()) {
                 $errorInfo = $stmt->errorInfo();
                 throw new Exception("Failed to fetch item/branch/shipment details for shipment item ID " . $shipmentItemId . ": [" . $errorInfo[0] . "] " . $errorInfo[2]);
            }
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$result) {
                throw new Exception("Shipment item " . $shipmentItemId . " details not found after update.");
            }

            $itemId = $result['item_id'];
            $shipmentId = $result['shipment_id'];
            $fromBranchId = $result['from_branch_id'];
            $toBranchId = $result['to_branch_id'];

            // If item is verified AND no discrepancy, update its location
            if (!$reportDiscrepancy) {
                $sql = "UPDATE items SET current_location_id = :branch_id WHERE item_id = :item_id";

                // EIS sequence values restart per branch. Always allocate a destination
                // sequence for an in-transit EIS item, then retry if an EIS sync inserts
                // the same sequence between allocation and the location update.
                $locationUpdated = false;
                $stmt = null;
                for ($attempt = 1; $attempt <= 3; $attempt++) {
                    $this->rebaseEisSequenceForDestination($itemId, $toBranchId);
                    $stmt = $this->conn->prepare($sql);
                    $stmt->bindValue(':branch_id', $toBranchId, PDO::PARAM_INT);
                    $stmt->bindValue(':item_id', $itemId, PDO::PARAM_INT);

                    try {
                        $locationUpdated = $stmt->execute();
                        break;
                    } catch (PDOException $e) {
                        $isEisSequenceCollision = $e->getCode() === '23000'
                            && strpos($e->getMessage(), 'uniq_items_eis_warehouse_branch_sequence') !== false;
                        if (!$isEisSequenceCollision || $attempt === 3) {
                            throw $e;
                        }
                    }
                }

                if (!$locationUpdated) {
                    $errorInfo = $stmt ? $stmt->errorInfo() : [null, null, 'Unknown database error'];
                    throw new Exception("Failed to update item location for item ID " . $itemId . ": [" . $errorInfo[0] . "] " . $errorInfo[2]);
                }

                // Get unique_code for logging
                $uniqueCodeSql = "SELECT unique_code FROM items WHERE item_id = :item_id";
                $uniqueCodeStmt = $this->conn->prepare($uniqueCodeSql);
                $uniqueCodeStmt->bindParam(':item_id', $itemId);
                $uniqueCodeStmt->execute();
                $uniqueCode = $uniqueCodeStmt->fetchColumn();

                error_log("Shipment: Logging received event for item_id: $itemId, unique_code: $uniqueCode, shipment_id: $shipmentId");

                // Log item tracking event - received
                $result = $this->tracking->logEvent(
                    $itemId,
                    $uniqueCode,
                    'received',
                    $toBranchId,
                    $shipmentId,
                    $verifiedBy,
                    [
                        'verification_date' => date('Y-m-d H:i:s'),
                        'verified_by' => $verifiedBy
                    ]
                );
                error_log("Shipment: Received tracking log result: " . ($result ? 'SUCCESS' : 'FAILED'));
            } else {
                $comment = trim((string)$discrepancyNotes);
                $itemLabel = trim(($result['item_name'] ?? '') . ' (' . ($result['unique_code'] ?? 'No code') . ')');
                $message = "Shipment #".$shipmentId." has an item error for ".$itemLabel.". Recipient comment: ".$comment;
                $this->notifyShipmentSender($fromBranchId, $toBranchId, $message);
            }

            // 2. Shipment-Level Update
            $this->_updateOverallShipmentStatus($shipmentId);

            // Don't log individual item verifications - we'll log a summary when shipment is completed
            // This prevents cluttering the logs with individual item entries

            return true;
			
			
			
			
			
			
        } catch (Exception $e) {
            error_log("Error in verifyShipmentItem (Shipment Item ID: {$shipmentItemId}): " . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Allocate an EIS-synced item a fresh sequence number at its destination branch.
     *
     * The locking read covers the destination's sequence range for this stock row,
     * preventing two shipment confirmations from selecting the same replacement.
     * Non-EIS items and items already at the destination are left unchanged.
     */
    private function rebaseEisSequenceForDestination($itemId, $toBranchId) {
        $itemSql = "SELECT current_location_id, source_eis_warehouse_stock_id, source_eis_sequence_number
                    FROM items
                    WHERE item_id = :item_id
                    FOR UPDATE";
        $itemStmt = $this->conn->prepare($itemSql);
        $itemStmt->bindValue(':item_id', $itemId, PDO::PARAM_INT);
        $itemStmt->execute();
        $item = $itemStmt->fetch(PDO::FETCH_ASSOC);

        if (!$item
            || $item['source_eis_warehouse_stock_id'] === null
            || $item['source_eis_sequence_number'] === null
            || (int)$item['current_location_id'] === (int)$toBranchId) {
            return;
        }

        $stockId = (int)$item['source_eis_warehouse_stock_id'];

        $sequencesSql = "SELECT source_eis_sequence_number
                         FROM items
                         WHERE source_eis_warehouse_stock_id = :stock_id
                           AND current_location_id = :branch_id
                           AND source_eis_sequence_number IS NOT NULL
                         ORDER BY source_eis_sequence_number
                         FOR UPDATE";
        $sequencesStmt = $this->conn->prepare($sequencesSql);
        $sequencesStmt->bindValue(':stock_id', $stockId, PDO::PARAM_INT);
        $sequencesStmt->bindValue(':branch_id', $toBranchId, PDO::PARAM_INT);
        $sequencesStmt->execute();
        $destinationSequences = array_map('intval', $sequencesStmt->fetchAll(PDO::FETCH_COLUMN));

        $nextSequence = $destinationSequences ? max($destinationSequences) + 1 : 1;
        $updateSql = "UPDATE items
                      SET source_eis_sequence_number = :sequence_number
                      WHERE item_id = :item_id";
        $updateStmt = $this->conn->prepare($updateSql);
        $updateStmt->bindValue(':sequence_number', $nextSequence, PDO::PARAM_INT);
        $updateStmt->bindValue(':item_id', $itemId, PDO::PARAM_INT);
        $updateStmt->execute();
    }

    /**
     * Reject a shipment from the receiving side and return every item to the sender.
     *
     * @param int $shipmentId The shipment ID
     * @param int $rejectedBy User ID rejecting the shipment
     * @param string $notes Rejection comment
     * @return bool True on success
     * @throws Exception If the shipment cannot be rejected
     */
    public function rejectShipmentAtReceiving($shipmentId, $rejectedBy, $notes) {
        $notes = trim((string)$notes);
        if ($notes === '') {
            throw new Exception('A rejection comment is required.');
        }

        $shipmentSql = "SELECT shipment_id, from_branch_id, to_branch_id, status
                        FROM shipments
                        WHERE shipment_id = :shipment_id
                        FOR UPDATE";
        $shipmentStmt = $this->conn->prepare($shipmentSql);
        $shipmentStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
        $shipmentStmt->execute();
        $shipment = $shipmentStmt->fetch(PDO::FETCH_ASSOC);

        if (!$shipment) {
            throw new Exception('Shipment not found.');
        }

        if ($shipment['status'] === 'completed') {
            throw new Exception('Completed shipments cannot be rejected.');
        }

        if ($shipment['status'] === 'rejected') {
            throw new Exception('This shipment has already been rejected.');
        }

        if (empty($shipment['from_branch_id'])) {
            throw new Exception('Shipment origin branch is missing.');
        }

        $itemsSql = "SELECT item_id FROM shipment_items WHERE shipment_id = :shipment_id";
        $itemsStmt = $this->conn->prepare($itemsSql);
        $itemsStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
        $itemsStmt->execute();
        $itemIds = $itemsStmt->fetchAll(PDO::FETCH_COLUMN);

        $returnSql = "UPDATE items SET current_location_id = :from_branch_id WHERE item_id = :item_id";
        $returnStmt = $this->conn->prepare($returnSql);
        foreach ($itemIds as $itemId) {
            $returnStmt->bindValue(':from_branch_id', $shipment['from_branch_id'], PDO::PARAM_INT);
            $returnStmt->bindValue(':item_id', $itemId, PDO::PARAM_INT);
            $returnStmt->execute();
        }

        $updateItemsSql = "UPDATE shipment_items
                           SET is_verified = 0,
                               verification_date = NOW(),
                               verified_by = :verified_by,
                               verification_notes = :notes,
                               discrepancy_reported = 1,
                               discrepancy_notes = :notes
                           WHERE shipment_id = :shipment_id";
        $updateItemsStmt = $this->conn->prepare($updateItemsSql);
        $updateItemsStmt->bindValue(':verified_by', $rejectedBy, PDO::PARAM_INT);
        $updateItemsStmt->bindValue(':notes', $notes);
        $updateItemsStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
        $updateItemsStmt->execute();

        $updateShipmentSql = "UPDATE shipments
                              SET status = 'rejected',
                                  verified_by = :verified_by,
                                  verification_notes = :notes,
                                  rejection_notes = :notes
                              WHERE shipment_id = :shipment_id";
        $updateShipmentStmt = $this->conn->prepare($updateShipmentSql);
        $updateShipmentStmt->bindValue(':verified_by', $rejectedBy, PDO::PARAM_INT);
        $updateShipmentStmt->bindValue(':notes', $notes);
        $updateShipmentStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
        $updateShipmentStmt->execute();

        $this->logShipmentAction($shipmentId, 'rejected', $rejectedBy, $notes);

        if ($this->logger) {
            $this->logger->logShipment('REJECT', $shipmentId, [
                'rejected_by' => $rejectedBy,
                'items_returned' => count($itemIds),
                'notes' => $notes
            ]);
        }

        $message = "Shipment #".$shipmentId." was rejected by the receiving branch. Comment: ".$notes;
        $this->notifyShipmentSender($shipment['from_branch_id'], $shipment['to_branch_id'], $message);

        return true;
    }

    /**
     * Updates the overall status of a shipment based on its items.
     *
     * @param int $shipmentId The ID of the shipment to update.
     * @throws Exception If database query fails.
     */
    private function _updateOverallShipmentStatus($shipmentId) {
        // Get counts of total items, verified items, and items with discrepancies
        $sql = "SELECT
                    COUNT(*) as total_items,
                    SUM(CASE WHEN is_verified = 1 THEN 1 ELSE 0 END) as verified_items,
                    SUM(CASE WHEN discrepancy_reported = 1 THEN 1 ELSE 0 END) as discrepancy_items
                FROM shipment_items
                WHERE shipment_id = :shipment_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':shipment_id', $shipmentId, PDO::PARAM_INT);

        if (!$stmt->execute()) {
            $errorInfo = $stmt->errorInfo();
            throw new Exception("Failed to count shipment items for status update (Shipment ID: {$shipmentId}): [" . $errorInfo[0] . "] " . $errorInfo[2]);
        }
        $counts = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$counts || $counts['total_items'] == 0) {
            return;
        }

        $shipmentSql = "SELECT status, from_branch_id, to_branch_id FROM shipments WHERE shipment_id = :shipment_id";
        $shipmentStmt = $this->conn->prepare($shipmentSql);
        $shipmentStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
        if (!$shipmentStmt->execute()) {
            $errorInfo = $shipmentStmt->errorInfo();
            throw new Exception("Failed to load shipment for status update (Shipment ID: {$shipmentId}): [" . $errorInfo[0] . "] " . $errorInfo[2]);
        }
        $shipment = $shipmentStmt->fetch(PDO::FETCH_ASSOC);
        if (!$shipment) {
            return;
        }

        $newStatus = null;

        // Determine the new overall status
        if ($counts['discrepancy_items'] > 0) {
            $newStatus = 'discrepancy';
        } elseif ($counts['verified_items'] == $counts['total_items']) {
            $newStatus = 'completed';
        } elseif ($counts['verified_items'] > 0) {
             $newStatus = 'verified';
        }

        // Only update if a new status is determined
        if ($newStatus !== null) {
            $updateSql = "UPDATE shipments SET status = :status WHERE shipment_id = :shipment_id AND status <> :status";
            $updateStmt = $this->conn->prepare($updateSql);
            $updateStmt->bindValue(':status', $newStatus);
            $updateStmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);

            if (!$updateStmt->execute()) {
                $errorInfo = $updateStmt->errorInfo();
                error_log("Failed to update overall shipment status for Shipment ID {$shipmentId} to '{$newStatus}': [" . $errorInfo[0] . "] " . $errorInfo[2]);
            } elseif ($updateStmt->rowCount() > 0) {
                if ($newStatus === 'completed' && $shipment['status'] !== 'completed') {
                    $message = "Shipment ".$shipmentId. " has been verified and confirmed.";
                    $this->notifyShipmentSender($shipment['from_branch_id'], $shipment['to_branch_id'], $message);
                } elseif ($newStatus === 'discrepancy' && $shipment['status'] !== 'discrepancy') {
                    $message = "Shipment #".$shipmentId." is paused because the receiving branch reported item errors.";
                    $this->notifyShipmentSender($shipment['from_branch_id'], $shipment['to_branch_id'], $message);
                }

                // Log the shipment completion
                if ($this->logger && $newStatus === 'completed') {
                    $shipmentData = [
                        'items_count' => $counts['total_items'],
                        'verified_items' => $counts['verified_items'],
                        'discrepancy_items' => $counts['discrepancy_items']
                    ];
                    $this->logger->logShipment('COMPLETE', $shipmentId, $shipmentData);
                }
            }
        }
    }

    private function notifyShipmentSender($fromBranchId, $toBranchId, $message) {
        if (!empty($fromBranchId)) {
            $this->notifications->createNotification((int)$fromBranchId, $message, 'branch', 0, false);
            return;
        }

        $this->notifications->createNotification((int)$toBranchId, $message, 'Admin', 0, false);
    }

    private function logShipmentAction($shipmentId, $action, $performedBy, $notes) {
        try {
            $sql = "INSERT INTO shipment_logs (shipment_id, action, performed_by, notes, action_date)
                    VALUES (:shipment_id, :action, :performed_by, :notes, NOW())";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue(':shipment_id', $shipmentId, PDO::PARAM_INT);
            $stmt->bindValue(':action', $action);
            $stmt->bindValue(':performed_by', $performedBy, PDO::PARAM_INT);
            $stmt->bindValue(':notes', $notes);
            $stmt->execute();
        } catch (Exception $e) {
            error_log("Shipment log insert failed for shipment {$shipmentId}: " . $e->getMessage());
        }
    }
    
    /**
 * Get shipments by their status.
 *
 * @param string $status The status to filter by (e.g., 'pending', 'completed').
 * @return array The shipments matching the status.
 */
public function getShipmentsByStatus($status) {
    try {
        $sql = "SELECT s.*,
                       b_from.branch_name AS from_branch_name,
                       b_to.branch_name AS to_branch_name,
                       u.username AS created_by_name
                FROM shipments s
                LEFT JOIN branches b_from ON s.from_branch_id = b_from.branch_id
                JOIN branches b_to ON s.to_branch_id = b_to.branch_id
                LEFT JOIN users u ON s.created_by = u.user_id
                WHERE s.status = :status
                ORDER BY s.shipment_date DESC";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':status', $status);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        error_log("Error fetching shipments by status {$status}: " . $e->getMessage());
        return [];
    }
}

    /**
     * Get warehouse shipment statistics
     *
     * @param string $startDate Start date for filtering
     * @param string $endDate End date for filtering
     * @return array Warehouse shipment statistics
     */
    public function getWarehouseShipmentStats($startDate = null, $endDate = null) {
        try {
            $warehouseBranchId = 7; // Warehouse branch ID

            // Build date filter
            $dateFilter = "";
            $params = [];
            if ($startDate && $endDate) {
                $dateFilter = " AND s.shipment_date BETWEEN :start_date AND :end_date";
                $params[':start_date'] = $startDate;
                $params[':end_date'] = $endDate;
            }

            $sql = "SELECT
                        COUNT(*) as total_shipments,
                        SUM(CASE WHEN s.status = 'pending' THEN 1 ELSE 0 END) as pending_shipments,
                        SUM(CASE WHEN s.status = 'completed' THEN 1 ELSE 0 END) as completed_shipments,
                        SUM(CASE WHEN s.status = 'verified' THEN 1 ELSE 0 END) as verified_shipments,
                        SUM(CASE WHEN s.status = 'discrepancy' THEN 1 ELSE 0 END) as discrepancy_shipments,
                        COUNT(DISTINCT si.item_id) as total_items_shipped,
                        AVG(item_counts.item_count) as avg_items_per_shipment
                    FROM shipments s
                    LEFT JOIN (
                        SELECT shipment_id, COUNT(*) as item_count
                        FROM shipment_items
                        GROUP BY shipment_id
                    ) item_counts ON s.shipment_id = item_counts.shipment_id
                    WHERE s.from_branch_id = :warehouse_id" . $dateFilter;

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':warehouse_id', $warehouseBranchId, PDO::PARAM_INT);

            // Bind date parameters
            foreach ($params as $key => $value) {
                $stmt->bindParam($key, $value);
            }

            $stmt->execute();
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);

            // Get monthly shipment trends
            $monthlySql = "SELECT
                                DATE_FORMAT(s.shipment_date, '%Y-%m') as month,
                                COUNT(*) as shipment_count,
                                COUNT(DISTINCT si.item_id) as items_shipped
                            FROM shipments s
                            LEFT JOIN shipment_items si ON s.shipment_id = si.shipment_id
                            WHERE s.from_branch_id = :warehouse_id" . $dateFilter . "
                            GROUP BY DATE_FORMAT(s.shipment_date, '%Y-%m')
                            ORDER BY month";

            $monthlyStmt = $this->conn->prepare($monthlySql);
            $monthlyStmt->bindParam(':warehouse_id', $warehouseBranchId, PDO::PARAM_INT);

            foreach ($params as $key => $value) {
                $monthlyStmt->bindParam($key, $value);
            }

            $monthlyStmt->execute();
            $monthlyTrends = $monthlyStmt->fetchAll(PDO::FETCH_ASSOC);

            return [
                'stats' => $stats,
                'monthly_trends' => $monthlyTrends
            ];
        } catch (Exception $e) {
            error_log("Error getting warehouse shipment stats: " . $e->getMessage());
            return [
                'stats' => [
                    'total_shipments' => 0,
                    'pending_shipments' => 0,
                    'completed_shipments' => 0,
                    'verified_shipments' => 0,
                    'discrepancy_shipments' => 0,
                    'total_items_shipped' => 0,
                    'avg_items_per_shipment' => 0
                ],
                'monthly_trends' => []
            ];
        }
    }

    /**
     * Get shipments from warehouse to branches
     *
     * @param string $startDate Start date filter
     * @param string $endDate End date filter
     * @return array Shipments from warehouse
     */
    public function getWarehouseShipments($startDate = null, $endDate = null) {
        try {
            $warehouseBranchId = 7;

            $sql = "SELECT s.*,
                           b_to.branch_name AS to_branch_name,
                           u.username AS created_by_name,
                           item_counts.item_count as total_items
                    FROM shipments s
                    JOIN branches b_to ON s.to_branch_id = b_to.branch_id
                    LEFT JOIN users u ON s.created_by = u.user_id
                    LEFT JOIN (
                        SELECT shipment_id, COUNT(*) as item_count
                        FROM shipment_items
                        GROUP BY shipment_id
                    ) item_counts ON s.shipment_id = item_counts.shipment_id
                    WHERE s.from_branch_id = :warehouse_id";

            $params = [':warehouse_id' => $warehouseBranchId];

            if ($startDate && $endDate) {
                $sql .= " AND s.shipment_date BETWEEN :start_date AND :end_date";
                $params[':start_date'] = $startDate;
                $params[':end_date'] = $endDate;
            }

            $sql .= " ORDER BY s.shipment_date DESC LIMIT 50";

            $stmt = $this->conn->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting warehouse shipments: " . $e->getMessage());
            return [];
        }
    }
    
}
?>
