<?php
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, POST");

try {
    $db = new PDO("sqlite:database.sqlite");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Create requisitions tables if they don't exist
    $db->exec("CREATE TABLE IF NOT EXISTS stock_requisitions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        req_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        warehouse_id INTEGER,
        warehouse_name TEXT,
        user_name TEXT,
        status TEXT DEFAULT 'pending'
    )");

    $db->exec("CREATE TABLE IF NOT EXISTS stock_requisition_details (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        requisition_id INTEGER,
        item_id INTEGER,
        item_name TEXT,
        req_qty REAL,
        current_qty REAL,
        other_qty REAL
    )");
    
    try { $db->exec("ALTER TABLE stock_requisitions ADD COLUMN comments TEXT"); } catch(Exception $e) {}
    try { $db->exec("ALTER TABLE stock_requisition_details ADD COLUMN unit TEXT"); } catch(Exception $e) {}
    try { $db->exec("ALTER TABLE stock_requisition_details ADD COLUMN conv_factor REAL"); } catch(Exception $e) {}
    try { $db->exec("ALTER TABLE stock_requisitions ADD COLUMN intended_approver TEXT"); } catch(Exception $e) {}
    try { $db->exec("ALTER TABLE stock_requisitions ADD COLUMN paper_approved_by TEXT"); } catch(Exception $e) {}

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

    
    if ($_SERVER['REQUEST_METHOD'] === 'POST' && $action === 'sign_request') {
        $input = json_decode(file_get_contents('php://input'), true);
        if(!isset($input['id']) || !isset($input['paper_approved_by'])) {
            echo json_encode(["success" => false, "error" => "Missing data"]);
            exit;
        }
        $stmt_check = $db->prepare("SELECT paper_approved_by FROM stock_requisitions WHERE id = ?");
        $stmt_check->execute([$input['id']]);
        $existing = $stmt_check->fetchColumn();
        $new_val = $existing ? $existing . ',' . $input['paper_approved_by'] : $input['paper_approved_by'];
        
        $stmt = $db->prepare("UPDATE stock_requisitions SET paper_approved_by = ? WHERE id = ?");
        if($stmt->execute([$new_val, $input['id']])) {
            echo json_encode(["success" => true]);
        } else {
            echo json_encode(["success" => false]);
        }
        exit;
    }

    if ($_SERVER['REQUEST_METHOD'] === 'GET' && $action === 'fetch_items') {
        $warehouse_id = isset($_GET['warehouse_id']) ? $_GET['warehouse_id'] : null;

        if (!$warehouse_id) {
            echo json_encode(["error" => "warehouse_id is required"]);
            exit;
        }

        // Fetch all items
        $items_stmt = $db->query("SELECT id, data FROM items");
        $items = [];
        while ($row = $items_stmt->fetch(PDO::FETCH_ASSOC)) {
            $data = json_decode($row['data'], true);
            if (is_array($data)) {
                $data['id'] = $row['id'];
                $items[$row['id']] = $data;
            }
        }

        // Fetch all transactions to compute balance
        // Note: For large DBs, this is done in memory on the frontend in Easy ERP.
        // Doing it here as requested by module design.
        $trans_stmt = $db->query("SELECT data FROM transactions");
        $balances = []; // itemId => ['current' => qty, 'other' => qty]
        foreach ($items as $id => $item) {
            $balances[$id] = ['current' => 0, 'other' => 0];
        }

        while ($row = $trans_stmt->fetch(PDO::FETCH_ASSOC)) {
            $inv = json_decode($row['data'], true);
            if (!is_array($inv) || empty($inv['items'])) continue;

            $type = isset($inv['type']) ? $inv['type'] : '';
            $status = isset($inv['status']) ? $inv['status'] : '';
            if ($status === 'draft') continue;

            $is_in = ($type === 'in' || $type === 'opening_balance' || $type === 'manufacture');
            $is_out = ($type === 'out' || $type === 'out_project' || $type === 'depreciate_asset');
            $is_transfer = ($type === 'transfer');

            foreach ($inv['items'] as $it) {
                $itemId = isset($it['item_id']) ? $it['item_id'] : null;
                $qty = isset($it['qty']) ? (float)$it['qty'] : 0;
                
                if (!$itemId || !isset($balances[$itemId])) continue;

                if ($is_in) {
                    $wh = isset($inv['to_warehouse_id']) ? $inv['to_warehouse_id'] : null;
                    if ($wh == $warehouse_id) $balances[$itemId]['current'] += $qty;
                    else $balances[$itemId]['other'] += $qty;
                } elseif ($is_out) {
                    $wh = isset($inv['from_warehouse_id']) ? $inv['from_warehouse_id'] : null;
                    if ($wh == $warehouse_id) $balances[$itemId]['current'] -= $qty;
                    else $balances[$itemId]['other'] -= $qty;
                } elseif ($is_transfer) {
                    $from_wh = isset($inv['from_warehouse_id']) ? $inv['from_warehouse_id'] : null;
                    $to_wh = isset($inv['to_warehouse_id']) ? $inv['to_warehouse_id'] : null;
                    
                    if ($from_wh == $warehouse_id) $balances[$itemId]['current'] -= $qty;
                    else $balances[$itemId]['other'] -= $qty;

                    if ($to_wh == $warehouse_id) $balances[$itemId]['current'] += $qty;
                    else $balances[$itemId]['other'] += $qty;
                }
            }
        }

        // Format response
        $result = [];
        foreach ($items as $id => $item) {
            $item['current_store_qty'] = $balances[$id]['current'];
            $item['other_stores_qty'] = $balances[$id]['other'];
            $result[] = $item;
        }

        echo json_encode(["success" => true, "items" => $result]);
        exit;
    }

    if ($_SERVER['REQUEST_METHOD'] === 'POST' && $action === 'save_request') {
        $input = json_decode(file_get_contents("php://input"), true);
        
        if (!$input || empty($input['warehouse_id']) || empty($input['items'])) {
            echo json_encode(["error" => "Invalid data"]);
            exit;
        }

        $commentsJson = isset($input['comments']) ? json_encode($input['comments']) : '[]';
        $intended = isset($input['intended_approver']) ? $input['intended_approver'] : '';

        if (!empty($input['id'])) {
            $req_id = $input['id'];
            $stmt = $db->prepare("UPDATE stock_requisitions SET warehouse_id=?, warehouse_name=?, user_name=?, comments=?, intended_approver=? WHERE id=?");
            $stmt->execute([
                $input['warehouse_id'], 
                $input['warehouse_name'], 
                $input['user_name'],
                $commentsJson,
                $intended,
                $req_id
            ]);
            
            // Delete old details
            $del_stmt = $db->prepare("DELETE FROM stock_requisition_details WHERE requisition_id=?");
            $del_stmt->execute([$req_id]);
        } else {
            $stmt = $db->prepare("INSERT INTO stock_requisitions (warehouse_id, warehouse_name, user_name, comments, intended_approver) VALUES (?, ?, ?, ?, ?)");
            $stmt->execute([
                $input['warehouse_id'], 
                $input['warehouse_name'], 
                $input['user_name'],
                $commentsJson,
                $intended
            ]);
            $req_id = $db->lastInsertId();
        }

        $stmt_det = $db->prepare("INSERT INTO stock_requisition_details (requisition_id, item_id, item_name, req_qty, current_qty, other_qty, unit, conv_factor) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        
        foreach ($input['items'] as $item) {
            $stmt_det->execute([
                $req_id,
                $item['item_id'],
                $item['item_name'],
                $item['req_qty'],
                $item['current_qty'],
                $item['other_qty'],
                isset($item['unit']) ? $item['unit'] : '',
                isset($item['conv_factor']) ? $item['conv_factor'] : 1
            ]);
        }

        echo json_encode(["success" => true, "req_id" => $req_id]);
        exit;
    }


    if ($_SERVER['REQUEST_METHOD'] === 'POST' && $action === 'delete_request') {
        $input = json_decode(file_get_contents("php://input"), true);
        if(!isset($input['id'])) {
            echo json_encode(["success" => false, "error" => "Missing data"]);
            exit;
        }
        $id = intval($input['id']);
        $stmt1 = $db->prepare("DELETE FROM stock_requisitions WHERE id = ?");
        $stmt2 = $db->prepare("DELETE FROM stock_requisition_details WHERE requisition_id = ?");
        if($stmt1->execute([$id]) && $stmt2->execute([$id])) {
            echo json_encode(["success" => true]);
        } else {
            echo json_encode(["success" => false, "error" => "Failed to delete"]);
        }
        exit;
    }

    if ($_SERVER['REQUEST_METHOD'] === 'GET' && $action === 'fetch_archive') {
        $stmt = $db->query("SELECT * FROM stock_requisitions ORDER BY id DESC LIMIT 50");
        $results = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $results[] = $row;
        }
        echo json_encode(["success" => true, "data" => $results]);
        exit;
    }

    if ($_SERVER['REQUEST_METHOD'] === 'GET' && $action === 'fetch_req_details') {
        $req_id = isset($_GET['id']) ? intval($_GET['id']) : 0;
        
        $req_stmt = $db->prepare("SELECT * FROM stock_requisitions WHERE id = ?");
        $req_stmt->execute([$req_id]);
        $req = $req_stmt->fetch(PDO::FETCH_ASSOC);
        
        if(!$req) {
            echo json_encode(["error" => "Requisition not found"]);
            exit;
        }
        
        $det_stmt = $db->prepare("SELECT * FROM stock_requisition_details WHERE requisition_id = ?");
        $det_stmt->execute([$req_id]);
        $details = [];
        while ($row = $det_stmt->fetch(PDO::FETCH_ASSOC)) {
            $details[] = $row;
        }
        
        echo json_encode(["success" => true, "request" => $req, "details" => $details]);
        exit;
    }

} catch(Exception $e) {
    http_response_code(500);
    echo json_encode(["error" => $e->getMessage()]);
}
?>
