<?php
// =========================================================================
// ENTERPRISE DISPENSARY API ENGINE (3-TIER WORKFLOW)
// =========================================================================
if (session_status() === PHP_SESSION_NONE) { session_start(); }
ini_set('display_errors', 0);
error_reporting(E_ALL);
ob_start();

require_once dirname(__DIR__) . '/db.php';
header('Content-Type: application/json');

$staff_id = (int)($_SESSION['staff_id'] ?? 0);
if ($staff_id <= 0) {
    $getAnyStaff = $conn->query("SELECT staff_id FROM staff LIMIT 1");
    $staff_id = ($getAnyStaff && $getAnyStaff->num_rows > 0) ? (int)$getAnyStaff->fetch_assoc()['staff_id'] : 1;
}

$action = $_POST['action'] ?? $_GET['action'] ?? '';

// System Rules
$sys_res = $conn->query("SELECT date_format, time_format, strict_prepay FROM system_workflow_rules LIMIT 1");
$sys = $sys_res ? $sys_res->fetch_assoc() : [];
$df = $sys['date_format'] ?? 'd-M-Y';
$tf = ($sys['time_format'] ?? '24') == '24' ? 'H:i' : 'h:i A';
$global_date_format = "$df, $tf";
$strict_prepay = (int)($sys['strict_prepay'] ?? 1);

try {
    // 1. LIVE PATIENT SEARCH
    if ($action === 'search_patient') {
        $term = $conn->real_escape_string($_GET['term'] ?? '');
        $sql = "SELECT v.id as visit_id, p.patient_no, p.first_name, p.last_name, v.payment_category 
                FROM visit_master v 
                JOIN patient p ON v.patient_no = p.patient_no 
                WHERE p.first_name LIKE '%$term%' OR p.last_name LIKE '%$term%' OR p.patient_no LIKE '%$term%' 
                ORDER BY v.id DESC LIMIT 15";
        $res = $conn->query($sql);
        $data = [];
        if ($res) {
            while ($row = $res->fetch_assoc()) {
                $data[] = ['id' => $row['visit_id'], 'text' => $row['first_name'] . ' ' . $row['last_name'] . ' (' . $row['patient_no'] . ') - ' . $row['payment_category']];
            }
        }
        if(ob_get_length()) ob_end_clean(); echo json_encode(['results' => $data]); exit;
    }

    // 2. INITIALIZE DEPENDENCIES
    if ($action === 'fetch_dependencies') {
        $locs = []; $drugs = []; $freqs = [];

        $qL = $conn->query("SELECT id, location_name FROM inventory_locations WHERE is_active = 1 ORDER BY location_name ASC");
        if ($qL) while ($r = $qL->fetch_assoc()) $locs[] = $r;

        $qD = $conn->query("SELECT id, service_name, drug_formulation, drug_strength, strength_unit FROM service_catalog WHERE service_type='Drug' AND is_active=1 ORDER BY service_name ASC");
        if ($qD) {
            while ($r = $qD->fetch_assoc()) {
                $drugs[] = [
                    'id' => $r['id'],
                    'name' => trim($r['service_name']),
                    'form' => $r['drug_formulation'] ?? 'Standard',
                    'str' => trim(($r['drug_strength'] ?? '') . ' ' . ($r['strength_unit'] ?? ''))
                ];
            }
        }

        $qF = $conn->query("SELECT freq_code, description FROM drug_frequency ORDER BY id ASC");
        if ($qF) while ($r = $qF->fetch_assoc()) $freqs[] = $r;

        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'locations' => $locs, 'drugs' => $drugs, 'frequencies' => $freqs]); exit;
    }

    // 3. FETCH PENDING QUEUE LIST
    if ($action === 'fetch_pending_queue') {
        $loc = $_POST['location_id'] ?? 'ALL';
        $start = $_POST['start_date'] ?? date('Y-m-01');
        $end = $_POST['end_date'] ?? date('Y-m-t');
        $ins = $_POST['insurance'] ?? 'ALL';
        $p_type = $_POST['patient_type'] ?? 'ALL';

        $locName = '';
        if ($loc !== 'ALL') {
            $q = $conn->query("SELECT location_name FROM inventory_locations WHERE id = " . (int)$loc);
            $locName = $q ? ($q->fetch_assoc()['location_name'] ?? '') : '';
        }

        $sql = "SELECT DISTINCT cp.visit_id, v.payment_category, v.nhis_service_type 
                FROM clinical_prescriptions cp
                JOIN visit_master v ON cp.visit_id = v.id
                WHERE cp.status IN ('Pending Pharmacy', 'Billed', 'Partially Dispensed') 
                AND DATE(cp.created_at) BETWEEN '$start' AND '$end'";
        
        if ($locName !== '') $sql .= " AND cp.dispensing_pharmacy = '" . $conn->real_escape_string($locName) . "'";
        if ($ins !== 'ALL') $sql .= " AND v.payment_category = '" . $conn->real_escape_string($ins) . "'";
        if ($p_type !== 'ALL') $sql .= " AND v.nhis_service_type = '" . $conn->real_escape_string($p_type) . "'";

        $res = $conn->query($sql);
        $data = [];
        
        if ($res) {
            while ($row = $res->fetch_assoc()) {
                $v_id = (int)$row['visit_id'];
                
                $qPat = $conn->query("SELECT p.patient_no, p.first_name, p.last_name, g.gender_name FROM visit_master v JOIN patient p ON v.patient_no = p.patient_no LEFT JOIN gender g ON p.gender_id = g.id WHERE v.id = $v_id LIMIT 1");
                $pat = $qPat ? $qPat->fetch_assoc() : null;

                $qStats = $conn->query("SELECT COUNT(id) as total_drugs, MIN(created_at) as requested_at FROM clinical_prescriptions WHERE visit_id = $v_id AND status IN ('Pending Pharmacy', 'Billed', 'Partially Dispensed')");
                $stats = $qStats ? $qStats->fetch_assoc() : null;

                $qDept = $conn->query("SELECT d.department_name FROM clinical_prescriptions cp JOIN staff st ON cp.prescribed_by = st.staff_id JOIN departments d ON st.staff_department_id = d.id WHERE cp.visit_id = $v_id LIMIT 1");
                $origin = $qDept ? ($qDept->fetch_assoc()['department_name'] ?? '') : '';

                if ($pat && $stats && $stats['total_drugs'] > 0) {
                    $data[] = [
                        'visit_id' => $v_id,
                        'patient_name' => trim($pat['first_name'] . ' ' . $pat['last_name']),
                        'patient_no' => $pat['patient_no'],
                        'gender_name' => $pat['gender_name'] ?? 'Unknown',
                        'req_time_fmt' => date($global_date_format, strtotime($stats['requested_at'])),
                        'total_drugs' => $stats['total_drugs'],
                        'insurance' => $row['payment_category'] ?? 'Cash',
                        'origin' => ($row['nhis_service_type'] === 'In-patient') ? 'IPD Ward' : ($origin ?: 'OPD Triage')
                    ];
                }
            }
        }
        usort($data, function($a, $b) { return strtotime($a['req_time_fmt']) - strtotime($b['req_time_fmt']); });
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'data' => $data]); exit;
    }

    // 4. FETCH PATIENT SPECIFIC PRESCRIPTIONS
    if ($action === 'fetch_patient_prescriptions') {
        $visit_id = (int)$_POST['visit_id'];
        $location_id = (int)$_POST['location_id'];
        $tab = $_POST['tab'] ?? 'unbilled';

        $v_q = $conn->query("SELECT nhis_service_type, patient_no FROM visit_master WHERE id = $visit_id");
        $v_info = $v_q ? $v_q->fetch_assoc() : [];
        $nhis_type = $v_info['nhis_service_type'] ?? 'Out-patient';
        $pat_no = $v_info['patient_no'] ?? '';
        
        $unpaid_balance = 0;
        if (!empty($pat_no)) {
            $bal_q = $conn->query("SELECT SUM(balance_due) as total_owed FROM patient_invoices WHERE patient_no = '$pat_no' AND status != 'Paid'");
            $unpaid_balance = (float)($bal_q ? ($bal_q->fetch_assoc()['total_owed'] ?? 0) : 0);
        }

        $dispense_locked = ($strict_prepay === 1 && $unpaid_balance > 0 && $nhis_type !== 'In-patient');

        if ($tab === 'issued') { $status_filter = "IN ('Dispensed', 'Returned')"; }
        elseif ($tab === 'dispense') { $status_filter = "IN ('Billed', 'Partially Dispensed')"; }
        else { $status_filter = "IN ('Pending Pharmacy')"; }

        // CRITICAL FIX: Joining Patient table via Visit Master to prevent Null failures
        $sql = "SELECT p.id as rx_id, p.service_id, p.drug_name, p.formulation, p.strength, p.dose, p.frequency, p.duration_days, p.quantity as qty_prescribed, p.remarks, p.status, p.created_at,
                       COALESCE(stk.quantity, 0) as stock_available, sc.cash_price_adult, v.payment_category, pat.other_insurance_id as insurance_company_id
                FROM clinical_prescriptions p
                JOIN visit_master v ON p.visit_id = v.id
                JOIN patient pat ON v.patient_no = pat.patient_no
                LEFT JOIN inventory_stock stk ON p.service_id = stk.service_id AND stk.location_id = $location_id
                LEFT JOIN service_catalog sc ON p.service_id = sc.id
                WHERE p.visit_id = $visit_id AND p.status $status_filter
                ORDER BY p.id DESC"; 

        $res = $conn->query($sql);
        $prescriptions = [];
        if ($res) {
            while ($row = $res->fetch_assoc()) {
                $row['can_dispense'] = ((int)$row['stock_available'] >= (int)$row['qty_prescribed']);
                $row['req_time_fmt'] = date($global_date_format, strtotime($row['created_at']));
                
                $unit_price = (float)($row['cash_price_adult'] ?? 0);
                if ($row['payment_category'] !== 'Cash' && $row['payment_category'] !== 'NHIS') {
                    $ins_id = (int)$row['insurance_company_id'];
                    $qCorp = $conn->query("SELECT copay_amount_adult FROM service_corporate_tariffs WHERE service_id={$row['service_id']} AND insurance_type_id=$ins_id LIMIT 1");
                    if($qCorp && $c = $qCorp->fetch_assoc()) {
                        $unit_price = (float)$c['copay_amount_adult'];
                    }
                }
                
                $row['unit_price'] = $unit_price;
                $row['total_price'] = $unit_price * (float)$row['qty_prescribed'];

                $prescriptions[] = $row;
            }
        }
        
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'prescriptions' => $prescriptions, 'financials' => ['strict_prepay' => $strict_prepay, 'unpaid_balance' => $unpaid_balance, 'patient_type' => $nhis_type, 'dispense_locked' => $dispense_locked]]); exit;
    }

    // 5. LIVE PHARMACIST NOTES SAVE
    if ($action === 'save_pharmacist_note') {
        $rx_id = (int)$_POST['rx_id'];
        $notes = trim($_POST['notes']);
        $stmt = $conn->prepare("UPDATE clinical_prescriptions SET remarks = ? WHERE id = ?");
        $stmt->bind_param("si", $notes, $rx_id);
        $stmt->execute();
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Notes saved.']); exit;
    }

    // 6. RETURN DRUG ENGINE (RESTORES STOCK)
    if ($action === 'return_drug') {
        $rx_id = (int)$_POST['rx_id'];
        $location_id = (int)$_POST['location_id'];

        $conn->begin_transaction();
        $rx_q = $conn->query("SELECT service_id, quantity, drug_name FROM clinical_prescriptions WHERE id = $rx_id AND status = 'Dispensed'");
        $rx = $rx_q->fetch_assoc();
        if (!$rx) throw new Exception("Drug cannot be returned. It may not have been dispensed yet.");

        $qty_to_return = (int)$rx['quantity'];
        
        $conn->query("UPDATE inventory_stock SET quantity = quantity + $qty_to_return WHERE service_id = {$rx['service_id']} AND location_id = $location_id");
        
        $stmtTxLog = $conn->prepare("INSERT INTO inventory_transactions (service_id, location_id, transaction_type, quantity_changed, reference_id, performed_by) VALUES (?, ?, 'Adjustment', ?, ?, ?)");
        $stmtTxLog->bind_param("iiiii", $rx['service_id'], $location_id, $qty_to_return, $rx_id, $staff_id);
        $stmtTxLog->execute();

        $conn->query("UPDATE clinical_prescriptions SET status = 'Returned', remarks = CONCAT(COALESCE(remarks,''), ' [Returned to Stock]') WHERE id = $rx_id");
        
        $conn->commit();
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Medication returned to stock.']); exit;
    }

    // 7. ADD / UPDATE / REJECT
    if ($action === 'add_prescription' || $action === 'update_prescription') {
        $rx_id = (int)($_POST['rx_id'] ?? 0);
        $dose = trim($_POST['dose']); 
        $freq = trim($_POST['frequency']);
        $dur = (int)$_POST['duration']; 
        $qty = (int)$_POST['total_qty']; 
        $remarks = trim($_POST['remarks'] ?? '');

        if ($action === 'add_prescription') {
            $visit_id = (int)$_POST['visit_id'];
            $loc_id = (int)$_POST['location_id'];
            $svc_id = (int)$_POST['service_id'];

            $dupCheck = $conn->query("SELECT id FROM clinical_prescriptions WHERE visit_id = $visit_id AND service_id = $svc_id AND status NOT IN ('Rejected', 'Cancelled', 'Returned')");
            if ($dupCheck && $dupCheck->num_rows > 0) throw new Exception("Duplicate Entry: Medication already exists in queue.");
            
            $locName = $conn->query("SELECT location_name FROM inventory_locations WHERE id = $loc_id")->fetch_assoc()['location_name'] ?? 'Main Pharmacy';
            $svc = $conn->query("SELECT service_name, drug_formulation, drug_strength, strength_unit FROM service_catalog WHERE id = $svc_id")->fetch_assoc();
            $pat_no = $conn->query("SELECT patient_no FROM visit_master WHERE id = $visit_id")->fetch_assoc()['patient_no'] ?? '';
            $s_val = trim(($svc['drug_strength'] ?? '') . ' ' . ($svc['strength_unit'] ?? ''));

            $stmt = $conn->prepare("INSERT INTO clinical_prescriptions (visit_id, patient_no, service_id, drug_name, formulation, strength, dose, frequency, duration_days, quantity, dispensing_pharmacy, remarks, status, prescribed_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Pending Pharmacy', ?)");
            $stmt->bind_param("isisssssiissi", $visit_id, $pat_no, $svc_id, $svc['service_name'], $svc['drug_formulation'], $s_val, $dose, $freq, $dur, $qty, $locName, $remarks, $staff_id);
            $stmt->execute();
            if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Medication added.']); exit;
        } else {
            $stmt = $conn->prepare("UPDATE clinical_prescriptions SET dose = ?, frequency = ?, duration_days = ?, quantity = ?, remarks = ? WHERE id = ?");
            $stmt->bind_param("ssiisi", $dose, $freq, $dur, $qty, $remarks, $rx_id);
            $stmt->execute();
            if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Prescription updated.']); exit;
        }
    }

    if ($action === 'reject_prescription') {
        $rx_id = (int)$_POST['rx_id'];
        $conn->query("UPDATE clinical_prescriptions SET status = 'Rejected' WHERE id = $rx_id");
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Prescription cancelled.']); exit;
    }

    // 8. BILLING ROUTING
    if ($action === 'bill_prescriptions') {
        $visit_id = (int)$_POST['visit_id'];
        $rx_ids = $_POST['rx_id'] ?? [];
        if (empty($rx_ids)) throw new Exception("No medications selected for billing.");

        $pat_no = $conn->query("SELECT patient_no FROM visit_master WHERE id=$visit_id")->fetch_assoc()['patient_no'] ?? '';
        $total_invoice_amt = 0;

        $conn->begin_transaction();
        
        $stmtVd = $conn->prepare("INSERT INTO visit_details (visit_master_id, service_id, quantity, billed_amount, status) VALUES (?, ?, ?, ?, 'Pending')");

        foreach ($rx_ids as $rx_id) {
            $rx_id = (int)$rx_id;
            $qty = (int)($_POST['dispense_qty'][$rx_id] ?? 0);
            $price = (float)($_POST['unit_price'][$rx_id] ?? 0);
            $svc_id = (int)($_POST['service_id'][$rx_id] ?? 0);
            
            if ($qty <= 0) continue;

            $line_total = $qty * $price;
            $total_invoice_amt += $line_total;
            
            $conn->query("UPDATE clinical_prescriptions SET status = 'Billed', quantity = $qty WHERE id = $rx_id AND status = 'Pending Pharmacy'");
            
            if ($svc_id > 0 && $line_total > 0) {
                $stmtVd->bind_param("iiid", $visit_id, $svc_id, $qty, $line_total);
                $stmtVd->execute();
            }
        }

        if ($total_invoice_amt > 0) {
            $inv_no = 'PHM-' . strtoupper(substr(md5(uniqid()), 0, 8));
            $conn->query("INSERT INTO patient_invoices (patient_no, invoice_no, department_origin, total_amount, balance_due, status, created_at) VALUES ('$pat_no', '$inv_no', 'Pharmacy', $total_invoice_amt, $total_invoice_amt, 'Unpaid', NOW())");
        }

        $conn->commit();
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Items routed to Cashier. Moved to Pending Dispense tab.']); exit;
    }

    // 9. DISPENSE & STOCK DEDUCTION
    if ($action === 'process_dispense') {
        $visit_id = (int)$_POST['visit_id'];
        $location_id = (int)$_POST['location_id'];
        $rx_ids = $_POST['rx_id'] ?? [];
        if (empty($rx_ids) || !$location_id) throw new Exception("No medications selected for dispensing.");

        $pat = $conn->query("SELECT nhis_service_type, patient_no FROM visit_master WHERE id=$visit_id")->fetch_assoc();
        $pat_no = $pat['patient_no'] ?? '';
        $v_type = $pat['nhis_service_type'] ?? '';

        if ($strict_prepay === 1 && $v_type !== 'In-patient') {
            $inv_q = $conn->query("SELECT SUM(balance_due) as total_owed FROM patient_invoices WHERE patient_no='$pat_no' AND status != 'Paid'");
            if (($inv_q->fetch_assoc()['total_owed'] ?? 0) > 0) throw new Exception("Strict Pre-Pay Violation: Patient has unpaid invoices. Dispensing blocked.");
        }

        $conn->begin_transaction();
        $stmtStkCheck = $conn->prepare("SELECT quantity FROM inventory_stock WHERE service_id = ? AND location_id = ? FOR UPDATE");
        $stmtStkUpdate = $conn->prepare("UPDATE inventory_stock SET quantity = quantity - ? WHERE service_id = ? AND location_id = ?");
        $stmtTxLog = $conn->prepare("INSERT INTO inventory_transactions (service_id, location_id, transaction_type, quantity_changed, reference_id, performed_by) VALUES (?, ?, 'Dispense', ?, ?, ?)");

        foreach ($rx_ids as $rx_id) {
            $qty_to_dispense = (int)($_POST['dispense_qty'][$rx_id] ?? 0);
            if ($qty_to_dispense <= 0) continue;

            $rx = $conn->query("SELECT service_id, drug_name FROM clinical_prescriptions WHERE id = $rx_id AND status = 'Billed'")->fetch_assoc();
            if (!$rx) continue; 

            $stmtStkCheck->bind_param("ii", $rx['service_id'], $location_id); $stmtStkCheck->execute();
            $stkRes = $stmtStkCheck->get_result()->fetch_assoc();
            if (!$stkRes || $stkRes['quantity'] < $qty_to_dispense) throw new Exception("Insufficient stock for " . $rx['drug_name'] . ".");

            $stmtStkUpdate->bind_param("iii", $qty_to_dispense, $rx['service_id'], $location_id); $stmtStkUpdate->execute();
            $neg_qty = -$qty_to_dispense;
            $stmtTxLog->bind_param("iiiii", $rx['service_id'], $location_id, $neg_qty, $rx_id, $staff_id); $stmtTxLog->execute();
            $conn->query("UPDATE clinical_prescriptions SET status = 'Dispensed', dispensed_by = $staff_id, end_date = NOW() WHERE id = $rx_id");
        }
        $conn->commit();
        if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'success', 'message' => 'Items physically dispensed. Stock deducted.']); exit;
    }

} catch (Throwable $e) { 
    try { if (isset($conn) && $conn->ping()) $conn->rollback(); } catch(Throwable $t) {}
    if(ob_get_length()) ob_end_clean(); echo json_encode(['status' => 'error', 'message' => $e->getMessage()]); exit;
}
?>