<?php
/**
 * Usage Manager for minute-based quota management.
 *
 * Handles all minute quota operations including balance tracking,
 * deductions, monthly resets, and blocked call recording.
 *
 * Legacy local billing path (pre-cutover). When SITESTAFFR_BILLING_CUTOVER
 * is enabled, get_minutes_balance() reads from middleware-backed cache instead.
 *
 * - Consumption order: Use included minutes FIRST, then purchased (add-on)
 * - 30-second free threshold (calls <30s don't deduct minutes)
 * - When exhausted: Entitlement gate blocks at middleware level
 *
 * @link       https://github.com/sitestaffr/sitestaffr-plugin
 * @since      1.0.0
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 */

/**
 * Usage Manager class for minute quota management.
 *
 * Implements singleton pattern to ensure single instance across plugin.
 * Provides methods for checking balance, deducting minutes, purchasing minutes,
 * and handling monthly billing cycle resets.
 *
 * @since      1.0.0
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 * @author     SiteStaffr
 */
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin table names are internally derived from $wpdb->prefix and values are sanitized/prepared before query execution.
class SiteStaffr_Usage_Manager {

    /**
     * Singleton instance.
     *
     * @since    1.0.0
     * @access   private
     * @var      SiteStaffr_Usage_Manager    $instance    Singleton instance.
     */
    private static $instance = null;

    /**
     * Free threshold in seconds.
     *
     * Calls shorter than this don't deduct minutes.
     *
     * @since    1.0.0
     * @access   private
     * @var      int    $free_threshold    Free threshold in seconds.
     */
    private $free_threshold = 30;

    /**
     * Get singleton instance.
     *
     * @since    1.0.0
     * @return   SiteStaffr_Usage_Manager    Singleton instance.
     */
    public static function get_instance() {
        if ( self::$instance === null ) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Private constructor to prevent direct instantiation.
     *
     * @since    1.0.0
     */
    private function __construct() {
        // Private constructor for singleton pattern
    }

    /**
     * Prevent cloning of the instance.
     *
     * @since    1.0.0
     */
    private function __clone() {
        // Prevent cloning
    }

    /**
     * Prevent unserialization of the instance.
     *
     * @since    1.0.0
     */
    public function __wakeup() {
        throw new Exception( 'Cannot unserialize singleton' );
    }

    /**
     * Get the included-minutes quota for the current plan.
     *
     * Returns the correct per-plan quota instead of a hardcoded value.
     * Reads plan name from the middleware billing cache first; falls back to
     * the sitestaffr_subscription option; defaults to Starter (60) when unknown.
     *
     * @since    0.14.44
     * @return   int    Included minutes quota for the active plan.
     */
    private static function get_plan_included_minutes() {
        $plan_quotas = array(
            'trial'    => 30,
            'starter'  => 60,
            'business' => 300,
            'pro'      => 700,
        );
        $plan_name = '';
        if ( class_exists( 'SiteStaffr_Billing_State' ) ) {
            $cache = SiteStaffr_Billing_State::get_cache();
            $plan_name = isset( $cache['plan_name'] ) ? sanitize_key( $cache['plan_name'] ) : '';
        }
        if ( empty( $plan_name ) ) {
            $subscription = get_option( 'sitestaffr_subscription', array() );
            $plan_name = isset( $subscription['plan_name'] ) ? sanitize_key( $subscription['plan_name'] ) : 'starter';
        }
        return isset( $plan_quotas[ $plan_name ] ) ? (int) $plan_quotas[ $plan_name ] : 60;
    }

    /**
     * Get current minutes balance.
     *
     * Returns current balance for both included and purchased minutes.
     * Automatically checks if monthly reset is needed and performs it.
     *
     * @since    1.0.0
     * @return   array    Balance array with keys: included, purchased, total.
     */
    public function get_minutes_balance() {
        // Phase 5 cutover path: read middleware-backed billing cache.
        if ( class_exists( 'SiteStaffr_Billing_State' ) && SiteStaffr_Billing_State::is_cutover_enabled() ) {
            $cache_balance = SiteStaffr_Billing_State::get_minutes_balance();
            if ( is_array( $cache_balance ) ) {
                return $cache_balance;
            }
        }

        // Check if monthly reset needed (this modifies the balance if needed)
        $this->check_monthly_reset();

        // Get current balance from wp_options
        $balance = get_option( 'sitestaffr_minute_balance', array() );

        // Ensure all required keys exist with defaults
        $balance = wp_parse_args(
            $balance,
            array(
                'included_minutes_remaining'       => self::get_plan_included_minutes(),
                'purchased_minutes_remaining'      => 0,
                'last_reset_date'                  => current_time( 'Y-m-d' ),
                'total_included_used_this_period'  => 0,
                'total_purchased_used_lifetime'    => 0,
            )
        );

        // Return formatted balance
        return array(
            'included'  => (int) $balance['included_minutes_remaining'],
            'purchased' => (int) $balance['purchased_minutes_remaining'],
            'total'     => (int) $balance['included_minutes_remaining'] + (int) $balance['purchased_minutes_remaining'],
        );
    }

    /**
     * Check if minutes are available.
     *
     * @since    1.0.0
     * @param    int    $required_minutes    Number of minutes required (default: 1).
     * @return   bool                        True if sufficient minutes available, false otherwise.
     */
    public function has_minutes_available( $required_minutes = 1 ) {
        $balance = $this->get_minutes_balance();
        return $balance['total'] >= $required_minutes;
    }

    /**
     * Deduct minutes from balance based on call duration.
     *
     * Implements business logic:
     * - Calls < 30 seconds: No deduction
     * - Calculate billable_minutes: ROUND(duration / 60) — full duration counts
     * - Deduct from included minutes first
     * - If included exhausted, deduct from purchased
     * - Update balance option
     * - Update deducted_from column in calls table
     * - Log transaction
     *
     * @since    1.0.0
     * @param    int    $call_id           Call ID from database.
     * @param    int    $duration_seconds  Call duration in seconds.
     * @return   array|WP_Error            Success array or WP_Error on failure.
     */
    public function deduct_minutes( $call_id, $duration_seconds ) {
        global $wpdb;

        // Validate inputs
        if ( ! is_numeric( $call_id ) || $call_id <= 0 ) {
            return new WP_Error(
                'invalid_call_id',
                __( 'Invalid conversation ID provided', 'sitestaffr' )
            );
        }

        if ( ! is_numeric( $duration_seconds ) || $duration_seconds < 0 ) {
            return new WP_Error(
                'invalid_duration',
                __( 'Invalid duration provided', 'sitestaffr' )
            );
        }

        // If duration < 30 seconds, no deduction
        if ( $duration_seconds < $this->free_threshold ) {
            $calls_table = $wpdb->prefix . 'phoneease_calls';
            $classification_update = $wpdb->update(
                $calls_table,
                array(
                    'is_billable'      => 0,
                    'billable_minutes' => 0,
                ),
                array( 'id' => (int) $call_id ),
                array( '%d', '%d' ),
                array( '%d' )
            );

            if ( false === $classification_update ) {
                SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Failed to mark under-threshold call {$call_id} as non-billable - {$wpdb->last_error}" );
            }

            return array(
                'success'  => true,
                'deducted' => 0,
                'balance'  => $this->get_minutes_balance(),
                'reason'   => 'under_free_threshold',
            );
        }

        // Calculate billable minutes: ROUND(duration / 60)
        // Calls >= 30s are billed for the full duration (the threshold only exempts sub-30s calls).
        $billable_seconds = $duration_seconds;
        $billable_minutes = (int) round( $billable_seconds / 60 );

        // Start transaction for atomic operation
        $wpdb->query( 'START TRANSACTION' );

        try {
            // Get current balance (with fresh monthly reset check)
            $balance = get_option( 'sitestaffr_minute_balance', array() );

            // Ensure all required keys exist with defaults
            $balance = wp_parse_args(
                $balance,
                array(
                    'included_minutes_remaining'       => self::get_plan_included_minutes(),
                    'purchased_minutes_remaining'      => 0,
                    'last_reset_date'                  => current_time( 'Y-m-d' ),
                    'total_included_used_this_period'  => 0,
                    'total_purchased_used_lifetime'    => 0,
                )
            );

            $included_remaining  = (int) $balance['included_minutes_remaining'];
            $purchased_remaining = (int) $balance['purchased_minutes_remaining'];

            // Check if sufficient balance available
            $total_available = $included_remaining + $purchased_remaining;
            if ( $total_available < $billable_minutes ) {
                $wpdb->query( 'ROLLBACK' );

                return new WP_Error(
                    'insufficient_minutes',
                    sprintf(
                        /* translators: %1$d: Required minutes, %2$d: Available minutes */
                        __( 'Insufficient minutes. Required: %1$d, Available: %2$d', 'sitestaffr' ),
                        $billable_minutes,
                        $total_available
                    )
                );
            }

            // Deduct from included minutes first, then purchased
            $deducted_from = '';
            $minutes_from_included = 0;
            $minutes_from_purchased = 0;

            if ( $included_remaining >= $billable_minutes ) {
                // All from included
                $minutes_from_included = $billable_minutes;
                $balance['included_minutes_remaining'] = $included_remaining - $billable_minutes;
                $deducted_from = 'included';
            } else {
                // Part from included, part from purchased
                $minutes_from_included = $included_remaining;
                $minutes_from_purchased = $billable_minutes - $included_remaining;

                $balance['included_minutes_remaining'] = 0;
                $balance['purchased_minutes_remaining'] = $purchased_remaining - $minutes_from_purchased;
                $deducted_from = 'included+purchased';
            }

            // Update usage counters
            $balance['total_included_used_this_period'] = (int) $balance['total_included_used_this_period'] + $minutes_from_included;
            $balance['total_purchased_used_lifetime'] = (int) $balance['total_purchased_used_lifetime'] + $minutes_from_purchased;

            // Update balance option
            $update_result = update_option( 'sitestaffr_minute_balance', $balance );

            if ( ! $update_result && get_option( 'sitestaffr_minute_balance' ) !== $balance ) {
                throw new Exception( 'Failed to update minute balance option' );
            }

            // Update calls table with billable_minutes and deducted_from
            $calls_table = $wpdb->prefix . 'phoneease_calls';

            $update_call_result = $wpdb->update(
                $calls_table,
                array(
                    'billable_minutes' => $billable_minutes,
                    'deducted_from'    => $deducted_from,
                ),
                array( 'id' => $call_id ),
                array( '%s', '%s' ),
                array( '%d' )
            );

            if ( $update_call_result === false ) {
                throw new Exception( 'Failed to update call record: ' . $wpdb->last_error );
            }

            // Log transaction in billing_transactions table
            $billing_table = $wpdb->prefix . 'phoneease_billing_transactions';

            $transaction_result = $wpdb->insert(
                $billing_table,
                array(
                    'user_id'            => get_current_user_id(),
                    'transaction_type'   => 'deduction',
                    'amount'             => 0.00,
                    'minutes_added'      => -$billable_minutes,
                    'description'        => sprintf(
                        'Call deduction: %d minutes (%s) - Call ID: %d',
                        $billable_minutes,
                        $deducted_from,
                        $call_id
                    ),
                    'created_at'         => current_time( 'mysql' ),
                ),
                array( '%d', '%s', '%f', '%d', '%s', '%s' )
            );

            if ( $transaction_result === false ) {
                throw new Exception( 'Failed to log billing transaction: ' . $wpdb->last_error );
            }

            // Commit transaction
            $wpdb->query( 'COMMIT' );

            SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Successfully deducted {$billable_minutes} minutes for call ID {$call_id}" );

            return array(
                'success'  => true,
                'deducted' => $billable_minutes,
                'balance'  => array(
                    'included'  => (int) $balance['included_minutes_remaining'],
                    'purchased' => (int) $balance['purchased_minutes_remaining'],
                    'total'     => (int) $balance['included_minutes_remaining'] + (int) $balance['purchased_minutes_remaining'],
                ),
            );

        } catch ( Exception $e ) {
            // Rollback on any error
            $wpdb->query( 'ROLLBACK' );

            SiteStaffr_Logger::legacy( 'SiteStaffr Usage Manager: Deduction failed - ' . $e->getMessage() );

            return new WP_Error(
                'deduction_failed',
                sprintf(
                    /* translators: %s: Error message */
                    __( 'Failed to deduct minutes: %s', 'sitestaffr' ),
                    $e->getMessage()
                )
            );
        }
    }

    /**
     * Purchase additional minutes.
     *
     * Adds purchased minutes to balance ($10 = 100 minutes).
     * Records in billing_transactions table.
     * Updates minute_balance option.
     *
     * @since    1.0.0
     * @param    float     $amount                  Amount paid (e.g., 10.00).
     * @param    string    $stripe_transaction_id   Stripe transaction ID (optional).
     * @return   array|WP_Error                     Success array or WP_Error on failure.
     */
    public function purchase_minutes( $amount, $stripe_transaction_id = null ) {
        global $wpdb;

        // Validate amount
        if ( ! is_numeric( $amount ) || $amount <= 0 ) {
            return new WP_Error(
                'invalid_amount',
                __( 'Invalid purchase amount provided', 'sitestaffr' )
            );
        }

        // Calculate minutes: $10 = 100 minutes
        $minutes_purchased = (int) ( $amount / 10 ) * 100;

        if ( $minutes_purchased <= 0 ) {
            return new WP_Error(
                'invalid_minutes',
                __( 'Purchase amount too small to generate minutes', 'sitestaffr' )
            );
        }

        SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Processing purchase of {$minutes_purchased} minutes for \${$amount}" );

        // Start transaction
        $wpdb->query( 'START TRANSACTION' );

        try {
            // Get current balance
            $balance = get_option( 'sitestaffr_minute_balance', array() );

            // Ensure all required keys exist with defaults
            $balance = wp_parse_args(
                $balance,
                array(
                    'included_minutes_remaining'       => self::get_plan_included_minutes(),
                    'purchased_minutes_remaining'      => 0,
                    'last_reset_date'                  => current_time( 'Y-m-d' ),
                    'total_included_used_this_period'  => 0,
                    'total_purchased_used_lifetime'    => 0,
                )
            );

            // Add to purchased minutes
            $balance['purchased_minutes_remaining'] = (int) $balance['purchased_minutes_remaining'] + $minutes_purchased;

            // Update balance option
            $update_result = update_option( 'sitestaffr_minute_balance', $balance );

            if ( ! $update_result && get_option( 'sitestaffr_minute_balance' ) !== $balance ) {
                throw new Exception( 'Failed to update minute balance option' );
            }

            // Record transaction in billing_transactions table
            $billing_table = $wpdb->prefix . 'phoneease_billing_transactions';

            $transaction_result = $wpdb->insert(
                $billing_table,
                array(
                    'user_id'               => get_current_user_id(),
                    'transaction_type'      => 'purchase',
                    'amount'                => $amount,
                    'minutes_added'         => $minutes_purchased,
                    'description'           => sprintf(
                        'Purchased %d minutes for $%.2f',
                        $minutes_purchased,
                        $amount
                    ),
                    'stripe_transaction_id' => $stripe_transaction_id,
                    'created_at'            => current_time( 'mysql' ),
                ),
                array( '%d', '%s', '%f', '%d', '%s', '%s', '%s' )
            );

            if ( $transaction_result === false ) {
                throw new Exception( 'Failed to log billing transaction: ' . $wpdb->last_error );
            }

            // Commit transaction
            $wpdb->query( 'COMMIT' );

            SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Successfully purchased {$minutes_purchased} minutes" );

            return array(
                'success'       => true,
                'minutes_added' => $minutes_purchased,
                'new_balance'   => array(
                    'included'  => (int) $balance['included_minutes_remaining'],
                    'purchased' => (int) $balance['purchased_minutes_remaining'],
                    'total'     => (int) $balance['included_minutes_remaining'] + (int) $balance['purchased_minutes_remaining'],
                ),
            );

        } catch ( Exception $e ) {
            // Rollback on any error
            $wpdb->query( 'ROLLBACK' );

            SiteStaffr_Logger::legacy( 'SiteStaffr Usage Manager: Purchase failed - ' . $e->getMessage() );

            return new WP_Error(
                'purchase_failed',
                sprintf(
                    /* translators: %s: Error message */
                    __( 'Failed to purchase minutes: %s', 'sitestaffr' ),
                    $e->getMessage()
                )
            );
        }
    }

    /**
     * Check if monthly reset is needed and perform it.
     *
     * Called automatically in get_minutes_balance().
     * Checks if billing cycle has rolled over.
     * If yes:
     * - Reset included_minutes_remaining to plan quota (get_plan_included_minutes())
     * - Update last_reset_date
     * - Reset total_included_used_this_period to 0
     * - Log reset in billing_transactions
     * - DO NOT touch purchased minutes
     *
     * @since    1.0.0
     * @return   bool    True if reset occurred, false otherwise.
     */
    public function check_monthly_reset() {
        global $wpdb;

        // Get subscription settings
        $subscription = get_option( 'sitestaffr_subscription', array() );

        $subscription = wp_parse_args(
            $subscription,
            array(
                'billing_cycle_start' => current_time( 'Y-m-d' ),
                'next_billing_date'   => current_datetime()->modify( '+1 month' )->format( 'Y-m-d' ),
            )
        );

        // Get current balance
        $balance = get_option( 'sitestaffr_minute_balance', array() );

        $balance = wp_parse_args(
            $balance,
            array(
                'included_minutes_remaining'       => self::get_plan_included_minutes(),
                'purchased_minutes_remaining'      => 0,
                'last_reset_date'                  => current_time( 'Y-m-d' ),
                'total_included_used_this_period'  => 0,
                'total_purchased_used_lifetime'    => 0,
            )
        );

        $last_reset_date = $balance['last_reset_date'];
        $current_date = current_time( 'Y-m-d' );

        // Check if we've passed the next billing date
        if ( $current_date < $subscription['next_billing_date'] ) {
            // No reset needed
            return false;
        }

        SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Monthly reset triggered (last reset: {$last_reset_date}, current: {$current_date})" );

        // Start transaction
        $wpdb->query( 'START TRANSACTION' );

        try {
            $previous_included_remaining = $balance['included_minutes_remaining'];
            $previous_included_used = $balance['total_included_used_this_period'];

            // Reset included minutes to the plan quota
            $balance['included_minutes_remaining'] = self::get_plan_included_minutes();

            // Update last reset date
            $balance['last_reset_date'] = $current_date;

            // Reset included usage counter for new period
            $balance['total_included_used_this_period'] = 0;

            // DO NOT touch purchased minutes or purchased usage counter

            // Update balance option
            $update_result = update_option( 'sitestaffr_minute_balance', $balance );

            if ( ! $update_result && get_option( 'sitestaffr_minute_balance' ) !== $balance ) {
                throw new Exception( 'Failed to update minute balance option' );
            }

            // Update subscription next billing date (advance by 1 month)
            $subscription['billing_cycle_start'] = $current_date;
            $current_cycle_date                 = date_create_immutable( $current_date, wp_timezone() );
            $subscription['next_billing_date'] = $current_cycle_date instanceof DateTimeImmutable
                ? $current_cycle_date->modify( '+1 month' )->format( 'Y-m-d' )
                : current_datetime()->modify( '+1 month' )->format( 'Y-m-d' );

            $subscription_result = update_option( 'sitestaffr_subscription', $subscription );

            if ( ! $subscription_result && get_option( 'sitestaffr_subscription' ) !== $subscription ) {
                throw new Exception( 'Failed to update subscription option' );
            }

            // Log reset in billing_transactions table
            $billing_table = $wpdb->prefix . 'phoneease_billing_transactions';

            $transaction_result = $wpdb->insert(
                $billing_table,
                array(
                    'user_id'          => get_current_user_id(),
                    'transaction_type' => 'monthly_reset',
                    'amount'           => 0.00,
                    'minutes_added'    => self::get_plan_included_minutes(),
                    'description'      => sprintf(
                        'Monthly billing cycle reset: %d included minutes restored (previous period: %d remaining, %d used)',
                        self::get_plan_included_minutes(),
                        $previous_included_remaining,
                        $previous_included_used
                    ),
                    'created_at'       => current_time( 'mysql' ),
                ),
                array( '%d', '%s', '%f', '%d', '%s', '%s' )
            );

            if ( $transaction_result === false ) {
                throw new Exception( 'Failed to log reset transaction: ' . $wpdb->last_error );
            }

            // Commit transaction
            $wpdb->query( 'COMMIT' );

            SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Monthly reset completed successfully (next reset: {$subscription['next_billing_date']})" );

            return true;

        } catch ( Exception $e ) {
            // Rollback on any error
            $wpdb->query( 'ROLLBACK' );

            SiteStaffr_Logger::legacy( 'SiteStaffr Usage Manager: Monthly reset failed - ' . $e->getMessage() );

            return false;
        }
    }

    /**
     * Record blocked call (when no minutes available).
     *
     * CEO DECISION: Record blocked calls in database for analytics.
     * Creates call record with:
     * - minute_quota_exceeded = 1
     * - call_status = 'blocked-no-minutes'
     * - billable_minutes = 0
     *
     * @since    1.0.0
     * @param    string    $phone_number    Caller phone number.
     * @param    string    $session_id      Session identifier (optional).
     * @return   int|WP_Error               Call ID on success, WP_Error on failure.
     */
    public function record_blocked_call( $phone_number, $session_id = null ) {
        global $wpdb;

        // Validate phone number
        if ( empty( $phone_number ) ) {
            return new WP_Error(
                'invalid_phone_number',
                __( 'Phone number is required', 'sitestaffr' )
            );
        }

        $to_number = '';

        // Generate session_id if not provided
        if ( empty( $session_id ) ) {
            $session_id = 'BLOCKED_' . uniqid();
        }

        // Insert blocked call record
        $calls_table = $wpdb->prefix . 'phoneease_calls';

        $result = $wpdb->insert(
            $calls_table,
            array(
                'session_id'             => $session_id,
                'from_number'            => $phone_number,
                'to_number'              => $to_number,
                'call_status'            => 'blocked-no-minutes',
                'is_billable'            => 0,
                'call_duration'          => 0,
                'billable_minutes'       => 0.00,
                'minute_quota_exceeded'  => 1,
                'started_at'             => time(), // Unix timestamp (timezone-agnostic)
                'ended_at'               => current_time( 'mysql' ),
                'is_read'                => 0,
                'is_test_call'           => 0,
                'created_at'             => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%d', '%s', '%d', '%d', '%s' ) // started_at is now %d (int)
        );

        if ( $result === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Usage Manager: Failed to record blocked call - ' . $wpdb->last_error );

            return new WP_Error(
                'database_error',
                sprintf(
                    /* translators: %s: Error message */
                    __( 'Failed to record blocked conversation: %s', 'sitestaffr' ),
                    $wpdb->last_error
                )
            );
        }

        $call_id = $wpdb->insert_id;

        SiteStaffr_Logger::legacy( "SiteStaffr Usage Manager: Blocked call recorded with ID {$call_id}" );

        return $call_id;
    }

    /**
     * Get usage statistics for reporting.
     *
     * Returns usage statistics for a given date range.
     * If no dates provided, returns stats for current billing period.
     *
     * @since    1.0.0
     * @param    string    $start_date    Start date (Y-m-d format, optional).
     * @param    string    $end_date      End date (Y-m-d format, optional).
     * @return   array                    Usage statistics array.
     */
    public function get_usage_stats( $start_date = null, $end_date = null ) {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';

        // If no date range provided, use current billing period
        if ( empty( $start_date ) || empty( $end_date ) ) {
            $balance = get_option( 'sitestaffr_minute_balance', array() );
            $start_date = isset( $balance['last_reset_date'] ) ? $balance['last_reset_date'] : current_time( 'Y-m-d' );
            $end_date = current_time( 'Y-m-d' );
        }

        // Normalize date inputs to full-day datetime window.
        $start_dt = preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $start_date ) ? $start_date . ' 00:00:00' : $start_date;
        $end_dt   = preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $end_date ) ? $end_date . ' 23:59:59' : $end_date;

        $free_threshold = (int) $this->free_threshold;
        $effective_minutes_expr = "CASE
            WHEN billable_minutes IS NOT NULL AND billable_minutes > 0 THEN billable_minutes
            WHEN call_duration >= {$free_threshold} THEN ROUND(call_duration / 60)
            ELSE 0
        END";

        // Minutes are voice-only. Text chats live in the same table with a
        // billable on-screen duration, so every minute SUM below is scoped to
        // voice; without this, text time is double-counted as voice minutes.
        $voice_only = SiteStaffr_Call_Manager::voice_minutes_where_sql();

        /*
         * Dynamic table identifiers and SQL expressions below are built from
         * trusted internals ($wpdb->prefix and integer-cast thresholds).
         * User input remains parameterized via placeholders.
         */
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Get total calls in period (excluding test calls and blocked calls).
        $total_calls = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND minute_quota_exceeded = 0",
                $start_dt,
                $end_dt
            )
        );

        // Get blocked calls in period (excluding test calls).
        $blocked_calls = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND minute_quota_exceeded = 1",
                $start_dt,
                $end_dt
            )
        );

        // Get billable calls in period.
        $billable_calls = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND minute_quota_exceeded = 0
                 AND is_billable = 1
                 AND {$effective_minutes_expr} > 0",
                $start_dt,
                $end_dt
            )
        );

        // Total billable minutes used in the window (fallback-safe).
        $total_used = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM({$effective_minutes_expr}), 0) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND minute_quota_exceeded = 0
                 AND is_billable = 1
                 AND {$voice_only}",
                $start_dt,
                $end_dt
            )
        );

        // Get included minutes used (deducted_from = 'included')
        $included_used = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM({$effective_minutes_expr}), 0) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND deducted_from = 'included'
                 AND {$voice_only}",
                $start_dt,
                $end_dt
            )
        );

        // Get purchased minutes used (deducted_from contains 'purchased')
        $purchased_used = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM({$effective_minutes_expr}), 0) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND deducted_from LIKE %s
                 AND {$voice_only}",
                $start_dt,
                $end_dt,
                '%purchased%'
            )
        );

        // For 'included+purchased' calls, we need to subtract the included portion
        // This is a simplification - in production you'd want to track this more precisely
        $mixed_calls_total = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM({$effective_minutes_expr}), 0) FROM {$calls_table}
                 WHERE created_at >= %s AND created_at <= %s
                 AND deducted_from = 'included+purchased'
                 AND {$voice_only}",
                $start_dt,
                $end_dt
            )
        );

        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        $included_used = (float) $included_used;
        $purchased_used = (float) $purchased_used;
        $total_used = (float) $total_used;

        // If legacy deduction fields are absent, attribute remaining usage to included minutes.
        if ( ( $included_used + $purchased_used ) <= 0 && $total_used > 0 ) {
            $included_used = $total_used;
        } elseif ( $total_used > ( $included_used + $purchased_used ) ) {
            $included_used += ( $total_used - ( $included_used + $purchased_used ) );
        }

        return array(
            'included_used'  => (int) round( $included_used ),
            'purchased_used' => (int) round( $purchased_used ),
            'billable_calls' => (int) $billable_calls,
            'blocked_calls'  => (int) $blocked_calls,
            'total_calls'    => (int) $total_calls,
            'start_date'     => $start_date,
            'end_date'       => $end_date,
        );
    }
}
// phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
