<?php
require_once 'vendor/phpmailer/src/PHPMailer.php';
require_once 'vendor/autoload.php';

class NotificationHandler {
    private $conn;
    private $mailer;
    private $africastalking;
    private $company_name;
    
    public function __construct($db_connection) {
        $this->conn = $db_connection;
        $this->initializeMailer();
        $this->initializeAfricasTalking();
        
        // Get company name from settings
        $sql = "SELECT setting_value FROM settings WHERE setting_key = 'system_name'";
        $result = $this->conn->query($sql);
        $this->company_name = $result->fetch_assoc()['setting_value'];
    }
    
    private function initializeMailer() {
        $this->mailer = new PHPMailer\PHPMailer\PHPMailer(true);
        
        // Get email settings
        $sql = "SELECT setting_key, setting_value FROM settings WHERE setting_key IN 
                ('email_host', 'email_port', 'email_username', 'email_password', 'email_sender')";
        $result = $this->conn->query($sql);
        $settings = [];
        while ($row = $result->fetch_assoc()) {
            $settings[$row['setting_key']] = $row['setting_value'];
        }
        
        // Configure PHPMailer
        $this->mailer->isSMTP();
        $this->mailer->Host = $settings['email_host'];
        $this->mailer->SMTPAuth = true;
        $this->mailer->Username = $settings['email_username'];
        $this->mailer->Password = $settings['email_password'];
        $this->mailer->SMTPSecure = PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        $this->mailer->Port = $settings['email_port'];
        $this->mailer->setFrom($settings['email_sender'], $this->company_name);
    }
    
    private function initializeAfricasTalking() {
        // Initialize Africa's Talking API
        $username = "your-username"; // Get from settings
        $apiKey = "your-api-key"; // Get from settings
        $this->africastalking = new AfricasTalking($username, $apiKey);
    }
    
    public function queueBirthdayNotifications() {
        $sql = "SELECT m.*, pa.email, pa.mobile_no, c.corporate 
                FROM member_info m 
                JOIN principal_applicant pa ON m.family_no = pa.family_no
                JOIN corporate c ON m.corp_id = c.corp_id
                WHERE DATE_FORMAT(m.dob, '%m-%d') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), '%m-%d')
                AND m.status = 1";
                
        $result = $this->conn->query($sql);
        
        while ($member = $result->fetch_assoc()) {
            $template = $this->getTemplate('BIRTHDAY_REMINDER');
            
            // Prepare notification data
            $data = [
                'member_name' => $member['first_name'] . ' ' . $member['surname'],
                'company_name' => $this->company_name
            ];
            
            // Queue notifications
            $this->queueNotification($template, $data, $member['email'], $member['mobile_no']);
        }
    }
    
    public function queueSchemeRenewalNotifications() {
        $sql = "SELECT ca.*, c.corporate, c.email as corporate_email
                FROM corp_anniversary ca
                JOIN corporate c ON ca.corp_id = c.corp_id 
                WHERE ca.renewal_date BETWEEN NOW() 
                AND DATE_ADD(NOW(), INTERVAL 30 DAY)
                AND ca.renewal_notified = 0";
                
        $result = $this->conn->query($sql);
        
        while ($renewal = $result->fetch_assoc()) {
            $template = $this->getTemplate('SCHEME_RENEWAL');
            
            // Get notification recipients
            $recipients = $this->getNotificationRecipients($template['template_id']);
            
            foreach ($recipients as $recipient) {
                $data = [
                    'recipient_name' => $recipient['name'],
                    'corporate_name' => $renewal['corporate'],
                    'renewal_date' => date('d/m/Y', strtotime($renewal['renewal_date'])),
                    'company_name' => $this->company_name
                ];
                
                $this->queueNotification($template, $data, $recipient['email'], $recipient['phone']);
            }
            
            // Update notification status
            $sql = "UPDATE corp_anniversary SET renewal_notified = 1 
                    WHERE corp_id = ? AND anniv = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('si', $renewal['corp_id'], $renewal['anniv']);
            $stmt->execute();
        }
    }
    
    public function queuePendingDebitNotifications() {
        $sql = "SELECT pdn.*, c.corporate 
                FROM premium_debit_notes pdn
                JOIN corporate c ON pdn.corp_id = c.corp_id
                WHERE pdn.status = 'UNALLOCATED'
                AND DATE(pdn.created_at) = CURDATE()";
                
        $result = $this->conn->query($sql);
        
        while ($debit = $result->fetch_assoc()) {
            $template = $this->getTemplate('PENDING_DEBIT');
            $recipients = $this->getNotificationRecipients($template['template_id']);
            
            foreach ($recipients as $recipient) {
                $data = [
                    'recipient_name' => $recipient['name'],
                    'debit_no' => $debit['debit_no'],
                    'corporate_name' => $debit['corporate'],
                    'amount' => number_format($debit['amount'], 2),
                    'company_name' => $this->company_name
                ];
                
                $this->queueNotification($template, $data, $recipient['email'], $recipient['phone']);
            }
        }
    }
    
    private function getTemplate($templateCode) {
        $sql = "SELECT * FROM notification_templates 
                WHERE template_code = ? AND is_active = 1";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $templateCode);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    private function getNotificationRecipients($templateId) {
        $sql = "SELECT ns.*, u.full_name as name, u.email 
                FROM notification_settings ns
                LEFT JOIN users u ON ns.recipient_value = u.user_id 
                WHERE ns.template_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $templateId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    private function queueNotification($template, $data, $email = null, $phone = null) {
        // Replace placeholders in templates
        $subject = $this->replacePlaceholders($template['subject_template'], $data);
        $message = $this->replacePlaceholders($template['body_template'], $data);
        $smsMessage = $this->replacePlaceholders($template['sms_template'], $data);
        
        $sql = "INSERT INTO notification_queue 
                (template_id, recipient_email, recipient_phone, subject, message, 
                 sms_message, scheduled_at) 
                VALUES (?, ?, ?, ?, ?, ?, NOW())";
                
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('isssss', 
            $template['template_id'],
            $email,
            $phone,
            $subject,
            $message,
            $smsMessage
        );
        $stmt->execute();
    }
    
    private function replacePlaceholders($template, $data) {
        foreach ($data as $key => $value) {
            $template = str_replace('{' . $key . '}', $value, $template);
        }
        return $template;
    }
    
    public function processNotificationQueue() {
        $sql = "SELECT * FROM notification_queue 
                WHERE status = 'PENDING' 
                AND retry_count < 3
                AND scheduled_at <= NOW()
                LIMIT 50";
                
        $result = $this->conn->query($sql);
        
        while ($notification = $result->fetch_assoc()) {
            try {
                if ($notification['recipient_email']) {
                    $this->sendEmail(
                        $notification['recipient_email'],
                        $notification['subject'],
                        $notification['message']
                    );
                }
                
                if ($notification['recipient_phone']) {
                    $this->sendSMS(
                        $notification['recipient_phone'],
                        $notification['sms_message']
                    );
                }
                
                $this->updateNotificationStatus($notification['queue_id'], 'SENT');
                
            } catch (Exception $e) {
                $this->handleNotificationError($notification['queue_id'], $e->getMessage());
            }
        }
    }
    
    private function sendEmail($to, $subject, $message) {
        $this->mailer->clearAddresses();
        $this->mailer->addAddress($to);
        $this->mailer->Subject = $subject;
        $this->mailer->Body = $message;
        $this->mailer->send();
    }
    
    private function sendSMS($to, $message) {
        $sms = $this->africastalking->sms();
        $result = $sms->send([
            'to' => $to,
            'message' => $message
        ]);
        
        if (!$result['status']) {
            throw new Exception('SMS sending failed: ' . $result['message']);
        }
    }
    
    private function updateNotificationStatus($queueId, $status, $errorMessage = null) {
        $sql = "UPDATE notification_queue SET 
                status = ?, 
                sent_at = CASE WHEN ? = 'SENT' THEN NOW() ELSE NULL END,
                error_message = ?,
                retry_count = retry_count + 1
                WHERE queue_id = ?";
                
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('sssi', $status, $status, $errorMessage, $queueId);
        $stmt->execute();
    }
    
    private function handleNotificationError($queueId, $errorMessage) {
        $this->updateNotificationStatus($queueId, 'FAILED', $errorMessage);
    }
}
?>