<?php
// classes/ChatbotAppointmentBridge.php
// Bridges the chatbot with your existing appointment system

class ChatbotAppointmentBridge {
    private $db;
    private $chatbot;
    
    public function __construct($db, $chatbot) {
        $this->db = $db;
        $this->chatbot = $chatbot;
    }
    
    /**
     * Handle appointment booking through chatbot
     */
    public function processAppointmentBooking($data) {
        try {
            $this->db->begin_transaction();
            
            // Validate patient exists or create new patient
            $patient_id = $this->getOrCreatePatient($data);
            
            // Check if slot is available
            if (!$this->isSlotAvailable($data['doctor_id'], $data['appointment_date'], $data['appointment_time'])) {
                throw new Exception("Selected time slot is no longer available");
            }
            
            // Generate appointment number
            $appointment_number = $this->generateAppointmentNumber();
            
            // Insert appointment
            $stmt = $this->db->prepare("
                INSERT INTO appointments (
                    appointment_number, patient_id, doctor_id, slot_id,
                    appointment_date, appointment_time, appointment_type,
                    booking_source, chief_complaint, status, 
                    reminder_sent, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, 'ONLINE', ?, 'SCHEDULED', 0, NOW())
            ");
            
            $stmt->bind_param(
                "siiiisss",
                $appointment_number,
                $patient_id,
                $data['doctor_id'],
                $data['slot_id'],
                $data['appointment_date'],
                $data['appointment_time'],
                $data['appointment_type'],
                $data['chief_complaint']
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to create appointment");
            }
            
            $appointment_id = $stmt->insert_id;
            
            // Update slot booking count
            $this->updateSlotBooking($data['slot_id']);
            
            // Send confirmation messages (handled by trigger + job queue)
            // But also send immediate confirmation via chatbot
            $this->sendImmediateConfirmation($appointment_id, $patient_id);
            
            // Log activity
            $this->logActivity($appointment_id, $patient_id, 'appointment_booked_via_chatbot');
            
            $this->db->commit();
            
            return [
                'success' => true,
                'appointment_id' => $appointment_id,
                'appointment_number' => $appointment_number,
                'message' => "✅ Appointment booked successfully! Your reference number is: {$appointment_number}"
            ];
            
        } catch (Exception $e) {
            $this->db->rollback();
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Get or create patient from chatbot data
     */
    private function getOrCreatePatient($data) {
        // Check if patient exists by phone
        $stmt = $this->db->prepare("
            SELECT p.patient_id 
            FROM patients p
            INNER JOIN users u ON p.user_id = u.user_id
            WHERE u.phone_primary = ?
        ");
        $stmt->bind_param("s", $data['phone']);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($row = $result->fetch_assoc()) {
            return $row['patient_id'];
        }
        
        // Create new patient if not exists
        return $this->createNewPatient($data);
    }
    
    /**
     * Create new patient from chatbot data
     */
    private function createNewPatient($data) {
        // Generate patient number
        $stmt = $this->db->prepare("SELECT GeneratePatientNumber() as patient_number");
        $stmt->execute();
        $result = $stmt->get_result();
        $patient_number = $result->fetch_assoc()['patient_number'];
        
        // Insert user record first
        $stmt = $this->db->prepare("
            INSERT INTO users (
                username, full_name, email, phone_primary,
                user_type, status, created_at
            ) VALUES (?, ?, ?, ?, 'PATIENT', 'ACTIVE', NOW())
        ");
        
        $username = strtolower(str_replace(' ', '_', $data['name'])) . '_' . substr($data['phone'], -4);
        $stmt->bind_param("ssss", $username, $data['name'], $data['email'], $data['phone']);
        $stmt->execute();
        $user_id = $stmt->insert_id;
        
        // Insert patient record
        $stmt = $this->db->prepare("
            INSERT INTO patients (
                user_id, patient_number, date_of_birth, gender,
                blood_group, registration_date, registration_type,
                is_active, created_at
            ) VALUES (?, ?, ?, ?, NULL, CURDATE(), 'WALK_IN', 1, NOW())
        ");
        
        $stmt->bind_param("isss", $user_id, $patient_number, $data['dob'], $data['gender']);
        $stmt->execute();
        
        return $stmt->insert_id;
    }
    
    /**
     * Check if appointment slot is available
     */
    private function isSlotAvailable($doctor_id, $date, $time) {
        $stmt = $this->db->prepare("
            SELECT slot_id, max_patients, booked_patients, is_available
            FROM appointment_slots
            WHERE doctor_id = ? 
            AND slot_date = ? 
            AND start_time <= ? 
            AND end_time > ?
            AND is_available = 1
            AND booked_patients < max_patients
        ");
        
        $stmt->bind_param("isss", $doctor_id, $date, $time, $time);
        $stmt->execute();
        $result = $stmt->get_result();
        
        return $result->num_rows > 0;
    }
    
    /**
     * Update slot booking count
     */
    private function updateSlotBooking($slot_id) {
        $stmt = $this->db->prepare("
            UPDATE appointment_slots 
            SET booked_patients = booked_patients + 1,
                is_available = CASE 
                    WHEN booked_patients + 1 >= max_patients THEN 0 
                    ELSE 1 
                END
            WHERE slot_id = ?
        ");
        $stmt->bind_param("i", $slot_id);
        $stmt->execute();
    }
    
    /**
     * Generate unique appointment number
     */
    private function generateAppointmentNumber() {
        $prefix = 'APT';
        $date = date('Ymd');
        
        $stmt = $this->db->prepare("
            SELECT COUNT(*) as count 
            FROM appointments 
            WHERE DATE(created_at) = CURDATE()
        ");
        $stmt->execute();
        $result = $stmt->get_result();
        $count = $result->fetch_assoc()['count'] + 1;
        
        return $prefix . $date . str_pad($count, 4, '0', STR_PAD_LEFT);
    }
    
    /**
     * Send immediate confirmation through chatbot
     */
    private function sendImmediateConfirmation($appointment_id, $patient_id) {
        // Get appointment details
        $stmt = $this->db->prepare("
            SELECT 
                a.appointment_number,
                a.appointment_date,
                a.appointment_time,
                a.appointment_type,
                u.full_name as doctor_name,
                d.specialization
            FROM appointments a
            INNER JOIN doctors d ON a.doctor_id = d.doctor_id
            INNER JOIN users u ON d.user_id = u.user_id
            WHERE a.appointment_id = ?
        ");
        $stmt->bind_param("i", $appointment_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $appt = $result->fetch_assoc();
        
        $message = "🎉 **Appointment Confirmed!**\n\n" .
                   "📋 **Reference:** {$appt['appointment_number']}\n" .
                   "👨‍⚕️ **Doctor:** Dr. {$appt['doctor_name']}\n" .
                   "🏥 **Specialty:** {$appt['specialization']}\n" .
                   "📅 **Date:** " . date('l, F j, Y', strtotime($appt['appointment_date'])) . "\n" .
                   "🕐 **Time:** " . date('g:i A', strtotime($appt['appointment_time'])) . "\n" .
                   "📝 **Type:** {$appt['appointment_type']}\n\n" .
                   "⚠️ **Important:**\n" .
                   "• Arrive 15 minutes early\n" .
                   "• Bring your ID and insurance card\n" .
                   "• Bring any previous medical records\n\n" .
                   "You will receive a WhatsApp reminder 24 hours before your appointment.\n\n" .
                   "Need to reschedule? Reply with 'reschedule' or call us at " . ChatbotConfig::RECEPTION_PHONE;
        
        return $message;
    }
    
    /**
     * Get available appointment slots
     */
    public function getAvailableSlots($doctor_id, $date) {
        $stmt = $this->db->prepare("
            SELECT 
                slot_id,
                start_time,
                end_time,
                appointment_type,
                room_number,
                max_patients,
                booked_patients,
                (max_patients - booked_patients) as available_slots
            FROM appointment_slots
            WHERE doctor_id = ?
            AND slot_date = ?
            AND is_available = 1
            AND booked_patients < max_patients
            ORDER BY start_time ASC
        ");
        
        $stmt->bind_param("is", $doctor_id, $date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $slots = [];
        while ($row = $result->fetch_assoc()) {
            $slots[] = [
                'slot_id' => $row['slot_id'],
                'time' => date('g:i A', strtotime($row['start_time'])) . ' - ' . date('g:i A', strtotime($row['end_time'])),
                'type' => $row['appointment_type'],
                'room' => $row['room_number'],
                'available' => $row['available_slots']
            ];
        }
        
        return $slots;
    }
    
    /**
     * Get available doctors
     */
    public function getAvailableDoctors($date = null) {
        $date = $date ?? date('Y-m-d');
        
        $stmt = $this->db->prepare("
            SELECT DISTINCT
                d.doctor_id,
                u.full_name,
                d.specialization,
                d.sub_specialization,
                d.consultation_fee,
                d.experience_years,
                d.languages_spoken,
                COUNT(DISTINCT s.slot_id) as available_slots
            FROM doctors d
            INNER JOIN users u ON d.user_id = u.user_id
            LEFT JOIN appointment_slots s ON d.doctor_id = s.doctor_id 
                AND s.slot_date = ?
                AND s.is_available = 1
                AND s.booked_patients < s.max_patients
            WHERE d.is_active = 1
            AND u.status = 'ACTIVE'
            GROUP BY d.doctor_id
            HAVING available_slots > 0
            ORDER BY u.full_name ASC
        ");
        
        $stmt->bind_param("s", $date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $doctors = [];
        while ($row = $result->fetch_assoc()) {
            $doctors[] = [
                'doctor_id' => $row['doctor_id'],
                'name' => $row['full_name'],
                'specialization' => $row['specialization'],
                'sub_specialization' => $row['sub_specialization'],
                'fee' => number_format($row['consultation_fee'], 2),
                'experience' => $row['experience_years'] . ' years',
                'languages' => json_decode($row['languages_spoken'], true),
                'available_slots' => $row['available_slots']
            ];
        }
        
        return $doctors;
    }
    
    /**
     * Get patient's upcoming appointments
     */
    public function getPatientAppointments($patient_id, $limit = 5) {
        $stmt = $this->db->prepare("
            SELECT 
                a.appointment_id,
                a.appointment_number,
                a.appointment_date,
                a.appointment_time,
                a.appointment_type,
                a.status,
                u.full_name as doctor_name,
                d.specialization
            FROM appointments a
            INNER JOIN doctors d ON a.doctor_id = d.doctor_id
            INNER JOIN users u ON d.user_id = u.user_id
            WHERE a.patient_id = ?
            AND a.status IN ('SCHEDULED', 'CONFIRMED')
            AND a.appointment_date >= CURDATE()
            ORDER BY a.appointment_date ASC, a.appointment_time ASC
            LIMIT ?
        ");
        
        $stmt->bind_param("ii", $patient_id, $limit);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $appointments = [];
        while ($row = $result->fetch_assoc()) {
            $appointments[] = [
                'id' => $row['appointment_id'],
                'reference' => $row['appointment_number'],
                'date' => date('M j, Y', strtotime($row['appointment_date'])),
                'time' => date('g:i A', strtotime($row['appointment_time'])),
                'doctor' => 'Dr. ' . $row['doctor_name'],
                'specialty' => $row['specialization'],
                'type' => $row['appointment_type'],
                'status' => $row['status']
            ];
        }
        
        return $appointments;
    }
    
    /**
     * Cancel appointment through chatbot
     */
    public function cancelAppointment($appointment_id, $reason) {
        try {
            $this->db->begin_transaction();
            
            $stmt = $this->db->prepare("
                UPDATE appointments 
                SET status = 'CANCELLED',
                    cancellation_reason = ?,
                    updated_at = NOW()
                WHERE appointment_id = ?
                AND status IN ('SCHEDULED', 'CONFIRMED')
            ");
            
            $stmt->bind_param("si", $reason, $appointment_id);
            
            if (!$stmt->execute() || $stmt->affected_rows === 0) {
                throw new Exception("Unable to cancel appointment. It may already be cancelled or completed.");
            }
            
            // Free up the slot
            $this->freeAppointmentSlot($appointment_id);
            
            $this->db->commit();
            
            return [
                'success' => true,
                'message' => "✅ Appointment cancelled successfully. You can book a new appointment anytime."
            ];
            
        } catch (Exception $e) {
            $this->db->rollback();
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Free up appointment slot when cancelled
     */
    private function freeAppointmentSlot($appointment_id) {
        $stmt = $this->db->prepare("
            UPDATE appointment_slots s
            INNER JOIN appointments a ON s.slot_id = a.slot_id
            SET s.booked_patients = s.booked_patients - 1,
                s.is_available = 1
            WHERE a.appointment_id = ?
        ");
        $stmt->bind_param("i", $appointment_id);
        $stmt->execute();
    }
    
    /**
     * Log chatbot activity
     */
    private function logActivity($appointment_id, $patient_id, $action) {
        $stmt = $this->db->prepare("
            INSERT INTO activity_log (
                user_id, action, module, record_id, 
                record_type, ip_address, user_agent, logged_at
            ) VALUES (
                (SELECT user_id FROM patients WHERE patient_id = ?),
                ?, 'chatbot', ?, 'appointment',
                ?, ?, NOW()
            )
        ");
        
        $ip = $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
        $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? 'Chatbot';
        
        $stmt->bind_param("isiss", $patient_id, $action, $appointment_id, $ip, $user_agent);
        $stmt->execute();
    }
}