<?php
/**
 * Call management business logic.
 *
 * @link       https://github.com/sitestaffr/sitestaffr-plugin
 * @since      1.0.0
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes
 */

/**
 * Call management business logic class.
 *
 * Handles database operations for call records including fetching,
 * filtering, updating read status, and deletion.
 *
 * @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,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Trusted plugin table names are internally derived from $wpdb->prefix; dynamic WHERE clauses and IN placeholders are assembled with sanitized values and prepared placeholders.
class SiteStaffr_Call_Manager {
    /**
     * Cached flag for reviewed_at column availability in calls table.
     *
     * @var bool|null
     */
    private static $calls_table_has_reviewed_at = null;

    /**
     * Check whether calls table has the reviewed_at column.
     *
     * Some legacy installs may still be missing this column. Read/unread
     * operations must continue to work in that case.
     *
     * @since    0.14.289
     * @return   bool
     */
    private static function calls_table_has_reviewed_at_column() {
        global $wpdb;

        if ( null !== self::$calls_table_has_reviewed_at ) {
            return self::$calls_table_has_reviewed_at;
        }

        $table_name = esc_sql( $wpdb->prefix . 'phoneease_calls' );
        $column     = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW COLUMNS FROM `' . $table_name . '` LIKE %s',
                'reviewed_at'
            )
        );

        self::$calls_table_has_reviewed_at = ! empty( $column );
        return self::$calls_table_has_reviewed_at;
    }

    /**
     * Normalize business-hours values to canonical keys used by analytics logic.
     *
     * @since    0.14.186
     * @param    string $business_hours Raw business-hours value.
     * @return   string                 Canonical key or original trimmed value.
     */
    private static function normalize_business_hours_value( $business_hours ) {
        $hours = strtolower( trim( (string) $business_hours ) );

        if ( '' === $hours ) {
            return '';
        }

        // Canonical 24/7 variants.
        if ( preg_match( '/^24\s*(?:\/|-|x|_)?\s*7$/', $hours ) ) {
            return '24-7';
        }

        // Canonical "standard business hours" variants used across setup/settings flows.
        $standard_variants = array(
            'mon-fri-9-5',
            'mon-fri 9am-5pm, sat-sun closed',
            'monday-friday 9am-5pm',
            'monday-friday 9am-5pm, saturday-sunday closed',
        );

        if ( in_array( $hours, $standard_variants, true ) ) {
            return 'mon-fri-9-5';
        }

        return trim( (string) $business_hours );
    }

    /**
     * Parse a time token (for example "9:00 AM" or "17:30") to HH:MM.
     *
     * @since    0.14.186
     * @param    string $token Time token.
     * @return   string|null   HH:MM or null when invalid.
     */
    private static function parse_time_token_to_24h( $token ) {
        $value = strtolower( trim( (string) $token ) );
        if ( '' === $value ) {
            return null;
        }

        if ( preg_match( '/^([01]?\d|2[0-3]):([0-5]\d)$/', $value, $matches ) ) {
            return sprintf( '%02d:%02d', (int) $matches[1], (int) $matches[2] );
        }

        if ( preg_match( '/^([1-9]|1[0-2])(?::([0-5]\d))?\s*([ap]m)$/', $value, $matches ) ) {
            $hour = (int) $matches[1];
            $mins = isset( $matches[2] ) ? (int) $matches[2] : 0;
            $ampm = $matches[3];

            if ( 'pm' === $ampm && 12 !== $hour ) {
                $hour += 12;
            }
            if ( 'am' === $ampm && 12 === $hour ) {
                $hour = 0;
            }

            return sprintf( '%02d:%02d', $hour, $mins );
        }

        return null;
    }

    /**
     * Parse day-labeled custom schedules such as "Mon: 9:00 AM-5:00 PM, Tue: Closed".
     *
     * Returns null when format is not recognized.
     *
     * @since    0.14.186
     * @param    string $business_hours Raw business-hours value.
     * @param    string $day_of_week    Current day key (monday..sunday).
     * @param    string $time_of_day    Current time HH:MM.
     * @return   bool|null              True/false when parsed, null when unrecognized.
     */
    private static function evaluate_named_day_schedule( $business_hours, $day_of_week, $time_of_day ) {
        $schedule = array();
        $matched = preg_match_all(
            '/\b(mon(?:day)?|tue(?:sday)?|wed(?:nesday)?|thu(?:rsday)?|fri(?:day)?|sat(?:urday)?|sun(?:day)?)\s*:\s*([^,]+)/i',
            (string) $business_hours,
            $parts,
            PREG_SET_ORDER
        );

        if ( ! $matched || empty( $parts ) ) {
            return null;
        }

        $day_map = array(
            'mon' => 'monday',
            'monday' => 'monday',
            'tue' => 'tuesday',
            'tuesday' => 'tuesday',
            'wed' => 'wednesday',
            'wednesday' => 'wednesday',
            'thu' => 'thursday',
            'thursday' => 'thursday',
            'fri' => 'friday',
            'friday' => 'friday',
            'sat' => 'saturday',
            'saturday' => 'saturday',
            'sun' => 'sunday',
            'sunday' => 'sunday',
        );

        foreach ( $parts as $row ) {
            $raw_day = strtolower( trim( (string) $row[1] ) );
            $raw_value = trim( (string) $row[2] );
            if ( ! isset( $day_map[ $raw_day ] ) ) {
                continue;
            }
            $schedule[ $day_map[ $raw_day ] ] = $raw_value;
        }

        if ( empty( $schedule ) || ! isset( $schedule[ $day_of_week ] ) ) {
            return null;
        }

        $day_value = strtolower( trim( (string) $schedule[ $day_of_week ] ) );
        if ( 'closed' === $day_value ) {
            return false;
        }

        if ( ! preg_match( '/^(.+?)\s*-\s*(.+)$/', $schedule[ $day_of_week ], $time_parts ) ) {
            return null;
        }

        $start = self::parse_time_token_to_24h( $time_parts[1] );
        $end   = self::parse_time_token_to_24h( $time_parts[2] );
        if ( null === $start || null === $end ) {
            return null;
        }

        // Overnight schedules (for example 10:00 PM-2:00 AM) wrap across midnight.
        if ( $start > $end ) {
            return ( $time_of_day >= $start || $time_of_day < $end );
        }

        return ( $time_of_day >= $start && $time_of_day < $end );
    }

    /**
     * Determine if business hours represent 24/7 operations.
     *
     * @since    0.14.186
     * @param    string $business_hours Business hours setting.
     * @return   bool
     */
    public static function is_24_7_schedule( $business_hours ) {
        return '24-7' === self::normalize_business_hours_value( $business_hours );
    }

    /**
     * Get calls with optional filtering and pagination.
     *
     * @since    1.0.0
     * @param    array    $args    Query arguments.
     * @return   array             Array of call objects.
     */
    public static function get_calls( $args = array() ) {
        global $wpdb;

        $defaults = array(
            'per_page'        => 20,
            'page'            => 1,
            'orderby'         => 'created_at',
            'order'           => 'DESC',
            'status'          => '',
            'is_read'         => '',
            'handled'         => '',    // '' = all, 0 = not-yet-handled, 1 = handled
            'is_test_call'    => '',    // '' = all, 0 = regular only, 1 = test only
            'include_test'    => true, // Deprecated: calls are treated uniformly.
            'search'          => '',
            'date_from'       => '',
            'date_to'         => '',
            'trash'           => false,
            'include_deleted' => false,
            'qualified_lead'  => false,
            'channel'         => '',    // voice | text | widget | button
            'has_lead'        => false,
        );

        $args = wp_parse_args( $args, $defaults );

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $where = array( '1=1' );
        $where_values = array();

        // Channel filter: mode (voice/text) or placement (widget/button),
        // both derived from stored service_type values.
        $channel_where = self::channel_where_fragment( $args['channel'] );
        if ( '' !== $channel_where ) {
            $where[] = $channel_where;
        }

        // "Has lead" filter (captured-contact conversations only).
        if ( ! empty( $args['has_lead'] ) ) {
            $where[] = "is_qualified_lead = 1 AND persona != 'onboarding'";
        }

        // Soft-delete and purge filtering
        if ( ! $args['include_deleted'] ) {
            if ( $args['trash'] ) {
                $where[] = 'deleted_at IS NOT NULL AND purged_at IS NULL';
            } else {
                $where[] = 'deleted_at IS NULL AND purged_at IS NULL';
            }
        }

        // Filter by status
        if ( ! empty( $args['status'] ) ) {
            $where[] = 'call_status = %s';
            $where_values[] = $args['status'];
        }

        // Filter by read/unread.
        // Treat NULL as unread for legacy rows created before strict defaults.
        if ( $args['is_read'] !== '' ) {
            $requested_read_state = (int) $args['is_read'];
            if ( 0 === $requested_read_state ) {
                $where[] = '(is_read = 0 OR is_read IS NULL)';
            } else {
                $where[] = 'is_read = %d';
                $where_values[] = $requested_read_state;
            }
        }

        // Filter by handled/follow-up state (deliberate "followed up", distinct
        // from read/viewed). NULL handled_at = not yet handled.
        if ( $args['handled'] !== '' ) {
            $where[] = ( 0 === (int) $args['handled'] ) ? 'handled_at IS NULL' : 'handled_at IS NOT NULL';
        }

        // Filter by test call status
        if ( $args['is_test_call'] !== '' ) {
            $where[] = 'is_test_call = %d';
            $where_values[] = (int) $args['is_test_call'];
        }

        // Filter to qualified leads only (Qualified Leads tab).
        if ( ! empty( $args['qualified_lead'] ) ) {
            $where[] = "is_qualified_lead = 1 AND persona != 'onboarding'";
        }

        // Search in AI summary (primary) and session_id (secondary)
        if ( ! empty( $args['search'] ) ) {
            $where[] = '(ai_summary LIKE %s OR session_id LIKE %s OR from_number LIKE %s)';
            $search_term = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_values[] = $search_term;
            $where_values[] = $search_term;
            $where_values[] = $search_term;
        }

        // Filter by date range
        if ( ! empty( $args['date_from'] ) ) {
            $where[] = 'created_at >= %s';
            $where_values[] = $args['date_from'];
        }

        if ( ! empty( $args['date_to'] ) ) {
            $where[] = 'created_at <= %s';
            $where_values[] = $args['date_to'];
        }

        // Build WHERE clause — each fragment is either a literal string or contains
        // prepare() placeholders (%s, %d) matched by $where_values entries.
        $where_sql = implode( ' AND ', $where );

        // Map orderby to a literal SQL ORDER BY clause.
        // Each case is a hardcoded string — no variables reach the SQL.
        $order_direction = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC';
        switch ( $args['orderby'] ) {
            case 'id':
                $order_clause = 'ORDER BY `id` ' . $order_direction;
                break;
            case 'started_at':
                $order_clause = 'ORDER BY `started_at` ' . $order_direction;
                break;
            case 'call_status':
                $order_clause = 'ORDER BY `call_status` ' . $order_direction;
                break;
            case 'from_number':
                $order_clause = 'ORDER BY `from_number` ' . $order_direction;
                break;
            case 'call_duration':
                $order_clause = 'ORDER BY `call_duration` ' . $order_direction;
                break;
            case 'lead':
                $order_clause = 'ORDER BY `is_qualified_lead` ' . $order_direction . ', `created_at` DESC';
                break;
            case 'channel':
                $order_clause = 'ORDER BY `service_type` ' . $order_direction . ', `created_at` DESC';
                break;
            default:
                $order_clause = 'ORDER BY `created_at` ' . $order_direction;
                break;
        }

        // Calculate offset.
        $offset = ( $args['page'] - 1 ) * $args['per_page'];

        $where_values[] = (int) $args['per_page'];
        $where_values[] = (int) $offset;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is $wpdb->prefix + hardcoded literal; $where_sql is built from literal SQL fragments with prepare() placeholders; $order_clause is a hardcoded literal from the switch above.
        $sql = $wpdb->prepare(
            "SELECT * FROM `{$table_name}` WHERE {$where_sql} {$order_clause} LIMIT %d OFFSET %d",
            ...$where_values
        );

        return $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- fully prepared above.
    }

    /**
     * WHERE fragment (literal SQL, no user data) scoping a query to voice
     * conversations for billed-minute math.
     *
     * Minutes are a VOICE-ONLY metric: text chats (service_type 'chat_widget' /
     * 'chat_button') carry an on-screen elapsed `call_duration` and default to
     * is_billable=1, so any minutes SUM that doesn't exclude them double-counts
     * text as voice minutes (the Usage & Billing "minutes used" bug). We exclude
     * text explicitly rather than allow-listing voice values so legacy/MW voice
     * rows (NULL, 'phone', 'webrtc*', 'web', 'widget', '') always still count.
     *
     * This is the single definition every minutes surface must share.
     *
     * @return string Literal SQL predicate; no user data.
     */
    public static function voice_minutes_where_sql() {
        return "(service_type NOT IN ('chat_widget', 'chat_button') OR service_type IS NULL)";
    }

    /**
     * WHERE fragment (literal SQL, no user data) for a channel filter key.
     *
     * The 'widget' bucket includes the legacy service_type values
     * ('web', 'widget', '', NULL) — same rule as the Usage page's channel
     * pills, so "Widget" means the same thing on both surfaces.
     *
     * @param string $channel voice | text | widget | button.
     * @return string
     */
    private static function channel_where_fragment( $channel ) {
        switch ( $channel ) {
            case 'voice':
                return "service_type IN ('webrtc', 'webrtc_widget', 'webrtc_button', 'phone')";
            case 'text':
                return "service_type IN ('chat_widget', 'chat_button')";
            case 'widget':
                return "(service_type IN ('webrtc', 'webrtc_widget', 'chat_widget', 'web', 'widget', '') OR service_type IS NULL)";
            case 'button':
                return "service_type IN ('webrtc_button', 'chat_button')";
        }
        return '';
    }

    /**
     * Human-readable channel label for a stored service_type.
     *
     * @param string $service_type Stored service_type value.
     * @return string
     */
    public static function get_channel_label( $service_type ) {
        $labels = array(
            'webrtc'        => __( 'Voice · Widget', 'sitestaffr' ),
            'webrtc_widget' => __( 'Voice · Widget', 'sitestaffr' ),
            'webrtc_button' => __( 'Voice · Button', 'sitestaffr' ),
            'chat_widget'   => __( 'Text · Widget', 'sitestaffr' ),
            'chat_button'   => __( 'Text · Button', 'sitestaffr' ),
            'phone'         => __( 'Voice', 'sitestaffr' ),
        );
        return isset( $labels[ $service_type ] ) ? $labels[ $service_type ] : '';
    }

    /**
     * Mark every unread (non-trashed) conversation as read.
     *
     * @return int Rows updated.
     */
    public static function mark_all_read() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'phoneease_calls';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- no caching layer for admin list state.
        $updated = $wpdb->query(
            $wpdb->prepare(
                'UPDATE %i SET is_read = 1 WHERE (is_read = 0 OR is_read IS NULL) AND deleted_at IS NULL AND purged_at IS NULL',
                $table_name
            )
        );

        return (int) $updated;
    }

    /**
     * Get total count of calls matching filters.
     *
     * @since    1.0.0
     * @param    array    $args    Query arguments.
     * @return   int               Total count.
     */
    public static function get_calls_count( $args = array() ) {
        global $wpdb;

        $defaults = array(
            'status'          => '',
            'is_read'         => '',
            'handled'         => '',
            'is_test_call'    => '',
            'include_test'    => true,
            'search'          => '',
            'date_from'       => '',
            'date_to'         => '',
            'trash'           => false,
            'include_deleted' => false,
            'qualified_lead'  => false,
            'channel'         => '',
            'has_lead'        => false,
        );

        $args = wp_parse_args( $args, $defaults );

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $where = array( '1=1' );
        $where_values = array();

        $channel_where = self::channel_where_fragment( $args['channel'] );
        if ( '' !== $channel_where ) {
            $where[] = $channel_where;
        }

        if ( ! empty( $args['has_lead'] ) ) {
            $where[] = "is_qualified_lead = 1 AND persona != 'onboarding'";
        }

        // Soft-delete and purge filtering
        if ( ! $args['include_deleted'] ) {
            if ( $args['trash'] ) {
                $where[] = 'deleted_at IS NOT NULL AND purged_at IS NULL';
            } else {
                $where[] = 'deleted_at IS NULL AND purged_at IS NULL';
            }
        }

        // Filter by status
        if ( ! empty( $args['status'] ) ) {
            $where[] = 'call_status = %s';
            $where_values[] = $args['status'];
        }

        // Filter by read/unread.
        // Treat NULL as unread for legacy rows created before strict defaults.
        if ( $args['is_read'] !== '' ) {
            $requested_read_state = (int) $args['is_read'];
            if ( 0 === $requested_read_state ) {
                $where[] = '(is_read = 0 OR is_read IS NULL)';
            } else {
                $where[] = 'is_read = %d';
                $where_values[] = $requested_read_state;
            }
        }

        // Filter by handled/follow-up state (deliberate "followed up", distinct
        // from read/viewed). NULL handled_at = not yet handled.
        if ( $args['handled'] !== '' ) {
            $where[] = ( 0 === (int) $args['handled'] ) ? 'handled_at IS NULL' : 'handled_at IS NOT NULL';
        }

        // Filter by test call status
        if ( $args['is_test_call'] !== '' ) {
            $where[] = 'is_test_call = %d';
            $where_values[] = (int) $args['is_test_call'];
        }

        // Filter to qualified leads only (Qualified Leads tab).
        if ( ! empty( $args['qualified_lead'] ) ) {
            $where[] = "is_qualified_lead = 1 AND persona != 'onboarding'";
        }

        // Search in AI summary (primary) and session_id (secondary)
        if ( ! empty( $args['search'] ) ) {
            $where[] = '(ai_summary LIKE %s OR session_id LIKE %s OR from_number LIKE %s)';
            $search_term = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_values[] = $search_term;
            $where_values[] = $search_term;
            $where_values[] = $search_term;
        }

        // Filter by date range
        if ( ! empty( $args['date_from'] ) ) {
            $where[] = 'created_at >= %s';
            $where_values[] = $args['date_from'];
        }

        if ( ! empty( $args['date_to'] ) ) {
            $where[] = 'created_at <= %s';
            $where_values[] = $args['date_to'];
        }

        // Build WHERE clause
        $where_sql = implode( ' AND ', $where );

        // Build and execute query.
        if ( ! empty( $where_values ) ) {
            $sql = $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . $table_name . ' WHERE ' . $where_sql,
                ...$where_values
            );
        } else {
            $sql = 'SELECT COUNT(*) FROM ' . $table_name . ' WHERE ' . $where_sql;
        }

        return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query has no untrusted input when unprepared branch is used.
    }

    /**
     * Get single call by ID.
     *
     * @since    1.0.0
     * @param    int    $call_id    Call ID.
     * @return   object|null        Call object or null if not found.
     */
    public static function get_call_by_id( $call_id ) {
        global $wpdb;

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

        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . $table_name . ' WHERE id = %d',
                $call_id
            )
        );
    }

    /**
     * Mark calls as read.
     *
     * @since    1.0.0
     * @param    array    $call_ids    Array of call IDs.
     * @return   int|false             Number of rows updated or false on failure.
     */
    public static function mark_as_read( $call_ids ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $call_ids = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        // Always update read-state first so core UX works on all schema versions.
        $sql = $wpdb->prepare(
            'UPDATE ' . $table_name . ' SET is_read = 1 WHERE id IN (' . $in_clause . ')',
            ...$call_ids
        );

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.

        if ( $result === false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: Database error: ' . $wpdb->last_error );
            return false;
        }

        // reviewed_at is best-effort metadata; skip when legacy schema lacks the column.
        if ( self::calls_table_has_reviewed_at_column() ) {
            $reviewed_values = array_merge( array( current_time( 'mysql' ) ), $call_ids );
            $reviewed_sql = $wpdb->prepare(
                'UPDATE ' . $table_name . ' SET reviewed_at = %s WHERE id IN (' . $in_clause . ')',
                ...$reviewed_values
            );
            $reviewed_result = $wpdb->query( $reviewed_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $reviewed_sql is prepared above.
            if ( false === $reviewed_result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: Failed to update reviewed_at: ' . $wpdb->last_error );
            }
        }

        return $result;
    }

    /**
     * Mark calls as unread.
     *
     * @since    1.0.0
     * @param    array    $call_ids    Array of call IDs.
     * @return   int|false             Number of rows updated or false on failure.
     */
    public static function mark_as_unread( $call_ids ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $call_ids = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        $sql = $wpdb->prepare(
            'UPDATE ' . $table_name . ' SET is_read = 0 WHERE id IN (' . $in_clause . ')',
            ...$call_ids
        );

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.
        if ( false === $result ) {
            return false;
        }

        // Keep reviewed_at in sync when column exists.
        if ( self::calls_table_has_reviewed_at_column() ) {
            $reviewed_sql = $wpdb->prepare(
                'UPDATE ' . $table_name . ' SET reviewed_at = NULL WHERE id IN (' . $in_clause . ')',
                ...$call_ids
            );
            $reviewed_result = $wpdb->query( $reviewed_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $reviewed_sql is prepared above.
            if ( false === $reviewed_result ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: Failed to clear reviewed_at: ' . $wpdb->last_error );
            }
        }

        return $result;
    }

    /**
     * Set (or clear) the deliberate "followed up / handled" flag on calls.
     *
     * The admin-side writer for `handled_at` (the wp-admin "Followed up" control),
     * mirroring the public SiteStaffr_Leads::set_handled. Distinct from read/unread
     * — viewing a conversation never marks it handled.
     *
     * @param array $call_ids Call IDs.
     * @param bool  $handled  True to mark handled (now), false to clear.
     * @return int|false Rows updated, or false on error / bad input.
     */
    public static function set_handled( $call_ids, $handled ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $call_ids = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        if ( $handled ) {
            $sql = $wpdb->prepare(
                'UPDATE ' . $table_name . ' SET handled_at = %s WHERE id IN (' . $in_clause . ')',
                ...array_merge( array( gmdate( 'Y-m-d H:i:s' ) ), $call_ids )
            );
        } else {
            $sql = $wpdb->prepare(
                'UPDATE ' . $table_name . ' SET handled_at = NULL WHERE id IN (' . $in_clause . ')',
                ...$call_ids
            );
        }

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.
        if ( false === $result ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: Failed to set handled_at: ' . $wpdb->last_error );
            return false;
        }

        // Following up implies you've seen it: marking handled also marks read
        // (clears the "unread" badge). Undo doesn't un-read. (Mario, 2026-07-20.)
        if ( $handled ) {
            self::mark_as_read( $call_ids );
        }

        if ( class_exists( 'SiteStaffr_Leads' ) ) {
            SiteStaffr_Leads::bump_cache_generation();
        }

        return $result;
    }

    /**
     * Permanently delete calls by anonymizing PII while preserving billing data.
     *
     * Instead of hard-deleting rows (which destroys usage/billing aggregates),
     * this method nulls PII columns, sets purged_at, and deletes related
     * transcripts and AI metadata. Billing-relevant columns (call_duration,
     * billable_minutes, is_billable, created_at, etc.) are preserved so
     * dashboard aggregates remain accurate.
     *
     * @since    1.0.0
     * @param    array    $call_ids    Array of call IDs.
     * @return   int|false             Number of rows anonymized or false on failure.
     */
    public static function delete_calls( $call_ids ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $calls_table       = $wpdb->prefix . 'phoneease_calls';
        $transcripts_table = $wpdb->prefix . 'phoneease_transcripts';
        $ai_metadata_table = $wpdb->prefix . 'phoneease_ai_metadata';

        $call_ids     = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        // 1. Delete related transcripts (no longer cascaded since we're not deleting the parent row).
        $wpdb->query(
            $wpdb->prepare(
                'DELETE FROM ' . $transcripts_table . ' WHERE call_id IN (' . $in_clause . ')',
                ...$call_ids
            )
        );

        // 2. Delete related AI metadata.
        $wpdb->query(
            $wpdb->prepare(
                'DELETE FROM ' . $ai_metadata_table . ' WHERE call_id IN (' . $in_clause . ')',
                ...$call_ids
            )
        );

        // 3. Anonymize PII columns and mark as purged.
        $purge_values = array_merge( array( current_time( 'mysql' ) ), $call_ids );
        $sql = $wpdb->prepare(
            'UPDATE ' . $calls_table . "
             SET session_id = NULL,
                 from_number = NULL,
                 to_number = NULL,
                 visitor_ip = NULL,
                 recording_url = NULL,
                 ai_summary = NULL,
                 ai_followup = NULL,
                 is_qualified_lead = 0,
                 lead_name = NULL,
                 lead_phone = NULL,
                 lead_email = NULL,
                 lead_interest = NULL,
                 purged_at = %s
             WHERE id IN (" . $in_clause . ')',
            ...$purge_values
        );

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.

        // Purging clears lead PII — invalidate cached completed-cycle results.
        if ( class_exists( 'SiteStaffr_Leads' ) ) {
            SiteStaffr_Leads::bump_cache_generation();
        }

        return $result;
    }

    /**
     * Soft-delete calls (move to trash).
     *
     * @since    1.0.0
     * @param    array    $call_ids    Array of call IDs.
     * @return   int|false             Number of rows updated or false on failure.
     */
    public static function trash_calls( $call_ids ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $call_ids    = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause   = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        $trash_values = array_merge( array( current_time( 'mysql' ) ), $call_ids );
        $sql = $wpdb->prepare(
            'UPDATE ' . $table_name . ' SET deleted_at = %s WHERE id IN (' . $in_clause . ') AND deleted_at IS NULL',
            ...$trash_values
        );

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.

        // Trashing can remove a lead from a past (cached) cycle — invalidate.
        if ( class_exists( 'SiteStaffr_Leads' ) ) {
            SiteStaffr_Leads::bump_cache_generation();
        }

        return $result;
    }

    /**
     * Restore calls from trash.
     *
     * @since    1.0.0
     * @param    array    $call_ids    Array of call IDs.
     * @return   int|false             Number of rows updated or false on failure.
     */
    public static function restore_calls( $call_ids ) {
        global $wpdb;

        if ( empty( $call_ids ) || ! is_array( $call_ids ) ) {
            return false;
        }

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $call_ids    = array_values( array_filter( array_map( 'intval', $call_ids ) ) );
        if ( empty( $call_ids ) ) {
            return false;
        }
        $in_clause   = implode( ',', array_fill( 0, count( $call_ids ), '%d' ) );

        $sql = $wpdb->prepare(
            'UPDATE ' . $table_name . ' SET deleted_at = NULL WHERE id IN (' . $in_clause . ')',
            ...$call_ids
        );

        $result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared above with placeholder expansion.

        // Restoring can re-add a lead to a past (cached) cycle — invalidate.
        if ( class_exists( 'SiteStaffr_Leads' ) ) {
            SiteStaffr_Leads::bump_cache_generation();
        }

        return $result;
    }

    /**
     * Get count of trashed calls.
     *
     * @since    1.0.0
     * @return   int    Count of trashed calls.
     */
    public static function get_trash_count() {
        global $wpdb;

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

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE deleted_at IS NOT NULL AND purged_at IS NULL', $table_name ) );
    }

    /**
     * Get count of new (unread) calls.
     *
     * Excludes test calls from the count.
     *
     * @since    1.0.0
     * @return   int    Count of unread calls.
     */
    public static function get_new_calls_count() {
        global $wpdb;

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

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE (is_read = 0 OR is_read IS NULL) AND deleted_at IS NULL AND purged_at IS NULL', $table_name ) );
    }

    /**
     * Get call by session ID.
     *
     * @since    1.0.0
     * @param    string    $session_id    Session identifier.
     * @return   object|null              Call object or null if not found.
     */
    public static function get_call_by_session_id( $session_id ) {
        global $wpdb;

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

        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM ' . $table_name . ' WHERE session_id = %s',
                $session_id
            )
        );
    }

    /**
     * Update call status and related fields.
     *
     * This method is used by the status webhook to update call records when
     * Legacy call flow notifies us of call completion or status changes.
     *
     * @since    1.0.0
     * @param    string    $session_id       Session identifier.
     * @param    string    $call_status      Call status (completed, failed, busy, no-answer, canceled).
     * @param    int       $call_duration    Call duration in seconds.
     * @param    string    $recording_url    Optional recording URL.
     * @param    string    $ai_model         Optional AI model used for the call (e.g., 'claude-sonnet-4-5').
     * @return   int|false                   Call ID on success, false on failure.
     */
    public static function update_call_status( $session_id, $call_status, $call_duration = 0, $recording_url = '', $ai_model = '' ) {
        global $wpdb;

        if ( empty( $session_id ) ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: update_call_status() - Missing session_id' );
            return false;
        }

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

        // Build update data array dynamically
        $update_data = array(
            'call_status'   => sanitize_text_field( $call_status ),
            'call_duration' => intval( $call_duration ),
        );
        $update_format = array( '%s', '%d' );

        // Add recording URL if provided
        if ( ! empty( $recording_url ) ) {
            $update_data['recording_url'] = sanitize_text_field( $recording_url );
            $update_format[] = '%s';
        }

        // Add AI model if provided (for multi-model testing)
        if ( ! empty( $ai_model ) ) {
            $update_data['ai_model'] = sanitize_text_field( $ai_model );
            $update_format[] = '%s';
        }

        // Set ended_at timestamp for terminal call statuses
        // These are all the statuses that indicate the call has ended
        $terminal_statuses = array( 'completed', 'no-answer', 'failed', 'busy', 'canceled' );
        if ( in_array( $call_status, $terminal_statuses, true ) ) {
            $update_data['ended_at'] = current_time( 'mysql' );
            $update_format[] = '%s';

            // Persist billable classification at write-time to avoid
            // downstream metric drift from inferred read-time logic.
            $is_billable = ( 'completed' === $call_status && intval( $call_duration ) >= 30 ) ? 1 : 0;
            $billable_minutes = ( 1 === $is_billable ) ? max( 1, (int) round( intval( $call_duration ) / 60 ) ) : 0;
            $update_data['is_billable'] = $is_billable;
            $update_data['billable_minutes'] = $billable_minutes;
            $update_format[] = '%d';
            $update_format[] = '%d';
        }

        // Perform database update
        $updated = $wpdb->update(
            $table_name,
            $update_data,
            array( 'session_id' => $session_id ),
            $update_format,
            array( '%s' )
        );

        if ( $updated !== false ) {
            // Get call ID for return value
            $call_id = $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT id FROM ' . $table_name . ' WHERE session_id = %s',
                    $session_id
                )
            );

            return $call_id;
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: Database update FAILED for session ' . $session_id . ' - Error: ' . $wpdb->last_error );
            return false;
        }
    }

    /**
     * Format phone number for display.
     *
     * @since    1.0.0
     * @param    string    $number    Phone number.
     * @return   string               Formatted phone number.
     */
    public static function format_phone_number( $number ) {
        // Remove all non-numeric characters
        $clean = preg_replace( '/[^0-9]/', '', $number );

        // Format US numbers: +17863337300 → (786) 333-7300
        if ( strlen( $clean ) === 11 && substr( $clean, 0, 1 ) === '1' ) {
            $area_code = substr( $clean, 1, 3 );
            $prefix = substr( $clean, 4, 3 );
            $line = substr( $clean, 7, 4 );
            return "($area_code) $prefix-$line";
        }

        // Format 10-digit numbers
        if ( strlen( $clean ) === 10 ) {
            $area_code = substr( $clean, 0, 3 );
            $prefix = substr( $clean, 3, 3 );
            $line = substr( $clean, 6, 4 );
            return "($area_code) $prefix-$line";
        }

        // Return original if doesn't match expected format
        return $number;
    }

    /**
     * Update AI model used for a call.
     *
     * This method is called when we receive the model information from the middleware
     * response, allowing us to track which model handled each call (production calls).
     *
     * @since    1.0.0
     * @param    int       $call_id    Call ID from wp_sitestaffr_calls table.
     * @param    string    $ai_model   AI model name (e.g., 'gemini-2.0-flash-exp', 'claude-sonnet-4-5').
     * @return   int|false             Number of rows updated or false on failure.
     */
    public static function update_call_model( $call_id, $ai_model ) {
        global $wpdb;

        if ( empty( $call_id ) || ! is_numeric( $call_id ) ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: update_call_model() - Invalid call_id' );
            return false;
        }

        if ( empty( $ai_model ) ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Call_Manager: update_call_model() - Empty ai_model' );
            return false;
        }

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

        $updated = $wpdb->update(
            $table_name,
            array( 'ai_model' => sanitize_text_field( $ai_model ) ),
            array( 'id' => intval( $call_id ) ),
            array( '%s' ),
            array( '%d' )
        );

        if ( $updated !== false ) {
            return $updated;
        } else {
            SiteStaffr_Logger::legacy( "SiteStaffr Call_Manager: Failed to update AI model for call_id $call_id - Error: " . $wpdb->last_error );
            return false;
        }
    }

    /**
     * Determine if a conversation should count toward the plan minute limit.
     *
     * Filters out non-billable connections to protect customers from being
     * charged for non-conversations. Applies to both WebRTC widget and
     * future phone integrations. Detection rules:
     * - Duration under 30 seconds (accidental clicks, wrong number, instant close)
     * - Zero visitor speech detected (widget opened but visitor never spoke)
     * - Fewer than 3 transcript entries (visitor closed during AI greeting)
     *
     * @since    1.0.0
     * @param    int    $call_id    Call ID from wp_sitestaffr_calls table.
     * @return   bool               True if billable (counts toward limit), false if non-billable.
     */
    public static function is_call_billable( $call_id ) {
        global $wpdb;

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

        // Get call data
        $call = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT call_duration, call_status FROM ' . $calls_table . ' WHERE id = %d',
                $call_id
            )
        );

        if ( ! $call ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Billing Filter: Call {$call_id} not found in database" );
            return false;
        }

        // Rule 1: Duration check (under 30 seconds = non-billable)
        // Connections under 30 seconds are typically accidental clicks or wrong numbers
        if ( $call->call_duration > 0 && $call->call_duration < 30 ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Billing Filter: Call {$call_id} filtered - duration under 30s ({$call->call_duration}s)" );
            return false;
        }

        // Rule 2: Check if visitor spoke at all
        // Silent connections (no visitor speech) indicate accidental opens or idle sessions
        $caller_messages = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . $transcripts_table . " WHERE call_id = %d AND speaker = 'caller'",
                $call_id
            )
        );

        if ( $caller_messages === null || $caller_messages === 0 || $caller_messages === '0' ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Billing Filter: Call {$call_id} filtered - no visitor speech detected" );
            return false;
        }

        // Rule 3: Check for meaningful engagement (at least 3 exchanges)
        // Fewer than 3 entries means visitor closed during or right after the AI greeting
        $total_messages = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . $transcripts_table . ' WHERE call_id = %d',
                $call_id
            )
        );

        if ( $total_messages === null || $total_messages < 3 ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Billing Filter: Call {$call_id} filtered - insufficient engagement ({$total_messages} messages)" );
            return false;
        }

        // Passed all filters - this is a billable conversation
        SiteStaffr_Logger::legacy( "SiteStaffr Billing Filter: Call {$call_id} marked as billable - passed all filters (duration: {$call->call_duration}s, visitor_messages: {$caller_messages}, total: {$total_messages})" );
        return true;
    }

    /**
     * Get count of calls this month with optional filtering.
     *
     * @since    1.0.0
     * @param    array    $args    Optional filter arguments (is_billable, etc.).
     * @return   int               Count of calls matching filters.
     */
    public static function count_calls_this_month( $args = array() ) {
        global $wpdb;

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

        // Base WHERE clause for current month.
        $where_values = array();
        $where        = "WHERE DATE_FORMAT(created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')";

        // Add is_billable filter if specified
        if ( isset( $args['is_billable'] ) ) {
            $where          .= ' AND is_billable = %d';
            $where_values[] = intval( $args['is_billable'] );
        }

        // Build SQL query.
        if ( ! empty( $where_values ) ) {
            $sql = $wpdb->prepare(
                'SELECT COUNT(*) FROM ' . $table_name . ' ' . $where,
                ...$where_values
            );
        } else {
            $sql = 'SELECT COUNT(*) FROM ' . $table_name . ' ' . $where;
        }

        $count = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is prepared when optional filters are applied.
        return intval( $count );
    }

    /**
     * Check if a timestamp falls within business hours.
     *
     * @since    0.10.16
     * @param    string    $timestamp    MySQL timestamp (Y-m-d H:i:s format).
     * @param    string    $business_hours    Business hours setting.
     * @return   bool                         True if within business hours, false otherwise.
     */
    public static function is_within_business_hours( $timestamp, $business_hours ) {
        $normalized_hours = self::normalize_business_hours_value( $business_hours );

        // If 24/7, all calls are within business hours
        if ( '24-7' === $normalized_hours ) {
            return true;
        }

        // Parse timestamp
        $dt = new DateTime( $timestamp, wp_timezone() );
        $day_of_week = strtolower( $dt->format( 'l' ) ); // monday, tuesday, etc.
        $time_of_day = $dt->format( 'H:i' ); // 24-hour format HH:MM

        // For standard hours (mon-fri-9-5)
        if ( 'mon-fri-9-5' === $normalized_hours ) {
            // Check if weekday
            $weekdays = array( 'monday', 'tuesday', 'wednesday', 'thursday', 'friday' );
            if ( ! in_array( $day_of_week, $weekdays, true ) ) {
                return false; // Weekend = after hours
            }

            // Check if between 9am and 5pm
            if ( $time_of_day < '09:00' || $time_of_day >= '17:00' ) {
                return false; // Outside 9-5 = after hours
            }

            return true; // Weekday during 9-5
        }

        // For custom hours, parse the JSON schedule
        if ( strpos( trim( (string) $business_hours ), '{' ) === 0 ) {
            $schedule = json_decode( $business_hours, true );
            if ( ! is_array( $schedule ) ) {
                // Invalid JSON, default to treating as within hours to avoid false positives
                return true;
            }

            // Check if day is in schedule and marked as open
            if ( ! isset( $schedule[ $day_of_week ] ) || ! isset( $schedule[ $day_of_week ]['open'] ) ) {
                return false; // Day not defined or not open
            }

            if ( ! $schedule[ $day_of_week ]['open'] ) {
                return false; // Day marked as closed
            }

            // Check time range
            $start = isset( $schedule[ $day_of_week ]['start'] ) ? $schedule[ $day_of_week ]['start'] : '00:00';
            $end = isset( $schedule[ $day_of_week ]['end'] ) ? $schedule[ $day_of_week ]['end'] : '23:59';

            if ( $time_of_day < $start || $time_of_day >= $end ) {
                return false; // Outside business hours
            }

            return true; // Within business hours
        }

        // For day-labeled custom schedules (for example "Mon: 9:00 AM-5:00 PM, Tue: Closed").
        $named_schedule_result = self::evaluate_named_day_schedule( $business_hours, $day_of_week, $time_of_day );
        if ( null !== $named_schedule_result ) {
            return (bool) $named_schedule_result;
        }

        // Unknown format, default to within hours
        return true;
    }

    /**
     * Count after-hours calls for current month.
     *
     * @since    0.10.16
     * @return   int    Count of after-hours calls.
     */
    public static function count_after_hours_calls_this_month() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_calls';
        $business_hours = get_option( 'sitestaffr_business_hours', 'mon-fri-9-5' );

        // If 24/7 business, no calls are after-hours.
        if ( self::is_24_7_schedule( $business_hours ) ) {
            return 0;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $calls = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, created_at FROM %i WHERE DATE_FORMAT(created_at, '%%Y-%%m') = DATE_FORMAT(NOW(), '%%Y-%%m')",
                $table_name
            )
        );

        if ( empty( $calls ) ) {
            return 0;
        }

        // Count calls that are outside business hours
        $after_hours_count = 0;
        foreach ( $calls as $call ) {
            if ( ! self::is_within_business_hours( $call->created_at, $business_hours ) ) {
                $after_hours_count++;
            }
        }

        return $after_hours_count;
    }

    /**
     * Calculate answer rate percentage for current month.
     *
     * @since    0.10.16
     * @return   float    Answer rate percentage (0-100).
     */
    public static function get_answer_rate_this_month() {
        $total_calls = self::count_calls_this_month();

        if ( $total_calls === 0 ) {
            return 100; // No calls = 100% (default to positive)
        }

        $billable_calls = self::count_calls_this_month( array( 'is_billable' => 1 ) );

        // Answer rate = (billable calls / total calls) * 100
        $answer_rate = ( $billable_calls / $total_calls ) * 100;

        return round( $answer_rate, 1 ); // Round to 1 decimal place
    }
}
// phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
