<?php
/**
 * Member Management Functions
 */
function add_member($data) {
    $role  = get_user_role();

    if($role !== 'member-admin') {
        return new WP_Error('add_member_error', 'You do not have permission to add members');
    }

    // Sanitize inputs
    $username = sanitize_user($data['username']);
    $email = sanitize_email($data['email']);
    
    // Create WP user
    $user_id = wp_create_user($username, wp_generate_password(), $email);
    
    if (is_wp_error($user_id)) {    
        return $user_id;
    }

    // Set role
    $user = new WP_User($user_id);

    if(isset($data['role']) && $data['role'] === 'member-admin') {
        $user->set_role('member-admin');
    } else {
        $user->set_role('member');
    }

    // Add member meta
    if (isset($data['ni_number'])) {
        update_user_meta($user_id, 'ni_number', sanitize_text_field($data['ni_number']));
    }
    if (isset($data['first_name'])) {
        update_user_meta($user_id, 'first_name', sanitize_text_field($data['first_name']));
    }
    if (isset($data['last_name'])) {
        update_user_meta($user_id, 'last_name', sanitize_text_field($data['last_name']));
    }
    if (isset($data['honorific'])) {
        update_user_meta($user_id, 'honorific', sanitize_text_field($data['honorific']));
    }
    if(isset($data['is_active'])) {
        update_user_meta($user_id, 'is_active', sanitize_text_field($data['is_active']));
    }
    
    return $user_id;
}

function reset_and_send_password($user_id) {
    $user = get_user_by('ID', $user_id);
    if (!$user) {
        return new WP_Error('invalid_user', 'User not found');
    }

    // Generate a random password
    $new_password = wp_generate_password();
    
    // Update the user's password
    wp_set_password($new_password, $user_id);
    
    // Prepare email data
    $message = "Hello " . $user->first_name . ",<br><br>" .
                    "Your password has been reset.<br>Your new password is: " . $new_password . "<br><br>" .
                    "Please login <a href='" . site_url('/members-area/') . "'>here</a> with this password and change it immediately.<br><br>" .
                    "Login URL: <a href='" . home_url('/#login') . "'>" . home_url('/#login') . "</a><br>";
    
    $sent = ma_mail($user->user_email, 'Password Reset Request', $message, false, false);
    
    return $sent ? true : new WP_Error('email_failed', 'Failed to send password reset email');
}

/**
 * Reset user password and send admin reset notification email
 * 
 * @param int $user_id The ID of the user whose password to reset
 * @return bool|WP_Error True on success, WP_Error on failure
 */
function admin_reset_user_password($user_id) {
    $user = get_user_by('id', $user_id);
    if (!$user) {
        return new WP_Error('invalid_user', 'User not found');
    }

    // Generate random password
    $new_password = wp_generate_password(12, true);
    
    // Update user's password
    wp_set_password($new_password, $user_id);
    
    // Send email to user
    $to = $user->user_email;
    $subject = 'Your password has been reset';
    $message = "<p>Your password has been reset by an administrator.</p>
                <p>Your new temporary password is: " . $new_password . "</p>
                <p>Please login <a href='" . site_url('/members-area/') . "'>here</a> and change your password as soon as possible.</p>";

    $sent = ma_mail($to, $subject, $message, false, false);
    
    return $sent ? true : new WP_Error('email_failed', 'Failed to send password reset email');
}

function update_member($user_id, $data) {
    // Update basic user data
    $user_data = array(
        'ID' => $user_id
    );

    $role  = get_user_role();

    if($user_id !== get_current_user_id() && $role !== 'member-admin') {
        return new WP_Error('update_member_error', 'You do not have permission to update this member');
    }
    
    if (!empty($data['email'])) {
        $user_data['user_email'] = sanitize_email($data['email']);
    }

    //lets also check the old password is correct
    if(isset($data['old_password']) &&  wp_check_password($data['old_password'], $user_id) && isset($data['password']) && !empty($data['password'])) {
        $user_data['user_pass'] = $data['password'];
    }
    
    $result = wp_update_user($user_data);

    if(isset($data['role'])) {
        if($data['role'] === 'member-admin') {
            $user->set_role('member-admin');
        } else {
            $user->set_role('member');
        }
    }
    
    if (is_wp_error($result)) {
        return $result;
    }

    // Update meta
    if (isset($data['ni_number'])) {
        update_user_meta($user_id, 'ni_number', sanitize_text_field($data['ni_number']));
    }
    if (isset($data['first_name'])) {
        update_user_meta($user_id, 'first_name', sanitize_text_field($data['first_name']));
    }
    if (isset($data['last_name'])) {
        update_user_meta($user_id, 'last_name', sanitize_text_field($data['last_name']));
    }
    if (isset($data['honorific'])) {
        update_user_meta($user_id, 'honorific', sanitize_text_field($data['honorific']));
    }
    if(isset($data['is_active'])) {
        update_user_meta($user_id, 'is_active', sanitize_text_field($data['is_active']));
    }
    
    return true;
}
/**
 * Document Views Functions
 */
function get_user_document_views($user_id) {
    $views = get_user_meta($user_id, 'document_views', true);
    return !empty($views) ? $views : array();
}

function add_user_document_view($user_id, $document_id) {
    $views = get_user_document_views($user_id);
    $views[$document_id] = current_time('mysql');
    return update_user_meta($user_id, 'document_views', $views);
}

function has_user_viewed_document($user_id, $document_id) {
    $views = get_user_document_views($user_id);
    return isset($views[$document_id]);
}

/**
 * Assignment Views Functions
 */
function get_user_assignment_views($user_id) {
    $views = get_user_meta($user_id, 'assignment_views', true);
    return !empty($views) ? $views : array();
}

function add_user_assignment_view($user_id, $assignment_id) {
    $views = get_user_assignment_views($user_id);
    $views[$assignment_id] = current_time('mysql');
    return update_user_meta($user_id, 'assignment_views', $views);
}

function has_user_viewed_assignment($user_id, $assignment_id) {
    $views = get_user_assignment_views($user_id);
    return isset($views[$assignment_id]);
}

/**
 * Statement Views Functions
 */
function get_user_statement_views($user_id) {
    $views = get_user_meta($user_id, 'statement_views', true);
    return !empty($views) ? $views : array();
}

function add_user_statement_view($user_id, $statement_id) {
    $views = get_user_statement_views($user_id);
    $views[$statement_id] = current_time('mysql');
    return update_user_meta($user_id, 'statement_views', $views);
}

function has_user_viewed_statement($user_id, $statement_id) {
    $views = get_user_statement_views($user_id);
    return isset($views[$statement_id]);
}

/**
 * Get all user views
 */
function get_user_all_views($user_id) {
    return array(
        'documents' => get_user_document_views($user_id),
        'assignments' => get_user_assignment_views($user_id),
        'statements' => get_user_statement_views($user_id)
    );
}

/**
 * Assignment Management Functions
 */
function get_user_assignments($user_id) {
    global $wpdb;

    $ni_number = get_user_meta($user_id, 'ni_number', true);
    
    $sql = $wpdb->prepare(
        "SELECT * FROM Assignments WHERE CRBNumber = %s ORDER BY DateUploaded DESC",
        $ni_number
    );
    
    return $wpdb->get_results($sql);
}

function get_assignment_details($assignment_id) {
    global $wpdb;
    
    return $wpdb->get_row($wpdb->prepare(
        "SELECT * FROM Assignments WHERE AssignmentID = %d",
        $assignment_id
    ));
}

function mark_assignments_as_read($user_id, $assignment_ids) {
    if (!is_array($assignment_ids)) {
        $assignment_ids = array($assignment_ids);
    }
    
    foreach ($assignment_ids as $assignment_id) {
        add_user_assignment_view($user_id, $assignment_id);
    }
    
    return true;
}

/**
 * Holiday Pay Management Functions
 */
function get_user_holiday_pay_statements($user_id, $filters = array()) {
    global $wpdb;
    
    $sql = $wpdb->prepare(
        "SELECT HolidayPayStatements.*, 
                (SELECT COUNT(*) FROM UserStatementViews 
                 WHERE UserStatementViews.StatementID = HolidayPayStatements.StatementID 
                 AND UserID = %d) AS Viewed 
         FROM HolidayPayStatements 
         WHERE UserID = %d 
         ORDER BY DateAdded DESC 
         LIMIT %d OFFSET %d",
        $user_id,
        $user_id,
        $filters['per_page'] ?? 10,
        ($filters['page'] ?? 1) - 1 * ($filters['per_page'] ?? 10)
    );
    
    return $wpdb->get_results($sql);
}

function get_statement_details($statement_id) {
    global $wpdb;
    
    return $wpdb->get_row($wpdb->prepare(
        "SELECT * FROM HolidayPayStatements WHERE StatementID = %d",
        $statement_id
    ));
}

function generate_statement_pdf($statement_id) {
    $statement = get_statement_details($statement_id);
    if (!$statement) {
        return false;
    }
    
    // TODO: Implement PDF generation logic
    // This would use a library like TCPDF or similar
    
    return false;
}

/**
 * Schedule Management Functions
 */
function get_user_schedules($user_id, $start_date = null, $end_date = null) {
    global $wpdb;

    $where_clauses = array('s.UserID = %d', 's.is_active = 1', "s.status = 'approved'");
    $where_values = array($user_id);
    $schedule_filter = '';

    if (!empty($start_date) && !empty($end_date)) {
        $schedule_filter = "
            AND s.ScheduleID IN (
                SELECT ScheduleID
                FROM Schedules
                JOIN JSON_TABLE(CoverDates, '$[*]' 
                    COLUMNS(cover_date DATE PATH '$')
                ) jt ON TRUE
                WHERE UserID = %d AND is_active = 1 AND status = 'approved'
                  AND jt.cover_date BETWEEN %s AND %s
            )
        ";
        $where_values[] = $user_id; // repeat for subquery
        $where_values[] = $start_date;
        $where_values[] = $end_date;
    }

    $where_sql = implode(' AND ', $where_clauses);

    $sql = $wpdb->prepare(
        "SELECT s.*, 
                sch.Name AS SchoolName, 
                sch.Address, 
                sch.City, 
                sch.Postcode
         FROM Schedules s
         LEFT JOIN Schools sch ON s.SchoolID = sch.SchoolID
         WHERE $where_sql $schedule_filter
         ORDER BY CoverDates ASC",
         $where_values
    );

    //echo $sql;

    return $wpdb->get_results($sql);
}

function delete_schedule($schedule_id, $reason = '') {
    global $wpdb;
    
    $result = $wpdb->update(
        'Schedules',
        array(
            'is_active' => 0,
            'deletion_reason' => $reason,
            'UpdatedAt' => current_time('mysql')
        ),
        array('ScheduleID' => $schedule_id),
        array('%d', '%s', '%s'),
        array('%d')
    );

    if ($result === false) {
        return new WP_Error('delete_failed', 'Failed to delete schedule');
    }

    return true;
}

function get_schedule_details($schedule_id) {
    global $wpdb;
    
    return $wpdb->get_row($wpdb->prepare(
        "SELECT s.*, sch.Name as SchoolName, sch.Address, sch.City, sch.Postcode 
         FROM Schedules s 
         LEFT JOIN Schools sch ON s.SchoolID = sch.SchoolID 
         WHERE s.ScheduleID = %d AND s.is_active = 1 AND s.status = 'approved'",
        $schedule_id
    ));
}

/**
 * Update notification tracking fields for a schedule.
 *
 * @param int         $schedule_id The schedule identifier.
 * @param string      $status      One of pending|sent|suppressed|failed.
 * @param string|null $note        Optional note/summary for UI display.
 * @param int|null    $user_id     User responsible; defaults to current user.
 * @param string|null $timestamp   Optional timestamp override.
 *
 * @return bool True when the update succeeded, false otherwise.
 */
function update_schedule_notification_status($schedule_id, $status, $note = null, $user_id = null, $timestamp = null) {
    $allowed_statuses = ['pending', 'sent', 'partial', 'suppressed', 'failed'];
    if (!in_array($status, $allowed_statuses, true)) {
        return false;
    }

    global $wpdb;

    if ($user_id === null) {
        $user_id = get_current_user_id();
    }

    if ($timestamp === null) {
        $timestamp = current_time('mysql');
    }

    $data = [
        'notifications_status' => $status,
        'notifications_status_changed_at' => $timestamp,
        'notifications_status_changed_by' => $user_id,
    ];

    if ($note !== null && $note !== '') {
        $data['notifications_status_note'] = $note;
    } else {
        $data['notifications_status_note'] = null;
    }

    return $wpdb->update('Schedules', $data, ['ScheduleID' => $schedule_id]) !== false;
}

/**
 * Dispatch both member and school schedule notifications and track the outcome.
 *
 * @param int      $schedule_id The schedule identifier.
 * @param bool     $is_new      Whether this is a brand-new schedule (vs update).
 * @param int|null $user_id     User responsible; defaults to current user.
 *
 * @return array{
 *     member_sent:bool,
 *     school_sent:bool,
 *     status:string,
 *     note:?string
 * }
 */
function send_schedule_notifications($schedule_id, $is_new = true, $user_id = null) {
    $member_sent = send_member_schedule_notification($schedule_id, $is_new);
    $school_sent = send_school_schedule_notification($schedule_id, $is_new);

    $emails_sent = 0;
    if ($member_sent) {
        $emails_sent++;
    }

    if ($school_sent) {
        $emails_sent++;
    }

    if ($emails_sent === 2) {
        $status = 'sent';
    } elseif ($emails_sent === 1) {
        $status = 'partial';
    } else {
        $status = 'failed';
    }

    $note_parts = [];

    if (!$member_sent) {
        $note_parts[] = 'Member email failed';
    }

    if (!$school_sent) {
        $note_parts[] = 'School email failed';
    }

    $note = $note_parts ? implode('; ', $note_parts) : null;

    if ($user_id === null) {
        $user_id = get_current_user_id();
    }

    update_schedule_notification_status(
        $schedule_id,
        $status,
        $note,
        $user_id
    );

    return [
        'member_sent' => (bool) $member_sent,
        'school_sent' => (bool) $school_sent,
        'status' => $status,
        'note' => $note,
    ];
}

/**
 * Get user unavailability records
 * 
 * @param int $user_id The user ID to get unavailability for
 * @param string $start_date Start date in Y-m-d format
 * @param string $end_date End date in Y-m-d format
 * @return array Array of unavailability records
 */
function get_user_unavailability($user_id, $start_date = null, $end_date = null) {
    global $wpdb;
    
    // Base query for active unavailability events.
    $sql = "SELECT * FROM UserAvailability WHERE user_id = %d AND is_active = 1";
    $params = [$user_id];
    
    // If a date range is provided, check if the event overlaps with the given interval.
    if ($start_date && $end_date) {
        $sql .= " AND (
            (start_datetime <= %s AND end_datetime >= %s)
            OR 
            (repeat_type != 'none' AND (repeat_end_date IS NULL OR repeat_end_date >= %s))
        )";
        // For non-recurring events, we check:
        //   start_datetime <= $end_date AND end_datetime >= $start_date
        // For recurring events, we assume an event is relevant if its repetition extends into the range.
        $params[] = $end_date;   // event.start_datetime <= $end_date
        $params[] = $start_date; // event.end_datetime >= $start_date
        $params[] = $start_date; // for recurring events, repeat_end_date check
    }
    
    $query = $wpdb->get_results($wpdb->prepare($sql, $params));
    $events = [];
    
    foreach ($query as $row) {
        $processed_events = process_event($row, $start_date, $end_date);
        $events = array_merge($events, $processed_events);
    }
    
    return $events;
}

function process_event($row, $start, $end) {
    $events = [];
    $startDate = new DateTime($row->start_datetime);
    $endDate = new DateTime($row->end_datetime);
    $interval = $startDate->diff($endDate);
    if($end) {
        $endDateTime = new DateTime($end);
    } else {
        $endDateTime = new DateTime();
    }

    // Check if event is active
    if (isset($row->is_active) && $row->is_active == 0) {
        return $events;
    }

    // Fetch exceptions for this event
    global $wpdb;
    $exceptions = $wpdb->get_results($wpdb->prepare(
        "SELECT * FROM UserAvailabilityExceptions 
         WHERE availability_id = %d AND is_active = 1",
        $row->id
    ));

    $exceptionDates = [];
    foreach ($exceptions as $ex) {
        $exceptionDates[] = $ex->exception_date;
    }

    // Generate occurrences
    if ($row->repeat_type == 'none') {
        $dateStr = $startDate->format('Y-m-d');
        if (!in_array($dateStr, $exceptionDates)) {
            if ($startDate <= $endDateTime) {
                $events[] = [
                    'id' => $row->id,
                    'title' => 'Unavailable',
                    'start' => $startDate->format('Y-m-d\TH:i:s'),
                    'end' => $endDate->modify('+1 day')->format('Y-m-d\TH:i:s'),
                    'allDay' => $row->all_day == 1,
                    'backgroundColor' => '#D3D3D3',
                    'borderColor' => '#D3D3D3',
                    'extendedProps' => [
                        'repeatType' => $row->repeat_type,
                        'repeatEndDate' => $row->repeat_end_date
                    ]
                ];
            }
        }
    } else {
        // Recurring event
        $occurrenceDate = clone $startDate;
        $repeatEnd = $row->repeat_end_date ? new DateTime($row->repeat_end_date) : new DateTime($end);
        $occurrenceEndDate = clone $repeatEnd;

        // Adjust occurrenceEndDate to include the repeatEnd date
        switch ($row->repeat_type) {
            case 'daily':
                $occurrenceEndDate->add(new DateInterval('P1D'));
                break;
            case 'weekly':
                $occurrenceEndDate->add(new DateInterval('P1W'));
                break;
            case 'monthly':
                $occurrenceEndDate->add(new DateInterval('P1M'));
                break;
        }

        while ($occurrenceDate < $occurrenceEndDate && $occurrenceDate <= $endDateTime) {
            $dateStr = $occurrenceDate->format('Y-m-d');
            if (!in_array($dateStr, $exceptionDates)) {
                $newEndDate = clone $occurrenceDate;
                $newEndDate->add($interval);
                $events[] = [
                    'id' => $row->id,
                    'title' => 'Unavailable',
                    'start' => $occurrenceDate->format('Y-m-d\TH:i:s'),
                    'end' => $newEndDate->modify('+1 day')->format('Y-m-d\TH:i:s'),
                    'allDay' => $row->all_day == 1,
                    'backgroundColor' => '#D3D3D3',
                    'borderColor' => '#D3D3D3',
                    'extendedProps' => [
                        'repeatType' => $row->repeat_type,
                        'repeatEndDate' => $row->repeat_end_date
                    ]
                ];
            }

            // Move to next occurrence
            switch ($row->repeat_type) {
                case 'daily':
                    $occurrenceDate->add(new DateInterval('P1D'));
                    break;
                case 'weekly':
                    $occurrenceDate->add(new DateInterval('P1W'));
                    break;
                case 'monthly':
                    $occurrenceDate->add(new DateInterval('P1M'));
                    break;
                default:
                    $occurrenceDate = clone $repeatEnd;
                    $occurrenceDate->add(new DateInterval('P1D')); // Exit loop
                    break;
            }
        }
    }

    return $events;
}

function get_active_users() {
    $users = get_users(['role__in' => ['member', 'member-admin']]);
    foreach($users as $user) {
        if(get_user_meta($user->ID, 'is_active', true) === '1') {
            $active_users[] = $user;
        }
    }
    return $active_users;
}

/**
 * Helper function to generate statement table HTML
 */
function generate_statement_table($headings, $value) {
    $table = '<tr>';
    
    // Add headers
    foreach ($headings as $heading) {
        $table .= '<th class="px-3 py-2">' . esc_html($heading) . '</th>';
    }
    
    $table .= '</tr>';
    
    // Initialize column totals
    $colTotals = array();
    $alt = false;

    // Add data rows
    foreach ($value as $v) {
        $class = $alt ? 'odd' : 'even';
        $alt = !$alt;
        
        $gTotal = 0;
        $prefix = ($v['Cr/Dr'] == 'Debit') ? '-' : '';
        
        $table .= '<tr>';
        foreach ($headings as $heading) {
            if ($heading == 'Grand Total') {
                if (!isset($colTotals[$heading])) $colTotals[$heading] = 0;
                
                if ($prefix == '-') {
                    $colTotals['Grand Total'] -= $gTotal;
                } else {
                    $colTotals['Grand Total'] += $gTotal;
                }
                $table .= '<td class="px-3 py-2 grandTotal ' . $class . '">' . $prefix . '&pound;' . 
                         number_format((float)$gTotal, 2, '.', '') . '</td>';
            } else {
                if (array_key_exists($heading, $v)) {
                    if (!isset($colTotals[$heading])) $colTotals[$heading] = 0;
                    
                    if ($heading == 'Misc Pay' || $heading == 'Misc Hol Pay') {
                        $gTotal += $v[$heading];
                        if ($prefix == '-') {
                            $colTotals[$heading] -= $v[$heading];
                        } else {
                            $colTotals[$heading] += $v[$heading];
                        }
                        $table .= '<td class="px-3 py-2 value ' . $class . '">' . $prefix . '&pound;' . 
                                 number_format((float)$v[$heading], 2, '.', '') . '</td>';
                    } elseif ((float)$heading > 0) {
                        $gTotal += (float)$heading * $v[$heading];
                        if ($prefix == '-') {
                            $colTotals[$heading] -= $v[$heading];
                        } else {
                            $colTotals[$heading] += $v[$heading];
                        }
                        $table .= '<td class="px-3 py-2 value ' . $class . '">' . $prefix . esc_html($v[$heading]) . '</td>';
                    } else {
                        $table .= '<td class="px-3 py-2 ' . $class . '">' . esc_html($v[$heading]) . '</td>';
                    }
                } else {
                    $table .= '<td class="px-3 py-2 ' . $class . '">&nbsp;</td>';
                }
            }
        }
        $table .= '</tr>';
    }

    // Add totals row
    $table .= '<tr>';
    foreach ($headings as $heading) {
        if ((float)$heading > 0) {
            $table .= '<td class="px-3 py-2 colTotal">' . number_format((float)$colTotals[$heading], 2) . '</td>';
        } elseif ($heading == 'Grand Total' || $heading == 'Misc Pay' || $heading == 'Misc Hol Pay') {
            $table .= '<td class="px-3 py-2 colTotal grandTotal">&pound;' . 
                     number_format((float)$colTotals[$heading], 2, '.', '') . '</td>';
        } else {
            $table .= '<td class="px-3 py-2 hide">&nbsp;</td>';
        }
    }
    $table .= '</tr>';

    return $table;
}

/**
 * Get holiday pay statement data
 */
function get_statement_data($statement_id) {
    global $wpdb;
    
    // Get statement dates and user details
    $statement = $wpdb->get_row($wpdb->prepare(
        "SELECT s.StartDate, s.EndDate, u.ID as UserID,
         (SELECT meta_value FROM {$wpdb->usermeta} WHERE user_id = u.ID AND meta_key = 'ni_number') as NINumber
         FROM HolidayPayStatements s 
         INNER JOIN {$wpdb->users} u ON s.UserID = u.ID 
         WHERE s.StatementID = %d",
        $statement_id
    ));

    if (!$statement) {
        return new WP_Error('not_found', 'Statement not found');
    }

    // Get holiday pay records
    $records = $wpdb->get_results($wpdb->prepare(
        "SELECT * FROM HolidayPay 
         WHERE Date >= %s AND Date <= %s AND NINumber = %s 
         ORDER BY Date ASC",
        $statement->StartDate,
        $statement->EndDate,
        $statement->NINumber
    ));

    if (empty($records)) {
        return new WP_Error('no_data', 'No holiday pay records found');
    }

    // Process the records
    $headings = array('Month', 'Cr/Dr');
    $value = array();
    $addMiscPay = false;
    $addMiscHolPay = false;

    foreach ($records as $record) {
        $date = strtotime($record->Date);
        $dateFormatted = date("F Y", $date);
        $dateFriendly = date("FY", $date);
        
        $credit = $record->IsCredit ? "Credit" : "Debit";
        $key = $dateFriendly . '-' . $credit;

        if (!array_key_exists($key, $value)) {
            $value[$key] = array(
                'Month' => $dateFormatted,
                'Cr/Dr' => $credit
            );
        }

        if ($record->IsMiscPay) {
            $addMiscPay = true;
            $value[$key]['Misc Pay'] = $record->PayValue;
        } elseif ($record->IsMiscHolPay) {
            $addMiscHolPay = true;
            $value[$key]['Misc Hol Pay'] = $record->PayValue;
        } else {
            if (!in_array($record->PayValue, $headings)) {
                $headings[] = $record->PayValue;
            }
            $value[$key][$record->PayValue] = $record->PayUnits;
        }
    }

    // Add final columns
    if ($addMiscPay) $headings[] = "Misc Pay";
    if ($addMiscHolPay) $headings[] = "Misc Hol Pay";
    $headings[] = "Grand Total";

    return array(
        'statement' => $statement,
        'headings' => $headings,
        'value' => $value
    );
} 

function get_document_categories() {
    global $wpdb;

    return $wpdb->get_results("SELECT DocumentCategories.* FROM DocumentCategories LEFT JOIN Documents ON DocumentCategories.DocumentCategoryID = Documents.CategoryID WHERE DocumentCategories.Active=1 AND DocumentCategories.Deleted=0 AND Documents.DocumentID IS NOT NULL GROUP BY DocumentCategories.DocumentCategoryID HAVING COUNT(Documents.DocumentID) > 0");
}

function get_documents_by_category($category_id) {
    global $wpdb;

    return $wpdb->get_results($wpdb->prepare("SELECT * FROM Documents WHERE CategoryID = %d ORDER BY DateUploaded DESC", $category_id));
}

function add_user_unavailability($user_id, $start_datetime, $end_datetime, $all_day = false, $repeat_type = 'none', $repeat_end_date = null) {
    global $wpdb;

    if (!current_user_can('manage_members') && $user_id != get_current_user_id()) {
        return new WP_Error('invalid_user', 'You do not have permission to add unavailability for this user', array('status' => 403));
    }   

    // Handle all-day events
    if ($all_day) {
        $start = new DateTime($start_datetime);
        $end = new DateTime($end_datetime);
        $start_datetime = $start->format('Y-m-d');
        $end_datetime = $end->format('Y-m-d');
    }

    // Validate end date/time is after start date/time
    if (strtotime($end_datetime) < strtotime($start_datetime)) {
        return new WP_Error('invalid_dates', 'End date/time must be after start date/time', array('status' => 400));
    }

    // Validate repeat end date if provided
    if ($repeat_end_date && $repeat_type !== 'none') {
        if (strtotime($repeat_end_date) < strtotime($start_datetime)) {
            return new WP_Error('invalid_repeat_end', 'Repeat end date cannot be before the start date', array('status' => 400));
        }
    }

    return $wpdb->insert('UserAvailability', array(
        'user_id' => $user_id,
        'start_datetime' => $start_datetime,
        'end_datetime' => $end_datetime,
        'all_day' => $all_day ? 1 : 0,
        'repeat_type' => $repeat_type,
        'repeat_end_date' => $repeat_end_date,
        'is_active' => 1
    ), array(
        '%d',
        '%s',
        '%s',
        '%d',
        '%s',
        '%s',
        '%d'
    ));
}

function update_user_unavailability($event_id, $start_datetime, $end_datetime, $all_day = false, $repeat_type = 'none', $repeat_end_date = null, $modify_type = 'all', $original_start = null) {
    global $wpdb;

    // Get the event
    $event = $wpdb->get_row($wpdb->prepare("SELECT * FROM UserAvailability WHERE id = %d", $event_id));

    if (!$event) {
        return new WP_Error('invalid_event', 'Event not found', array('status' => 404));
    }

    if (!current_user_can('manage_members') && $event->user_id != get_current_user_id()) {
        return new WP_Error('invalid_user', 'You do not have permission to update unavailability for this user', array('status' => 403));
    }

    // Handle all-day events
    if ($all_day) {
        $start = new DateTime($start_datetime);
        $end = new DateTime($end_datetime);
        $start_datetime = $start->format('Y-m-d');
        $end_datetime = $end->format('Y-m-d');
    }

    // Handle different modification types
    switch ($modify_type) {
        case 'this':
            if (!$original_start) {
                return new WP_Error('missing_original_start', 'Original start date/time is required for this operation', array('status' => 400));
            }
            
            // Add exception for the original occurrence
            $wpdb->insert('UserAvailabilityExceptions', array(
                'availability_id' => $event_id,
                'exception_date' => date('Y-m-d', strtotime($original_start)),
                'action' => 'delete',
                'is_active' => 1
            ));

            // Create new standalone event
            return add_user_unavailability($event->user_id, $start_datetime, $end_datetime, $all_day);

        case 'future':
            if (!$original_start) {
                return new WP_Error('missing_original_start', 'Original start date/time is required for this operation', array('status' => 400));
            }

            // Update end date of original event
            $original_date = new DateTime($original_start);
            $original_date->modify('-1 day');
            $wpdb->update('UserAvailability', 
                array('repeat_end_date' => $original_date->format('Y-m-d')),
                array('id' => $event_id)
            );

            // Create new event for future occurrences
            return add_user_unavailability($event->user_id, $start_datetime, $end_datetime, $all_day, $repeat_type, $repeat_end_date);

        case 'all':
            return $wpdb->update('UserAvailability', array(
                'start_datetime' => $start_datetime,
                'end_datetime' => $end_datetime,
                'all_day' => $all_day ? 1 : 0,
                'repeat_type' => $repeat_type,
                'repeat_end_date' => $repeat_end_date
            ), array('id' => $event_id));

        default:
            return new WP_Error('invalid_modify_type', 'Invalid modify type', array('status' => 400));
    }
}

function delete_user_unavailability($event_id, $modify_type = 'all', $original_start = null) {
    global $wpdb;

    // Get the event
    $event = $wpdb->get_row($wpdb->prepare("SELECT * FROM UserAvailability WHERE id = %d", $event_id));

    if (!$event) {
        return new WP_Error('invalid_event', 'Event not found', array('status' => 404));
    }
    
    if (!current_user_can('manage_members') && $event->user_id != get_current_user_id()) {
        return new WP_Error('invalid_user', 'You do not have permission to delete unavailability for this user', array('status' => 403));
    }   

    switch ($modify_type) {
        case 'this':
            if (!$original_start) {
                return new WP_Error('missing_original_start', 'Original start date/time is required for this operation', array('status' => 400));
            }
            
            return $wpdb->insert('UserAvailabilityExceptions', array(
                'availability_id' => $event_id,
                'exception_date' => date('Y-m-d', strtotime($original_start)),
                'action' => 'delete',
                'is_active' => 1
            ));

        case 'future':
            if (!$original_start) {
                return new WP_Error('missing_original_start', 'Original start date/time is required for this operation', array('status' => 400));
            }
            
            $original_date = new DateTime($original_start);
            $original_date->modify('-1 day');
            return $wpdb->update('UserAvailability', 
                array('repeat_end_date' => $original_date->format('Y-m-d')),
                array('id' => $event_id)
            );

        case 'all':
            return $wpdb->update('UserAvailability', 
                array('is_active' => 0),
                array('id' => $event_id)
            );

        default:
            return new WP_Error('invalid_modify_type', 'Invalid modify type', array('status' => 400));
    }
}

/**
 * Send email notification to a member about a schedule assignment
 * 
 * @param int $schedule_id The ID of the schedule
 * @param bool $is_new Whether this is a new assignment (true) or an update (false)
 * @return bool Whether the email was sent successfully
 */
function send_member_schedule_notification($schedule_id, $is_new = true) {
    global $wpdb;
    
    // Get schedule details
    $schedule = get_schedule_details($schedule_id);
    if (!$schedule) return false;
    
    // Get user data
    $user_id = $schedule->UserID;
    $user = get_userdata($user_id);
    if (!$user) return false;
    
    // Get school data
    $school = get_school($schedule->SchoolID);
    if (!$school) return false;
    
    $first_name = get_user_meta($user_id, 'first_name', true);
    $school_name = $school->Name;
    $school_address = $school->Address;
    $school_city = $school->City;
    $school_postcode = $school->Postcode;
    $order_number = $schedule->OrderNumber;
    $qualification = get_user_meta($user_id, 'qualification', true);
    $qualification_check_date = get_user_meta($user_id, 'qualification_check_date', true);
    $qualification_details = members_area_format_qualification_details($qualification, format_date($qualification_check_date));
    $right_to_work_doc = members_area_format_right_to_work_doc(get_user_meta($user_id, 'right_to_work_doc', true));
    $right_to_work_date = get_user_meta($user_id, 'right_to_work_date', true);
    $right_to_work_details = members_area_format_qualification_details($right_to_work_doc, format_date($right_to_work_date));
    
    $map_url = "https://maps.google.co.uk/maps?q=" . urlencode($school_name . "," . $school_address . "," . $school_postcode);
    $calendar_url = site_url('/members-area/schedules/');
    
    $subject = "ESL Written Confirmation of Assignments issued on " . date('d-M-y');
    if ($is_new) {
        //$subject = "New Teaching Assignment (Order Number: $order_number) at $school_name";
        //$intro_text = "You have been assigned to teach at";
        $calendar_text = "This assignment has been added to your member calendar.";
    } else {
        //$subject = "Updated Teaching Assignment (Order Number: $order_number) at $school_name";
        //$intro_text = "Your teaching assignment has been updated at";
        $calendar_text = "This updated assignment is reflected in your member calendar.";
    }

    $employee_notification_bullet_points = get_field('employee_notification_bullet_points', 'option');
    
    // Get employee notification intro text with mailmerge support
    $employee_notification_intro = get_field('employee_notification_intro', 'option') ?: 
        '<p>Dear {first_name},</p>
        
        <p>Thank you for accepting the assignment as detailed below and indicating your willingness to work in the position which {school_name} seeks to fill with a suitably qualified, experienced practitioner who additionally has all the necessary additional authorisations required by the law or professional body.</p>
     
        <p>This document is written confirmation and forms part of our commitment to better share information with team members and client schools alike. In the event that changes are made we will make further contact by telephone and once agreed, confirm those changes in writing.</p>
     
        <p>If you require any further details please do not hesitate to contact ESL on 0121 414 1166 or by e-mailing <a href="mailto:enquiry@esl.uk.net">enquiry@esl.uk.net</a>.</p>
     
        <p>Please note that salary payment is made on the 7th of the following calendar month. When the 7th falls on a statutory holiday or a bank holiday, the date payment is brought forward.</p>
     
        <p><strong>ESL Reference:</strong> ESL Order {order_number} confirmed or amended on the {date}.</p>';
    
    // Replace mailmerge placeholders
    $employee_notification_intro = str_replace([
        '{first_name}',
        '{teacher_name}',
        '{school_name}',
        '{order_number}',
        '{date}'
    ], [
        $first_name,
        $user->display_name,
        $school_name,
        $order_number,
        date('d-M-y')
    ], $employee_notification_intro);
    
    // Get employee notification note with mailmerge support
    $employee_notification_note = get_field('employee_notification_note', 'option') ?: 
        '<p>If your records are not in agreement with ours, please do make urgent contact by calling 0121 414 1166. This will permit us the opportunity to rectify any discrepancy.</p>';
    
    // Replace mailmerge placeholders in note
    $employee_notification_note = str_replace([
        '{first_name}',
        '{teacher_name}',
        '{school_name}',
        '{order_number}',
        '{date}'
    ], [
        $first_name,
        $user->display_name,
        $school_name,
        $order_number,
        date('d-M-y')
    ], $employee_notification_note);
    
    $message = "
    $employee_notification_intro
 
    <table style='width: 100%; border-collapse: collapse; margin-bottom: 20px; text-align: left;'>
        <tr>
            <th>Period of Cover:</th>
            <td>{$schedule->CoverDatesText}</td>
        </tr>
        <tr>
            <th>Client School:</th>
            <td>$school_name, $school_address, $school_city, $school_postcode<br>
            <a href=\"$map_url\" target=\"_blank\">View on Google Maps</a></td>
        </tr>
        <tr>
            <th>Year Group/Subject:</th>
            <td>{$schedule->YearGroup}</td>
        </tr>
        <tr>
            <th>Rate:</th>
            <td>{$schedule->Rate}</td>
        </tr>
        <tr>
            <th>The following experience, training, qualifications and authorisations deemed necessary by the hirer or required by law or any professional body:</th>
            <td>$qualification_details</td>
        </tr>
        <tr>
            <th>Document confirming Right to Work and Date of Check:</th>
            <td>$right_to_work_details</td>
        </tr>
        <tr>
            <th>Remarks (if applicable):</th>
            <td>{$schedule->STM1}</td>
        </tr>
        <tr>
            <th>Hours of Work:</th>
            <td>{$schedule->STM2}</td>
        </tr>
        <tr>
            <th>Health and Safety:</th>
            <td>{$schedule->HealthAndSafety}</td>
        </tr>
        <tr>
            <th>Expenses Payable by or to the Worker:</th>
            <td>{$schedule->Expenses}</td>
        </tr>
    </table>
 
    " . $employee_notification_note . "

     <div style='border: 1px solid #ddd; padding: 5px 10px; margin-bottom: 20px;'>
        <p style='margin-bottom: 5px;'>I can confirm that Education Staffing Link Limited:</p>
        $employee_notification_bullet_points
    </div>
    ";
    
    return ma_mail($user->user_email, $subject, $message);
}