<?php
require_once '../includes/auth.php';
require_once '../includes/db.php';
require_once '../includes/inventory_helper.php';

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

if (!isLoggedIn() || !hasRole(['owner', 'admin', 'manager', 'supervisor'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

// Handle JSON, POST, and GET data
$json = file_get_contents('php://input');
$data = json_decode($json, true) ?? [];

if (empty($data)) {
    $data = array_merge($_GET, $_POST);
}
$action = $data['action'] ?? '';

switch ($action) {
    case 'check-stock':
        $products = $data['products'] ?? [];
        if (empty($products)) {
            echo json_encode(['success' => true, 'materials' => [], 'sufficient' => true]);
            exit;
        }

        $requirements = [];
        foreach ($products as $p) {
            $productId = $p['id'] ?? null;
            if (!$productId) continue;
            $qty = (float)($p['quantity'] ?? 0);
            
            // Fetch BOM for this product
            $bom = fetchAll("
                SELECT iip.*, ii.name as material_name, ii.stock as available_stock, ii.unit
                FROM inventory_item_product iip
                JOIN inventory_items ii ON iip.inventory_item_id = ii.id
                WHERE iip.product_id = ?
            ", [$productId]);

            foreach ($bom as $item) {
                $materialId = $item['inventory_item_id'];
                // Requirement = (Qty per unit + Wastage per unit) * Product Quantity
                $needed = ($item['quantity'] + $item['wastage_quantity']) * $qty;
                
                if (!isset($requirements[$materialId])) {
                    $requirements[$materialId] = [
                        'id' => $materialId,
                        'name' => $item['material_name'],
                        'unit' => $item['unit'],
                        'required' => 0,
                        'available' => (float)$item['available_stock']
                    ];
                }
                $requirements[$materialId]['required'] += $needed;
            }
        }

        $sufficient = true;
        foreach ($requirements as &$req) {
            $req['shortage'] = max(0, $req['required'] - $req['available']);
            if ($req['shortage'] > 0) $sufficient = false;
        }

        echo json_encode([
            'success' => true, 
            'materials' => array_values($requirements), 
            'sufficient' => $sufficient
        ]);
        break;

    case 'finish-line':
        $id = $data['id'] ?? '';
        $userId = $_SESSION['user_id'] ?? null;
        $company_id = $_SESSION['company_id'];
        
        try {
            $pdo->beginTransaction();

            // 1. Deduct stock based on Stage 1 completion
            $deduction = deductProductionStock($id, $userId);
            if (!$deduction['success']) {
                throw new Exception($deduction['message']);
            }

            // 1.5 Unreserve stock for the whole line
            unreserveProductionStock($id);

            // 2. Fetch Line Details and Stage 1 Results
            $line = fetch("SELECT * FROM production_lines WHERE id = ?", [$id]);
            $stage1 = fetch("SELECT completed_units FROM line_stages WHERE production_line_id = ? ORDER BY sort_order ASC LIMIT 1", [$id]);
            $completedUnits = $stage1['completed_units'] ?? 0;

            // 3. Create Main Report Record (The Financial Snapshot)
            query("INSERT INTO production_reports (production_line_id, company_id, line_name, started_at, finished_at, total_units_produced) VALUES (?, ?, ?, ?, NOW(), ?)", [
                $id, $company_id, $line['name'], $line['start_date'], $completedUnits
            ]);
            $reportId = $pdo->lastInsertId();

            // 4. Capture Materials Used (from the deduction log just created by deductProductionStock)
            $lastLog = fetch("SELECT id FROM production_completion_logs WHERE production_line_id = ? ORDER BY created_at DESC LIMIT 1", [$id]);
            $logId = $lastLog['id'];
            
            $materials = fetchAll("
                SELECT pcm.inventory_item_id, pcm.deducted_qty, ii.sku, ii.name, ii.unit, ii.buying_unit_price 
                FROM production_completion_materials pcm
                JOIN inventory_items ii ON pcm.inventory_item_id = ii.id
                WHERE pcm.completion_log_id = ?
            ", [$logId]);

            $totalMaterialCost = 0;
            foreach ($materials as $m) {
                $unitPrice = (float)$m['buying_unit_price'];
                $subtotal = (float)$m['deducted_qty'] * $unitPrice;
                $totalMaterialCost += $subtotal;
                query("INSERT INTO report_materials (report_id, inventory_item_id, sku, name, quantity_used, unit, unit_price_at_time, subtotal) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [
                    $reportId, $m['inventory_item_id'], $m['sku'], $m['name'], $m['deducted_qty'], $m['unit'], $unitPrice, $subtotal
                ]);
            }

            // 5. Capture Labor Costs (Aggregate task_time_logs for all tasks in this line)
            $labor = fetchAll("
                SELECT e.id as employee_id, e.name as employee_name, SUM(ttl.hours_spent) as total_hours, e.hourly_rate 
                FROM task_time_logs ttl
                JOIN tasks t ON ttl.task_id = t.id
                JOIN employees e ON ttl.user_id = e.user_id
                JOIN line_stages ls ON t.line_stage_id = ls.id
                WHERE ls.production_line_id = ?
                GROUP BY e.id
            ", [$id]);

            $totalLaborCost = 0;
            foreach ($labor as $l) {
                $hours = (float)$l['total_hours'];
                $rate = (float)($l['hourly_rate'] ?: 0);
                $subtotal = $hours * $rate;
                $totalLaborCost += $subtotal;
                query("INSERT INTO report_labor (report_id, employee_id, employee_name, total_hours, hourly_rate_at_time, subtotal) VALUES (?, ?, ?, ?, ?, ?)", [
                    $reportId, $l['employee_id'], $l['employee_name'], $hours, $rate, $subtotal
                ]);
            }

            // 6. Capture Products Produced (Style Breakdown)
            $products = fetchAll("
                SELECT plp.product_id, p.sku, p.name, plp.quantity 
                FROM production_line_products plp
                JOIN products p ON plp.product_id = p.id
                WHERE plp.production_line_id = ?
            ", [$id]);

            foreach ($products as $p) {
                // Calculate estimated cost per unit for this style in this run
                $totalRunCost = $totalMaterialCost + $totalLaborCost;
                $costPerUnit = $completedUnits > 0 ? ($totalRunCost / $completedUnits) : 0;
                
                query("INSERT INTO report_products (report_id, product_id, product_sku, product_name, quantity_produced, cost_per_unit) VALUES (?, ?, ?, ?, ?, ?)", [
                    $reportId, $p['product_id'], $p['sku'], $p['name'], $p['quantity'], $costPerUnit
                ]);
            }

            // 7. Update Master Report with final totals
            query("UPDATE production_reports SET total_material_cost = ?, total_labor_cost = ?, grand_total_cost = ? WHERE id = ?", [
                $totalMaterialCost, $totalLaborCost, ($totalMaterialCost + $totalLaborCost), $reportId
            ]);

            // 8. Mark the line as finished/inactive
            query("UPDATE production_lines SET status = 'Completed', is_active = 0 WHERE id = ?", [$id]);

            $pdo->commit();
            echo json_encode(['success' => true, 'message' => 'Production finished and comprehensive costing report generated successfully.']);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => 'Failed to finish production: ' . $e->getMessage()]);
        }
        break;

    case 'save':
        $line = $data['line'];
        if (is_string($line)) $line = json_decode($line, true);
        $id = $line['id'] ?? '';
        $company_id = $_SESSION['company_id'];
        
        try {
            $pdo->beginTransaction();

            if ($id) {
                $sql = "UPDATE production_lines SET name=?, start_date=?, end_date=?, status=? WHERE id=? AND company_id=?";
                query($sql, [$line['name'], $line['start_date'], $line['end_date'], $line['status'] ?? 'Active', $id, $company_id]);
                $lineId = $id;
            } else {
                $sql = "INSERT INTO production_lines (company_id, name, start_date, end_date, status) VALUES (?, ?, ?, ?, ?)";
                query($sql, [$company_id, $line['name'], $line['start_date'], $line['end_date'], 'Active']);
                $lineId = $pdo->lastInsertId();
            }

            // If stages are provided, mark as initialized
            if (!empty($line['stages'])) {
                query("UPDATE production_lines SET is_initialized = 1 WHERE id = ?", [$lineId]);
            }

            // Save Product mappings
            query("DELETE FROM production_line_products WHERE production_line_id = ?", [$lineId]);
            if (!empty($line['products'])) {
                foreach ($line['products'] as $p) {
                    query("INSERT INTO production_line_products (production_line_id, product_id, quantity) VALUES (?, ?, ?)", [$lineId, $p['id'], $p['quantity']]);
                }
            }

            // Save Stage mappings (Workflow Sequence & Resources)
            query("DELETE FROM line_stages WHERE production_line_id = ?", [$lineId]);
            if (!empty($line['stages'])) {
                foreach ($line['stages'] as $index => $stage) {
                    query("INSERT INTO line_stages (production_line_id, workflow_stage_id, sort_order, start_date, end_date, quantity, completed_units, current_status_note) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [
                        $lineId, 
                        $stage['workflow_stage_id'], 
                        $index + 1,
                        $stage['start_date'],
                        $stage['end_date'],
                        $stage['quantity'] ?? 0,
                        0, // completed_units initial
                        $stage['remarks']
                    ]);
                    $lineStageId = $pdo->lastInsertId();

                    // Save Staff
                    if (!empty($stage['staff_ids'])) {
                        foreach ($stage['staff_ids'] as $staffId) {
                            query("INSERT INTO line_stage_staff (line_stage_id, employee_id) VALUES (?, ?)", [$lineStageId, $staffId]);
                        }
                    }

                    // Save Machinery
                    if (!empty($stage['machine_ids'])) {
                        foreach ($stage['machine_ids'] as $machineId) {
                            query("INSERT INTO line_stage_machinery (line_stage_id, machinery_id) VALUES (?, ?)", [$lineStageId, $machineId]);
                        }
                    }

                    // AUTO-ALLOCATE TASKS for the FIRST STAGE employees
                    if ($index === 0) {
                        $stageName = fetch("SELECT name FROM workflow_stages WHERE id = ?", [$stage['workflow_stage_id']])['name'];
                        foreach ($stage['staff_ids'] as $staffId) {
                            query("INSERT INTO tasks (employee_id, title, description, status, start_date, due_date, line_stage_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())", [
                                $staffId,
                                "Production: " . $line['name'],
                                "Work Assignment: $stageName. Target: " . ($stage['quantity'] ?? 0) . " units.",
                                'Pending',
                                $stage['start_date'] ?: $line['start_date'],
                                $stage['end_date'] ?: $line['end_date'],
                                $lineStageId
                            ]);
                        }
                    }
                }
            }

            // Reserve stock if initialized and not already reserved
            if (!empty($line['stages'])) {
                reserveProductionStock($lineId);
            }

            $pdo->commit();
            
            echo json_encode(['success' => true, 'message' => $id ? 'Production line updated' : 'New production line initialized']);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    case 'toggle-status':
        $id = $data['id'];
        $is_active = $data['status']; // 0 or 1
        $status_text = $is_active ? 'Active' : 'Inactive';
        query("UPDATE production_lines SET is_active = ?, status = ? WHERE id = ?", [$is_active, $status_text, $id]);
        echo json_encode(['success' => true, 'message' => "Production line set to $status_text"]);
        break;

    case 'get-update-data':
        $id = $_GET['id'] ?? $data['id'];
        $line = fetch("
            SELECT pl.*, 
                   (SELECT SUM(quantity) FROM production_line_products WHERE production_line_id = pl.id) as total_quantity
            FROM production_lines pl 
            WHERE pl.id = ?
        ", [$id]);

        if ($line) {
            $line['products'] = fetchAll("SELECT p.id, p.name, p.sku, lp.quantity FROM production_line_products lp JOIN products p ON lp.product_id = p.id WHERE lp.production_line_id = ?", [$id]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Line not found']);
            exit;
        }

        // Fetch stages with resources and logs
        $stages = fetchAll("
            SELECT ls.*, ws.name as stage_name, ws.color_code 
            FROM line_stages ls 
            JOIN workflow_stages ws ON ls.workflow_stage_id = ws.id 
            WHERE ls.production_line_id = ? 
            ORDER BY ls.sort_order ASC
        ", [$id]);

        if (!empty($stages)) {
            $stageIds = array_column($stages, 'id');
            $placeholders = implode(',', array_fill(0, count($stageIds), '?'));

            // Bulk fetch Staff
            $allStaff = fetchAll("
                SELECT lss.line_stage_id, e.id, e.name, e.profile_picture,
                       (SELECT SUM(hours_spent) FROM task_time_logs tl JOIN tasks t ON tl.task_id = t.id WHERE t.employee_id = e.id AND t.line_stage_id = lss.line_stage_id) as total_hours
                FROM line_stage_staff lss 
                JOIN employees e ON lss.employee_id = e.id 
                WHERE lss.line_stage_id IN ($placeholders)
            ", $stageIds);

            // Bulk fetch Logs
            $allLogs = fetchAll("
                SELECT tl.*, t.title as task_title, t.line_stage_id, t.employee_id, ts.name as status_name, ts.color_code as status_color
                FROM task_time_logs tl 
                JOIN tasks t ON tl.task_id = t.id 
                LEFT JOIN task_statuses ts ON tl.task_status_id = ts.id
                WHERE t.line_stage_id IN ($placeholders)
                ORDER BY tl.log_date DESC, tl.id DESC
            ", $stageIds);

            if (!empty($allLogs)) {
                $logIds = array_column($allLogs, 'id');
                $logPlaceholders = implode(',', array_fill(0, count($logIds), '?'));
                $allTaskAttachments = fetchAll("SELECT * FROM task_log_attachments WHERE log_id IN ($logPlaceholders)", $logIds);
            } else {
                $allTaskAttachments = [];
            }

            // Bulk fetch Machinery
            $allMachinery = fetchAll("
                SELECT lsm.line_stage_id, m.name, m.model_no 
                FROM line_stage_machinery lsm 
                JOIN machinery m ON lsm.machinery_id = m.id 
                WHERE lsm.line_stage_id IN ($placeholders)
            ", $stageIds);

            // Bulk fetch Updates
            $allUpdates = fetchAll("
                SELECT u.*, e.name as manager_name, e.profile_picture as manager_avatar, r.name as manager_role
                FROM line_stage_updates u
                JOIN users usr ON u.user_id = usr.id
                LEFT JOIN employees e ON usr.id = e.user_id
                LEFT JOIN roles r ON e.role_id = r.id
                WHERE u.line_stage_id IN ($placeholders)
                ORDER BY u.created_at DESC
            ", $stageIds);

            if (!empty($allUpdates)) {
                $updateIds = array_column($allUpdates, 'id');
                $updPlaceholders = implode(',', array_fill(0, count($updateIds), '?'));
                $allAttachments = fetchAll("SELECT * FROM line_stage_update_attachments WHERE update_id IN ($updPlaceholders)", $updateIds);
            } else {
                $allAttachments = [];
            }

            // Map data back to stages
            foreach ($stages as &$ls) {
                $ls['staff'] = array_values(array_filter($allStaff, function($s) use ($ls) { return $s['line_stage_id'] == $ls['id']; }));
                foreach ($ls['staff'] as &$s) {
                    $s['logs'] = array_values(array_filter($allLogs, function($l) use ($ls, $s) { 
                        return $l['line_stage_id'] == $ls['id'] && $l['employee_id'] == $s['id']; 
                    }));
                    foreach ($s['logs'] as &$log) {
                        $log['attachments'] = array_values(array_filter($allTaskAttachments, function($a) use ($log) { 
                            return $a['log_id'] == $log['id']; 
                        }));
                    }
                }
                $ls['machinery'] = array_values(array_filter($allMachinery, function($m) use ($ls) { return $m['line_stage_id'] == $ls['id']; }));
                $ls['updates'] = array_values(array_filter($allUpdates, function($u) use ($ls) { return $u['line_stage_id'] == $ls['id']; }));
                foreach ($ls['updates'] as &$upd) {
                    $upd['attachments'] = array_values(array_filter($allAttachments, function($a) use ($upd) { return $a['update_id'] == $upd['id']; }));
                }
            }
        }

        echo json_encode(['success' => true, 'data' => [
            'line' => $line, 
            'stages' => $stages,
            'statuses' => fetchAll("SELECT name, internal_value, color_code FROM workflow_statuses ORDER BY id ASC")
        ]]);
        break;

    case 'save-stage-update':
        $lineStageId = $_POST['line_stage_id'];
        $units = $_POST['completed_units'] ?? 0;
        $comments = $_POST['comments'] ?? '';
        $status = $_POST['status'] ?? 'Operational';
        $userId = $_SESSION['user_id'];

        // Step 1: Pre-process file uploads OUTSIDE the transaction
        $uploadedFiles = [];
        if (isset($_FILES['attachments'])) {
            $uploadDir = '../uploads/stages/';
            if (!is_dir($uploadDir)) mkdir($uploadDir, 0777, true);
            
            foreach ($_FILES['attachments']['name'] as $key => $name) {
                if ($_FILES['attachments']['error'][$key] === UPLOAD_ERR_OK) {
                    $ext = pathinfo($name, PATHINFO_EXTENSION);
                    $fileName = time() . '_stg_' . uniqid() . '_' . $key . '.' . $ext;
                    if (move_uploaded_file($_FILES['attachments']['tmp_name'][$key], $uploadDir . $fileName)) {
                        $uploadedFiles[] = [
                            'path' => 'uploads/stages/' . $fileName,
                            'name' => $name
                        ];
                    }
                }
            }
        }

        try {
            $pdo->beginTransaction();
            
            // Get current stage status before update
            $currentStage = fetch("SELECT status FROM line_stages WHERE id = ?", [$lineStageId]);
            $oldStatus = $currentStage['status'] ?? 'WIP';

            // Check if this is a status-changing event
            $isStatusChange = ($status === 'Blocked' && $oldStatus !== 'Blocked') || ($oldStatus === 'Blocked' && ($status === 'Operational' || $status === 'Unblocked' || $status === 'WIP'));

            // Step 2: Database updates
            query("INSERT INTO line_stage_updates (line_stage_id, user_id, completed_units, comments, status, is_status_change) VALUES (?, ?, ?, ?, ?, ?)", [
                $lineStageId, $userId, $units, $comments, $status, $isStatusChange ? 1 : 0
            ]);
            $updateId = $pdo->lastInsertId();

            query("UPDATE line_stages SET completed_units = completed_units + ? WHERE id = ?", [$units, $lineStageId]);

            if ($status === 'Blocked') {
                query("UPDATE line_stages SET status = 'Blocked', current_status_note = ? WHERE id = ?", [$comments, $lineStageId]);
                $line = fetch("SELECT production_line_id FROM line_stages WHERE id = ?", [$lineStageId]);
                if ($line) {
                    query("UPDATE production_lines SET status = 'Blocked' WHERE id = ?", [$line['production_line_id']]);
                }
            } else if (($status === 'Operational' || $status === 'Unblocked' || $status === 'WIP') && $oldStatus === 'Blocked') {
                query("UPDATE line_stages SET status = 'WIP', current_status_note = NULL WHERE id = ?", [$lineStageId]);
                $line = fetch("SELECT production_line_id FROM line_stages WHERE id = ?", [$lineStageId]);
                if ($line) {
                    $otherBlocked = fetch("SELECT id FROM line_stages WHERE production_line_id = ? AND status = 'Blocked' LIMIT 1", [$line['production_line_id']]);
                    if (!$otherBlocked) {
                        $lineDetails = fetch("SELECT is_active FROM production_lines WHERE id = ?", [$line['production_line_id']]);
                        $newStatus = ($lineDetails['is_active'] ?? 1) ? 'Active' : 'Inactive';
                        query("UPDATE production_lines SET status = ? WHERE id = ?", [$newStatus, $line['production_line_id']]);
                    }
                }
            }

            // Step 3: Link already uploaded files in DB
            foreach ($uploadedFiles as $file) {
                query("INSERT INTO line_stage_update_attachments (update_id, file_path, file_name) VALUES (?, ?, ?)", [
                    $updateId, $file['path'], $file['name']
                ]);
            }

            $pdo->commit();
            echo json_encode(['success' => true, 'message' => 'Stage update saved successfully']);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        break;

    case 'delete-stage-update':
        $id = $data['id'];
        try {
            $pdo->beginTransaction();
            // Fetch update with stage status
            $update = fetch("
                SELECT u.*, ls.status as stage_status 
                FROM line_stage_updates u 
                JOIN line_stages ls ON u.line_stage_id = ls.id 
                WHERE u.id = ?
            ", [$id]);

            if ($update) {
                // Protect completed stage logs
                if ($update['stage_status'] === 'Completed') {
                    throw new Exception("Logs from completed stages cannot be deleted to maintain production history integrity.");
                }

                // Protect status-change logs (Blocked/Unblocked events)
                if ($update['is_status_change']) {
                    throw new Exception("Operational status change logs (Blocked/Unblocked) cannot be deleted to maintain audit integrity.");
                }

                // Check if same day (allow managers/owners to delete same-day logs for corrections)
                $isToday = date('Y-m-d', strtotime($update['created_at'])) === date('Y-m-d');
                if ($isToday || hasRole(['owner', 'admin'])) {
                    // Update cumulative units in line_stages
                    query("UPDATE line_stages SET completed_units = GREATEST(0, completed_units - ?) WHERE id = ?", [$update['completed_units'], $update['line_stage_id']]);
                    
                    // Cleanup attachments
                    $attachments = fetchAll("SELECT * FROM line_stage_update_attachments WHERE update_id = ?", [$id]);
                    foreach ($attachments as $a) {
                        $filePath = '../' . $a['file_path'];
                        if (file_exists($filePath)) unlink($filePath);
                    }
                    
                    query("DELETE FROM line_stage_update_attachments WHERE update_id = ?", [$id]);
                    query("DELETE FROM line_stage_updates WHERE id = ?", [$id]);
                    
                    $pdo->commit();
                    echo json_encode(['success' => true, 'message' => 'Log entry deleted and totals recalculated']);
                } else {
                    throw new Exception("Only current-day logs can be deleted for correction.");
                }
            } else {
                throw new Exception("Log entry not found");
            }
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        break;

    case 'complete-stage':
        $lineStageId = $data['line_stage_id'];
        try {
            $pdo->beginTransaction();

            // Mark current stage as completed
            query("UPDATE line_stages SET status = 'Completed', updated_at = NOW() WHERE id = ?", [$lineStageId]);
            
            // Suspend all non-completed tasks for this stage
            query("UPDATE tasks SET status = 'Suspended', updated_at = NOW() WHERE line_stage_id = ? AND status != 'Completed'", [$lineStageId]);
            
            // Get current stage info
            $current = fetch("SELECT * FROM line_stages WHERE id = ?", [$lineStageId]);
            $lineId = $current['production_line_id'];
            $sortOrder = $current['sort_order'];

            // Find NEXT stage
            $next = fetch("SELECT * FROM line_stages WHERE production_line_id = ? AND sort_order > ? ORDER BY sort_order ASC LIMIT 1", [$lineId, $sortOrder]);

            if ($next) {
                // Set next stage to 'WIP'
                query("UPDATE line_stages SET status = 'WIP', updated_at = NOW() WHERE id = ?", [$next['id']]);
                
                // AUTO-ASSIGN TASKS for the NEXT stage staff
                $staff = fetchAll("SELECT employee_id FROM line_stage_staff WHERE line_stage_id = ?", [$next['id']]);
                $lineName = fetch("SELECT name FROM production_lines WHERE id = ?", [$lineId])['name'];
                $stageName = fetch("SELECT name FROM workflow_stages WHERE id = ?", [$next['workflow_stage_id']])['name'];

                foreach ($staff as $s) {
                    // Check if task already exists for this stage/employee to avoid duplicates
                    $exists = fetch("SELECT id FROM tasks WHERE employee_id = ? AND line_stage_id = ?", [$s['employee_id'], $next['id']]);
                    if (!$exists) {
                        query("INSERT INTO tasks (employee_id, title, description, status, start_date, due_date, line_stage_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())", [
                            $s['employee_id'],
                            "Production: $lineName - $stageName",
                            "New assignment: Proceed with '$stageName' stage. Target units: " . ($next['quantity'] ?? 0) . ". Work should begin as per the schedule.",
                            'Pending',
                            $next['start_date'],
                            $next['end_date'],
                            $next['id']
                        ]);
                    }
                }
                $msg = "Stage completed. Next stage ($stageName) is now Active.";
            } else {
                // No more stages, don't auto-complete. Wait for manager to click "Finish Production"
                $msg = "All stages completed. Please proceed to Final Review to finish production and update stock.";
            }

            $pdo->commit();
            echo json_encode(['success' => true, 'message' => $msg]);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
        break;

    case 'delete':
        $id = $data['id'] ?? '';
        if (!$id) {
            echo json_encode(['success' => false, 'message' => 'Line ID is required']);
            exit;
        }

        try {
            $pdo->beginTransaction();

            // 1. Nullify references in orders
            query("UPDATE orders SET production_line_id = NULL WHERE production_line_id = ?", [$id]);

            // 2. Get all line stages to cleanup attachments
            $stages = fetchAll("SELECT id FROM line_stages WHERE production_line_id = ?", [$id]);
            $stageIds = array_column($stages, 'id');

            if (!empty($stageIds)) {
                $placeholders = implode(',', array_fill(0, count($stageIds), '?'));

                // 3. Cleanup line_stage_update_attachments and files
                $updAttachments = fetchAll("
                    SELECT a.* FROM line_stage_update_attachments a
                    JOIN line_stage_updates u ON a.update_id = u.id
                    WHERE u.line_stage_id IN ($placeholders)
                ", $stageIds);
                foreach ($updAttachments as $a) {
                    $filePath = '../' . $a['file_path'];
                    if (file_exists($filePath)) unlink($filePath);
                }
                query("DELETE a FROM line_stage_update_attachments a JOIN line_stage_updates u ON a.update_id = u.id WHERE u.line_stage_id IN ($placeholders)", $stageIds);

                // 4. Cleanup task_log_attachments and files
                $taskAttachments = fetchAll("
                    SELECT a.* FROM task_log_attachments a
                    JOIN task_time_logs tl ON a.log_id = tl.id
                    JOIN tasks t ON tl.task_id = t.id
                    WHERE t.line_stage_id IN ($placeholders)
                ", $stageIds);
                foreach ($taskAttachments as $a) {
                    $filePath = '../' . $a['file_path'];
                    if (file_exists($filePath)) unlink($filePath);
                }
                query("DELETE a FROM task_log_attachments a JOIN task_time_logs tl ON a.log_id = tl.id JOIN tasks t ON tl.task_id = t.id WHERE t.line_stage_id IN ($placeholders)", $stageIds);

                // 5. Delete updates, logs, tasks, staff/machine assignments
                query("DELETE FROM line_stage_updates WHERE line_stage_id IN ($placeholders)", $stageIds);
                query("DELETE tl FROM task_time_logs tl JOIN tasks t ON tl.task_id = t.id WHERE t.line_stage_id IN ($placeholders)", $stageIds);
                query("DELETE FROM tasks WHERE line_stage_id IN ($placeholders)", $stageIds);
                query("DELETE FROM line_stage_staff WHERE line_stage_id IN ($placeholders)", $stageIds);
                query("DELETE FROM line_stage_machinery WHERE line_stage_id IN ($placeholders)", $stageIds);
                query("DELETE FROM line_stages WHERE production_line_id = ?", [$id]);
            }

            // 6. Delete product mappings and the line itself
            query("DELETE FROM production_line_products WHERE production_line_id = ?", [$id]);
            query("DELETE FROM production_lines WHERE id = ?", [$id]);

            $pdo->commit();
            echo json_encode(['success' => true, 'message' => 'Production line and all associated data deleted successfully']);
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        }
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}
