<?php
/**
 * ajax/save_consultation.php
 * Handles: save (draft / complete), discard_draft, preauth_hold
 *
 * KEY CHANGE (2025-xx-xx):
 *   When status = COMPLETED, auto-create or append to the visit's INV invoice
 *   so that downstream departments (pharmacy, audiology) always find a single
 *   invoice to append their charges to.  The pre-flight search is identical to
 *   the pattern used in new_audiology_consultation.php and process_dispensing.php.
 *
 * ── Schema facts (verified against live DB dump) ─────────────────────────────
 *
 * consultations columns:
 *   consultation_id, consultation_number, visit_id, patient_id, doctor_id,
 *   consultation_type, chief_complaint, history_present_illness,
 *   past_medical_history, family_history, social_history,
 *   ear_examination, nose_examination, throat_examination, neck_examination,
 *   cranial_nerves, vital_signs, general_examination,
 *   diagnosis_primary, diagnosis_primary_icd11,
 *   diagnosis_secondary, diagnosis_secondary_icd11,
 *   differential_diagnosis, treatment_plan,
 *   medications_prescribed, procedures_recommended,
 *   investigations_ordered, follow_up_instructions, next_appointment,
 *   referrals, doctor_notes, consultation_fee, cpt_code,
 *   doctor_commission, status, started_at, completed_at,
 *   created_at, updated_at
 *
 * patient_visits.status ENUM (VERIFIED — 'IN_PROGRESS' does NOT exist):
 *   REGISTERED | WAITING | IN_CONSULTATION | COMPLETED | CANCELLED
 *
 * patient_queue.queue_status ENUM:
 *   WAITING | IN_SERVICE | COMPLETED | TRANSFERRED | CANCELLED | NO_SHOW | ROUTED
 *
 * invoices columns relevant to billing centre:
 *   visit_id (NOT NULL), patient_id (NOT NULL)
 *
 * invoice_items columns:
 *   item_id, invoice_id, item_type, reference_id, description,
 *   quantity, unit_price, total_price, provider_id,
 *   commission_rate, commission_amount, date_of_service
 *
 * appointments columns (NO visit_id, NO consultation_id, NO created_by):
 *   appointment_number (NOT NULL), patient_id, doctor_id,
 *   appointment_date (NOT NULL), appointment_time (NOT NULL),
 *   appointment_type, booking_source, status, notes,
 *   created_at, updated_at
 *
 * patients.registration_type: CASH | INSURANCE | CORPORATE | NHIF | WELFARE
 * ─────────────────────────────────────────────────────────────────────────────
 */

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

include '../layouts/dbconn.php'; // provides $conn (MySQLi)

/* ── Auth guard ──────────────────────────────────────────────────────────── */
if (!isset($_SESSION['user_id'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorised.']);
    exit;
}
$logged_user_id = (int)$_SESSION['user_id'];

/* ═══════════════════════════════════════════════════════════════════════════
   HELPERS
═══════════════════════════════════════════════════════════════════════════ */

/** Encode to normalised JSON string for a JSON column. Returns null if empty/invalid. */
function safe_json($value): ?string
{
    if ($value === null || $value === '') return null;
    if (is_array($value))                  return json_encode($value);
    if (is_string($value)) {
        $d = json_decode($value, true);
        return (json_last_error() === JSON_ERROR_NONE) ? json_encode($d) : null;
    }
    return null;
}

/** Trimmed string from $_POST. */
function pstr(string $key, string $default = ''): string
{
    return trim($_POST[$key] ?? $default);
}

/** Integer from $_POST. */
function pint(string $key, int $default = 0): int
{
    return (int)($_POST[$key] ?? $default);
}

/** Nullable text — returns null when blank. */
function pnull(string $key): ?string
{
    $v = trim($_POST[$key] ?? '');
    return $v !== '' ? $v : null;
}

/** Emit JSON error and halt. */
function fail(string $msg, ?Throwable $e = null): void
{
    if ($e) error_log('save_consultation: ' . $e->getMessage() . "\n" . $e->getTraceAsString());
    echo json_encode(['success' => false, 'message' => $msg]);
    exit;
}

/* ═══════════════════════════════════════════════════════════════════════════
   ROUTE ON $action
═══════════════════════════════════════════════════════════════════════════ */
$action = pstr('action');

/* ──────────────────────────────────────────────────────────────────────────
   ACTION: discard_draft
────────────────────────────────────────────────────────────────────────── */
if ($action === 'discard_draft') {

    $consultation_id = pint('consultation_id');
    $visit_id        = pint('visit_id');

    if (!$consultation_id) fail('Missing consultation ID.');

    $stmt = $conn->prepare("
        UPDATE consultations
        SET    status = 'CANCELLED', updated_at = NOW()
        WHERE  consultation_id = ?
          AND  status IN ('DRAFT','IN_PROGRESS','PENDING_PREAUTH')
    ");
    if (!$stmt) fail('Prepare error: ' . $conn->error);
    $stmt->bind_param('i', $consultation_id);
    if (!$stmt->execute()) fail('Execute error: ' . $stmt->error);
    $stmt->close();

    if ($visit_id > 0) {
        $vstmt = $conn->prepare("
            UPDATE patient_visits SET status = 'REGISTERED', updated_at = NOW()
            WHERE  visit_id = ?
        ");
        if (!$vstmt) fail('Prepare error: ' . $conn->error);
        $vstmt->bind_param('i', $visit_id);
        if (!$vstmt->execute()) fail('Execute error: ' . $vstmt->error);
        $vstmt->close();
    }

    echo json_encode(['success' => true, 'message' => 'Draft discarded.']);
    exit;
}

/* ──────────────────────────────────────────────────────────────────────────
   ACTION: preauth_hold
────────────────────────────────────────────────────────────────────────── */
if ($action === 'preauth_hold') {

    $consultation_id = pint('consultation_id');
    $visit_id        = pint('visit_id');
    $preauth_notes   = pstr('preauth_notes');

    if (!$consultation_id || !$visit_id) fail('Missing consultation or visit ID.');
    if ($preauth_notes === '')           fail('Pre-auth notes are required.');

    $stamp = '[PRE-AUTH HOLD – ' . date('d M Y H:i') . "]\n" . $preauth_notes;

    $stmt = $conn->prepare("
        UPDATE consultations
        SET    status       = 'PENDING_PREAUTH',
               doctor_notes = CONCAT(
                                  COALESCE(doctor_notes,''),
                                  CASE WHEN COALESCE(doctor_notes,'') != '' THEN '\n\n' ELSE '' END,
                                  ?
                              ),
               updated_at   = NOW()
        WHERE  consultation_id = ?
    ");
    if (!$stmt) fail('Prepare error: ' . $conn->error);
    $stmt->bind_param('si', $stamp, $consultation_id);
    if (!$stmt->execute()) fail('Execute error: ' . $stmt->error);
    $stmt->close();

    $vstmt = $conn->prepare("
        UPDATE patient_visits SET status = 'IN_CONSULTATION', updated_at = NOW()
        WHERE  visit_id = ?
    ");
    if (!$vstmt) fail('Prepare error: ' . $conn->error);
    $vstmt->bind_param('i', $visit_id);
    if (!$vstmt->execute()) fail('Execute error: ' . $vstmt->error);
    $vstmt->close();

    echo json_encode([
        'success'         => true,
        'consultation_id' => $consultation_id,
        'visit_id'        => $visit_id,
        'message'         => 'Consultation held pending pre-authorisation.',
    ]);
    exit;
}

/* ═══════════════════════════════════════════════════════════════════════════
   DEFAULT: save / complete consultation
═══════════════════════════════════════════════════════════════════════════ */

/* ── Status whitelist ────────────────────────────────────────────────────── */
$allowed = ['DRAFT','IN_PROGRESS','COMPLETED','CANCELLED','PENDING_PREAUTH'];
$status  = in_array(pstr('status'), $allowed, true) ? pstr('status') : 'IN_PROGRESS';

/* ── Core IDs ────────────────────────────────────────────────────────────── */
$consultation_id = pint('consultation_id');
$visit_id        = pint('visit_id');
$patient_id      = pint('patient_id');
$doctor_id       = pint('doctor_id');

if (!$visit_id || !$patient_id || !$doctor_id) {
    fail('Missing required IDs. Got: visit=' . $visit_id
        . ', patient=' . $patient_id . ', doctor=' . $doctor_id);
}

/* ── Scalar fields ───────────────────────────────────────────────────────── */
$consultation_type         = pstr('consultation_type', 'INITIAL');
$chief_complaint           = pnull('chief_complaint');
$history_present_illness   = pnull('history_present_illness');
$past_medical_history      = pnull('past_medical_history');
$family_history            = pnull('family_history');
$social_history            = pnull('social_history');
$general_examination       = pnull('general_examination');
$diagnosis_primary         = pnull('diagnosis_primary');
$diagnosis_primary_icd11   = pnull('diagnosis_primary_icd11');
$diagnosis_secondary       = pnull('diagnosis_secondary');
$diagnosis_secondary_icd11 = pnull('diagnosis_secondary_icd11');
$differential_diagnosis    = pnull('differential_diagnosis');
$treatment_plan            = pnull('treatment_plan');
$follow_up_instructions    = pnull('follow_up_instructions');
$next_appointment          = pnull('next_appointment');
$doctor_notes              = pnull('doctor_notes');
$cpt_code                  = pnull('cpt_code');
$consultation_fee          = (float)($_POST['consultation_fee']  ?? 0);
$doctor_commission         = (float)($_POST['doctor_commission'] ?? 0);
$is_pro_bono_visit         = (int)($_POST['pro_bono_visit'] ?? 0) === 1;
if ($consultation_type === 'PRO_BONO') {
    $is_pro_bono_visit = true;
}
if ($is_pro_bono_visit) {
    $consultation_type = 'PRO_BONO';
    $consultation_fee = 0.0;
    $doctor_commission = 0.0;
}

/* ── JSON fields ─────────────────────────────────────────────────────────── */
$ear_examination        = safe_json($_POST['ear_examination']        ?? null);
$nose_examination       = safe_json($_POST['nose_examination']       ?? null);
$throat_examination     = safe_json($_POST['throat_examination']     ?? null);
$neck_examination       = safe_json($_POST['neck_examination']       ?? null);
$cranial_nerves         = safe_json($_POST['cranial_nerves']         ?? null);
$medications_prescribed = safe_json($_POST['medications_prescribed'] ?? null);
$procedures_recommended = safe_json($_POST['procedures_recommended'] ?? null);
$investigations_ordered = safe_json($_POST['investigations_ordered'] ?? null);
$referrals              = safe_json($_POST['referrals']              ?? null);

/* ── Validation for COMPLETED ────────────────────────────────────────────── */
if ($status === 'COMPLETED') {
    if (empty($diagnosis_primary)) fail('Primary diagnosis is required to complete a consultation.');
}

/* ── Map → patient_visits.status ─────────────────────────────────────────────
 *  COMPLETED  → COMPLETED
 *  CANCELLED  → CANCELLED
 *  all others → IN_CONSULTATION
 * ──────────────────────────────────────────────────────────────────── */
$visit_status = match ($status) {
    'CANCELLED' => 'CANCELLED',
    default     => 'IN_CONSULTATION',
};

/* ═══════════════════════════════════════════════════════════════════════════
   DATABASE TRANSACTION
═══════════════════════════════════════════════════════════════════════════ */
$conn->begin_transaction();

try {

    /* ────────────────────────────────────────────────────────────────────
       INSERT or UPDATE consultations
    ──────────────────────────────────────────────────────────────────── */

    if ($consultation_id > 0) {

        /* ── UPDATE ─────────────────────────────────────────────────── */
        $upd = $conn->prepare("
            UPDATE consultations SET
                visit_id                  = ?,
                patient_id                = ?,
                doctor_id                 = ?,
                consultation_type         = ?,
                chief_complaint           = ?,
                history_present_illness   = ?,
                past_medical_history      = ?,
                family_history            = ?,
                social_history            = ?,
                ear_examination           = ?,
                nose_examination          = ?,
                throat_examination        = ?,
                neck_examination          = ?,
                cranial_nerves            = ?,
                general_examination       = ?,
                diagnosis_primary         = ?,
                diagnosis_primary_icd11   = ?,
                diagnosis_secondary       = ?,
                diagnosis_secondary_icd11 = ?,
                differential_diagnosis    = ?,
                treatment_plan            = ?,
                medications_prescribed    = ?,
                procedures_recommended    = ?,
                investigations_ordered    = ?,
                referrals                 = ?,
                follow_up_instructions    = ?,
                next_appointment          = ?,
                doctor_notes              = ?,
                cpt_code                  = ?,
                consultation_fee          = ?,
                doctor_commission         = ?,
                status                    = ?,
                completed_at              = IF(? = 'COMPLETED', NOW(), completed_at),
                updated_at                = NOW()
            WHERE consultation_id = ?
        ");
        if (!$upd) throw new Exception('UPDATE prepare failed: ' . $conn->error);

        $upd->bind_param(
            'iiissssssssssssssssssssssssssddssi',
            $visit_id, $patient_id, $doctor_id,
            $consultation_type, $chief_complaint, $history_present_illness,
            $past_medical_history, $family_history, $social_history,
            $ear_examination, $nose_examination, $throat_examination,
            $neck_examination, $cranial_nerves, $general_examination,
            $diagnosis_primary, $diagnosis_primary_icd11,
            $diagnosis_secondary, $diagnosis_secondary_icd11,
            $differential_diagnosis, $treatment_plan,
            $medications_prescribed, $procedures_recommended,
            $investigations_ordered, $referrals,
            $follow_up_instructions, $next_appointment,
            $doctor_notes, $cpt_code,
            $consultation_fee, $doctor_commission,
            $status,
            $status,          // second copy for IF() → completed_at
            $consultation_id
        );
        if (!$upd->execute()) throw new Exception('UPDATE execute failed: ' . $upd->error);
        $upd->close();

    } else {

        /* ── INSERT (insert-then-update pattern to avoid MAX() race condition) */
        $temp_number = 'CONS-TEMP-' . uniqid('', true);

        $ins = $conn->prepare("
            INSERT INTO consultations (
                consultation_number, visit_id, patient_id, doctor_id,
                consultation_type, chief_complaint, history_present_illness,
                past_medical_history, family_history, social_history,
                ear_examination, nose_examination, throat_examination,
                neck_examination, cranial_nerves, general_examination,
                diagnosis_primary, diagnosis_primary_icd11,
                diagnosis_secondary, diagnosis_secondary_icd11,
                differential_diagnosis, treatment_plan,
                medications_prescribed, procedures_recommended,
                investigations_ordered, referrals,
                follow_up_instructions, next_appointment,
                doctor_notes, cpt_code,
                consultation_fee, doctor_commission,
                status, started_at, completed_at, created_at, updated_at
            ) VALUES (
                ?, ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?,
                ?, ?, ?, ?, ?,
                ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?,
                NOW(),
                IF(? = 'COMPLETED', NOW(), NULL),
                NOW(), NOW()
            )
        ");
        if (!$ins) throw new Exception('INSERT prepare failed: ' . $conn->error);

        $ins->bind_param(
            'siiissssssssssssssssssssssssssddss',
            $temp_number,
            $visit_id, $patient_id, $doctor_id,
            $consultation_type, $chief_complaint, $history_present_illness,
            $past_medical_history, $family_history, $social_history,
            $ear_examination, $nose_examination, $throat_examination,
            $neck_examination, $cranial_nerves, $general_examination,
            $diagnosis_primary, $diagnosis_primary_icd11,
            $diagnosis_secondary, $diagnosis_secondary_icd11,
            $differential_diagnosis, $treatment_plan,
            $medications_prescribed, $procedures_recommended,
            $investigations_ordered, $referrals,
            $follow_up_instructions, $next_appointment,
            $doctor_notes, $cpt_code,
            $consultation_fee, $doctor_commission,
            $status,
            $status   // second copy for IF() → completed_at
        );
        if (!$ins->execute()) throw new Exception('INSERT execute failed: ' . $ins->error);
        $consultation_id = (int)$conn->insert_id;
        $ins->close();

        $consultation_number = 'CONS-' . date('Ymd') . '-' . str_pad($consultation_id, 3, '0', STR_PAD_LEFT);

        $upd_num = $conn->prepare("
            UPDATE consultations
            SET    consultation_number = ?, updated_at = NOW()
            WHERE  consultation_id = ?
        ");
        if (!$upd_num) throw new Exception('Number UPDATE prepare failed: ' . $conn->error);
        $upd_num->bind_param('si', $consultation_number, $consultation_id);
        if (!$upd_num->execute()) throw new Exception('Number UPDATE execute failed: ' . $upd_num->error);
        $upd_num->close();
    }

    /* ────────────────────────────────────────────────────────────────────
       Update patient_visits.status
    ──────────────────────────────────────────────────────────────────── */
    $vstmt = $conn->prepare("
        UPDATE patient_visits
        SET    status            = ?,
               registration_type = IF(? = 1, 'PRO_BONO', registration_type),
               consultation_fee  = IF(? = 1, 0.00, consultation_fee),
               updated_at        = NOW()
        WHERE  visit_id = ?
    ");
    if (!$vstmt) throw new Exception('Visit UPDATE prepare failed: ' . $conn->error);
    $pro_bono_int = $is_pro_bono_visit ? 1 : 0;
    $vstmt->bind_param('siii', $visit_status, $pro_bono_int, $pro_bono_int, $visit_id);
    if (!$vstmt->execute()) throw new Exception('Visit UPDATE execute failed: ' . $vstmt->error);
    $vstmt->close();

    /* ────────────────────────────────────────────────────────────────────
       Update patient_queue.queue_status
       NULL = integer evaluates to NULL in MySQL (not TRUE), so two-branch
       OR guard is required for walk-ins with visit_id = NULL.
    ──────────────────────────────────────────────────────────────────── */
    $queue_status = match ($status) {
        'CANCELLED' => 'CANCELLED',
        default     => 'IN_SERVICE',
    };

    $qstmt = $conn->prepare("
        UPDATE patient_queue
        SET    queue_status           = ?,
               service_end_time       = IF(? IN ('COMPLETED','CANCELLED'), NOW(), service_end_time),
               service_start_time     = IF(service_start_time IS NULL, NOW(), service_start_time),
               total_service_minutes  = IF(
                   ? IN ('COMPLETED','CANCELLED'),
                   TIMESTAMPDIFF(MINUTE, COALESCE(service_start_time, queue_entry_time), NOW()),
                   total_service_minutes
               ),
               updated_by             = ?
        WHERE  queue_status IN ('WAITING','IN_SERVICE')
          AND  DATE(queue_entry_time) = CURDATE()
          AND  (
                  visit_id = ?
               OR (visit_id IS NULL AND patient_id = ?)
               )
    ");
    if (!$qstmt) throw new Exception('Queue UPDATE prepare failed: ' . $conn->error);
    $qstmt->bind_param('sssiii',
        $queue_status,
        $queue_status,
        $queue_status,
        $logged_user_id,
        $visit_id,
        $patient_id
    );
    if (!$qstmt->execute()) throw new Exception('Queue UPDATE execute failed: ' . $qstmt->error);
    $qstmt->close();

    /* ════════════════════════════════════════════════════════════════════
       AUTO-CREATE PRESCRIPTIONS + PHARMACY DISPENSING RECORD
       Runs on every save (draft AND completed) so pharmacy queue is
       populated as soon as the doctor prescribes anything.
    ════════════════════════════════════════════════════════════════════ */
    $meds_array = [];
    if (!empty($_POST['medications_prescribed'])) {
        $decoded = json_decode($_POST['medications_prescribed'], true);
        if (is_array($decoded)) {
            $meds_array = array_values(array_filter($decoded, fn($m) => !empty($m['name'])));
        }
    }

    if (!empty($meds_array)) {

        $pt_res = $conn->query("
            SELECT u.full_name, u.phone_primary, p.insurance_provider_id
            FROM   patients p
            JOIN   users    u ON p.user_id = u.user_id
            WHERE  p.patient_id = $patient_id
            LIMIT  1
        ");
        $pt_row   = $pt_res ? $pt_res->fetch_assoc() : [];
        $pt_name  = $pt_row['full_name']           ?? 'Unknown';
        $pt_phone = $pt_row['phone_primary']        ?? null;
        $ins_id   = (int)($pt_row['insurance_provider_id'] ?? 0) ?: null;

        $rx_chk = $conn->prepare("
            SELECT COUNT(*) AS cnt
            FROM   prescriptions
            WHERE  consultation_id = ?
              AND  visit_id        = ?
        ");
        if (!$rx_chk) throw new Exception('Rx check prepare failed: ' . $conn->error);
        $rx_chk->bind_param('ii', $consultation_id, $visit_id);
        $rx_chk->execute();
        $existing_rx_count = (int)($rx_chk->get_result()->fetch_assoc()['cnt'] ?? 0);
        $rx_chk->close();

        if ($existing_rx_count === 0) {

            $disp_num_res = $conn->query("
                SELECT CONCAT(
                    'DISP-', DATE_FORMAT(NOW(),'%Y%m%d'), '-',
                    LPAD(COALESCE(MAX(CAST(SUBSTRING_INDEX(dispensing_number,'-',-1) AS UNSIGNED)),0)+1, 3,'0')
                ) AS new_number
                FROM pharmacy_dispensing
                WHERE dispensing_number LIKE CONCAT('DISP-',DATE_FORMAT(NOW(),'%Y%m%d'),'%')
            ");
            $disp_number = $disp_num_res
                ? ($disp_num_res->fetch_assoc()['new_number'] ?? ('DISP-' . date('Ymd') . '-001'))
                : ('DISP-' . date('Ymd') . '-001');

            $disp_ins = $conn->prepare("
                INSERT INTO pharmacy_dispensing (
                    dispensing_number, prescription_id, patient_id, patient_type,
                    visit_id, patient_name, patient_phone, dispensed_by,
                    total_amount, amount_paid, payment_method, payment_status,
                    insurance_provider_id, notes, created_at, updated_at
                ) VALUES (
                    ?, NULL, ?, 'REGISTERED',
                    ?, ?, ?, ?,
                    0.00, 0.00, 'CASH', 'PENDING',
                    ?, 'Auto-created from consultation', NOW(), NOW()
                )
            ");
            if (!$disp_ins) throw new Exception('Dispensing INSERT prepare failed: ' . $conn->error);

            $disp_ins->bind_param(
                'siissii',
                $disp_number,
                $patient_id,
                $visit_id,
                $pt_name,
                $pt_phone,
                $logged_user_id,
                $ins_id
            );
            if (!$disp_ins->execute()) throw new Exception('Dispensing INSERT execute failed: ' . $disp_ins->error);
            $dispensing_id = (int)$conn->insert_id;
            $disp_ins->close();

            $today        = date('Y-m-d');
            $total_amount = 0.00;

            foreach ($meds_array as $med) {
                $inv_id    = (int)($med['inventory_id'] ?? 0);
                $med_name  = trim($med['name']      ?? '');
                $dosage    = trim($med['dosage']     ?? '');
                $frequency = trim($med['frequency']  ?? '');
                $duration  = trim($med['duration']   ?? '');
                $qty       = max(1, (int)($med['quantity'] ?? 1));
                $price     = (float)($med['price']   ?? 0);

                if (empty($med_name)) continue;

                $rx_num_res = $conn->query("
                    SELECT CONCAT(
                        'RX-', DATE_FORMAT(NOW(),'%Y%m%d'), '-',
                        LPAD(COALESCE(MAX(CAST(SUBSTRING_INDEX(prescription_number,'-',-1) AS UNSIGNED)),0)+1, 3,'0')
                    ) AS new_number
                    FROM prescriptions
                    WHERE prescription_number LIKE CONCAT('RX-',DATE_FORMAT(NOW(),'%Y%m%d'),'%')
                ");
                $rx_number = $rx_num_res
                    ? ($rx_num_res->fetch_assoc()['new_number'] ?? ('RX-' . date('Ymd') . '-001'))
                    : ('RX-' . date('Ymd') . '-001');

                $med_type = 'TABLET';
                if ($inv_id > 0) {
                    $df_res = $conn->query("
                        SELECT dosage_form FROM pharmacy_inventory
                        WHERE  inventory_id = $inv_id LIMIT 1
                    ");
                    if ($df_res) {
                        $med_type = $df_res->fetch_assoc()['dosage_form'] ?? 'TABLET';
                    }
                }

                $rx_ins = $conn->prepare("
                    INSERT INTO prescriptions (
                        prescription_number, patient_id, doctor_id, visit_id,
                        consultation_id, medication_name, medication_type,
                        dosage, frequency, duration, quantity,
                        prescribed_date, status, dispensed, cost,
                        created_by, created_at, updated_at
                    ) VALUES (
                        ?, ?, ?, ?,
                        ?, ?, ?,
                        ?, ?, ?, ?,
                        ?, 'ACTIVE', 0, ?,
                        ?, NOW(), NOW()
                    )
                ");
                if (!$rx_ins) throw new Exception('Prescription INSERT prepare failed: ' . $conn->error);

                $rx_ins->bind_param(
                    'siiiisssssssdi',
                    $rx_number,
                    $patient_id,
                    $doctor_id,
                    $visit_id,
                    $consultation_id,
                    $med_name,
                    $med_type,
                    $dosage,
                    $frequency,
                    $duration,
                    $qty,
                    $today,
                    $price,
                    $logged_user_id
                );
                if (!$rx_ins->execute()) throw new Exception('Prescription INSERT execute failed: ' . $rx_ins->error);
                $rx_ins->close();

                if ($inv_id > 0) {
                    $dosage_instructions = $frequency . ($duration ? " for $duration" : '');

                    $item_ins = $conn->prepare("
                        INSERT INTO pharmacy_dispensing_items (
                            dispensing_id, inventory_id, item_name,
                            quantity, unit_price, dosage_instructions, created_at
                        ) VALUES (?, ?, ?, ?, ?, ?, NOW())
                    ");
                    if (!$item_ins) throw new Exception('Dispensing item INSERT prepare failed: ' . $conn->error);

                    $item_ins->bind_param(
                        'iiisds',
                        $dispensing_id,
                        $inv_id,
                        $med_name,
                        $qty,
                        $price,
                        $dosage_instructions
                    );
                    if (!$item_ins->execute()) throw new Exception('Dispensing item INSERT execute failed: ' . $item_ins->error);
                    $item_ins->close();

                    $total_amount += $price * $qty;
                }
            }

            $upd_total = $conn->prepare("
                UPDATE pharmacy_dispensing
                SET    total_amount = ?, updated_at = NOW()
                WHERE  dispensing_id = ?
            ");
            if (!$upd_total) throw new Exception('Dispensing total UPDATE prepare failed: ' . $conn->error);
            $upd_total->bind_param('di', $total_amount, $dispensing_id);
            if (!$upd_total->execute()) throw new Exception('Dispensing total UPDATE execute failed: ' . $upd_total->error);
            $upd_total->close();
        }
    }

    /* ════════════════════════════════════════════════════════════════════
       AUTO-CREATE / APPEND VISIT INVOICE  (COMPLETED consultations only)
       ─────────────────────────────────────────────────────────────────
       Inserts BOTH the consultation fee AND any procedure line items
       (procedures_recommended JSON) so that items like Audiometry, Aural
       Toilet etc. appear on the invoice immediately — not just the fee.

       Pre-flight: Search 1 by visit_id → Search 2 patient 30-day fallback
       → create new INV if neither found.
    ════════════════════════════════════════════════════════════════════ */

    // ── Decode procedures_recommended → array of procedures to bill ──
    // Use the raw POST value (not the safe_json()-encoded string) so the
    // original pricing keys (final_price, standard_price, pricing_id…)
    // are all still accessible.
    $procs_for_invoice = [];
    if (!empty($_POST['procedures_recommended'])) {
        $decoded_procs = json_decode($_POST['procedures_recommended'], true);
        if (is_array($decoded_procs)) {
            foreach ($decoded_procs as $proc_entry) {
                $pname = trim($proc_entry['procedure'] ?? $proc_entry['service_name'] ?? '');
                if ($pname === '') continue;
                if ($is_pro_bono_visit) {
                    $proc_entry['standard_price'] = 0;
                    $proc_entry['final_price'] = 0;
                    $proc_entry['discount_amount'] = 0;
                }
                $procs_for_invoice[] = $proc_entry;
            }
        }
    }

    // Sum procedure amounts for the invoice header total
    $procedures_total = 0.0;
    foreach ($procs_for_invoice as $pe) {
        $procedures_total += (float)($pe['final_price'] ?? $pe['standard_price'] ?? 0);
    }

    // Combined total: consultation fee + all ordered procedures
    $invoice_total_cons = $is_pro_bono_visit ? 0.0 : ($consultation_fee + $procedures_total);

    if ($status === 'COMPLETED' && $invoice_total_cons > 0) {

        // ── Closure: insert PROCEDURE line items into an invoice ──────
        // Defined once, called in both the "append" and "create" branches.
        $append_proc_items = function(int $inv_id) use ($conn, $procs_for_invoice) {
            foreach ($procs_for_invoice as $pe) {
                $pname  = trim($pe['procedure'] ?? $pe['service_name'] ?? '');
                if ($pname === '') continue;
                $qty    = max(1, (int)($pe['quantity'] ?? 1));
                $unitp  = (float)($pe['standard_price'] ?? 0);
                $finalp = (float)($pe['final_price'] ?? ($unitp * $qty));
                if ($finalp <= 0 && $unitp <= 0) continue;
                $pid_ref = !empty($pe['pricing_id']) ? (int)$pe['pricing_id'] : null;

                $sp = $conn->prepare(
                    "INSERT INTO invoice_items
                        (invoice_id, item_type, reference_id, description,
                         quantity, unit_price, total_price, date_of_service)
                     VALUES (?, 'PROCEDURE', ?, ?, ?, ?, ?, CURDATE())"
                );
                if (!$sp) continue;
                $sp->bind_param('iisidd', $inv_id, $pid_ref, $pname, $qty, $unitp, $finalp);
                $sp->execute();
                $sp->close();
            }
        };

        $auto_inv_id = null;

        // ── Search 1: by visit_id ────────────────────────────────────
        $ichk1 = $conn->prepare(
            "SELECT invoice_id FROM invoices
             WHERE  visit_id = ?
               AND  status NOT IN ('CANCELLED','VOID','PAID')
             ORDER  BY created_at DESC LIMIT 1"
        );
        if (!$ichk1) throw new Exception('Invoice check 1 prepare failed: ' . $conn->error);
        $ichk1->bind_param('i', $visit_id);
        $ichk1->execute();
        $ichk1_row = $ichk1->get_result()->fetch_assoc();
        $ichk1->close();
        if ($ichk1_row) $auto_inv_id = (int)$ichk1_row['invoice_id'];

        // ── Search 2: patient_id fallback (30 days) ──────────────────
        if (!$auto_inv_id) {
            $ichk2 = $conn->prepare(
                "SELECT invoice_id FROM invoices
                 WHERE  patient_id = ?
                   AND  created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                   AND  status NOT IN ('CANCELLED','VOID','PAID')
                 ORDER  BY created_at DESC LIMIT 1"
            );
            if (!$ichk2) throw new Exception('Invoice check 2 prepare failed: ' . $conn->error);
            $ichk2->bind_param('i', $patient_id);
            $ichk2->execute();
            $ichk2_row = $ichk2->get_result()->fetch_assoc();
            $ichk2->close();
            if ($ichk2_row) $auto_inv_id = (int)$ichk2_row['invoice_id'];
        }

        $cons_desc = 'Consultation Fee — '
                   . str_replace('_', ' ', $consultation_type)
                   . ' (' . date('d M Y') . ')';

        if ($auto_inv_id) {

            // ── Existing invoice: stamp visit_id if missing ───────────
            $conn->query(
                "UPDATE invoices
                 SET    visit_id   = $visit_id,
                        updated_at = NOW()
                 WHERE  invoice_id = $auto_inv_id
                   AND  (visit_id IS NULL OR visit_id = 0)"
            );

            // ── Append consultation fee line ──────────────────────────
            if ($consultation_fee > 0) {
                $stmt_ii = $conn->prepare(
                    "INSERT INTO invoice_items
                        (invoice_id, item_type, reference_id, description,
                         quantity, unit_price, total_price,
                         provider_id, commission_rate, commission_amount, date_of_service)
                     VALUES (?, 'CONSULTATION', ?, ?, 1, ?, ?, ?, 0.00, ?, CURDATE())"
                );
                if (!$stmt_ii) throw new Exception('Invoice item INSERT prepare failed: ' . $conn->error);
                $stmt_ii->bind_param('iisddid',
                    $auto_inv_id, $consultation_id, $cons_desc,
                    $consultation_fee, $consultation_fee,
                    $doctor_id, $doctor_commission
                );
                if (!$stmt_ii->execute()) throw new Exception('Invoice item INSERT execute failed: ' . $stmt_ii->error);
                $stmt_ii->close();
            }

            // ── Append procedure line items (Audiometry, Aural Toilet…)
            $append_proc_items($auto_inv_id);

            // ── Recalculate totals from ALL line items ────────────────
            $conn->query(
                "UPDATE invoices
                 SET subtotal        = (SELECT COALESCE(SUM(total_price),0) FROM invoice_items WHERE invoice_id = $auto_inv_id),
                     total_amount    = (SELECT COALESCE(SUM(total_price),0) FROM invoice_items WHERE invoice_id = $auto_inv_id),
                     patient_portion = (SELECT COALESCE(SUM(total_price),0) FROM invoice_items WHERE invoice_id = $auto_inv_id)
                                       - COALESCE(insurance_covered,0),
                     balance         = (SELECT COALESCE(SUM(total_price),0) FROM invoice_items WHERE invoice_id = $auto_inv_id)
                                       - COALESCE(amount_paid,0),
                     updated_at      = NOW()
                 WHERE invoice_id = $auto_inv_id"
            );

        } else {

            // ── No open invoice — create one ──────────────────────────
            $pt_inf_res = $conn->query(
                "SELECT p.registration_type, p.insurance_provider_id
                 FROM   patients p
                 WHERE  p.patient_id = $patient_id LIMIT 1"
            );
            $pt_inf = $pt_inf_res ? $pt_inf_res->fetch_assoc() : [];

            $reg_type_inv = strtoupper(trim($pt_inf['registration_type'] ?? 'CASH'));
            $ins_prov_inv = ($reg_type_inv === 'INSURANCE' && !empty($pt_inf['insurance_provider_id']))
                            ? (int)$pt_inf['insurance_provider_id']
                            : null;
            $bill_to_inv  = $ins_prov_inv ? 'INSURANCE' : 'PATIENT';
            $ins_prov_sql = $ins_prov_inv ? (string)$ins_prov_inv : 'NULL';

            $inv_seq_res = $conn->query(
                "SELECT MAX(CAST(SUBSTRING(invoice_number, 4) AS UNSIGNED)) AS max_seq
                 FROM invoices
                 WHERE invoice_number REGEXP '^PMS[0-9]+'"
            );
            
            $inv_seq = 1;
            
            if ($inv_seq_res) {
                $row = $inv_seq_res->fetch_assoc();
                if (!empty($row['max_seq'])) {
                    $inv_seq = (int)$row['max_seq'] + 1;
                }
            }
            
            $new_inv_number = 'PMS' . str_pad($inv_seq, 7, '0', STR_PAD_LEFT);
            $inv_date       = date('Y-m-d');
            $due_date       = date('Y-m-d', strtotime('+7 days'));
            $inv_notes      = 'Auto-created: Consultation — ' . date('d M Y');

            $has_cb = (bool)($conn->query("SHOW COLUMNS FROM invoices LIKE 'created_by'")->num_rows ?? 0);
            $cb_sql = $has_cb ? ',created_by' : '';
            $cb_ph  = $has_cb ? ',?' : '';

            $ins_inv_sql = "INSERT INTO invoices
                (invoice_number, visit_id, patient_id, invoice_date, due_date,
                 bill_to, insurance_provider_id,
                 subtotal, tax_amount, discount_amount, insurance_covered,
                 total_amount, patient_portion, amount_paid, balance,
                 payment_terms, status, notes{$cb_sql})
             VALUES (?, ?, ?, ?, ?,
                     ?, {$ins_prov_sql},
                     ?, 0, 0, 0, ?, ?, 0, ?,
                     'Immediate', 'SENT', ?{$cb_ph})";

            $stmt_inv = $conn->prepare($ins_inv_sql);
            if (!$stmt_inv) throw new Exception('Invoice INSERT prepare failed: ' . $conn->error);

            // Use $invoice_total_cons (fee + procedures) for ALL header amounts
            $inv_params = [
                $new_inv_number, $visit_id, $patient_id, $inv_date, $due_date,
                $bill_to_inv,
                $invoice_total_cons,  // subtotal
                $invoice_total_cons,  // total_amount
                $invoice_total_cons,  // patient_portion
                $invoice_total_cons,  // balance
                $inv_notes,
            ];
            $inv_types = 'siisssdddds';
            if ($has_cb) { $inv_params[] = $logged_user_id; $inv_types .= 'i'; }

            $stmt_inv->bind_param($inv_types, ...$inv_params);
            if (!$stmt_inv->execute()) throw new Exception('Invoice INSERT execute failed: ' . $stmt_inv->error);
            $auto_inv_id = (int)$conn->insert_id;
            $stmt_inv->close();

            // Insert consultation fee line
            if ($consultation_fee > 0) {
                $stmt_ii2 = $conn->prepare(
                    "INSERT INTO invoice_items
                        (invoice_id, item_type, reference_id, description,
                         quantity, unit_price, total_price,
                         provider_id, commission_rate, commission_amount, date_of_service)
                     VALUES (?, 'CONSULTATION', ?, ?, 1, ?, ?, ?, 0.00, ?, CURDATE())"
                );
                if (!$stmt_ii2) throw new Exception('Invoice item INSERT (new) prepare failed: ' . $conn->error);
                $stmt_ii2->bind_param('iisddid',
                    $auto_inv_id, $consultation_id, $cons_desc,
                    $consultation_fee, $consultation_fee,
                    $doctor_id, $doctor_commission
                );
                if (!$stmt_ii2->execute()) throw new Exception('Invoice item INSERT (new) execute failed: ' . $stmt_ii2->error);
                $stmt_ii2->close();
            }

            // Insert procedure line items (Audiometry, Aural Toilet…)
            $append_proc_items($auto_inv_id);
        }

    } // end if (COMPLETED && invoice_total > 0)

    /* ────────────────────────────────────────────────────────────────────
       Auto-create follow-up appointment (COMPLETED only)
       NOTE: appointments has NO visit_id / consultation_id columns.
    ──────────────────────────────────────────────────────────────────── */
    if ($status === 'COMPLETED' && !empty($next_appointment)) {

        $chk = $conn->prepare("
            SELECT appointment_id FROM appointments
            WHERE  patient_id = ? AND doctor_id = ? AND appointment_date = ?
              AND  status != 'CANCELLED'
            LIMIT 1
        ");
        if (!$chk) throw new Exception('Appointment check prepare failed: ' . $conn->error);
        $chk->bind_param('iis', $patient_id, $doctor_id, $next_appointment);
        $chk->execute();
        $existing = $chk->get_result()->fetch_assoc();
        $chk->close();

        if (!$existing) {

            $anum_res = $conn->query("
                SELECT CONCAT(
                    'APT-', DATE_FORMAT(NOW(),'%Y%m%d'), '-',
                    LPAD(COALESCE(MAX(CAST(SUBSTRING_INDEX(appointment_number,'-',-1) AS UNSIGNED)),0)+1, 3,'0')
                ) AS new_number
                FROM appointments
                WHERE appointment_number LIKE CONCAT('APT-',DATE_FORMAT(NOW(),'%Y%m%d'),'%')
            ");
            $appt_number = ($anum_res)
                ? ($anum_res->fetch_assoc()['new_number'] ?? ('APT-' . date('Ymd') . '-001'))
                : ('APT-' . date('Ymd') . '-001');

            $appt_notes = 'Auto-created follow-up. Ref: visit_id=' . $visit_id
                        . ', consultation_id=' . $consultation_id;

            $appt = $conn->prepare("
                INSERT INTO appointments (
                    appointment_number, patient_id, doctor_id,
                    appointment_date, appointment_time,
                    appointment_type, booking_source, status,
                    notes, created_at, updated_at
                ) VALUES (
                    ?, ?, ?,
                    ?, '09:00:00',
                    'FOLLOW_UP', 'STAFF', 'SCHEDULED',
                    ?, NOW(), NOW()
                )
            ");
            if (!$appt) throw new Exception('Appointment INSERT prepare failed: ' . $conn->error);

            $appt->bind_param(
                'siiss',
                $appt_number,
                $patient_id,
                $doctor_id,
                $next_appointment,
                $appt_notes
            );
            if (!$appt->execute()) throw new Exception('Appointment INSERT execute failed: ' . $appt->error);
            $appt->close();
        }
    }

    $conn->commit();

    echo json_encode([
        'success'         => true,
        'consultation_id' => $consultation_id,
        'visit_id'        => $visit_id,
        'patient_id'      => $patient_id,
        'pro_bono'        => $is_pro_bono_visit,
        'status'          => $status,
        'message'         => match ($status) {
            'COMPLETED'       => 'Consultation completed successfully.',
            'PENDING_PREAUTH' => 'Consultation held pending pre-authorisation.',
            default           => 'Draft saved successfully.',
        },
    ]);

} catch (Exception $e) {
    if ($conn->in_transaction) $conn->rollback();
    error_log('save_consultation error: ' . $e->getMessage() . "\n" . $e->getTraceAsString());
    echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
}