<?php
require_once __DIR__ . '/../config.php';

function calcFulfillment($ordered, $received) {
    if (empty($ordered) || $ordered == 0) return 0;
    return min(100, round(($received / $ordered) * 100, 2));
}

function calcStockOnHand($itemCode) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(quantity_in), 0) - COALESCE(SUM(quantity_out), 0) as soh 
        FROM inventory_transaction WHERE item_code = ?
    ");
    $stmt->execute([$itemCode]);
    return $stmt->fetch()['soh'] ?? 0;
}

function calcStockValue($itemCode) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(quantity_in), 0) as qty_in, COALESCE(SUM(quantity_in * unit_cost), 0) as cost_in,
               COALESCE(SUM(quantity_out), 0) as qty_out
        FROM inventory_transaction WHERE item_code = ?
    ");
    $stmt->execute([$itemCode]);
    $row = $stmt->fetch();
    
    if ($row['qty_in'] == 0) return 0;
    $avgCost = $row['qty_in'] > 0 ? ($row['cost_in'] / $row['qty_in']) : 0;
    $soh = $row['qty_in'] - $row['qty_out'];
    
    return $soh * $avgCost;
}

function getLowStockItems() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT i.ItemCode, i.ItemName, i.Category, i.UnitOfMeasure, i.ReorderLevel,
               COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as StockOnHand
        FROM items_master i
        LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
        GROUP BY i.ItemCode
        HAVING StockOnHand < i.ReorderLevel OR StockOnHand IS NULL
        ORDER BY StockOnHand ASC
    ");
    return $stmt->fetchAll();
}

function getInventorySummary() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT 
            source_type as Category,
            COUNT(DISTINCT item_code) as ItemCount,
            COALESCE(SUM(quantity_in), 0) as TotalIn,
            COALESCE(SUM(quantity_out), 0) as TotalOut,
            COALESCE(SUM(quantity_in * unit_cost), 0) as TotalValue
        FROM inventory_transaction
        GROUP BY source_type
    ");
    return $stmt->fetchAll();
}

function getProcurementStats($startDate = null, $endDate = null) {
    $pdo = getDBConnection();
    $sql = "SELECT 
                o.OrderID, o.ItemCode, o.ItemDescription, o.Quantity as OrderedQty, o.UnitPrice,
                o.Supplier, o.PO_Date, o.PO_DateApproved,
                g.Quantity_Received,
                CASE WHEN o.Quantity > 0 AND g.Quantity_Received IS NOT NULL 
                     THEN g.Quantity_Received / o.Quantity * 100 ELSE 0 END as FulfillmentPct
            FROM orders o
            LEFT JOIN grv g ON o.Reference = g.Order_No";
    $params = [];
    
    if ($startDate && $endDate) {
        $sql .= " WHERE o.PO_Date BETWEEN ? AND ?";
        $params = [$startDate, $endDate];
    }
    
    $sql .= " ORDER BY o.PO_Date DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function getSupplierPerformance() {
    $pdo = getDBConnection();
    $stmt = $pdo->query("
        SELECT s.SupplierID, s.SupplierName, s.LeadTime_Days, s.Rating,
               COUNT(DISTINCT o.OrderID) as TotalOrders,
               COALESCE(SUM(o.Quantity * o.UnitPrice), 0) as TotalValue,
               AVG(g.Quantity_Received / o.Quantity * 100) as AvgFulfillment
        FROM suppliers s
        LEFT JOIN orders o ON s.SupplierID = o.Supplier
        LEFT JOIN grv g ON o.Reference = g.Order_No
        GROUP BY s.SupplierID
        ORDER BY TotalValue DESC
    ");
    return $stmt->fetchAll();
}

function getDashboardKPIs() {
    $pdo = getDBConnection();
    
    $totalOrders = $pdo->query("SELECT COUNT(*) as cnt FROM orders")->fetch()['cnt'] ?? 0;
    $totalItems = $pdo->query("SELECT COUNT(*) as cnt FROM items_master")->fetch()['cnt'] ?? 0;
    $totalSuppliers = $pdo->query("SELECT COUNT(*) as cnt FROM suppliers")->fetch()['cnt'] ?? 0;
    
    $lowStock = $pdo->query("
        SELECT COUNT(*) as cnt FROM (
            SELECT i.ItemCode, i.ReorderLevel, COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as soh
            FROM items_master i
            LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
            GROUP BY i.ItemCode, i.ReorderLevel
            HAVING soh < i.ReorderLevel OR (soh = 0 AND i.ReorderLevel > 0)
        ) as low_stock
    ")->fetch()['cnt'] ?? 0;
    
    $pendingPOs = $pdo->query("SELECT COUNT(*) as cnt FROM orders WHERE PO_DateApproved IS NULL")->fetch()['cnt'] ?? 0;
    
    $totalValue = $pdo->query("SELECT COALESCE(SUM(quantity_in * unit_cost), 0) as val FROM inventory_transaction")->fetch()['val'] ?? 0;
    
    return [
        'total_orders' => $totalOrders,
        'total_items' => $totalItems,
        'total_suppliers' => $totalSuppliers,
        'low_stock' => $lowStock,
        'pending_pos' => $pendingPOs,
        'total_value' => $totalValue
    ];
}

function checkReorderAlert($itemCode) {
    $soh = calcStockOnHand($itemCode);
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT ReorderLevel FROM items_master WHERE ItemCode = ?");
    $stmt->execute([$itemCode]);
    $reorderLevel = $stmt->fetch()['ReorderLevel'] ?? 0;
    
    return $soh <= $reorderLevel;
}

function exportInventoryTransactions($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM inventory_transaction WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND transaction_date >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND transaction_date <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['item_code'])) {
        $sql .= " AND item_code = ?";
        $params[] = $filters['item_code'];
    }
    if (!empty($filters['source_type'])) {
        $sql .= " AND source_type = ?";
        $params[] = $filters['source_type'];
    }
    if (!empty($filters['trans_type'])) {
        $sql .= " AND trans_type = ?";
        $params[] = $filters['trans_type'];
    }

    $sql .= " ORDER BY transaction_date DESC, id DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importInventoryTransactions($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'skipped' => 0];

    $requiredCols = ['item_code', 'transaction_date', 'trans_type'];
    $firstRow = reset($data);
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    $columns = array_keys($firstRow);

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $itemCode = trim($row['item_code'] ?? '');
        if (empty($itemCode)) {
            $results['skipped']++;
            continue;
        }

        $checkStmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode = ?");
        $checkStmt->execute([$itemCode]);
        if (!$checkStmt->fetch()) {
            $results['errors'][] = "Item code not found: $itemCode (row " . ($idx + 1) . ")";
            $results['skipped']++;
            continue;
        }

        if ($dryRun) {
            $results['success']++;
            continue;
        }

        $insertCols = array_diff($columns, ['id', 'ItemCode']);
        
        $colNames = array_keys($row);
        $colNames = array_filter($colNames, fn($c) => !in_array($c, ['id']));
        
        $cols = implode(', ', array_map(fn($c) => "`$c`", $colNames));
        $placeholders = implode(', ', array_fill(0, count($colNames), '?'));
        
        $values = [];
        foreach ($colNames as $col) {
            $values[] = $row[$col] ?? null;
        }

        try {
            $stmt = $pdo->prepare("INSERT INTO inventory_transaction ($cols) VALUES ($placeholders)");
            $stmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Error importing row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }

    return $results;
}

function exportOrders($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM orders WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND PO_Date >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND PO_Date <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['supplier'])) {
        $sql .= " AND Supplier = ?";
        $params[] = $filters['supplier'];
    }
    if (!empty($filters['status'])) {
        $status = $filters['status'];
        if ($status === 'Pending') {
            $sql .= " AND PO_DateApproved IS NULL";
        } elseif ($status === 'Approved') {
            $sql .= " AND PO_DateApproved IS NOT NULL AND (SELECT COUNT(*) FROM grv WHERE Order_No = orders.Reference) = 0";
        }
    }

    $sql .= " ORDER BY PO_Date DESC, OrderID DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importOrders($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'skipped' => 0];

    $requiredCols = ['ItemCode', 'Quantity', 'PO_Date'];
    $firstRow = reset($data);
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $itemCode = trim($row['ItemCode'] ?? '');
        if (empty($itemCode)) {
            $results['skipped']++;
            continue;
        }

        $checkStmt = $pdo->prepare("SELECT ItemCode FROM items_master WHERE ItemCode = ?");
        $checkStmt->execute([$itemCode]);
        if (!$checkStmt->fetch()) {
            $results['errors'][] = "Item code not found: $itemCode (row " . ($idx + 1) . ")";
            $results['skipped']++;
            continue;
        }

        if (!empty($row['Supplier'])) {
            $suppStmt = $pdo->prepare("SELECT SupplierID FROM suppliers WHERE SupplierID = ?");
            $suppStmt->execute([$row['Supplier']]);
            if (!$suppStmt->fetch()) {
                $results['errors'][] = "Supplier not found: " . $row['Supplier'] . " (row " . ($idx + 1) . ")";
                $results['skipped']++;
                continue;
            }
        }

        if ($dryRun) {
            $results['success']++;
            continue;
        }

        $cols = ['OrderID', 'SourceID', 'Buyer', 'Reference', 'ItemCode', 'ItemDescription', 'UOM', 'ItemCategory', 'ItemGroup', 'GoodType', 'Detail', 'Quantity', 'UnitPrice', 'GrossPrice', 'Division', 'SupplySource', 'Supplier', 'Location', 'ReferenceRQ', 'ItemType', 'PO_Date', 'PO_DateApproved', 'PaymentTerms', 'Source', 'GoodsSupplied'];
        $colNames = array_filter(array_keys($row), fn($c) => in_array($c, $cols));
        
        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        $insertCols = implode(', ', array_map(fn($c) => "`$c`", $colNames));
        $placeholders = implode(', ', array_fill(0, count($colNames), '?'));
        
        $values = [];
        foreach ($colNames as $col) {
            $values[] = $row[$col] ?? null;
        }

        try {
            $stmt = $pdo->prepare("INSERT INTO orders ($insertCols) VALUES ($placeholders)");
            $stmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Error importing row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }

    return $results;
}

function exportGRV($filters = []) {
    $pdo = getDBConnection();
    $sql = "SELECT * FROM grv WHERE 1=1";
    $params = [];

    if (!empty($filters['start_date'])) {
        $sql .= " AND GRV_Date >= ?";
        $params[] = $filters['start_date'];
    }
    if (!empty($filters['end_date'])) {
        $sql .= " AND GRV_Date <= ?";
        $params[] = $filters['end_date'];
    }
    if (!empty($filters['order_no'])) {
        $sql .= " AND Order_No LIKE ?";
        $params[] = '%' . $filters['order_no'] . '%';
    }
    if (!empty($filters['supplier'])) {
        $sql .= " AND Supplier_Account LIKE ?";
        $params[] = '%' . $filters['supplier'] . '%';
    }

    $sql .= " ORDER BY GRV_Date DESC, GRV_ID DESC";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function importGRV($data, $dryRun = true) {
    $pdo = getDBConnection();
    $results = ['success' => 0, 'errors' => [], 'skipped' => 0];

    $requiredCols = ['Order_No', 'GRV_Date'];
    $firstRow = reset($data);
    foreach ($requiredCols as $col) {
        if (!isset($firstRow[$col])) {
            $results['errors'][] = "Missing required column: $col";
            return $results;
        }
    }

    foreach ($data as $idx => $row) {
        if ($idx === 0 || empty(array_filter($row))) continue;

        $orderNo = trim($row['Order_No'] ?? '');
        $grvDate = trim($row['GRV_Date'] ?? '');

        if (empty($orderNo) || empty($grvDate)) {
            $results['skipped']++;
            continue;
        }

        $checkStmt = $pdo->prepare("SELECT Reference FROM orders WHERE Reference = ?");
        $checkStmt->execute([$orderNo]);
        if (!$checkStmt->fetch()) {
            $results['errors'][] = "Order reference not found: $orderNo (row " . ($idx + 1) . ")";
            $results['skipped']++;
            continue;
        }

        if ($dryRun) {
            $results['success']++;
            continue;
        }

        $cols = ['Supplier_Account', 'Supplier_Description', 'GRV_Date', 'Order_No', 'Supplier_Invoice', 'Our_Reference', 'Item_Code', 'Item_Description', 'Detail', 'Ordered_Qty', 'Prev_Qty', 'Quantity_Received', 'Unit_Price', 'TaxTotal_Incl', 'Currency', 'Item_Group'];
        $colNames = array_filter(array_keys($row), fn($c) => in_array($c, $cols));

        if (empty($colNames)) {
            $results['skipped']++;
            continue;
        }

        $insertCols = implode(', ', array_map(fn($c) => "`$c`", $colNames));
        $placeholders = implode(', ', array_fill(0, count($colNames), '?'));

        $values = [];
        foreach ($colNames as $col) {
            $values[] = $row[$col] ?? null;
        }

        try {
            $stmt = $pdo->prepare("INSERT INTO grv ($insertCols) VALUES ($placeholders)");
            $stmt->execute($values);
            $results['success']++;
        } catch (Exception $e) {
            $results['errors'][] = "Error importing row " . ($idx + 1) . ": " . $e->getMessage();
        }
    }

    return $results;
}
?>