<?php
/**
 * Assignment Service
 * 
 * This service provides methods to manage member assignments
 */

namespace Services\Assignment;

use Services\Core\Database;
use Services\Notification\NotificationService;

class AssignmentService {
    private $db;
    private $notificationService;
    
    /**
     * Constructor
     */
    public function __construct() {
        require_once __DIR__ . '/../../config/database.php';
        require_once __DIR__ . '/../notification/NotificationService.php';
        
        $this->db = new Database();
        $this->notificationService = new NotificationService();
    }
    
    /**
     * Count pending assignments for an admin
     * 
     * @param int $adminId Admin ID
     * @return int Number of pending assignments
     */
    public function countPendingAssignmentsForAdmin($adminId) {
        try {
            $query = "SELECT COUNT(*) as count FROM assignments 
                     WHERE assigned_to_admin = :adminId 
                     AND status = 'pending'";
            
            $params = [':adminId' => $adminId];
            $result = $this->db->fetchOne($query, $params);
            
            return $result ? (int)$result['count'] : 0;
        } catch (\Exception $e) {
            error_log('Error counting pending assignments: ' . $e->getMessage());
            return 0;
        }
    }
    
    /**
     * Get assignment statistics
     * 
     * @return array Assignment statistics
     */
    public function getAssignmentStatistics() {
        try {
            $conn = $this->db->getConnection();
            
            // Get total assignments
            $totalQuery = "SELECT COUNT(*) as total FROM admin_assignments";
            $totalStmt = $conn->prepare($totalQuery);
            $totalStmt->execute();
            $totalResult = $totalStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Get active assignments
            $activeQuery = "SELECT COUNT(*) as active FROM admin_assignments WHERE status = 'active'";
            $activeStmt = $conn->prepare($activeQuery);
            $activeStmt->execute();
            $activeResult = $activeStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Get urgent assignments
            $urgentQuery = "SELECT COUNT(*) as urgent FROM admin_assignments WHERE priority = 'urgent' AND status = 'active'";
            $urgentStmt = $conn->prepare($urgentQuery);
            $urgentStmt->execute();
            $urgentResult = $urgentStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Get overdue assignments
            $overdueQuery = "SELECT COUNT(*) as overdue FROM admin_assignments 
                            WHERE status = 'active' 
                            AND due_date IS NOT NULL 
                            AND due_date < NOW()";
            $overdueStmt = $conn->prepare($overdueQuery);
            $overdueStmt->execute();
            $overdueResult = $overdueStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Get assignment type counts
            $typeQuery = "SELECT 
                            SUM(CASE WHEN assignment_type = 'review' THEN 1 ELSE 0 END) as type_review,
                            SUM(CASE WHEN assignment_type = 'approval' THEN 1 ELSE 0 END) as type_approval,
                            SUM(CASE WHEN assignment_type = 'verification' THEN 1 ELSE 0 END) as type_verification,
                            SUM(CASE WHEN assignment_type = 'follow_up' THEN 1 ELSE 0 END) as type_follow_up
                        FROM admin_assignments
                        WHERE status = 'active'";
            $typeStmt = $conn->prepare($typeQuery);
            $typeStmt->execute();
            $typeResult = $typeStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Get priority counts
            $priorityQuery = "SELECT 
                                SUM(CASE WHEN priority = 'low' THEN 1 ELSE 0 END) as priority_low,
                                SUM(CASE WHEN priority = 'normal' THEN 1 ELSE 0 END) as priority_normal,
                                SUM(CASE WHEN priority = 'high' THEN 1 ELSE 0 END) as priority_high,
                                SUM(CASE WHEN priority = 'urgent' THEN 1 ELSE 0 END) as priority_urgent
                            FROM admin_assignments
                            WHERE status = 'active'";
            $priorityStmt = $conn->prepare($priorityQuery);
            $priorityStmt->execute();
            $priorityResult = $priorityStmt->fetch(\PDO::FETCH_ASSOC);
            
            // Combine results
            $statistics = [
                'total' => $totalResult['total'],
                'active' => $activeResult['active'],
                'urgent' => $urgentResult['urgent'],
                'overdue' => $overdueResult['overdue']
            ];
            
            // Add type counts
            if ($typeResult) {
                $statistics = array_merge($statistics, $typeResult);
            }
            
            // Add priority counts
            if ($priorityResult) {
                $statistics = array_merge($statistics, $priorityResult);
            }
            
            return $statistics;
            
        } catch (\Exception $e) {
            return [
                'total' => 0,
                'active' => 0,
                'urgent' => 0,
                'overdue' => 0
            ];
        }
    }
    
    /**
     * Assign a member to an admin
     * 
     * @param int $memberId Member ID
     * @param int $assignedTo ID of the admin
     * @param int $assignedBy ID of the admin making the assignment
     * @param string $assignmentType Type of assignment ('review', 'approval', 'verification', 'follow_up')
     * @param string $priority Priority level ('low', 'normal', 'high', 'urgent')
     * @param string $notes Optional notes about the assignment
     * @param string $dueDate Optional due date (format: YYYY-MM-DD HH:MM:SS)
     * @return array Result with success status
     */
    public function assignMember($memberId, $assignedTo, $assignedBy, $assignmentType = 'review', $priority = 'normal', 
                                $notes = '', $dueDate = null) {
        // Deprecated function signature
        // Use the new signature: assignMember($memberId, $assignedTo, $assignedBy, $assignmentType = 'review', $priority = 'normal', $notes = '', $dueDate = null)
        return $this->assignMemberV2($memberId, $assignedTo, $assignedBy, $assignmentType, $priority, $notes, $dueDate);
    }
    
    /**
     * Assigns a member to an admin for review or other tasks
     * 
     * @param int $memberId The ID of the member to assign
     * @param int $assignedTo The ID of the admin to assign the member to
     * @param int $assignedBy The ID of the admin making the assignment
     * @param string $assignmentType Type of assignment (default: 'review')
     * @param string $priority Priority of the assignment (default: 'normal')
     * @param string $notes Optional notes about the assignment
     * @param string|null $dueDate Optional due date for the assignment
     * @return array Success status and message
     */
    public function assignMemberV2($memberId, $assignedTo, $assignedBy, $assignmentType = 'review', $priority = 'normal', 
                                $notes = '', $dueDate = null) {
        try {
            $conn = $this->db->getConnection();
            
            // Start transaction
            $conn->beginTransaction();
            
            // Check if member exists
            $memberQuery = "SELECT id, first_name, last_name FROM members WHERE id = :member_id";
            $memberStmt = $conn->prepare($memberQuery);
            $memberStmt->bindParam(':member_id', $memberId);
            $memberStmt->execute();
            
            $member = $memberStmt->fetch(\PDO::FETCH_ASSOC);
            
            if (!$member) {
                return [
                    'success' => false,
                    'message' => 'Member not found'
                ];
            }
            
            // Check if admin exists
            $adminQuery = "SELECT id, username FROM admin_users WHERE id = :assigned_to";
            $adminStmt = $conn->prepare($adminQuery);
            $adminStmt->bindParam(':assigned_to', $assignedTo);
            $adminStmt->execute();
            
            $admin = $adminStmt->fetch(\PDO::FETCH_ASSOC);
            
            if (!$admin) {
                return [
                    'success' => false,
                    'message' => 'Admin not found'
                ];
            }
            
            // Check if assignment already exists
            $checkQuery = "SELECT id FROM admin_assignments 
                          WHERE member_id = :member_id 
                          AND assigned_to = :assigned_to 
                          AND status = 'active'";
            
            $checkStmt = $conn->prepare($checkQuery);
            $checkStmt->bindParam(':member_id', $memberId);
            $checkStmt->bindParam(':assigned_to', $assignedTo);
            $checkStmt->execute();
            
            if ($checkStmt->rowCount() > 0) {
                return [
                    'success' => false,
                    'message' => 'Member is already assigned to this admin'
                ];
            }
            
            // Create assignment
            $query = "INSERT INTO admin_assignments 
                     (member_id, assigned_to, assigned_by, assignment_type, priority, status, notes, due_date, created_at) 
                     VALUES (:member_id, :assigned_to, :assigned_by, :assignment_type, :priority, 'active', :notes, :due_date, NOW())";
            
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':member_id', $memberId);
            $stmt->bindParam(':assigned_to', $assignedTo);
            $stmt->bindParam(':assigned_by', $assignedBy);
            $stmt->bindParam(':assignment_type', $assignmentType);
            $stmt->bindParam(':priority', $priority);
            $stmt->bindParam(':notes', $notes);
            $stmt->bindParam(':due_date', $dueDate);
            $stmt->execute();
            
            $assignmentId = $conn->lastInsertId();
            
            // Create notification for the assigned admin
            $memberName = $member['first_name'] . ' ' . $member['last_name'];
            $adminName = $admin['username'];
            
            // Create notification
            $notificationData = [
                'title' => 'New Member Assignment',
                'message' => "Member $memberName has been assigned to you for $assignmentType.",
                'type' => 'assignment',
                'priority' => $priority,
                'target_audience' => 'specific',
                'specific_recipients' => ['admin_' . $assignedTo],
                'created_by' => $assignedBy
            ];
            
            $this->notificationService->createNotification($notificationData);
            
            // Commit transaction
            $conn->commit();
            
            return [
                'success' => true,
                'assignment_id' => $assignmentId,
                'message' => "Member $memberName has been assigned to $adminName successfully for $assignmentType"
            ];
        } catch (\Exception $e) {
            // Rollback transaction on error
            if (isset($conn)) {
                $conn->rollBack();
            }
            
            return [
                'success' => false,
                'message' => 'Error assigning member: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Unassign a member by setting assignment status to cancelled
     * 
     * @param int $assignmentId Assignment ID
     * @param int $adminId Admin ID who is performing the unassignment
     * @param string $reason Optional reason for unassignment
     * @param string $status Status to set ('cancelled' by default, could also be 'completed')
     * @return array Result with success status
     */
    public function unassignMember($assignmentId, $adminId, $reason = '', $status = 'cancelled') {
        try {
            $conn = $this->db->getConnection();
            
            // Start transaction
            $conn->beginTransaction();
            
            // Check if assignment exists
            $assignmentQuery = "SELECT aa.id, m.id as member_id, m.first_name, m.last_name,
                                      aa.assigned_to, aa.assigned_by, aa.assignment_type, aa.priority
                               FROM admin_assignments aa
                               JOIN members m ON aa.member_id = m.id
                               WHERE aa.id = :assignment_id AND aa.status = 'active'";
            
            $assignmentStmt = $conn->prepare($assignmentQuery);
            $assignmentStmt->bindParam(':assignment_id', $assignmentId);
            $assignmentStmt->execute();
            
            $assignment = $assignmentStmt->fetch(\PDO::FETCH_ASSOC);
            
            if (!$assignment) {
                return [
                    'success' => false,
                    'message' => 'Active assignment not found'
                ];
            }
            
            // Validate status
            if (!in_array($status, ['cancelled', 'completed'])) {
                $status = 'cancelled';
            }
            
            // Update assignment status
            $query = "UPDATE admin_assignments 
                     SET status = :status, 
                         completed_by = :completed_by, 
                         notes = CONCAT(IFNULL(notes, ''), '\n', :reason), 
                         completed_at = NOW() 
                     WHERE id = :assignment_id";
            
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':assignment_id', $assignmentId);
            $stmt->bindParam(':completed_by', $adminId);
            $stmt->bindParam(':reason', $status === 'cancelled' ? "Cancelled: $reason" : "Completed: $reason");
            $stmt->bindParam(':status', $status);
            $stmt->execute();
            
            // Create notification for the previously assigned admin
            $memberName = $assignment['first_name'] . ' ' . $assignment['last_name'];
            
            // Create notification
            $notificationData = [
                'title' => $status === 'cancelled' ? 'Assignment Cancelled' : 'Assignment Completed',
                'message' => $status === 'cancelled' 
                    ? "Assignment for member $memberName has been cancelled."
                    : "Assignment for member $memberName has been completed.",
                'type' => 'assignment',
                'priority' => $assignment['priority'],
                'target_audience' => 'specific',
                'specific_recipients' => ['admin_' . $assignment['assigned_to']],
                'created_by' => $adminId
            ];
            
            $this->notificationService->createNotification($notificationData);
            
            // Commit transaction
            $conn->commit();
            
            return [
                'success' => true,
                'message' => $status === 'cancelled' 
                    ? "Assignment for member $memberName has been cancelled"
                    : "Assignment for member $memberName has been completed"
            ];
        } catch (\Exception $e) {
            // Rollback transaction on error
            if (isset($conn)) {
                $conn->rollBack();
            }
            
            return [
                'success' => false,
                'message' => 'Error unassigning member: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Get assignments for a member
     * 
     * @param int $memberId Member ID
     * @return array Assignments with success status
     */
    public function getMemberAssignments($memberId) {
        try {
            $conn = $this->db->getConnection();
            
            // Get active assignments
            $query = "SELECT ma.*, 
                            m.first_name as member_first_name, 
                            m.last_name as member_last_name,
                            a_by.username as assigned_by_name,
                            CASE 
                                WHEN ma.assigned_to_type = 'admin' THEN au.username
                                WHEN ma.assigned_to_type = 'group' THEN mg.name
                                ELSE NULL
                            END as assigned_to_name,
                            ua_by.username as unassigned_by_name
                     FROM member_assignments ma
                     JOIN members m ON ma.member_id = m.id
                     JOIN admin_users a_by ON ma.assigned_by_id = a_by.id
                     LEFT JOIN admin_users au ON ma.assigned_to_type = 'admin' AND ma.assigned_to_id = au.id
                     LEFT JOIN member_groups mg ON ma.assigned_to_type = 'group' AND ma.assigned_to_id = mg.id
                     LEFT JOIN admin_users ua_by ON ma.unassigned_by_id = ua_by.id
                     WHERE ma.member_id = :member_id
                     ORDER BY ma.is_active DESC, ma.created_at DESC";
            
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':member_id', $memberId);
            $stmt->execute();
            
            $assignments = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            return [
                'success' => true,
                'data' => $assignments
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'message' => 'Error retrieving member assignments: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Get members assigned to an admin
     * 
     * @param int $assignedTo Admin ID that members are assigned to
     * @param string $status Status filter ('active', 'completed', 'cancelled', 'overdue', or 'all')
     * @return array Assigned members with success status
     */
    public function getAssignedMembers($assignedTo, $status = 'active') {
        try {
            $conn = $this->db->getConnection();
            
            // Get assigned members
            $query = "SELECT aa.*, 
                            m.first_name, 
                            m.last_name,
                            m.email,
                            m.kso_id,
                            m.phone,
                            a_by.username as assigned_by_name,
                            ca_by.username as completed_by_name
                     FROM admin_assignments aa
                     JOIN members m ON aa.member_id = m.id
                     JOIN admin_users a_by ON aa.assigned_by = a_by.id
                     LEFT JOIN admin_users ca_by ON aa.completed_by = ca_by.id
                     WHERE aa.assigned_to = :assigned_to";
            
            if ($status !== 'all') {
                $query .= " AND aa.status = :status";
            }
            
            $query .= " ORDER BY aa.created_at DESC";
            
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':assigned_to', $assignedTo);
            
            if ($status !== 'all') {
                $stmt->bindParam(':status', $status);
            }
            
            $stmt->execute();
            
            $assignments = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            
            return [
                'success' => true,
                'data' => $assignments
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'message' => 'Error retrieving assigned members: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Get assignment statistics
     * 
     * @return array Assignment statistics
     */
    public function getAssignmentStats() {
        try {
            $conn = $this->db->getConnection();
            
            // Get total assignments
            $totalQuery = "SELECT COUNT(*) as total FROM member_assignments";
            $totalStmt = $conn->prepare($totalQuery);
            $totalStmt->execute();
            $total = $totalStmt->fetch(\PDO::FETCH_ASSOC)['total'];
            
            // Get active assignments
            $activeQuery = "SELECT COUNT(*) as active FROM member_assignments WHERE is_active = 1";
            $activeStmt = $conn->prepare($activeQuery);
            $activeStmt->execute();
            $active = $activeStmt->fetch(\PDO::FETCH_ASSOC)['active'];
            
            // Get assignments by type
            $typeQuery = "SELECT assigned_to_type, COUNT(*) as count 
                         FROM member_assignments 
                         WHERE is_active = 1 
                         GROUP BY assigned_to_type";
            $typeStmt = $conn->prepare($typeQuery);
            $typeStmt->execute();
            $byType = $typeStmt->fetchAll(\PDO::FETCH_ASSOC);
            
            // Get top admins with most assignments
            $topAdminsQuery = "SELECT au.id, au.username, COUNT(*) as assignments_count 
                              FROM member_assignments ma
                              JOIN admin_users au ON ma.assigned_to_id = au.id
                              WHERE ma.assigned_to_type = 'admin' AND ma.is_active = 1
                              GROUP BY ma.assigned_to_id
                              ORDER BY assignments_count DESC
                              LIMIT 5";
            $topAdminsStmt = $conn->prepare($topAdminsQuery);
            $topAdminsStmt->execute();
            $topAdmins = $topAdminsStmt->fetchAll(\PDO::FETCH_ASSOC);
            
            // Get top groups with most assignments
            $topGroupsQuery = "SELECT mg.id, mg.name, COUNT(*) as assignments_count 
                              FROM member_assignments ma
                              JOIN member_groups mg ON ma.assigned_to_id = mg.id
                              WHERE ma.assigned_to_type = 'group' AND ma.is_active = 1
                              GROUP BY ma.assigned_to_id
                              ORDER BY assignments_count DESC
                              LIMIT 5";
            $topGroupsStmt = $conn->prepare($topGroupsQuery);
            $topGroupsStmt->execute();
            $topGroups = $topGroupsStmt->fetchAll(\PDO::FETCH_ASSOC);
            
            // Get recent assignments
            $recentQuery = "SELECT ma.id, ma.created_at, 
                                 m.first_name, m.last_name,
                                 CASE 
                                    WHEN ma.assigned_to_type = 'admin' THEN au.username
                                    WHEN ma.assigned_to_type = 'group' THEN mg.name
                                    ELSE NULL
                                 END as assigned_to_name,
                                 ma.assigned_to_type
                           FROM member_assignments ma
                           JOIN members m ON ma.member_id = m.id
                           LEFT JOIN admin_users au ON ma.assigned_to_type = 'admin' AND ma.assigned_to_id = au.id
                           LEFT JOIN member_groups mg ON ma.assigned_to_type = 'group' AND ma.assigned_to_id = mg.id
                           WHERE ma.is_active = 1
                           ORDER BY ma.created_at DESC
                           LIMIT 10";
            $recentStmt = $conn->prepare($recentQuery);
            $recentStmt->execute();
            $recent = $recentStmt->fetchAll(\PDO::FETCH_ASSOC);
            
            return [
                'success' => true,
                'data' => [
                    'total_assignments' => (int)$total,
                    'active_assignments' => (int)$active,
                    'inactive_assignments' => (int)($total - $active),
                    'by_type' => $byType,
                    'top_admins' => $topAdmins,
                    'top_groups' => $topGroups,
                    'recent_assignments' => $recent
                ]
            ];
        } catch (\Exception $e) {
            return [
                'success' => false,
                'message' => 'Error retrieving assignment statistics: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Transfer assignments from one admin/group to another
     * 
     * @param int $fromId Source admin/group ID
     * @param string $fromType Source type ('admin' or 'group')
     * @param int $toId Target admin/group ID
     * @param string $toType Target type ('admin' or 'group')
     * @param int $adminId ID of the admin making the transfer
     * @param string $notes Optional notes about the transfer
     * @return array Result with success status
     */
    public function transferAssignments($fromId, $fromType, $toId, $toType, $adminId, $notes = '') {
        try {
            $conn = $this->db->getConnection();
            
            // Start transaction
            $conn->beginTransaction();
            
            // Get active assignments for the source
            $assignmentsQuery = "SELECT member_id 
                               FROM member_assignments 
                               WHERE assigned_to_id = :assigned_to_id 
                               AND assigned_to_type = :assigned_to_type 
                               AND is_active = 1";
            
            $assignmentsStmt = $conn->prepare($assignmentsQuery);
            $assignmentsStmt->bindParam(':assigned_to_id', $fromId);
            $assignmentsStmt->bindParam(':assigned_to_type', $fromType);
            $assignmentsStmt->execute();
            
            $assignments = $assignmentsStmt->fetchAll(\PDO::FETCH_ASSOC);
            
            if (empty($assignments)) {
                return [
                    'success' => false,
                    'message' => 'No active assignments found for the source'
                ];
            }
            
            // Deactivate old assignments
            $deactivateQuery = "UPDATE member_assignments 
                              SET is_active = 0, 
                                  unassigned_by_id = :admin_id, 
                                  unassignment_reason = :reason, 
                                  unassigned_at = NOW() 
                              WHERE assigned_to_id = :from_id 
                              AND assigned_to_type = :from_type 
                              AND is_active = 1";
            
            $deactivateStmt = $conn->prepare($deactivateQuery);
            $deactivateStmt->bindParam(':admin_id', $adminId);
            $transferReason = "Transferred to " . ($toType === 'admin' ? 'admin' : 'group') . " #$toId. $notes";
            $deactivateStmt->bindParam(':reason', $transferReason);
            $deactivateStmt->bindParam(':from_id', $fromId);
            $deactivateStmt->bindParam(':from_type', $fromType);
            $deactivateStmt->execute();
            
            // Create new assignments
            $insertQuery = "INSERT INTO member_assignments 
                          (member_id, assigned_to_id, assigned_to_type, assigned_by_id, notes, is_active, created_at) 
                          VALUES (:member_id, :to_id, :to_type, :admin_id, :notes, 1, NOW())";
            
            $insertStmt = $conn->prepare($insertQuery);
            $transferNotes = "Transferred from " . ($fromType === 'admin' ? 'admin' : 'group') . " #$fromId. $notes";
            
            $successCount = 0;
            
            foreach ($assignments as $assignment) {
                $insertStmt->bindParam(':member_id', $assignment['member_id']);
                $insertStmt->bindParam(':to_id', $toId);
                $insertStmt->bindParam(':to_type', $toType);
                $insertStmt->bindParam(':admin_id', $adminId);
                $insertStmt->bindParam(':notes', $transferNotes);
                $insertStmt->execute();
                
                $successCount++;
            }
            
            // Create notification for the new assignee
            $notificationData = [
                'title' => 'Assignment Transfer',
                'message' => "$successCount members have been transferred to you from " . 
                             ($fromType === 'admin' ? 'admin' : 'group') . " #$fromId.",
                'type' => 'assignment',
                'priority' => 'normal',
                'target_audience' => 'specific',
                'specific_recipients' => [$toType . '_' . $toId],
                'created_by' => $adminId
            ];
            
            $this->notificationService->createNotification($notificationData);
            
            // Commit transaction
            $conn->commit();
            
            return [
                'success' => true,
                'transferred_count' => $successCount,
                'message' => "$successCount members have been transferred successfully"
            ];
        } catch (\Exception $e) {
            // Rollback transaction on error
            if (isset($conn)) {
                $conn->rollBack();
            }
            
            return [
                'success' => false,
                'message' => 'Error transferring assignments: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Get admin information
     * 
     * @param int $adminId Admin ID
     * @return array|null Admin information or null if not found
     */
    public function getAdminInfo($adminId) {
        try {
            $conn = $this->db->getConnection();
            
            $query = "SELECT id, username as name, email FROM admin_users WHERE id = :admin_id";
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':admin_id', $adminId);
            $stmt->execute();
            
            return $stmt->fetch(\PDO::FETCH_ASSOC);
            
        } catch (\Exception $e) {
            return null;
        }
    }
    
    /**
     * Update assignment status
     * 
     * @param int $assignmentId Assignment ID
     * @param string $status New status ('active', 'completed', 'cancelled', 'overdue')
     * @return array Result with success status
     */
    public function updateAssignmentStatus($assignmentId, $status) {
        try {
            $conn = $this->db->getConnection();
            
            // Start transaction
            $conn->beginTransaction();
            
            // Get assignment details first (for notification)
            $getQuery = "SELECT a.*, 
                             m.first_name as member_first_name, m.last_name as member_last_name,
                             adm.username as assigned_to_name, adm.email as assigned_to_email
                         FROM admin_assignments a
                         JOIN members m ON a.member_id = m.id
                         JOIN admin_users adm ON a.assigned_to = adm.id
                         WHERE a.id = :assignment_id";
            $getStmt = $conn->prepare($getQuery);
            $getStmt->bindParam(':assignment_id', $assignmentId);
            $getStmt->execute();
            
            $assignment = $getStmt->fetch(\PDO::FETCH_ASSOC);
            
            if (!$assignment) {
                return [
                    'success' => false,
                    'message' => 'Assignment not found'
                ];
            }
            
            // Add member name for convenience
            $assignment['member_name'] = $assignment['member_first_name'] . ' ' . $assignment['member_last_name'];
            
            // Update status
            $query = "UPDATE admin_assignments 
                     SET status = :status, updated_at = NOW() 
                     WHERE id = :assignment_id";
            
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':status', $status);
            $stmt->bindParam(':assignment_id', $assignmentId);
            $result = $stmt->execute();
            
            if (!$result) {
                throw new \Exception('Failed to update assignment status');
            }
            
            // Add activity log
            $activityQuery = "INSERT INTO assignment_activity_log 
                             (assignment_id, action_type, details, created_at) 
                             VALUES (:assignment_id, :action_type, :details, NOW())";
            
            $activityStmt = $conn->prepare($activityQuery);
            $activityStmt->bindParam(':assignment_id', $assignmentId);
            $activityStmt->bindValue(':action_type', $status === 'completed' ? 'complete' : ($status === 'cancelled' ? 'cancel' : 'update'));
            $activityStmt->bindValue(':details', "Assignment status changed to " . ucfirst($status));
            $activityStmt->execute();
            
            // Commit transaction
            $conn->commit();
            
            return [
                'success' => true,
                'message' => 'Assignment status updated successfully',
                'assignment' => $assignment
            ];
            
        } catch (\Exception $e) {
            // Rollback transaction
            if (isset($conn) && $conn->inTransaction()) {
                $conn->rollBack();
            }
            
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Delete an assignment
     * 
     * @param int $assignmentId Assignment ID
     * @return array Result with success status
     */
    public function deleteAssignment($assignmentId) {
        try {
            $conn = $this->db->getConnection();
            
            // Start transaction
            $conn->beginTransaction();
            
            // Delete assignment
            $query = "DELETE FROM admin_assignments WHERE id = :assignment_id";
            $stmt = $conn->prepare($query);
            $stmt->bindParam(':assignment_id', $assignmentId);
            $result = $stmt->execute();
            
            if (!$result) {
                throw new \Exception('Failed to delete assignment');
            }
            
            // Delete related activity logs
            $activityQuery = "DELETE FROM assignment_activity_log WHERE assignment_id = :assignment_id";
            $activityStmt = $conn->prepare($activityQuery);
            $activityStmt->bindParam(':assignment_id', $assignmentId);
            $activityStmt->execute();
            
            // Commit transaction
            $conn->commit();
            
            return [
                'success' => true,
                'message' => 'Assignment deleted successfully'
            ];
            
        } catch (\Exception $e) {
            // Rollback transaction
            if (isset($conn) && $conn->inTransaction()) {
                $conn->rollBack();
            }
            
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }
}
