<?php
/**
 * Store Keeper API
 * Handles all store keeper operations: stock receiving, inventory updates, expiry/wastage tracking
 */

// Disable error display to prevent HTML in JSON response
ini_set('display_errors', 0);
error_reporting(E_ALL);

// Initialize session BEFORE setting headers
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

header('Content-Type: application/json');

require_once 'config.php';

// Check authentication
$is_authenticated = !empty($_SESSION['store_keeper_id']);
if (!$is_authenticated) {
    http_response_code(401);
    sendResponse(false, 'Unauthorized - Please login first', null);
}

$store_keeper_id = $_SESSION['store_keeper_id'];
$action = isset($_GET['action']) ? $_GET['action'] : (isset($_POST['action']) ? $_POST['action'] : null);
$method = $_SERVER['REQUEST_METHOD'];

try {
    // Create tables if not exists (suppress any warnings)
    @createStorekeeperTables();

    switch ($action) {
        // METRICS & DASHBOARD
        case 'get_metrics':
            getMetrics();
            break;

        case 'get_wastage_metrics':
            getWastageMetrics();
            break;

        case 'fix_old_wastage_records':
            fixOldWastageRecords();
            break;

        case 'check_wastage_records':
            checkWastageRecords();
            break;

        case 'directly_update_wastage':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            directlyUpdateWastage();
            break;

        case 'fix_invalid_dates':
            fixInvalidWastageDates();
            break;

        // STOCK RECEIVING
        case 'add_stock_receiving':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            addStockReceiving();
            break;

        case 'get_stock_receiving':
            getStockReceiving();
            break;

        case 'get_receipt':
            getReceipt();
            break;

        case 'get_pending_shipments':
            getPendingShipments();
            break;
        
        case 'get_po_for_receiving':
            getPoForReceiving();
            break;
        
        case 'close_remaining_shipment':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            closeRemainingShipment();
            break;

        // INVENTORY
        case 'get_inventory':
            getInventory();
            break;

        case 'get_low_stock':
            getLowStock();
            break;

        case 'adjust_inventory':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            adjustInventory();
            break;

        case 'get_recent_activities':
            getRecentActivities();
            break;

        // EXPIRY & WASTAGE
        case 'get_expiry_tracking':
            getExpiryTracking();
            break;

        case 'get_expiring_soon':
            getExpiringSoon();
            break;

        case 'set_ingredient_expiry_date':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            setIngredientExpiryDate();
            break;

        case 'log_wastage':
            if ($method !== 'POST') throw new Exception('Invalid request method');
            logWastage();
            break;

        case 'get_wastage_records':
            getWastageRecords();
            break;

        default:
            throw new Exception('Invalid action: ' . $action);
    }

} catch (Throwable $e) {
    http_response_code(400);
    sendResponse(false, $e->getMessage(), null);
}

/**
 * Create necessary tables
 */
function hasColumn($table, $column) {
    global $conn;

    $tableEsc = $conn->real_escape_string($table);
    $columnEsc = $conn->real_escape_string($column);
    $result = $conn->query("SHOW COLUMNS FROM `$tableEsc` LIKE '$columnEsc'");
    return $result && $result->num_rows > 0;
}

function hasIndex($table, $indexName) {
    global $conn;

    $tableEsc = $conn->real_escape_string($table);
    $indexEsc = $conn->real_escape_string($indexName);
    $result = $conn->query("SHOW INDEX FROM `$tableEsc` WHERE Key_name = '$indexEsc'");
    return $result && $result->num_rows > 0;
}

function createStorekeeperTables() {
    global $conn;

    // Enhance ingredients table with perishable tracking
    if (!hasColumn('ingredients', 'status')) {
        $conn->query("ALTER TABLE ingredients ADD COLUMN status VARCHAR(50) DEFAULT 'active'");
    }
    if (!hasColumn('ingredients', 'is_perishable')) {
        $conn->query("ALTER TABLE ingredients ADD COLUMN is_perishable TINYINT(1) DEFAULT 0");
    }
    if (!hasColumn('ingredients', 'expiry_date')) {
        $conn->query("ALTER TABLE ingredients ADD COLUMN expiry_date DATE DEFAULT NULL");
    }
    if (!hasColumn('ingredients', 'last_received_date')) {
        $conn->query("ALTER TABLE ingredients ADD COLUMN last_received_date DATE DEFAULT NULL");
    }
    if (!hasIndex('ingredients', 'idx_status')) {
        $conn->query("ALTER TABLE ingredients ADD INDEX idx_status (status)");
    }
    if (!hasIndex('ingredients', 'idx_perishable')) {
        $conn->query("ALTER TABLE ingredients ADD INDEX idx_perishable (is_perishable)");
    }
    if (!hasIndex('ingredients', 'idx_expiry')) {
        $conn->query("ALTER TABLE ingredients ADD INDEX idx_expiry (expiry_date)");
    }
    @$conn->query("UPDATE ingredients SET status = 'active' WHERE status IS NULL OR TRIM(status) = ''");

    // stock_receiving
    $sql = "CREATE TABLE IF NOT EXISTS stock_receiving (
        id INT PRIMARY KEY AUTO_INCREMENT,
        store_keeper_id INT,
        ingredient_id INT,
        ingredient_name VARCHAR(255),
        quantity_received DECIMAL(10, 3),
        unit VARCHAR(50),
        supplier_name VARCHAR(100),
        invoice_number VARCHAR(100),
        received_date DATE,
        received_time TIME,
        batch_number VARCHAR(100),
        expiry_date DATE,
        notes TEXT,
        po_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_sk (store_keeper_id),
        INDEX idx_ingredient (ingredient_id),
        INDEX idx_received_date (received_date),
        INDEX idx_po (po_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    @$conn->query($sql);
    // Backfill schema for existing installations.
    if (!hasColumn('stock_receiving', 'po_id')) {
        $conn->query("ALTER TABLE stock_receiving ADD COLUMN po_id INT NULL AFTER notes");
    }
    if (!hasIndex('stock_receiving', 'idx_po')) {
        $conn->query("ALTER TABLE stock_receiving ADD INDEX idx_po (po_id)");
    }
    // Link historical rows to PO where there is exactly one unambiguous match.
    @$conn->query("
        UPDATE stock_receiving sr
        INNER JOIN (
            SELECT
                sr2.id AS stock_receiving_id,
                MIN(po.id) AS matched_po_id,
                COUNT(po.id) AS match_count
            FROM stock_receiving sr2
            INNER JOIN purchase_orders po ON po.ingredient_id = sr2.ingredient_id
            INNER JOIN suppliers s ON s.id = po.supplier_id
            WHERE sr2.po_id IS NULL
              AND TRIM(LOWER(sr2.supplier_name)) = TRIM(LOWER(s.name))
              AND DATE(sr2.received_date) = DATE(COALESCE(po.delivery_date, po.created_at))
              AND po.status IN ('shipped', 'partially_received', 'delivered')
            GROUP BY sr2.id
            HAVING COUNT(po.id) = 1
        ) m ON m.stock_receiving_id = sr.id
        SET sr.po_id = m.matched_po_id
    ");

    // inventory_updates
    $sql = "CREATE TABLE IF NOT EXISTS inventory_updates (
        id INT PRIMARY KEY AUTO_INCREMENT,
        store_keeper_id INT,
        ingredient_id INT,
        ingredient_name VARCHAR(255),
        action VARCHAR(50),
        quantity_changed DECIMAL(10, 3),
        unit VARCHAR(50),
        previous_stock DECIMAL(10, 3),
        new_stock DECIMAL(10, 3),
        reason VARCHAR(255),
        notes TEXT,
        reference_doc VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_sk (store_keeper_id),
        INDEX idx_ingredient (ingredient_id),
        INDEX idx_action (action),
        INDEX idx_date (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    @$conn->query($sql);
    // Backfill bad historical rows caused by earlier bind type mismatch.
    @$conn->query("UPDATE inventory_updates SET action = 'adjust' WHERE action IS NULL OR TRIM(action) = '' OR action = '0'");
    @$conn->query("UPDATE inventory_updates iu
        INNER JOIN ingredients i ON i.id = iu.ingredient_id
        SET iu.unit = i.unit
        WHERE iu.unit IS NULL OR TRIM(iu.unit) = '' OR iu.unit = '0'");

    // expiry_wastage_tracking
    $sql = "CREATE TABLE IF NOT EXISTS expiry_wastage_tracking (
        id INT PRIMARY KEY AUTO_INCREMENT,
        store_keeper_id INT,
        ingredient_id INT,
        ingredient_name VARCHAR(255),
        type VARCHAR(50),
        quantity DECIMAL(10, 3),
        unit VARCHAR(50),
        expiry_date DATE,
        wastage_date DATE,
        reason VARCHAR(255),
        reason_details TEXT,
        disposal_method VARCHAR(100),
        status VARCHAR(50) DEFAULT 'documented',
        cost_impact DECIMAL(10, 2),
        notes TEXT,
        batch_number VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_sk (store_keeper_id),
        INDEX idx_ingredient (ingredient_id),
        INDEX idx_type (type),
        INDEX idx_wastage_date (wastage_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    @$conn->query($sql);

    // Normalize older rows so reports are accurate.
    @$conn->query("
        UPDATE expiry_wastage_tracking
        SET status = 'documented'
        WHERE status IS NULL
           OR TRIM(status) = ''
           OR LOWER(TRIM(status)) = 'pending'
    ");
    @$conn->query("
        UPDATE expiry_wastage_tracking ewt
        INNER JOIN ingredients i ON ewt.ingredient_id = i.id
        SET ewt.cost_impact = (ewt.quantity * i.unit_cost)
        WHERE (ewt.cost_impact IS NULL OR ewt.cost_impact = 0)
          AND ewt.quantity > 0
          AND i.unit_cost > 0
    ");
}

/**
 * Execute query and throw a useful exception when it fails.
 */
function queryOrThrow($sql, $context) {
    global $conn;

    $result = $conn->query($sql);
    if ($result === false) {
        throw new Exception($context . ': ' . $conn->error);
    }

    return $result;
}

/**
 * GET METRICS
 */
function getMetrics() {
    // Total ingredients
    $result = queryOrThrow("SELECT COUNT(*) as count FROM ingredients WHERE status = 'active'", 'Failed to load total ingredients');
    $totalIng = $result->fetch_assoc()['count'];

    // Low stock
    $result = queryOrThrow("SELECT COUNT(*) as count FROM ingredients WHERE current_stock <= minimum_stock AND status = 'active'", 'Failed to load low stock count');
    $lowStock = $result->fetch_assoc()['count'];

    // Expiring within 7 days
    $result = queryOrThrow("SELECT COUNT(*) as count FROM ingredients WHERE expiry_date IS NOT NULL AND DATEDIFF(expiry_date, CURDATE()) BETWEEN 0 AND 7 AND status = 'active'", 'Failed to load expiring count');
    $expiring = $result->fetch_assoc()['count'];

    // Received today
    $result = queryOrThrow("SELECT COUNT(*) as count FROM stock_receiving WHERE DATE(received_date) = CURDATE()", 'Failed to load received today count');
    $receivedToday = $result->fetch_assoc()['count'];

    sendResponse(true, 'Metrics retrieved', [
        'total_ingredients' => $totalIng,
        'low_stock_count' => $lowStock,
        'expiring_count' => $expiring,
        'received_today' => $receivedToday
    ]);
}

/**
 * GET WASTAGE METRICS
 */
function getWastageMetrics() {
    global $conn;

    // Expired items
    $result = $conn->query("SELECT COUNT(*) as count FROM ingredients WHERE expiry_date IS NOT NULL AND expiry_date < CURDATE()");
    $expired = $result->fetch_assoc()['count'];

    // Expiring this week
    $result = $conn->query("SELECT COUNT(*) as count FROM ingredients WHERE expiry_date IS NOT NULL AND DATEDIFF(expiry_date, CURDATE()) BETWEEN 0 AND 7");
    $expiringThisWeek = $result->fetch_assoc()['count'];

    // Wasted today
    $result = $conn->query("SELECT SUM(quantity) as qty FROM expiry_wastage_tracking WHERE DATE(wastage_date) = CURDATE()");
    $wastedRow = $result->fetch_assoc();
    $wasted = $wastedRow['qty'] ? $wastedRow['qty'] : '0';

    // Cost impact this month
    $result = $conn->query("SELECT SUM(cost_impact) as cost FROM expiry_wastage_tracking WHERE MONTH(wastage_date) = MONTH(CURDATE()) AND YEAR(wastage_date) = YEAR(CURDATE())");
    $costRow = $result->fetch_assoc();
    $cost = $costRow['cost'] ? $costRow['cost'] : '0';

    sendResponse(true, 'Wastage metrics retrieved', [
        'expired_count' => $expired,
        'expiring_this_week' => $expiringThisWeek,
        'wasted_today' => $wasted,
        'cost_impact' => $cost
    ]);
}

/**
 * FIX OLD WASTAGE RECORDS - Recalculate cost_impact for records saved before the bug fix
 */
function fixOldWastageRecords() {
    global $conn;

    // Find records with NULL or 0 cost_impact and recalculate based on quantity * unit_cost
    $sql = "UPDATE expiry_wastage_tracking ewt
            INNER JOIN ingredients i ON ewt.ingredient_id = i.id
            SET ewt.cost_impact = (ewt.quantity * i.unit_cost)
            WHERE (ewt.cost_impact IS NULL OR ewt.cost_impact = 0)
            AND ewt.quantity > 0
            AND i.unit_cost > 0";

    if ($conn->query($sql)) {
        $affectedRows = $conn->affected_rows;
        sendResponse(true, "Fixed $affectedRows old wastage records with calculated cost impact", [
            'records_updated' => $affectedRows
        ]);
    } else {
        sendResponse(false, 'Failed to fix old records: ' . $conn->error, null);
    }
}

/**
 * CHECK WASTAGE RECORDS - Debug function to see what's in the database
 */
function checkWastageRecords() {
    global $conn;

    $sql = "SELECT 
        ewt.id,
        ewt.ingredient_id,
        ewt.ingredient_name,
        ewt.quantity,
        ewt.unit,
        ewt.cost_impact,
        ewt.wastage_date,
        i.unit_cost,
        (ewt.quantity * i.unit_cost) as calculated_cost
    FROM expiry_wastage_tracking ewt
    LEFT JOIN ingredients i ON ewt.ingredient_id = i.id
    ORDER BY ewt.created_at DESC
    LIMIT 20";

    $result = $conn->query($sql);
    $data = [];

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }

    sendResponse(true, 'All wastage records for debugging', $data);
}

/**
 * DIRECTLY UPDATE WASTAGE - Update cost_impact for a specific record
 */
function directlyUpdateWastage() {
    global $conn;

    $input = json_decode(file_get_contents('php://input'), true);

    $recordId = (int)$input['record_id'];
    $costImpact = (float)$input['cost_impact'];

    $sql = "UPDATE expiry_wastage_tracking SET cost_impact = ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('di', $costImpact, $recordId);

    if ($stmt->execute()) {
        sendResponse(true, 'Wastage record updated successfully', [
            'record_id' => $recordId,
            'cost_impact' => $costImpact
        ]);
    } else {
        sendResponse(false, 'Failed to update record: ' . $stmt->error, null);
    }
    $stmt->close();
}

/**
 * FIX INVALID DATES - Update records with 0000-00-00 dates to today
 */
function fixInvalidWastageDates() {
    global $conn;

    $today = date('Y-m-d');

    // Find records with invalid dates and update them to today
    $sql = "UPDATE expiry_wastage_tracking 
            SET wastage_date = ?
            WHERE wastage_date = '0000-00-00' OR wastage_date IS NULL OR wastage_date = ''";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $today);

    if ($stmt->execute()) {
        $affectedRows = $conn->affected_rows;
        sendResponse(true, "Fixed $affectedRows records with invalid dates", [
            'records_updated' => $affectedRows,
            'new_date' => $today
        ]);
    } else {
        sendResponse(false, 'Failed to fix dates: ' . $stmt->error, null);
    }
    $stmt->close();
}

/**
 * ADD STOCK RECEIVING
 */
function addStockReceiving() {
    global $conn, $store_keeper_id;

    $input = json_decode(file_get_contents('php://input'), true);

    $ingredientId = (int)$input['ingredient_id'];
    $quantityReceived = (float)$input['quantity_received'];
    $quantity = (float)$input['quantity_received']; // For compatibility
    $costImpact = (float)($input['cost_impact'] ?? 0);
    $supplierName = htmlspecialchars($input['supplier_name'] ?? '');
    $invoiceNumber = htmlspecialchars($input['invoice_number'] ?? '');
    $receivedDate = $input['received_date'] ?? date('Y-m-d');
    $receivedTime = $input['received_time'] ?? date('H:i:s');
    $expiryDate = $input['expiry_date'] ?? null;
    $batchNumber = htmlspecialchars($input['batch_number'] ?? '');
    $notes = htmlspecialchars($input['notes'] ?? '');
    $poId = isset($input['po_id']) ? (int)$input['po_id'] : null;

    // Get ingredient details
    $stmt = $conn->prepare("SELECT name, unit, unit_cost FROM ingredients WHERE id = ?");
    $stmt->bind_param('i', $ingredientId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows === 0) {
        sendResponse(false, 'Ingredient not found', null);
    }

    $ingredient = $result->fetch_assoc();
    $stmt->close();

    // Auto-calculate cost impact when client sends 0/empty.
    if ($costImpact <= 0 && $quantity > 0) {
        $unitCost = (float)($ingredient['unit_cost'] ?? 0);
        if ($unitCost > 0) {
            $costImpact = round($quantity * $unitCost, 2);
        }
    }

    // Insert stock receiving record
    $sql = "INSERT INTO stock_receiving (
        store_keeper_id, ingredient_id, ingredient_name, quantity_received, unit,
        supplier_name, invoice_number, received_date, received_time, batch_number, expiry_date, notes, po_id
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param(
        'iisdssssssssi',
        $store_keeper_id, $ingredientId, $ingredient['name'], $quantityReceived, $ingredient['unit'],
        $supplierName, $invoiceNumber, $receivedDate, $receivedTime, $batchNumber, $expiryDate, $notes, $poId
    );

    if (!$stmt->execute()) {
        throw new Exception('Failed to log stock receiving: ' . $stmt->error);
    }

    $receiptId = $stmt->insert_id;
    $stmt->close();

    // Update ingredient stock
    $updateSql = "UPDATE ingredients SET current_stock = current_stock + ? WHERE id = ?";
    $updateStmt = $conn->prepare($updateSql);
    $updateStmt->bind_param('di', $quantityReceived, $ingredientId);
    $updateStmt->execute();
    $updateStmt->close();

    // Update expiry date if provided and track last received date
    if ($expiryDate) {
        $expiryUpdateSql = "UPDATE ingredients SET expiry_date = ?, last_received_date = ? WHERE id = ?";
        $expiryStmt = $conn->prepare($expiryUpdateSql);
        $expiryStmt->bind_param('ssi', $expiryDate, $receivedDate, $ingredientId);
        $expiryStmt->execute();
        $expiryStmt->close();
    } else {
        // Update last_received_date even if no expiry
        $lastReceivedSql = "UPDATE ingredients SET last_received_date = ? WHERE id = ?";
        $lastStmt = $conn->prepare($lastReceivedSql);
        $lastStmt->bind_param('si', $receivedDate, $ingredientId);
        $lastStmt->execute();
        $lastStmt->close();
    }

    // If this is linked to a PO, update the PO
    if ($poId) {
        $poStmt = $conn->prepare("SELECT quantity, received_quantity FROM purchase_orders WHERE id = ?");
        $poStmt->bind_param('i', $poId);
        $poStmt->execute();
        $poResult = $poStmt->get_result();
        
        if ($poResult->num_rows > 0) {
            $po = $poResult->fetch_assoc();
            $newReceivedQty = $po['received_quantity'] + $quantityReceived;
            $orderedQty = $po['quantity'];
            
            // Determine new PO status
            $newStatus = ($newReceivedQty >= $orderedQty) ? 'delivered' : 'partially_received';
            
            // Update PO - only update invoice_number if provided
            if (!empty($invoiceNumber)) {
                $updatePoSql = "UPDATE purchase_orders SET 
                    received_quantity = ?, 
                    status = ?, 
                    delivery_date = NOW(),
                    invoice_number = ?
                    WHERE id = ?";
                
                $updatePoStmt = $conn->prepare($updatePoSql);
                $updatePoStmt->bind_param('dssi', $newReceivedQty, $newStatus, $invoiceNumber, $poId);
            } else {
                $updatePoSql = "UPDATE purchase_orders SET 
                    received_quantity = ?, 
                    status = ?, 
                    delivery_date = NOW()
                    WHERE id = ?";
                
                $updatePoStmt = $conn->prepare($updatePoSql);
                $updatePoStmt->bind_param('dsi', $newReceivedQty, $newStatus, $poId);
            }
            
            $updatePoStmt->execute();
            $updatePoStmt->close();
        }
        $poStmt->close();
    }

    sendResponse(true, 'Stock received successfully', ['receipt_id' => $receiptId, 'po_id' => $poId]);
}

/**
 * GET STOCK RECEIVING
 */
function getStockReceiving() {
    global $conn;

    $date = isset($_GET['date']) ? $_GET['date'] : null;

    $sql = "SELECT 
        sr.*,
        po.po_number,
        po.status AS po_status,
        po.quantity AS po_quantity,
        GREATEST(po.quantity - COALESCE(po.received_quantity, 0), 0) AS current_po_remaining,
        (
            SELECT GREATEST(
                po.quantity - COALESCE(SUM(sr2.quantity_received), 0),
                0
            )
            FROM stock_receiving sr2
            WHERE sr2.po_id = po.id
              AND (
                sr2.created_at < sr.created_at
                OR (sr2.created_at = sr.created_at AND sr2.id <= sr.id)
              )
        ) AS remaining_after_receipt
    FROM stock_receiving sr
    LEFT JOIN purchase_orders po ON sr.po_id = po.id";
    if ($date) {
        $sql .= " WHERE DATE(sr.received_date) = ?";
    }
    $sql .= " ORDER BY sr.created_at DESC LIMIT 100";

    $stmt = $conn->prepare($sql);
    
    if (!$stmt) {
        sendResponse(false, 'Database error: ' . $conn->error, []);
    }
    
    if ($date) {
        $stmt->bind_param('s', $date);
    }

    if (!$stmt->execute()) {
        sendResponse(false, 'Query error: ' . $stmt->error, []);
    }
    
    $result = $stmt->get_result();
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    $stmt->close();

    sendResponse(true, 'Stock receiving records retrieved', $data);
}

/**
 * GET RECEIPT
 */
function getReceipt() {
    global $conn;

    if (!isset($_GET['id'])) {
        sendResponse(false, 'Receipt ID is required', null);
    }

    $id = (int)$_GET['id'];

    $sql = "SELECT * FROM stock_receiving WHERE id = ?";
    $stmt = $conn->prepare($sql);
    
    if (!$stmt) {
        sendResponse(false, 'Database error: ' . $conn->error, null);
    }
    
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        sendResponse(false, 'Receipt not found', null);
    }

    $receipt = $result->fetch_assoc();
    $stmt->close();

    sendResponse(true, 'Receipt retrieved', $receipt);
}

/**
 * GET PENDING SHIPMENTS (Shipped Purchase Orders)
 */
function getPendingShipments() {
    global $conn;

    $sql = "SELECT 
        po.id AS po_id,
        po.po_number,
        po.item_name,
        po.quantity,
        po.unit,
        po.received_quantity,
        po.unit_price,
        po.total_cost,
        po.ingredient_id,
        po.supplier_id,
        po.invoice_number,
        po.expected_delivery_date,
        po.notes,
        po.created_at,
        s.name AS supplier_name,
        i.name AS ingredient_db_name,
        (
            SELECT sr.expiry_date
            FROM stock_receiving sr
            WHERE sr.po_id = po.id
              AND sr.expiry_date IS NOT NULL
            ORDER BY sr.received_date DESC, sr.created_at DESC, sr.id DESC
            LIMIT 1
        ) AS po_last_expiry_date
    FROM purchase_orders po
    LEFT JOIN suppliers s ON po.supplier_id = s.id
    LEFT JOIN ingredients i ON po.ingredient_id = i.id
    WHERE po.status IN ('shipped', 'partially_received')
    ORDER BY po.expected_delivery_date ASC, po.created_at DESC";

    $result = $conn->query($sql);
    
    if (!$result) {
        sendResponse(false, 'Database error: ' . $conn->error, []);
    }
    
    $shipments = [];

    while ($row = $result->fetch_assoc()) {
        $shipments[] = $row;
    }

    sendResponse(true, 'Pending shipments retrieved', $shipments);
}

/**
 * GET PO FOR RECEIVING
 */
function getPoForReceiving() {
    global $conn;

    $poId = isset($_GET['po_id']) ? (int)$_GET['po_id'] : 0;
    if ($poId <= 0) {
        sendResponse(false, 'PO ID is required', null);
    }

    $stmt = $conn->prepare("
        SELECT
            po.id AS po_id,
            po.po_number,
            po.item_name,
            po.quantity,
            po.unit,
            po.received_quantity,
            po.status,
            po.ingredient_id,
            po.invoice_number,
            s.name AS supplier_name,
            (
                SELECT sr.expiry_date
                FROM stock_receiving sr
                WHERE sr.po_id = po.id
                  AND sr.expiry_date IS NOT NULL
                ORDER BY sr.received_date DESC, sr.created_at DESC, sr.id DESC
                LIMIT 1
            ) AS po_last_expiry_date
        FROM purchase_orders po
        LEFT JOIN suppliers s ON po.supplier_id = s.id
        WHERE po.id = ?
        LIMIT 1
    ");
    $stmt->bind_param('i', $poId);
    $stmt->execute();
    $result = $stmt->get_result();
    $po = $result->fetch_assoc();
    $stmt->close();

    if (!$po) {
        sendResponse(false, 'Purchase order not found', null);
    }

    $po['remaining_quantity'] = max(0, (float)$po['quantity'] - (float)$po['received_quantity']);
    sendResponse(true, 'PO receiving details retrieved', $po);
}

/**
 * CLOSE REMAINING SHIPMENT
 * Marks the undelivered balance of a partially delivered PO as cancelled.
 */
function closeRemainingShipment() {
    global $conn;

    $input = json_decode(file_get_contents('php://input'), true);
    $poId = isset($input['po_id']) ? (int)$input['po_id'] : 0;
    $reason = trim((string)($input['reason'] ?? 'Supplier could not deliver remaining quantity'));

    if ($poId <= 0) {
        throw new Exception('PO ID is required');
    }

    $conn->begin_transaction();
    try {
        $stmt = $conn->prepare("
            SELECT id, quantity, received_quantity, unit, status, notes
            FROM purchase_orders
            WHERE id = ?
            FOR UPDATE
        ");
        $stmt->bind_param('i', $poId);
        $stmt->execute();
        $po = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if (!$po) {
            throw new Exception('Purchase order not found');
        }

        $orderedQty = (float)$po['quantity'];
        $receivedQty = (float)$po['received_quantity'];
        $remainingQty = $orderedQty - $receivedQty;
        $status = strtolower(trim((string)$po['status']));

        if ($remainingQty <= 0) {
            throw new Exception('No remaining quantity to close');
        }

        if (in_array($status, ['cancelled', 'delivered'], true)) {
            throw new Exception('This purchase order is already closed');
        }

        $auditLine = sprintf(
            "[%s] Remaining %.3f %s short-closed by store keeper. Reason: %s",
            date('Y-m-d H:i:s'),
            $remainingQty,
            (string)$po['unit'],
            $reason
        );
        $existingNotes = (string)($po['notes'] ?? '');
        $updatedNotes = $existingNotes !== '' ? ($existingNotes . "\n" . $auditLine) : $auditLine;

        $update = $conn->prepare("
            UPDATE purchase_orders
            SET status = 'cancelled',
                delivery_date = COALESCE(delivery_date, NOW()),
                notes = ?
            WHERE id = ?
        ");
        $update->bind_param('si', $updatedNotes, $poId);
        if (!$update->execute()) {
            throw new Exception('Failed to close remaining quantity');
        }
        $update->close();

        $conn->commit();
        sendResponse(true, 'Remaining quantity closed successfully', [
            'po_id' => $poId,
            'remaining_closed' => $remainingQty
        ]);
    } catch (Throwable $e) {
        $conn->rollback();
        throw $e;
    }
}

/**
 * GET INVENTORY
 */
function getInventory() {
    global $conn;

    $sql = "SELECT 
        id, name, unit, current_stock, minimum_stock, unit_cost,
        CASE 
            WHEN current_stock <= minimum_stock THEN 'low'
            WHEN current_stock > (minimum_stock * 2) THEN 'adequate'
            ELSE 'normal'
        END as stock_status,
        last_updated
    FROM ingredients
    WHERE status = 'active'
    ORDER BY name ASC";

    $result = $conn->query($sql);
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Inventory retrieved', $data);
}

/**
 * GET LOW STOCK
 */
function getLowStock() {
    $sql = "SELECT id, name, current_stock, minimum_stock, unit
    FROM ingredients
    WHERE current_stock <= minimum_stock AND status = 'active'
    ORDER BY current_stock ASC";

    $result = queryOrThrow($sql, 'Failed to load low stock items');
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Low stock items retrieved', $data);
}

/**
 * ADJUST INVENTORY
 */
function adjustInventory() {
    global $conn, $store_keeper_id;

    $input = json_decode(file_get_contents('php://input'), true);

    $ingredientId = (int)$input['ingredient_id'];
    $quantityChanged = (float)$input['quantity_changed'];
    $action = htmlspecialchars($input['action']);
    $reason = htmlspecialchars($input['reason']);
    $referenceDoc = htmlspecialchars($input['reference_doc'] ?? '');
    $notes = htmlspecialchars($input['notes'] ?? '');

    // Get current stock
    $stmt = $conn->prepare("SELECT name, unit, current_stock FROM ingredients WHERE id = ?");
    $stmt->bind_param('i', $ingredientId);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        sendResponse(false, 'Ingredient not found', null);
    }

    $ingredient = $result->fetch_assoc();
    $previousStock = $ingredient['current_stock'];
    $stmt->close();

    // Calculate new stock
    $newStock = $previousStock;
    if ($action === 'add') {
        $newStock = $previousStock + $quantityChanged;
    } elseif ($action === 'subtract') {
        $newStock = $previousStock - $quantityChanged;
        if ($newStock < 0) {
            sendResponse(false, 'Insufficient stock', null);
        }
    } elseif ($action === 'adjust') {
        $newStock = $quantityChanged;
    }

    // Update ingredient stock
    $updateSql = "UPDATE ingredients SET current_stock = ? WHERE id = ?";
    $updateStmt = $conn->prepare($updateSql);
    $updateStmt->bind_param('di', $newStock, $ingredientId);

    if (!$updateStmt->execute()) {
        throw new Exception('Failed to update stock: ' . $updateStmt->error);
    }
    $updateStmt->close();

    // Log in inventory_updates
    $logSql = "INSERT INTO inventory_updates (
        store_keeper_id, ingredient_id, ingredient_name, action, quantity_changed,
        unit, previous_stock, new_stock, reason, notes, reference_doc
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    $logStmt = $conn->prepare($logSql);
    $logStmt->bind_param(
        'iissdsddsss',
        $store_keeper_id, $ingredientId, $ingredient['name'], $action, $quantityChanged,
        $ingredient['unit'], $previousStock, $newStock, $reason, $notes, $referenceDoc
    );
    $logStmt->execute();
    $logStmt->close();

    sendResponse(true, 'Inventory adjusted successfully', [
        'previous_stock' => $previousStock,
        'new_stock' => $newStock
    ]);
}

/**
 * GET RECENT ACTIVITIES
 */
function getRecentActivities() {
    $sql = "SELECT 
        id, ingredient_name, action, quantity_changed, unit, reason, created_at
    FROM inventory_updates
    ORDER BY created_at DESC
    LIMIT 20";

    $result = queryOrThrow($sql, 'Failed to load recent activities');
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Recent activities retrieved', $data);
}

/**
 * GET EXPIRY TRACKING
 */
function getExpiryTracking() {
    global $conn;

    $sql = "SELECT 
        id, name as ingredient_name, current_stock, unit, expiry_date, batch_number, is_perishable,
        CASE 
            WHEN expiry_date IS NULL THEN 99999
            ELSE DATEDIFF(expiry_date, CURDATE())
        END as days_to_expiry
    FROM ingredients
    WHERE status = 'active'
    ORDER BY CASE 
        WHEN expiry_date IS NULL THEN 99999
        ELSE DATEDIFF(expiry_date, CURDATE())
    END ASC";

    $result = $conn->query($sql);
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Expiry tracking retrieved', $data);
}

/**
 * GET EXPIRING SOON (7 days)
 */
function getExpiringSoon() {
    $sql = "SELECT 
        id, name as ingredient_name, current_stock, unit, expiry_date,
        DATEDIFF(expiry_date, CURDATE()) as days_until_expiry
    FROM ingredients
    WHERE expiry_date IS NOT NULL 
    AND DATEDIFF(expiry_date, CURDATE()) BETWEEN 0 AND 7
    AND status = 'active'
    ORDER BY expiry_date ASC";

    $result = queryOrThrow($sql, 'Failed to load expiring items');
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Expiring items retrieved', $data);
}

/**
 * SET INGREDIENT EXPIRY DATE
 */
function setIngredientExpiryDate() {
    global $conn;

    $input = json_decode(file_get_contents('php://input'), true);
    if (!is_array($input)) {
        throw new Exception('Invalid JSON payload');
    }

    $ingredientId = isset($input['ingredient_id']) ? (int)$input['ingredient_id'] : 0;
    $expiryDate = isset($input['expiry_date']) ? trim((string)$input['expiry_date']) : '';

    if ($ingredientId <= 0) {
        throw new Exception('Invalid ingredient id');
    }

    if ($expiryDate === '') {
        throw new Exception('Expiry date is required');
    }

    $parsed = DateTime::createFromFormat('Y-m-d', $expiryDate);
    if (!$parsed || $parsed->format('Y-m-d') !== $expiryDate) {
        throw new Exception('Invalid expiry date format');
    }

    $checkStmt = $conn->prepare("SELECT id FROM ingredients WHERE id = ? AND status = 'active' LIMIT 1");
    $checkStmt->bind_param('i', $ingredientId);
    $checkStmt->execute();
    $checkResult = $checkStmt->get_result();

    if ($checkResult->num_rows === 0) {
        $checkStmt->close();
        throw new Exception('Ingredient not found');
    }
    $checkStmt->close();

    $updateStmt = $conn->prepare("UPDATE ingredients SET expiry_date = ? WHERE id = ?");
    $updateStmt->bind_param('si', $expiryDate, $ingredientId);

    if (!$updateStmt->execute()) {
        $err = $updateStmt->error;
        $updateStmt->close();
        throw new Exception('Failed to update expiry date: ' . $err);
    }
    $updateStmt->close();

    sendResponse(true, 'Expiry date updated successfully', [
        'ingredient_id' => $ingredientId,
        'expiry_date' => $expiryDate
    ]);
}

/**
 * LOG WASTAGE
 */
function logWastage() {
    global $conn, $store_keeper_id;

    $input = json_decode(file_get_contents('php://input'), true);
    if (!is_array($input)) {
        throw new Exception('Invalid JSON payload');
    }

    $ingredientId = isset($input['ingredient_id']) ? (int)$input['ingredient_id'] : 0;
    $type = htmlspecialchars($input['type'] ?? '');
    $quantity = isset($input['quantity']) ? (float)$input['quantity'] : 0;
    $wastageDate = $input['wastage_date'] ?? date('Y-m-d');
    $reason = htmlspecialchars(trim((string)($input['reason'] ?? '')));
    $disposalMethod = htmlspecialchars($input['disposal_method'] ?? '');
    $expiryDate = $input['expiry_date'] ?? null;
    $batchNumber = htmlspecialchars($input['batch_number'] ?? '');
    $costImpact = (float)($input['cost_impact'] ?? 0);
    $notes = htmlspecialchars($input['notes'] ?? '');

    if ($ingredientId <= 0) {
        throw new Exception('Invalid ingredient id');
    }

    if ($type === '') {
        throw new Exception('Wastage type is required');
    }

    // Get ingredient details
    $stmt = $conn->prepare("SELECT name, unit, current_stock, expiry_date, batch_number FROM ingredients WHERE id = ? AND status = 'active'");
    $stmt->bind_param('i', $ingredientId);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        sendResponse(false, 'Ingredient not found', null);
    }

    $ingredient = $result->fetch_assoc();
    $stmt->close();

    $currentStock = (float)$ingredient['current_stock'];
    if ($currentStock <= 0) {
        throw new Exception('Ingredient current stock is already zero');
    }

    $ingredientExpiryDate = $ingredient['expiry_date'] ?? null;
    $isExpiredIngredient = !empty($ingredientExpiryDate) && $ingredientExpiryDate < date('Y-m-d');

    // If ingredient is already expired and wastage type is expiry, consume full stock automatically.
    if ($type === 'expiry' && $isExpiredIngredient) {
        $quantity = $currentStock;
        if ($reason === '') {
            $reason = 'Auto-marked as expired from expiry tracking';
        }
        if (empty($expiryDate)) {
            $expiryDate = $ingredientExpiryDate;
        }
        if ($batchNumber === '') {
            $batchNumber = $ingredient['batch_number'] ?? '';
        }
    }

    if ($quantity <= 0) {
        throw new Exception('Wastage quantity must be greater than zero');
    }

    if ($quantity > $currentStock) {
        throw new Exception('Wastage quantity cannot exceed current stock');
    }

    if ($reason === '') {
        throw new Exception('Reason is required');
    }

    $conn->begin_transaction();

    // Insert wastage record
    $sql = "INSERT INTO expiry_wastage_tracking (
        store_keeper_id, ingredient_id, ingredient_name, type, quantity, unit,
        expiry_date, wastage_date, reason, disposal_method, cost_impact, batch_number, notes, status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'documented')";

    $insertStmt = $conn->prepare($sql);
    $insertStmt->bind_param(
        'iissdssssdsss',
        $store_keeper_id, $ingredientId, $ingredient['name'], $type, $quantity, $ingredient['unit'],
        $expiryDate, $wastageDate, $reason, $disposalMethod, $costImpact, $batchNumber, $notes
    );

    if (!$insertStmt->execute()) {
        $conn->rollback();
        throw new Exception('Failed to log wastage: ' . $insertStmt->error);
    }

    $insertStmt->close();

    // Reduce stock
    $updateSql = "UPDATE ingredients SET current_stock = current_stock - ? WHERE id = ?";
    $updateStmt = $conn->prepare($updateSql);
    $updateStmt->bind_param('di', $quantity, $ingredientId);
    if (!$updateStmt->execute()) {
        $conn->rollback();
        throw new Exception('Failed to update stock: ' . $updateStmt->error);
    }
    $updateStmt->close();

    $conn->commit();

    sendResponse(true, 'Wastage logged successfully', null);
}

/**
 * GET WASTAGE RECORDS
 */
function getWastageRecords() {
    global $conn;

    $sql = "SELECT * FROM expiry_wastage_tracking ORDER BY created_at DESC LIMIT 100";

    $result = $conn->query($sql);
    $data = [];

    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }

    sendResponse(true, 'Wastage records retrieved', $data);
}

?>
