<?php
if ( ! defined( 'ABSPATH' ) ) exit;

class EMPR_Assignments {

    public static function for_shift( $shift_id ) {
        global $wpdb;
        // LEFT JOIN so orphaned rows (deleted WP user) are still returned.
        // sync() relies on this to remove stale assignments; INNER JOIN would skip them.
        $results = $wpdb->get_results( $wpdb->prepare(
            "SELECT a.*, COALESCE(u.display_name, '') AS display_name, COALESCE(u.user_email, '') AS user_email
             FROM {$wpdb->prefix}emr_assignments a
             LEFT JOIN {$wpdb->users} u ON u.ID = a.user_id
             WHERE a.shift_id = %d ORDER BY display_name",
            (int) $shift_id
        ) );
        return $results ? $results : array();
    }

    /**
     * Core approved-leave conflict check against an explicit date window.
     *
     * Returns a WP_Error describing the approved leave that overlaps the
     * [$start_date, $end_date] day window for $user_id, or true when the
     * window is clear. Compares date portions only — leave spans full days.
     *
     * This is the single source of truth used by every scheduling flow
     * (direct assignment, open-shift requests/approvals, swaps, shift edits)
     * so the "already on approved leave" rule stays consistent everywhere.
     *
     * @param int    $user_id
     * @param string $start_date Y-m-d (or any strtotime-parsable date)
     * @param string $end_date   Y-m-d (or any strtotime-parsable date)
     * @return true|WP_Error
     */
    public static function leave_conflict( $user_id, $start_date, $end_date ) {
        global $wpdb;

        // Normalize to date portions only.
        $start_date = wp_date( 'Y-m-d', strtotime( $start_date ) );
        $end_date   = wp_date( 'Y-m-d', strtotime( $end_date ) );

        $leave = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, leave_type, start_date, end_date
             FROM {$wpdb->prefix}emr_leave
             WHERE user_id  = %d
               AND status   = 'approved'
               AND start_date <= %s
               AND end_date   >= %s",
            (int) $user_id,
            $end_date,
            $start_date
        ) );

        if ( ! $leave ) return true;

        $name = get_userdata( (int) $user_id );
        /* translators: %d = numeric user ID, shown when the user account has no display name */
        $name = $name ? $name->display_name : sprintf( __( 'User #%d', 'employee-roster' ), (int) $user_id );
        $type_labels = array(
            'annual'   => __( 'Annual Leave', 'employee-roster' ),
            'sick'     => __( 'Sick Leave', 'employee-roster' ),
            'personal' => __( 'Personal Leave', 'employee-roster' ),
            'unpaid'   => __( 'Unpaid Leave', 'employee-roster' ),
            'other'    => __( 'Leave', 'employee-roster' ),
        );
        $type = isset( $type_labels[ $leave->leave_type ] ) ? $type_labels[ $leave->leave_type ] : __( 'Leave', 'employee-roster' );
        return new WP_Error(
            'leave_overlap',
            sprintf(
                /* translators: 1: employee name, 2: leave type, 3: leave start date, 4: leave end date */
                __( '%1$s has approved %2$s from %3$s to %4$s, so they cannot be scheduled on those days.', 'employee-roster' ),
                $name,
                $type,
                wp_date( 'D j M Y', strtotime( $leave->start_date ) ),
                wp_date( 'D j M Y', strtotime( $leave->end_date ) )
            )
        );
    }

    /**
     * Returns a WP_Error if the user has approved leave that covers any day of the
     * given shift, or true if the shift falls entirely outside any approved leave.
     */
    public static function check_leave_overlap( $shift_id, $user_id ) {
        $shift = EMPR_Shifts::get( $shift_id );
        if ( ! $shift ) return new WP_Error( 'not_found', __( 'Shift not found.', 'employee-roster' ) );
        return self::leave_conflict( (int) $user_id, $shift->start_datetime, $shift->end_datetime );
    }

    /**
     * Pre-creation leave conflict check: returns an array of WP_Error objects for
     * every user in $user_ids that has approved leave overlapping the proposed
     * [$start_datetime, $end_datetime] window. Mirrors check_time_conflicts() so a
     * new shift can be validated before any DB row exists.
     *
     * @param string $start_datetime
     * @param string $end_datetime
     * @param int[]  $user_ids
     * @return WP_Error[]
     */
    public static function check_leave_conflicts( $start_datetime, $end_datetime, $user_ids ) {
        $errors = array();
        foreach ( $user_ids as $user_id ) {
            $result = self::leave_conflict( (int) $user_id, $start_datetime, $end_datetime );
            if ( is_wp_error( $result ) ) {
                $errors[] = $result;
            }
        }
        return $errors;
    }

    /**
     * Pre-creation conflict check: returns an array of WP_Error objects for every
     * user in $user_ids that already has a confirmed/pending assignment overlapping
     * the proposed [$start_datetime, $end_datetime] window. No shift ID required.
     */
    public static function check_time_conflicts( $start_datetime, $end_datetime, $user_ids ) {
        global $wpdb;
        $errors = array();

        foreach ( $user_ids as $user_id ) {
            $overlap = $wpdb->get_row( $wpdb->prepare(
                "SELECT a.id, s.title, s.department, s.start_datetime, s.end_datetime
                 FROM {$wpdb->prefix}emr_assignments a
                 INNER JOIN {$wpdb->prefix}emr_shifts s ON s.id = a.shift_id
                 WHERE a.user_id  = %d
                   AND a.status   IN ('confirmed','pending')
                   AND s.start_datetime < %s
                   AND s.end_datetime   > %s",
                (int) $user_id,
                $end_datetime,
                $start_datetime
            ) );

            if ( $overlap ) {
                $name  = get_userdata( (int) $user_id );
                $name  = $name ? $name->display_name : "User #{$user_id}";
                $label = ! empty( $overlap->department ) ? $overlap->department : $overlap->title;
                $errors[] = new WP_Error(
                    'assignment_conflict',
                    sprintf(
                        '%s is already assigned to "%s" (%s – %s) which conflicts with this shift.',
                        $name,
                        $label,
                        wp_date( 'D j M, g:i A', strtotime( $overlap->start_datetime ) ),
                        wp_date( 'g:i A',        strtotime( $overlap->end_datetime ) )
                    )
                );
            }
        }

        return $errors;
    }

    /**
     * Returns a WP_Error if the user already has a confirmed or pending assignment
     * that overlaps with the given shift, or true if the slot is clear.
     */
    public static function check_overlap( $shift_id, $user_id ) {
        global $wpdb;
        $shift = EMPR_Shifts::get( $shift_id );
        if ( ! $shift ) return new WP_Error( 'not_found', 'Shift not found.' );

        $overlap = $wpdb->get_row( $wpdb->prepare(
            "SELECT a.id, s.title, s.start_datetime, s.end_datetime
             FROM {$wpdb->prefix}emr_assignments a
             INNER JOIN {$wpdb->prefix}emr_shifts s ON s.id = a.shift_id
             WHERE a.user_id    = %d
               AND a.shift_id  != %d
               AND a.status    IN ('confirmed','pending')
               AND s.start_datetime < %s
               AND s.end_datetime   > %s",
            (int) $user_id,
            (int) $shift_id,
            $shift->end_datetime,
            $shift->start_datetime
        ) );

        if ( $overlap ) {
            $name = get_userdata( (int) $user_id );
            $name = $name ? $name->display_name : "User #{$user_id}";
            return new WP_Error(
                'overlap',
                sprintf(
                    '%s already has a shift "%s" (%s – %s) that overlaps with this time slot.',
                    $name,
                    $overlap->title,
                    wp_date( 'D j M, g:i A', strtotime( $overlap->start_datetime ) ),
                    wp_date( 'g:i A',        strtotime( $overlap->end_datetime ) )
                )
            );
        }

        return true;
    }

    /**
     * Returns a WP_Error if adding one more confirmed assignment would exceed
     * the shift's slot_limit, or true when capacity is available.
     *
     * Applied to every code path that creates a confirmed assignment so that
     * manager direct-assignments (via sync), employee self-assigns, and
     * approval promotions all respect the same cap.
     *
     * @param int      $shift_id
     * @param object   $shift Optional pre-fetched shift row (saves a query).
     * @return true|WP_Error
     */
    public static function check_slot_limit( $shift_id, $shift = null ) {
        global $wpdb;
        if ( $shift === null ) {
            $shift = EMPR_Shifts::get( (int) $shift_id );
        }
        if ( ! $shift ) return new WP_Error( 'not_found', 'Shift not found.' );
        if ( is_null( $shift->slot_limit ) || (int) $shift->slot_limit <= 0 ) {
            return true; // unlimited / not a slotted shift
        }
        $confirmed = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}emr_assignments WHERE shift_id = %d AND status = 'confirmed'",
            (int) $shift_id
        ) );
        if ( $confirmed >= (int) $shift->slot_limit ) {
            return new WP_Error(
                'slot_full',
                sprintf(
                    /* translators: 1: confirmed count, 2: slot_limit */
                    __( 'Slot limit reached for this shift (%1$d / %2$d filled).', 'employee-roster' ),
                    $confirmed,
                    (int) $shift->slot_limit
                )
            );
        }
        return true;
    }

    /**
     * Deletes all remaining pending requests for a shift once its confirmed
     * assignments have reached or exceeded slot_limit. Returns the number
     * of pending rows removed (0 when the shift still has capacity or has
     * no slot_limit configured).
     *
     * Centralizes the auto-decline cascade used by both the Requests review
     * flow and the manager direct-assignment flow so behavior stays in sync.
     *
     * @param int $shift_id
     * @return int
     */
    public static function decline_pending_if_full( $shift_id ) {
        global $wpdb;
        $shift = $wpdb->get_row( $wpdb->prepare(
            "SELECT slot_limit FROM {$wpdb->prefix}emr_shifts WHERE id = %d",
            (int) $shift_id
        ) );
        if ( ! $shift || is_null( $shift->slot_limit ) || (int) $shift->slot_limit <= 0 ) {
            return 0;
        }
        $confirmed = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}emr_assignments WHERE shift_id = %d AND status = 'confirmed'",
            (int) $shift_id
        ) );
        if ( $confirmed < (int) $shift->slot_limit ) return 0;
        return (int) $wpdb->query( $wpdb->prepare(
            "UPDATE {$wpdb->prefix}emr_assignments SET status = 'declined', reviewed_by = %d, reviewed_at = %s
             WHERE shift_id = %d AND status = 'pending'",
            get_current_user_id(),
            current_time( 'mysql' ),
            (int) $shift_id
        ) );
    }

    public static function assign( $shift_id, $user_id, $status = 'confirmed' ) {
        global $wpdb;
        $ta = EMPR_DB::table( 'assignments' );

        $existing = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, status FROM {$wpdb->prefix}emr_assignments WHERE shift_id=%d AND user_id=%d",
            (int) $shift_id, (int) $user_id
        ) );

        if ( $existing ) {
            if ( $existing->status === $status ) {
                return new WP_Error( 'already_assigned', 'User is already assigned to this shift.' );
            }
            // Promoting pending → confirmed must respect slot_limit on open shifts.
            if ( $status === 'confirmed' && $existing->status !== 'confirmed' ) {
                $slot_err = self::check_slot_limit( (int) $shift_id );
                if ( is_wp_error( $slot_err ) ) return $slot_err;
            }
            // Re-application: an employee resubmitting after a prior decline clears
            // the review trail so the new request appears fresh in the history.
            if ( $status === 'pending' && in_array( $existing->status, array( 'declined' ), true ) ) {
                $wpdb->query( $wpdb->prepare(
                    "UPDATE {$wpdb->prefix}emr_assignments SET status = %s, assigned_by = %d, assigned_at = %s,
                                     reviewed_by = NULL, reviewed_at = NULL
                     WHERE id = %d",
                    $status,
                    get_current_user_id(),
                    current_time( 'mysql' ),
                    (int) $existing->id
                ) );
            } else {
                $wpdb->update( $ta, array( 'status' => $status ), array( 'id' => (int) $existing->id ) );
            }
            return (int) $existing->id;
        }

        $shift = EMPR_Shifts::get( $shift_id );
        if ( ! $shift ) return new WP_Error( 'not_found', 'Shift not found.' );

        // Slot limit guard for new confirmed assignments — applies to every code
        // path (manager direct-assign via sync, employee self-assign, approval).
        // Open / open_approval shifts with a slot_limit must never exceed it.
        if ( $status === 'confirmed' ) {
            $slot_err = self::check_slot_limit( (int) $shift_id, $shift );
            if ( is_wp_error( $slot_err ) ) return $slot_err;
        }

        // Shift overlap check — only for new assignments
        $overlap = self::check_overlap( (int) $shift_id, (int) $user_id );
        if ( is_wp_error( $overlap ) ) return $overlap;

        // Leave overlap check — block assignment if employee has approved leave that day
        $leave_overlap = self::check_leave_overlap( (int) $shift_id, (int) $user_id );
        if ( is_wp_error( $leave_overlap ) ) return $leave_overlap;

        $wpdb->insert( $ta, array(
            'shift_id'    => (int) $shift_id,
            'user_id'     => (int) $user_id,
            'status'      => $status,
            'assigned_by' => get_current_user_id(),
            'assigned_at' => current_time( 'mysql' ),
        ) );
        return (int) $wpdb->insert_id;
    }

    public static function unassign( $shift_id, $user_id ) {
        global $wpdb;
        return (bool) $wpdb->delete( EMPR_DB::table( 'assignments' ), array(
            'shift_id' => (int) $shift_id,
            'user_id'  => (int) $user_id,
        ) );
    }

    /**
     * Sync assignments for a shift to exactly the given user IDs.
     * Returns an array of WP_Error objects for any users that could not be added
     * (e.g. due to overlap). Removals always succeed silently.
     *
     * Pending open_approval requests are intentionally excluded from the reconcile:
     * they are managed only via the manager Requests review flow (approve/decline),
     * so editing a shift's confirmed assignees must not silently delete or alter
     * pending requests for that shift.
     */
    public static function sync( $shift_id, $user_ids ) {
        $current_rows  = self::for_shift( $shift_id );
        $confirmed_rows = array_filter( $current_rows, function ( $r ) {
            return isset( $r->status ) && $r->status === 'confirmed';
        } );
        $current   = array_map( 'intval', wp_list_pluck( $confirmed_rows, 'user_id' ) );
        $user_ids  = array_map( 'intval', $user_ids );
        $to_add    = array_diff( $user_ids, $current );
        $to_remove = array_diff( $current,  $user_ids );

        $errors = array();
        foreach ( $to_remove as $uid ) {
            self::unassign( (int) $shift_id, $uid );
        }
        foreach ( $to_add as $uid ) {
            $result = self::assign( (int) $shift_id, $uid, 'confirmed' );
            if ( is_wp_error( $result ) ) {
                $errors[] = $result;
            }
        }
        return $errors;
    }

    /**
     * Returns pending open_approval assignment requests for manager review.
     */
    public static function pending_requests() {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT a.id, a.shift_id, a.user_id, a.assigned_at,
                        s.title AS shift_title, s.start_datetime, s.end_datetime,
                        s.location, s.department, s.color, s.slot_limit,
                        (SELECT COUNT(*) FROM {$wpdb->prefix}emr_assignments a2
                          WHERE a2.shift_id = s.id AND a2.status = 'confirmed') AS confirmed_count,
                        u.display_name, u.user_email
                 FROM {$wpdb->prefix}emr_assignments a
                 INNER JOIN {$wpdb->prefix}emr_shifts s ON s.id = a.shift_id
                 INNER JOIN {$wpdb->users} u ON u.ID = a.user_id
                 WHERE a.status = %s AND s.assignment_type = %s
                 ORDER BY a.assigned_at DESC, a.id DESC",
                'pending',
                'open_approval'
            )
        );
        return $results ? $results : array();
    }

    /**
     * Returns paginated open_approval request history with derived status.
     *
     * Statuses returned:
     *   - pending    : awaiting review and the shift has not yet ended
     *   - approved   : was confirmed (stays "approved" even after the shift ends)
     *   - declined   : reviewer declined (or auto-declined when slot filled)
     *   - expired    : still pending after the shift's end_datetime passed
     *
     * @param array $args {
     *     @type string $status   one of pending|approved|declined|expired|all (default 'all')
     *     @type string $search   substring match on shift title or employee name
     *     @type int    $page     zero-based page index (default 0)
     *     @type int    $per_page page size (default 10, max 100)
     * }
     * @return array { items: array, total: int, page: int, per_page: int }
     */
    public static function request_history( $args = array() ) {
        global $wpdb;
        $now = current_time( 'mysql' );

        $status   = isset( $args['status'] ) ? sanitize_key( $args['status'] ) : 'all';
        $allowed  = array( 'pending', 'approved', 'declined', 'expired', 'all' );
        if ( ! in_array( $status, $allowed, true ) ) $status = 'all';

        $search   = isset( $args['search'] )   ? trim( (string) $args['search'] ) : '';
        $page     = isset( $args['page'] )     ? max( 0,   (int) $args['page'] )    : 0;
        $per_page = isset( $args['per_page'] ) ? max( 1, min( 100, (int) $args['per_page'] ) ) : 10;

        // Derived status expression — keeps history in sync with the shift clock
        // without needing a cron job to flip pending → expired. Confirmed requests
        // stay "approved" for the life of the record (no "completed" state).
        $derived = "CASE
            WHEN a.status = 'pending'   AND s.end_datetime < %s THEN 'expired'
            WHEN a.status = 'confirmed' THEN 'approved'
            ELSE a.status
        END";

        $where         = array( "s.assignment_type = 'open_approval'" );
        $where_params  = array(); // placeholders inside $where_sql (in order)

        if ( $status !== 'all' ) {
            $where[] = "($derived) = %s";
            $where_params[] = $now;
            $where_params[] = $status;
        }

        if ( $search !== '' ) {
            $like = '%' . $wpdb->esc_like( $search ) . '%';
            $where[] = '(s.title LIKE %s OR s.department LIKE %s OR u.display_name LIKE %s OR u.user_email LIKE %s)';
            $where_params[] = $like; $where_params[] = $like; $where_params[] = $like; $where_params[] = $like;
        }

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

        $count_sql = "SELECT COUNT(*)
            FROM {$wpdb->prefix}emr_assignments a
            INNER JOIN {$wpdb->prefix}emr_shifts s ON s.id = a.shift_id
            INNER JOIN {$wpdb->users} u ON u.ID = a.user_id
            WHERE $where_sql";
        if ( $where_params ) {
            $total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $where_params ) );
        } else {
            // No placeholders in WHERE — prepare() would warn; query directly.
            $total = (int) $wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        }

        $offset = $page * $per_page;
        // SELECT list contains $derived once → one %s placeholder for $now.
        // Then WHERE params, then LIMIT/OFFSET.
        $list_params = array_merge( array( $now ), $where_params, array( $per_page, $offset ) );

        $list_sql = "SELECT a.id, a.shift_id, a.user_id, a.status AS raw_status,
                        a.assigned_at, a.reviewed_at, a.reviewed_by,
                        $derived AS status,
                        s.title AS shift_title, s.start_datetime, s.end_datetime,
                        s.location, s.department, s.color, s.slot_limit,
                        u.display_name, u.user_email
                FROM {$wpdb->prefix}emr_assignments a
                INNER JOIN {$wpdb->prefix}emr_shifts s ON s.id = a.shift_id
                INNER JOIN {$wpdb->users} u ON u.ID = a.user_id
                WHERE $where_sql
                ORDER BY a.assigned_at DESC, a.id DESC
                LIMIT %d OFFSET %d";

        $items = $wpdb->get_results( $wpdb->prepare( $list_sql, $list_params ) );
        if ( ! $items ) $items = array();

        // Attach reviewer display name for approved/declined rows.
        foreach ( $items as $row ) {
            $row->reviewer_name = '';
            if ( ! empty( $row->reviewed_by ) ) {
                $u = get_userdata( (int) $row->reviewed_by );
                if ( $u ) $row->reviewer_name = $u->display_name;
            }
        }

        return array(
            'items'    => $items,
            'total'    => $total,
            'page'     => $page,
            'per_page' => $per_page,
        );
    }

    /**
     * Returns open shifts (open + open_approval) that the given user has NOT yet joined.
     */
    public static function available_for_user( $user_id, $start = null, $end = null ) {
        global $wpdb;

        $where  = array(
            "s.status = 'published'",
            "s.assignment_type IN ('open','open_approval')",
            's.start_datetime > %s',
        );
        $params = array( current_time( 'mysql' ) );

        if ( $start ) { $where[] = 's.start_datetime >= %s'; $params[] = $start; }
        if ( $end )   { $where[] = 's.end_datetime <= %s';   $params[] = $end; }

        $where_sql = implode( ' AND ', $where );
        $uid       = (int) $user_id;

        $sql = "SELECT s.*,
                    (SELECT a2.status FROM {$wpdb->prefix}emr_assignments a2 WHERE a2.shift_id = s.id AND a2.user_id = $uid LIMIT 1) AS my_status,
                    (SELECT COUNT(*) FROM {$wpdb->prefix}emr_assignments a3 WHERE a3.shift_id = s.id AND a3.status = 'confirmed') AS confirmed_count
                FROM {$wpdb->prefix}emr_shifts s
                WHERE $where_sql
                HAVING (s.slot_limit IS NULL OR confirmed_count < s.slot_limit)
                ORDER BY s.start_datetime ASC";

        $results = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
        return $results ? $results : array();
    }
}
