<?php
/**
 * Production Management Handler
 * Handles production orders, items, and material consumption
 */

function createProductionOrder($conn, $production_date, $remarks, $created_by) {
    try {
        // Generate production order number
        $order_number = generateProductionOrderNumber($conn);
        
        $stmt = $conn->prepare("
            INSERT INTO production_orders (order_number, production_date, remarks, created_by) 
            VALUES (?, ?, ?, ?)
        ");
        $stmt->bind_param("sssi", $order_number, $production_date, $remarks, $created_by);
        $stmt->execute();
        
        $production_order_id = $conn->insert_id;
        $stmt->close();
        
        return [
            'success' => true,
            'production_order_id' => $production_order_id,
            'order_number' => $order_number
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'error' => 'Failed to create production order: ' . $e->getMessage()
        ];
    }
}

function addProductionItem($conn, $production_order_id, $item_id, $quantity) {
    try {
        // Get item cost from BOM
        $item_cost = calculateBOMCost($conn, $item_id);
        
        $total_cost = $item_cost * $quantity;
        
        $stmt = $conn->prepare("
            INSERT INTO production_items (production_order_id, item_id, quantity, unit_cost, total_cost) 
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->bind_param("iiddd", $production_order_id, $item_id, $quantity, $item_cost, $total_cost);
        $stmt->execute();
        
        $production_item_id = $conn->insert_id;
        $stmt->close();
        
        return [
            'success' => true,
            'production_item_id' => $production_item_id
        ];
    } catch (Exception $e) {
        return [
            'success' => false,
            'error' => 'Failed to add production item: ' . $e->getMessage()
        ];
    }
}

function executeProduction($conn, $production_order_id) {
    try {
        $conn->begin_transaction();
        
        // Get all production items for this order
        $items_query = "
            SELECT pi.id, pi.item_id, pi.quantity, pi.unit_cost, pi.total_cost
            FROM production_items pi
            WHERE pi.production_order_id = ? AND pi.status = 'pending'
        ";
        
        $stmt = $conn->prepare($items_query);
        $stmt->bind_param("i", $production_order_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $total_order_cost = 0;
        
        while ($item = $result->fetch_assoc()) {
            $production_item_id = $item['id'];
            $item_id = $item['item_id'];
            $quantity = $item['quantity'];
            $item_cost = $item['unit_cost'];
            
            // 1. Add finished product to inventory
            $add_inventory = $conn->prepare("
                INSERT INTO inventory_movements (item_id, movement_type, reference_type, reference_id, quantity) 
                VALUES (?, 'in', 'production', ?, ?)
            ");
            $add_inventory->bind_param("iid", $item_id, $production_order_id, $quantity);
            $add_inventory->execute();
            $add_inventory->close();
            
            // 2. Get BOM materials and deduct them
            $bom_query = "
                SELECT bc.component_item_id, bc.quantity
                FROM bom_master bm
                JOIN bom_components bc ON bm.id = bc.bom_id
                WHERE bm.item_id = ?
            ";
            
            $bom_stmt = $conn->prepare($bom_query);
            $bom_stmt->bind_param("i", $item_id);
            $bom_stmt->execute();
            $bom_result = $bom_stmt->get_result();
            
            while ($bom = $bom_result->fetch_assoc()) {
                $material_id = $bom['component_item_id'];
                $material_qty = $bom['quantity'] * $quantity;
                
                // Get material cost
                $cost_query = $conn->prepare("SELECT cost_price FROM items WHERE id = ?");
                $cost_query->bind_param("i", $material_id);
                $cost_query->execute();
                $cost_result = $cost_query->get_result();
                $material_cost = $cost_result->fetch_assoc()['cost_price'] ?? 0;
                $cost_query->close();
                
                // Deduct material from inventory
                $deduct_material = $conn->prepare("
                    INSERT INTO inventory_movements (item_id, movement_type, reference_type, reference_id, quantity) 
                    VALUES (?, 'out', 'production', ?, ?)
                ");
                $deduct_material->bind_param("iid", $material_id, $production_order_id, $material_qty);
                $deduct_material->execute();
                $deduct_material->close();
                
                // Record material consumption
                $material_total_cost = $material_qty * $material_cost;
                $record_material = $conn->prepare("
                    INSERT INTO production_materials (production_item_id, material_item_id, quantity_consumed, unit_cost, total_cost) 
                    VALUES (?, ?, ?, ?, ?)
                ");
                $record_material->bind_param("iiddd", $production_item_id, $material_id, $material_qty, $material_cost, $material_total_cost);
                $record_material->execute();
                $record_material->close();
            }
            
            $bom_stmt->close();
            
            // 3. Mark production item as completed
            $update_item = $conn->prepare("UPDATE production_items SET status = 'completed' WHERE id = ?");
            $update_item->bind_param("i", $production_item_id);
            $update_item->execute();
            $update_item->close();
            
            $total_order_cost += $item['total_cost'];
        }
        
        // 4. Update production order status and total cost
        $update_order = $conn->prepare("
            UPDATE production_orders 
            SET status = 'completed', total_cost = ? 
            WHERE id = ?
        ");
        $update_order->bind_param("di", $total_order_cost, $production_order_id);
        $update_order->execute();
        $update_order->close();
        
        $conn->commit();
        
        return [
            'success' => true,
            'message' => 'Production completed successfully',
            'total_cost' => $total_order_cost
        ];
        
    } catch (Exception $e) {
        $conn->rollback();
        return [
            'success' => false,
            'error' => 'Failed to execute production: ' . $e->getMessage()
        ];
    }
}

function calculateBOMCost($conn, $item_id) {
    $query = "
        SELECT SUM(bc.quantity * i.cost_price) as total_cost
        FROM bom_master bm
        JOIN bom_components bc ON bm.id = bc.bom_id
        JOIN items i ON bc.component_item_id = i.id
        WHERE bm.item_id = ?
    ";
    
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $item_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    return $row['total_cost'] ?? 0;
}

function generateProductionOrderNumber($conn) {
    $max_attempts = 10;
    $attempt = 0;
    
    do {
        $attempt++;
        $timestamp = time();
        $random = mt_rand(1000, 9999); // Add random component
        $order_number = 'PROD' . $timestamp . $random;
        
        // Check if this order number already exists
        $check_stmt = $conn->prepare("SELECT id FROM production_orders WHERE order_number = ?");
        $check_stmt->bind_param("s", $order_number);
        $check_stmt->execute();
        $result = $check_stmt->get_result();
        $check_stmt->close();
        
        if ($result->num_rows === 0) {
            return $order_number; // Unique order number found
        }
        
        // If we've tried too many times, use microtime for uniqueness
        if ($attempt >= $max_attempts) {
            $microtime = microtime(true);
            $microtime_str = str_replace('.', '', number_format($microtime, 6));
            $order_number = 'PROD' . $microtime_str;
            break;
        }
        
    } while (true);
    
    return $order_number;
}

function getProductionOrders($conn, $status = null) {
    $where_clause = $status ? "WHERE po.status = ?" : "";
    $params = $status ? [$status] : [];
    $types = $status ? "s" : "";
    
    $query = "
        SELECT 
            po.id,
            po.order_number,
            po.production_date,
            po.status,
            po.remarks,
            po.total_cost,
            po.created_at,
            u.username as created_by_name,
            COUNT(pi.id) as item_count
        FROM production_orders po
        LEFT JOIN users u ON po.created_by = u.id
        LEFT JOIN production_items pi ON po.id = pi.production_order_id
        $where_clause
        GROUP BY po.id
        ORDER BY po.created_at DESC
    ";
    
    $stmt = $conn->prepare($query);
    if ($status) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    
    $orders = [];
    while ($row = $result->fetch_assoc()) {
        $orders[] = $row;
    }
    
    $stmt->close();
    return $orders;
}

function getProductionOrderDetails($conn, $production_order_id) {
    $query = "
        SELECT 
            po.*,
            u.username as created_by_name
        FROM production_orders po
        LEFT JOIN users u ON po.created_by = u.id
        WHERE po.id = ?
    ";
    
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $production_order_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $order = $result->fetch_assoc();
    $stmt->close();
    
    if (!$order) {
        return null;
    }
    
    // Get production items
    $items_query = "
        SELECT 
            pi.*,
            i.name as item_name,
            i.item_code
        FROM production_items pi
        JOIN items i ON pi.item_id = i.id
        WHERE pi.production_order_id = ?
        ORDER BY pi.id
    ";
    
    $items_stmt = $conn->prepare($items_query);
    $items_stmt->bind_param("i", $production_order_id);
    $items_stmt->execute();
    $items_result = $items_stmt->get_result();
    
    $items = [];
    while ($item = $items_result->fetch_assoc()) {
        $items[] = $item;
    }
    
    $items_stmt->close();
    $order['items'] = $items;
    
    return $order;
}
?>
