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

$method = $_SERVER['REQUEST_METHOD'];

if ($method === 'POST') {
    requireRole(['admin', 'manager']);
    handlePost();
} else {
    requireRole(['admin', 'manager', 'clerk']);
    handleGet();
}

function handleGet() {
    $date = $_GET['date'] ?? date('Y-m-d');
    
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT id, item_code, item_description, item_group, group_category, qty, unit_cost, value, valuation_date FROM inventory_valuation WHERE valuation_date = ? ORDER BY item_code");
    $stmt->execute([$date]);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $result = array_map(function($row) {
        return [
            'id' => (int)$row['id'],
            'ItemCode' => $row['item_code'],
            'ItemDescription' => $row['item_description'],
            'ItemGroup' => $row['item_group'],
            'GroupCategory' => $row['group_category'],
            'Qty' => (int)$row['qty'],
            'UnitCost' => (float)$row['unit_cost'],
            'Value' => (float)$row['value'],
            'ValuationDate' => $row['valuation_date']
        ];
    }, $data);
    
    jsonResponse(['data' => $result]);
}

function handlePost() {
    $action = $_POST['action'] ?? '';
    
    if ($action === 'update') {
        handleUpdate();
    } elseif ($action === 'import') {
        handleImport();
    } else {
        jsonResponse(['error' => 'Invalid action'], 400);
    }
}

function handleUpdate() {
    $id = $_POST['id'] ?? null;
    $itemCode = $_POST['item_code'] ?? '';
    $itemDescription = $_POST['item_description'] ?? null;
    $itemGroup = $_POST['item_group'] ?? null;
    $groupCategory = $_POST['group_category'] ?? null;
    $qty = $_POST['qty'] ?? 0;
    $unitCost = $_POST['unit_cost'] ?? 0;
    $valuationDate = $_POST['valuation_date'] ?? date('Y-m-d');
    
    if (empty($itemCode) || empty($id)) {
        jsonResponse(['error' => 'Missing required fields'], 400);
    }
    
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("UPDATE inventory_valuation SET item_code = ?, item_description = ?, item_group = ?, group_category = ?, qty = ?, unit_cost = ?, valuation_date = ? WHERE id = ?");
    $stmt->execute([$itemCode, $itemDescription, $itemGroup, $groupCategory, $qty, $unitCost, $valuationDate, $id]);
    
    jsonResponse(['success' => true, 'message' => 'Record updated successfully']);
}

function handleImport() {
    $valuationDate = $_POST['valuation_date'] ?? date('Y-m-d');
    
    if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
        jsonResponse(['error' => 'No file uploaded'], 400);
    }
    
    $file = $_FILES['file'];
    $filename = $file['tmp_name'];
    $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    
    try {
        $data = [];
        
        if ($extension === 'xlsx' || $extension === 'xls') {
            $data = readExcelFile($filename);
        } elseif ($extension === 'csv') {
            $data = readCSVFile($filename);
        } else {
            jsonResponse(['error' => 'Unsupported file format'], 400);
        }
        
        if (empty($data)) {
            jsonResponse(['error' => 'File is empty'], 400);
        }
        
        $pdo = getDBConnection();
        $results = ['success' => 0, 'errors' => [], 'count' => count($data)];
        
        $pdo->beginTransaction();
        
        try {
            $stmt = $pdo->prepare("INSERT INTO inventory_valuation (item_code, item_description, item_group, group_category, qty, unit_cost, valuation_date) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE item_description = VALUES(item_description), item_group = VALUES(item_group), group_category = VALUES(group_category), qty = VALUES(qty), unit_cost = VALUES(unit_cost)");
            
            foreach ($data as $idx => $row) {
                $itemCode = $row['item_code'] ?? $row['itemcode'] ?? '';
                if (empty($itemCode)) continue;
                
                try {
                    $stmt->execute([
                        $itemCode,
                        $row['item_description'] ?? $row['itemdescription'] ?? null,
                        $row['item_group'] ?? $row['itemgroup'] ?? null,
                        $row['group_category'] ?? $row['groupcategory'] ?? null,
                        (int)($row['qty'] ?? $row['quantity'] ?? 0),
                        (float)($row['unit_cost'] ?? $row['unitcost'] ?? 0),
                        $valuationDate
                    ]);
                    $results['success']++;
                } catch (Exception $e) {
                    $results['errors'][] = "Row " . ($idx + 1) . ": " . $e->getMessage();
                }
            }
            
            $pdo->commit();
        } catch (Exception $e) {
            $pdo->rollBack();
            $results['errors'][] = $e->getMessage();
        }
        
        jsonResponse($results);
    } catch (Exception $e) {
        jsonResponse(['error' => $e->getMessage()], 500);
    }
}

function readExcelFile($filename) {
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();
    
    if (empty($rows)) return [];
    
    $headers = array_shift($rows);
    $headers = array_map(function($h) {
        return strtolower(trim((string)$h));
    }, $headers);
    
    $data = [];
    foreach ($rows as $row) {
        $assoc = [];
        foreach ($headers as $idx => $header) {
            if (!empty($header)) {
                $assoc[$header] = $row[$idx] ?? '';
            }
        }
        if (!empty(array_filter($assoc))) {
            $data[] = $assoc;
        }
    }
    
    return $data;
}

function readCSVFile($filename) {
    $handle = fopen($filename, 'r');
    $data = [];
    
    if ($handle === false) {
        return [];
    }
    
    $headers = fgetcsv($handle);
    if ($headers === false) {
        fclose($handle);
        return [];
    }
    
    $headers = array_map(function($h) {
        return strtolower(trim($h, " \t\""));
    }, $headers);
    
    while (($row = fgetcsv($handle)) !== false) {
        $assoc = [];
        foreach ($headers as $idx => $header) {
            if (!empty($header) && isset($row[$idx])) {
                $assoc[$header] = $row[$idx];
            }
        }
        if (!empty(array_filter($assoc))) {
            $data[] = $assoc;
        }
    }
    
    fclose($handle);
    return $data;
}