<?php

require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../utilities/Logger.php';
require_once __DIR__ . '/../models/IPWhitelist.php';

/**
 * SenderIdRequest model for interacting with sender ID requests
 */
class SenderIdRequest {
    // Status constants
    const STATUS_PENDING = 0;    // Maps to false in the database
    const STATUS_APPROVED = 1;   // Maps to 1 in the database
    const STATUS_REJECTED = 2;   // Maps to 2 in the database
    
    /**
     * Create a new sender ID request
     * 
     * @param string $clientId API client ID
     * @param string $senderId Sender ID text
     * @param string $username Username for authentication
     * @param string $password Password for authentication
     * @param string $ip IP address of requester
     * @return array Created request data or error
     */
    public static function create($clientId, $senderId, $username, $password, $ip) {
        try {
            // Verify IP is whitelisted
            if (!IPWhitelist::isWhitelisted($clientId, $ip)) {
                Logger::log(Logger::WARNING, "IP not whitelisted", [
                    'client_id' => $clientId,
                    'ip' => $ip
                ]);
                
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'IP address not authorized for this API key',
                        'code' => 'UNAUTHORIZED_IP'
                    ]
                ];
            }
            
            // First authenticate the user
            $userId = self::authenticateUser($username, $password);
            if (!$userId) {
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'Invalid username or password',
                        'code' => 'AUTH_FAILED'
                    ]
                ];
            }
            
            // Check for duplicate sender ID for this user
            if (self::isDuplicate($userId, $senderId)) {
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'Sender ID already exists for this user',
                        'code' => 'DUPLICATE_SENDER_ID'
                    ]
                ];
            }
            
            $db = Database::getPrimaryConnection();
            $apiDb = Database::getApiConnection();
            
            // Create a new request ID
            $requestId = 'REQ_' . time() . rand(1000, 9999);
            
            // Start transaction for both databases
            $db->begin_transaction();
            $apiDb->begin_transaction();
            
            try {
                // Insert the sender ID request into Deywuro's database
                $stmt = $db->prepare("
                    INSERT INTO user_sender_ids (user_id, sender_id, status, comment, created_at, updated_at, username) 
                    VALUES (?, ?, ?, NULL, NOW(), NOW(), ?)
                ");
                
                // Status 0 = pending
                $status = self::STATUS_PENDING;
                $stmt->bind_param("isis", $userId, $senderId, $status, $username);
                
                if (!$stmt->execute()) {
                    throw new Exception("Failed to create sender ID request: " . $stmt->error);
                }
                
                $deywuroId = $db->insert_id;
                $stmt->close();
                
                // Insert tracking record into our API database
                $stmtApi = $apiDb->prepare("
                    INSERT INTO sender_id_requests 
                    (client_id, sender_id, request_id, user_id, status, deywuro_id, ip_address, created_at, updated_at, notification_sent) 
                    VALUES (?, ?, ?, ?, 'PENDING', ?, ?, NOW(), NOW(), 0)
                ");
                
                $stmtApi->bind_param("sssiss", $clientId, $senderId, $requestId, $userId, $deywuroId, $ip);
                
                if (!$stmtApi->execute()) {
                    throw new Exception("Failed to create API sender ID request record: " . $stmtApi->error);
                }
                
                $stmtApi->close();
                
                // Commit both transactions
                $db->commit();
                $apiDb->commit();
                
                // Notify admins about new request
                self::notifyAdmins($senderId, $username);
                
                // Send notification email
                self::sendNotificationEmail($username, $senderId, $requestId);
                
                Logger::log(Logger::INFO, "New sender ID request created", [
                    'client_id' => $clientId,
                    'sender_id' => $senderId,
                    'request_id' => $requestId,
                    'deywuro_id' => $deywuroId
                ]);
                
                // Return success response
                return [
                    'success' => true,
                    'data' => [
                        'request_id' => $requestId,
                        'sender_id' => $senderId,
                        'status' => 'PENDING',
                        'created_at' => date('c')
                    ]
                ];
                
            } catch (Exception $innerException) {
                // Rollback both transactions
                $db->rollback();
                $apiDb->rollback();
                throw $innerException;
            }
            
        } catch (Exception $e) {
            error_log("DETAILED ERROR: " . $e->getMessage());
            error_log("Error trace: " . $e->getTraceAsString());
            Logger::log(Logger::ERROR, "Sender ID creation error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => [
                    'message' => 'Failed to create sender ID request: ' . $e->getMessage(),
                    'code' => 'SERVER_ERROR'
                ]
            ];
        }
    }
    
    /**
     * Get a sender ID request by request ID
     * 
     * @param string $clientId API client ID
     * @param string $requestId Request ID
     * @return array Request data or error
     */
    public static function getByRequestId($clientId, $requestId) {
        try {
            // Verify IP is whitelisted
            $ip = $_SERVER['REMOTE_ADDR'];
            if (!IPWhitelist::isWhitelisted($clientId, $ip)) {
                Logger::log(Logger::WARNING, "IP not whitelisted for request lookup", [
                    'client_id' => $clientId,
                    'ip' => $ip
                ]);
                
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'IP address not authorized for this API key',
                        'code' => 'UNAUTHORIZED_IP'
                    ]
                ];
            }
            
            // First check our API tracking table
            $apiDb = Database::getApiConnection();
            $db = Database::getPrimaryConnection();
            
            $stmt = $apiDb->prepare("
                SELECT user_id, sender_id, status, created_at, updated_at, deywuro_id 
                FROM sender_id_requests
                WHERE request_id = ? AND client_id = ?
            ");
            
            $stmt->bind_param("ss", $requestId, $clientId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'Request not found',
                        'code' => 'NOT_FOUND'
                    ]
                ];
            }
            
            $apiRecord = $result->fetch_assoc();
            $stmt->close();
            
            // Now get additional info from Deywuro database
            $stmt = $db->prepare("
                SELECT u.status, u.comment, usr.username 
                FROM user_sender_ids u
                JOIN users usr ON u.user_id = usr.id
                WHERE u.id = ?
            ");
            
            $stmt->bind_param("i", $apiRecord['deywuro_id']);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                // If not found in main DB but exists in our tracking DB,
                // return what we have from our tracking DB
                $username = "Unknown";
                $comment = null;
                $status = $apiRecord['status'];
            } else {
                $deywuroRecord = $result->fetch_assoc();
                $username = $deywuroRecord['username'];
                $comment = $deywuroRecord['comment'];
                
                // Status can come from either database
                // Latest status is from Deywuro database
                $statusMap = [
                    self::STATUS_PENDING => 'PENDING',
                    self::STATUS_APPROVED => 'APPROVED',
                    self::STATUS_REJECTED => 'REJECTED'
                ];
                
                // For compatibility with existing database, false = 0
                if ($deywuroRecord['status'] === '0' || $deywuroRecord['status'] === 0 || $deywuroRecord['status'] === false) {
                    $status = $statusMap[self::STATUS_PENDING];
                } else {
                    $status = $statusMap[$deywuroRecord['status']] ?? 'UNKNOWN';
                }
                
                // Update our API database if status has changed
                if ($status !== $apiRecord['status']) {
                    $updateStmt = $apiDb->prepare("
                        UPDATE sender_id_requests 
                        SET status = ?, updated_at = NOW()
                        WHERE request_id = ?
                    ");
                    $updateStmt->bind_param("ss", $status, $requestId);
                    $updateStmt->execute();
                    $updateStmt->close();
                }
            }
            
            $stmt->close();
            
            return [
                'success' => true,
                'data' => [
                    'request_id' => $requestId,
                    'sender_id' => $apiRecord['sender_id'],
                    'status' => $status,
                    'created_at' => date('c', strtotime($apiRecord['created_at'])),
                    'updated_at' => date('c', strtotime($apiRecord['updated_at'])),
                    'rejection_reason' => $comment,
                    'username' => $username
                ]
            ];
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Get sender ID request error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => [
                    'message' => 'Failed to retrieve sender ID request',
                    'code' => 'SERVER_ERROR'
                ]
            ];
        }
    }
    
    /**
     * Get sender ID requests by sender ID
     * 
     * @param string $clientId API client ID
     * @param string $senderId Sender ID text
     * @return array Matching requests or error
     */
    public static function getBySenderId($clientId, $senderId) {
        try {
            // Verify IP is whitelisted
            $ip = $_SERVER['REMOTE_ADDR'];
            if (!IPWhitelist::isWhitelisted($clientId, $ip)) {
                Logger::log(Logger::WARNING, "IP not whitelisted for sender ID lookup", [
                    'client_id' => $clientId,
                    'ip' => $ip
                ]);
                
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'IP address not authorized for this API key',
                        'code' => 'UNAUTHORIZED_IP'
                    ]
                ];
            }
            
            // Query our API database for this client's sender IDs
            $apiDb = Database::getApiConnection();
            
            $stmt = $apiDb->prepare("
                SELECT request_id, sender_id, status, created_at, updated_at, user_id, deywuro_id
                FROM sender_id_requests
                WHERE sender_id = ? AND client_id = ?
            ");
            
            $stmt->bind_param("ss", $senderId, $clientId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'No requests found for this sender ID',
                        'code' => 'NOT_FOUND'
                    ]
                ];
            }
            
            $requests = [];
            $deywuroIds = [];
            
            // First gather all requests from our API database
            $apiRequests = [];
            while ($row = $result->fetch_assoc()) {
                $apiRequests[$row['deywuro_id']] = [
                    'request_id' => $row['request_id'],
                    'sender_id' => $row['sender_id'],
                    'status' => $row['status'],
                    'created_at' => date('c', strtotime($row['created_at'])),
                    'updated_at' => date('c', strtotime($row['updated_at'])),
                    'user_id' => $row['user_id'],
                    'deywuro_id' => $row['deywuro_id']
                ];
                
                $deywuroIds[] = $row['deywuro_id'];
            }
            
            $stmt->close();
            
            if (count($deywuroIds) > 0) {
                // Get additional info from Deywuro database
                $db = Database::getPrimaryConnection();
                
                // Create placeholders for IN clause
                $placeholders = str_repeat("?,", count($deywuroIds) - 1) . "?";
                
                $stmt = $db->prepare("
                    SELECT u.id, u.status, u.comment, usr.username 
                    FROM user_sender_ids u
                    JOIN users usr ON u.user_id = usr.id
                    WHERE u.id IN ({$placeholders})
                ");
                
                // Bind all parameters dynamically
                $bindTypes = str_repeat("i", count($deywuroIds));
                $bindParams = [$bindTypes];
                foreach ($deywuroIds as $id) {
                    $bindParams[] = &$id;
                }
                
                call_user_func_array([$stmt, 'bind_param'], $bindParams);
                $stmt->execute();
                $result = $stmt->get_result();
                
                // Update our requests with Deywuro database info
                while ($row = $result->fetch_assoc()) {
                    $id = $row['id'];
                    if (isset($apiRequests[$id])) {
                        // Map the status to string values
                        $statusMap = [
                            self::STATUS_PENDING => 'PENDING',
                            self::STATUS_APPROVED => 'APPROVED',
                            self::STATUS_REJECTED => 'REJECTED'
                        ];
                        
                        // For compatibility with existing database, false = 0
                        if ($row['status'] === '0' || $row['status'] === 0 || $row['status'] === false) {
                            $status = $statusMap[self::STATUS_PENDING];
                        } else {
                            $status = $statusMap[$row['status']] ?? 'UNKNOWN';
                        }
                        
                        $requests[] = [
                            'request_id' => $apiRequests[$id]['request_id'],
                            'sender_id' => $apiRequests[$id]['sender_id'],
                            'status' => $status,
                            'created_at' => $apiRequests[$id]['created_at'],
                            'updated_at' => $apiRequests[$id]['updated_at'],
                            'rejection_reason' => $row['comment'],
                            'username' => $row['username']
                        ];
                        
                        // Update our API database if status has changed
                        if ($status !== $apiRequests[$id]['status']) {
                            $updateStmt = $apiDb->prepare("
                                UPDATE sender_id_requests 
                                SET status = ?, updated_at = NOW()
                                WHERE deywuro_id = ?
                            ");
                            $updateStmt->bind_param("si", $status, $id);
                            $updateStmt->execute();
                            $updateStmt->close();
                        }
                    }
                }
                
                $stmt->close();
            }
            
            return [
                'success' => true,
                'data' => $requests
            ];
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Get sender ID by sender text error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => [
                    'message' => 'Failed to retrieve sender ID requests',
                    'code' => 'SERVER_ERROR'
                ]
            ];
        }
    }
    
    /**
     * Get all sender ID requests with pagination and filtering
     * 
     * @param string $clientId API client ID
     * @param array $filters Filters (status, date_from, date_to)
     * @param int $page Page number
     * @param int $limit Results per page
     * @return array Paginated requests or error
     */
    public static function getAll($clientId, $filters = [], $page = 1, $limit = 20) {
        try {
            // Verify IP is whitelisted
            $ip = $_SERVER['REMOTE_ADDR'];
            if (!IPWhitelist::isWhitelisted($clientId, $ip)) {
                Logger::log(Logger::WARNING, "IP not whitelisted for sender ID listing", [
                    'client_id' => $clientId,
                    'ip' => $ip
                ]);
                
                return [
                    'success' => false,
                    'error' => [
                        'message' => 'IP address not authorized for this API key',
                        'code' => 'UNAUTHORIZED_IP'
                    ]
                ];
            }
            
            // Use our API database for listing with pagination
            $apiDb = Database::getApiConnection();
            
            // Base query
            $query = "
                SELECT request_id, sender_id, status, created_at, updated_at, user_id, deywuro_id
                FROM sender_id_requests
                WHERE client_id = ?
            ";
            
            // Build where clause with filters
            $params = [$clientId];
            $types = "s";
            
            // Status filter
            if (!empty($filters['status'])) {
                $query .= " AND status = ?";
                $params[] = strtoupper($filters['status']);
                $types .= "s";
            }
            
            // Date from filter
            if (!empty($filters['date_from'])) {
                $query .= " AND DATE(created_at) >= ?";
                $params[] = $filters['date_from'];
                $types .= "s";
            }
            
            // Date to filter
            if (!empty($filters['date_to'])) {
                $query .= " AND DATE(created_at) <= ?";
                $params[] = $filters['date_to'];
                $types .= "s";
            }
            
            // Count total records for pagination
            $countQuery = str_replace("SELECT request_id, sender_id, status, created_at, updated_at, user_id, deywuro_id", "SELECT COUNT(*) as total", $query);
            $stmt = $apiDb->prepare($countQuery);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $totalResult = $stmt->get_result();
            $totalRow = $totalResult->fetch_assoc();
            $totalRecords = $totalRow['total'];
            $stmt->close();
            
            // Calculate pagination
            $totalPages = ceil($totalRecords / $limit);
            $offset = ($page - 1) * $limit;
            
            // Add order and limit
            $query .= " ORDER BY created_at DESC LIMIT ?, ?";
            $params[] = $offset;
            $params[] = $limit;
            $types .= "ii";
            
            // Get paginated results from API database
            $stmt = $apiDb->prepare($query);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $requests = [];
            $userIds = [];
            $deywuroIdMapping = [];
            
            while ($row = $result->fetch_assoc()) {
                $requests[] = [
                    'request_id' => $row['request_id'],
                    'sender_id' => $row['sender_id'],
                    'status' => $row['status'],
                    'created_at' => date('c', strtotime($row['created_at'])),
                    'updated_at' => date('c', strtotime($row['updated_at'])),
                    'rejection_reason' => null,
                    'username' => null,
                    'user_id' => $row['user_id'],
                    'deywuro_id' => $row['deywuro_id']
                ];
                
                $userIds[] = $row['user_id'];
                $deywuroIdMapping[$row['deywuro_id']] = count($requests) - 1;
            }
            
            $stmt->close();
            
            // Get usernames from primary database if we have results
            if (count($requests) > 0) {
                $db = Database::getPrimaryConnection();
                
                // First get usernames
                $uniqueUserIds = array_unique($userIds);
                $userPlaceholders = str_repeat("?,", count($uniqueUserIds) - 1) . "?";
                
                $userStmt = $db->prepare("
                    SELECT id, username FROM users WHERE id IN ({$userPlaceholders})
                ");
                
                $bindTypes = str_repeat("i", count($uniqueUserIds));
                $bindParams = [$bindTypes];
                foreach ($uniqueUserIds as $id) {
                    $bindParams[] = &$id;
                }
                
                call_user_func_array([$userStmt, 'bind_param'], $bindParams);
                $userStmt->execute();
                $userResult = $userStmt->get_result();
                
                $usernames = [];
                while ($userRow = $userResult->fetch_assoc()) {
                    $usernames[$userRow['id']] = $userRow['username'];
                }
                
                $userStmt->close();
                
                // Update requests with usernames
                foreach ($requests as $index => $request) {
                    if (isset($usernames[$request['user_id']])) {
                        $requests[$index]['username'] = $usernames[$request['user_id']];
                    }
                }
                
                // Get rejection reasons from user_sender_ids
                $deywuroIds = array_keys($deywuroIdMapping);
                if (count($deywuroIds) > 0) {
                    $deywuroPlaceholders = str_repeat("?,", count($deywuroIds) - 1) . "?";
                    
                    $commentStmt = $db->prepare("
                        SELECT id, comment FROM user_sender_ids WHERE id IN ({$deywuroPlaceholders})
                    ");
                    
                    $bindTypes = str_repeat("i", count($deywuroIds));
                    $bindParams = [$bindTypes];
                    foreach ($deywuroIds as $id) {
                        $bindParams[] = &$id;
                    }
                    
                    call_user_func_array([$commentStmt, 'bind_param'], $bindParams);
                    $commentStmt->execute();
                    $commentResult = $commentStmt->get_result();
                    
                    while ($commentRow = $commentResult->fetch_assoc()) {
                        $index = $deywuroIdMapping[$commentRow['id']];
                        $requests[$index]['rejection_reason'] = $commentRow['comment'];
                    }
                    
                    $commentStmt->close();
                }
                
                // Remove internal fields
                foreach ($requests as $index => $request) {
                    unset($requests[$index]['user_id']);
                    unset($requests[$index]['deywuro_id']);
                }
            }
            
            return [
                'success' => true,
                'data' => [
                    'requests' => $requests,
                    'pagination' => [
                        'total' => $totalRecords,
                        'page' => $page,
                        'limit' => $limit,
                        'pages' => $totalPages
                    ]
                ]
            ];
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Get all sender IDs error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => [
                    'message' => 'Failed to retrieve sender ID requests',
                    'code' => 'SERVER_ERROR'
                ]
            ];
        }
    }
    
    /**
     * Send notifications to admins about new sender ID request
     * 
     * @param string $senderId Sender ID requested
     * @param string $username Username of requester
     * @return void
     */
    private static function notifyAdmins($senderId, $username) {
        try {
            // Get user details
            $db = Database::getPrimaryConnection();
            $stmt = $db->prepare("
                SELECT u.first_name, u.last_name, u.phone_number, u.email
                FROM users u
                WHERE u.username = ?
            ");
            
            $stmt->bind_param("s", $username);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                Logger::log(Logger::WARNING, "User not found for admin notification", ['username' => $username]);
                return;
            }
            
            $user = $result->fetch_assoc();
            $stmt->close();
            
            // Prepare notification message
            $clientName = $user['first_name'] . ' ' . $user['last_name'];
            $phoneNumber = $user['phone_number'];
            
            $message = "Hello Team,\n\n";
            $message .= "The following client has submitted a new SENDER ID for approval\n";
            $message .= "Client: {$clientName}\n";
            $message .= "Phone Number: {$phoneNumber}\n";
            $message .= "SENDER ID: {$senderId}\n";
            $message .= "Thank You,\nDeywuro API";
            
            // Send SMS notification
            self::sendSms($message, ADMIN_PHONE_NUMBERS);
            
            // Send email notification
            $emailData = [
                'client' => $clientName,
                'phone' => $phoneNumber,
                'source' => $senderId
            ];
            
            self::sendEmail(ADMIN_EMAIL, 'SENDER ID REQUEST', $emailData);
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Admin notification error: " . $e->getMessage());
        }
    }
    
    /**
     * Send SMS notification
     * 
     * @param string $message SMS message
     * @param string $phoneNumbers Comma-separated phone numbers
     * @return void
     */
    private static function sendSms($message, $phoneNumbers) {
        try {
            // Prepare API request
            $url = SMS_API_URL;
            $params = [
                'username' => SMS_USERNAME,
                'password' => SMS_PASSWORD,
                'source' => SMS_SOURCE,
                'destination' => $phoneNumbers,
                'message' => $message
            ];
            
            // Initialize cURL session
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url . '?' . http_build_query($params));
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_TIMEOUT, API_TIMEOUT);
            
            // Execute request
            $response = curl_exec($ch);
            $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);
            
            if ($httpCode != 200) {
                Logger::log(Logger::WARNING, "SMS notification failed", [
                    'http_code' => $httpCode,
                    'response' => $response
                ]);
            }
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "SMS sending error: " . $e->getMessage());
        }
    }
    
    /**
     * Send email notification
     * 
     * @param string $to Recipient email
     * @param string $subject Email subject
     * @param array $data Email data
     * @return void
     */
    private static function sendEmail($to, $subject, $data) {
        try {
            // Simple email headers
            $headers = "From: sms@deywuro.com\r\n";
            $headers .= "Reply-To: sms@deywuro.com\r\n";
            $headers .= "MIME-Version: 1.0\r\n";
            $headers .= "Content-Type: text/html; charset=UTF-8\r\n";
            
            // Build email body (simple template)
            $body = "
                <html>
                <head>
                    <title>Sender ID Request</title>
                </head>
                <body>
                    <h2>New Sender ID Request</h2>
                    <p><strong>Client:</strong> {$data['client']}</p>
                    <p><strong>Phone Number:</strong> {$data['phone']}</p>
                    <p><strong>Sender ID:</strong> {$data['source']}</p>
                    <p>This request was submitted via the API.</p>
                </body>
                </html>
            ";
            
            // Send email
            mail($to, $subject, $body, $headers);
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Email sending error: " . $e->getMessage());
        }
    }
    
    /**
     * Authenticate user credentials
     * 
     * @param string $username Username
     * @param string $password Password
     * @return int|false User ID if valid, false otherwise
     */
    private static function authenticateUser($username, $password) {
        try {
            error_log("Attempting to authenticate user: $username");
            $db = Database::getPrimaryConnection();
            
            $stmt = $db->prepare("
                SELECT id, password 
                FROM users 
                WHERE username = ?
            ");
            
            if (!$stmt) {
                error_log("Prepare statement failed: " . $db->error);
                return false;
            }
            
            $stmt->bind_param("s", $username);
            if (!$stmt->execute()) {
                error_log("Execute failed: " . $stmt->error);
                return false;
            }
            
            $result = $stmt->get_result();
            
            if ($result->num_rows == 0) {
                error_log("No user found with username: $username");
                return false;
            }
            
            $user = $result->fetch_assoc();
            error_log("User found with ID: " . $user['id']);
            $stmt->close();
            
            // Check if password is correct
            // For debugging only - remove in production
            error_log("DB Password: " . substr($user['password'], 0, 3) . "..." . substr($user['password'], -3));
            error_log("Input Password: " . substr($password, 0, 3) . "..." . substr($password, -3));
            
            // Note: In the existing system, passwords might be stored directly or hashed
            // This assumes passwords are hashed with PHP's password_hash
            if (password_verify($password, $user['password'])) {
                error_log("Password verified with hash");
                return $user['id'];
            }
            
            // As fallback, check if the password is stored directly
            if ($password === $user['password']) {
                error_log("Password matched directly");
                return $user['id'];
            }
            
            error_log("Password does not match");
            return false;
            
        } catch (Exception $e) {
            $errorMsg = "User authentication error: " . $e->getMessage();
            Logger::log(Logger::ERROR, $errorMsg);
            error_log($errorMsg);
            return false;
        }
    }
    
    /**
     * Check if a sender ID already exists for this user
     * 
     * @param int $userId User ID
     * @param string $senderId Sender ID text
     * @return bool True if duplicate exists
     */
    private static function isDuplicate($userId, $senderId) {
        try {
            $db = Database::getPrimaryConnection();
            
            $stmt = $db->prepare("
                SELECT id 
                FROM user_sender_ids 
                WHERE user_id = ? AND sender_id = ?
            ");
            
            $stmt->bind_param("is", $userId, $senderId);
            $stmt->execute();
            $result = $stmt->get_result();
            $isDuplicate = $result->num_rows > 0;
            $stmt->close();
            
            return $isDuplicate;
            
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, "Duplicate check error: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Send notification email for sender ID creation
     * 
     * @param string $username Username
     * @param string $senderId Sender ID
     * @param string $requestId Request ID
     * @return bool Success status
     */
    private static function sendNotificationEmail($username, $senderId, $requestId)
    {
        try {
            // Get user email from database
            $db = Database::getPrimaryConnection();
            $stmt = $db->prepare("
                SELECT u.email, u.first_name, u.last_name, c.contact_person_email, c.contact_person_phone, c.name
                FROM users u
                LEFT JOIN companies c ON u.company_id = c.id
                WHERE u.username = ?
            ");
            $stmt->bind_param("s", $username);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($row = $result->fetch_assoc()) {
                // Use contact_person_email if available, otherwise fall back to user email
                $email = !empty($row['contact_person_email']) ? $row['contact_person_email'] : $row['email'];
                $name = $row['first_name'] . ' ' . $row['last_name'];
                $companyName = $row['name'];
                $phone = $row['contact_person_phone'];
                
                if (empty($email)) {
                    Logger::log(Logger::WARNING, 'No email found for user', ['username' => $username]);
                    return false;
                }
                
                // Prepare email
                $subject = "Sender ID Request - {$senderId}";
                $headers = [
                    'From: Deywuro <no-reply@deywuro.com>',
                    'Content-Type: text/html; charset=UTF-8'
                ];
                
                $message = "
                <html>
                <head>
                    <title>Sender ID Request</title>
                    <style>
                        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
                        .container { max-width: 600px; margin: 0 auto; padding: 20px; }
                        .header { background: #0078d4; color: white; padding: 20px; text-align: center; }
                        .content { padding: 20px; }
                        .footer { padding: 20px; text-align: center; font-size: 12px; color: #666; }
                        .button { display: inline-block; padding: 10px 20px; background: #0078d4; color: white; text-decoration: none; border-radius: 4px; }
                        table { border-collapse: collapse; width: 100%; }
                        th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
                    </style>
                </head>
                <body>
                    <div class='container'>
                        <div class='header'>
                            <h1>Sender ID Request Submitted</h1>
                        </div>
                        <div class='content'>
                            <p>Dear {$name},</p>
                            <p>Your request for the sender ID '<strong>{$senderId}</strong>' has been submitted successfully.</p>
                            <p>Please find the details of your request below:</p>
                            
                            <table>
                                <tr>
                                    <th>Request ID:</th>
                                    <td>{$requestId}</td>
                                </tr>
                                <tr>
                                    <th>Sender ID:</th>
                                    <td>{$senderId}</td>
                                </tr>
                                <tr>
                                    <th>Status:</th>
                                    <td>Pending Approval</td>
                                </tr>
                                <tr>
                                    <th>Submission Date:</th>
                                    <td>" . date('Y-m-d H:i:s') . "</td>
                                </tr>
                            </table>
                            
                            <p>Your sender ID request is now being reviewed. You will be notified once the request is approved or rejected.</p>
                            
                            <p>If you have any questions, please contact our support team at support@deywuro.com</p>
                        </div>
                        <div class='footer'>
                            <p>&copy; " . date('Y') . " Deywuro. All rights reserved.</p>
                        </div>
                    </div>
                </body>
                </html>
                ";
                
                // Send email
                $sent = mail($email, $subject, $message, implode("\r\n", $headers));
                
                // Also send notification to admin
                $adminEmail = 'admin@deywuro.com';
                $adminSubject = "New Sender ID Request - {$senderId}";
                $adminMessage = "
                <html>
                <head>
                    <title>New Sender ID Request</title>
                    <style>
                        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
                        .container { max-width: 600px; margin: 0 auto; padding: 20px; }
                        .header { background: #0078d4; color: white; padding: 20px; text-align: center; }
                        .content { padding: 20px; }
                        .footer { padding: 20px; text-align: center; font-size: 12px; color: #666; }
                        .button { display: inline-block; padding: 10px 20px; background: #0078d4; color: white; text-decoration: none; border-radius: 4px; }
                        table { border-collapse: collapse; width: 100%; }
                        th, td { padding: 8px; text-align: left; border-bottom: 1px solid #ddd; }
                    </style>
                </head>
                <body>
                    <div class='container'>
                        <div class='header'>
                            <h1>New Sender ID Request</h1>
                        </div>
                        <div class='content'>
                            <p>A new sender ID request has been submitted:</p>
                            
                            <table>
                                <tr>
                                    <th>Request ID:</th>
                                    <td>{$requestId}</td>
                                </tr>
                                <tr>
                                    <th>Sender ID:</th>
                                    <td>{$senderId}</td>
                                </tr>
                                <tr>
                                    <th>Username:</th>
                                    <td>{$username}</td>
                                </tr>
                                <tr>
                                    <th>Company:</th>
                                    <td>{$companyName}</td>
                                </tr>
                                <tr>
                                    <th>Contact Phone:</th>
                                    <td>{$phone}</td>
                                </tr>
                                <tr>
                                    <th>Contact Email:</th>
                                    <td>{$email}</td>
                                </tr>
                                <tr>
                                    <th>Submission Date:</th>
                                    <td>" . date('Y-m-d H:i:s') . "</td>
                                </tr>
                            </table>
                            
                            <p>Please review this request in the admin dashboard.</p>
                        </div>
                        <div class='footer'>
                            <p>&copy; " . date('Y') . " Deywuro. All rights reserved.</p>
                        </div>
                    </div>
                </body>
                </html>
                ";
                
                mail($adminEmail, $adminSubject, $adminMessage, implode("\r\n", $headers));
                
                Logger::log(Logger::INFO, 'Notification email sent', [
                    'username' => $username, 
                    'sender_id' => $senderId,
                    'email' => $email,
                    'status' => $sent ? 'sent' : 'failed'
                ]);
                
                return $sent;
            }
            
            return false;
        } catch (Exception $e) {
            Logger::log(Logger::ERROR, 'Failed to send notification email', [
                'username' => $username,
                'sender_id' => $senderId,
                'error' => $e->getMessage()
            ]);
            return false;
        }
    }
}