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

class EMPR_Leave {

    /**
     * Builds the shared WHERE clause + params for query()/count().
     * Returns array( $where_sql, $params ).
     */
    private static function build_where( $args ) {
        $where  = array( '1 = %d' );
        $params = array( 1 );
        if ( ! empty( $args['user_id'] ) ) { $where[] = 'l.user_id = %d';     $params[] = (int) $args['user_id']; }
        if ( ! empty( $args['status'] ) )  { $where[] = 'l.status = %s';      $params[] = $args['status']; }
        if ( ! empty( $args['start'] ) )   { $where[] = 'l.end_date >= %s';   $params[] = $args['start']; }
        if ( ! empty( $args['end'] ) )     { $where[] = 'l.start_date <= %s'; $params[] = $args['end']; }
        return array( implode( ' AND ', $where ), $params );
    }

    /**
     * @param array $args Optional: user_id, status, start, end, limit, offset.
     *                    When 'limit' is set, results are paged (offset defaults to 0).
     *                    Always sorted newest-first by created_at.
     */
    public static function query( $args = array() ) {
        global $wpdb;
        list( $where_sql, $params ) = self::build_where( $args );

        $sql = "SELECT l.*, u.display_name, u.user_email
                FROM {$wpdb->prefix}emr_leave l
                INNER JOIN {$wpdb->users} u ON u.ID = l.user_id
                WHERE $where_sql
                ORDER BY l.created_at DESC, l.id DESC";

        if ( isset( $args['limit'] ) ) {
            $sql     .= ' LIMIT %d OFFSET %d';
            $params[] = max( 1, (int) $args['limit'] );
            $params[] = isset( $args['offset'] ) ? max( 0, (int) $args['offset'] ) : 0;
        }

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

}
