<?php
/**
 * Operational audit trail, notification delivery records, booking funnel,
 * customer timezone capture, and token-protected ICS subscription feeds.
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- This module owns custom analytics tables, performs versioned schema maintenance, and reads current operational data; table identifiers use only the WordPress prefix and fixed suffixes.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class YATOON_Insights {
    const SCHEMA_VERSION = '4.3.6';
    const FEED_TOKEN_OPTION = 'yatoon_ics_feed_token';

    private static $pending_mail = array();

    public static function init() {
        self::install_schema();
        add_action( 'admin_menu', array( __CLASS__, 'add_menu' ), 25 );
        add_action( 'admin_post_yatoon_regenerate_ics_token', array( __CLASS__, 'regenerate_ics_token' ) );
        add_action( 'wp_ajax_yatoon_track_funnel', array( __CLASS__, 'track_funnel' ) );
        add_action( 'wp_ajax_nopriv_yatoon_track_funnel', array( __CLASS__, 'track_funnel' ) );
        add_filter( 'wp_mail', array( __CLASS__, 'mail_attempted' ) );
        add_action( 'wp_mail_succeeded', array( __CLASS__, 'mail_succeeded' ) );
        add_action( 'wp_mail_failed', array( __CLASS__, 'mail_failed' ) );
        add_action( 'yatoon_booking_created', array( __CLASS__, 'booking_created' ), 95, 1 );
        add_action( 'yatoon_booking_status_changed', array( __CLASS__, 'booking_status_changed' ), 95, 3 );
        add_action( 'yatoon_payment_status_changed', array( __CLASS__, 'payment_status_changed' ), 95, 4 );
        add_action( 'yatoon_prune_funnel_events', array( __CLASS__, 'prune_funnel_events' ) );
        add_action( 'template_redirect', array( __CLASS__, 'maybe_render_ics' ), 1 );
        if ( ! wp_next_scheduled( 'yatoon_prune_funnel_events' ) ) {
            wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'yatoon_prune_funnel_events' );
        }
        if ( defined( 'WP_CLI' ) && WP_CLI ) {
            \WP_CLI::add_command( 'yatoon dst-test', array( __CLASS__, 'cli_dst_test' ) );
        }
    }

    public static function install_schema() {
        if ( self::SCHEMA_VERSION === get_option( 'yatoon_insights_schema_version', '' ) ) {
            return;
        }
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();
        dbDelta( "CREATE TABLE {$wpdb->prefix}yatoon_audit_log (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            event varchar(80) NOT NULL,
            object_type varchar(40) DEFAULT '',
            object_id bigint(20) DEFAULT NULL,
            actor_id bigint(20) DEFAULT NULL,
            message text NOT NULL,
            context longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY event (event),
            KEY object_lookup (object_type, object_id),
            KEY created_at (created_at)
        ) {$charset};" );
        dbDelta( "CREATE TABLE {$wpdb->prefix}yatoon_notification_log (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            channel varchar(20) NOT NULL DEFAULT 'email',
            recipient_hash varchar(64) DEFAULT '',
            subject varchar(255) DEFAULT '',
            status varchar(30) NOT NULL DEFAULT 'attempted',
            error_message text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY created_at (created_at)
        ) {$charset};" );
        dbDelta( "CREATE TABLE {$wpdb->prefix}yatoon_funnel_events (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            session_hash varchar(64) NOT NULL,
            event varchar(50) NOT NULL,
            service_id bigint(20) DEFAULT NULL,
            staff_id bigint(20) DEFAULT NULL,
            location_id bigint(20) DEFAULT NULL,
            error_code varchar(100) DEFAULT '',
            client_timezone varchar(100) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY event (event),
            KEY session_hash (session_hash),
            KEY created_at (created_at)
        ) {$charset};" );
        $bookings = $wpdb->prefix . 'yatoon_bookings';
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifier is the WordPress prefix plus a hardcoded suffix.
        $has_timezone = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$bookings}` LIKE %s", 'client_timezone' ) );
        if ( ! $has_timezone ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifier is the WordPress prefix plus a hardcoded suffix.
            $wpdb->query( "ALTER TABLE `{$bookings}` ADD COLUMN client_timezone varchar(100) DEFAULT NULL AFTER customer_phone" );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifier is the WordPress prefix plus a hardcoded suffix.
        $has_location = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$bookings}` LIKE %s", 'location_id' ) );
        if ( ! $has_location ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifier is the WordPress prefix plus a hardcoded suffix.
            $wpdb->query( "ALTER TABLE `{$bookings}` ADD COLUMN location_id bigint(20) DEFAULT NULL AFTER client_timezone, ADD KEY location_id (location_id)" );
        }
        if ( ! get_option( self::FEED_TOKEN_OPTION, '' ) ) {
            add_option( self::FEED_TOKEN_OPTION, wp_generate_password( 40, false, false ), '', false );
        }
        if ( YATOON_Schema_Verifier::tables_exist( array( 'yatoon_audit_log', 'yatoon_notification_log', 'yatoon_funnel_events' ) ) ) {
            update_option( 'yatoon_insights_schema_version', self::SCHEMA_VERSION, false );
        }
    }

    public static function audit( $event, $message, $object_type = '', $object_id = 0, array $context = array() ) {
        global $wpdb;
        $wpdb->insert(
            $wpdb->prefix . 'yatoon_audit_log',
            array(
                'event'       => sanitize_key( $event ),
                'object_type' => sanitize_key( $object_type ),
                'object_id'   => $object_id ? absint( $object_id ) : null,
                'actor_id'    => get_current_user_id() ?: null,
                'message'     => sanitize_text_field( $message ),
                'context'     => $context ? wp_json_encode( self::redact( $context ) ) : null,
                'created_at'  => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%d', '%d', '%s', '%s', '%s' )
        );
    }

    private static function redact( array $context ) {
        foreach ( $context as $key => $value ) {
            if ( preg_match( '/token|secret|authorization|password/i', (string) $key ) ) {
                $context[ $key ] = '[redacted]';
            } elseif ( is_array( $value ) ) {
                $context[ $key ] = self::redact( $value );
            }
        }
        return $context;
    }

    public static function booking_created( $booking ) {
        $booking_id = absint( is_array( $booking ) ? ( $booking['booking_id'] ?? $booking['id'] ?? 0 ) : ( $booking->id ?? 0 ) );
        $timezone = self::request_timezone();
        $location_id = absint( wp_unslash( $_POST['yatoon_location_id'] ?? 0 ) ); // phpcs:ignore WordPress.Security.NonceVerification.Missing -- booking_created fires only after the booking request has been authorized upstream.
        if ( $booking_id && ( $timezone || $location_id ) ) {
            global $wpdb;
            $data = array();
            $format = array();
            if ( $timezone ) { $data['client_timezone'] = $timezone;
$format[] = '%s'; }
            if ( $location_id ) { $data['location_id'] = $location_id;
$format[] = '%d'; }
            $wpdb->update( $wpdb->prefix . 'yatoon_bookings', $data, array( 'id' => $booking_id ), $format, array( '%d' ) );
            $group_id = $wpdb->get_var( $wpdb->prepare( "SELECT group_booking_id FROM {$wpdb->prefix}yatoon_bookings WHERE id=%d", $booking_id ) );
            if ( $group_id ) {
                $wpdb->update( $wpdb->prefix . 'yatoon_bookings', $data, array( 'group_booking_id' => $group_id ), $format, array( '%s' ) );
            }
        }
        self::audit( 'booking_created', __( 'Booking created', 'yatoon-booking-system' ), 'booking', $booking_id, array( 'client_timezone' => $timezone, 'location_id' => $location_id ) );
    }

    public static function booking_status_changed( $booking_id, $new_status, $old_status = '' ) {
        self::audit( 'booking_status_changed', sprintf( 'Booking status: %s → %s', $old_status, $new_status ), 'booking', $booking_id );
    }

    public static function payment_status_changed( $booking_id, $new_status, $old_status = '', $note = '' ) {
        self::audit( 'payment_status_changed', sprintf( 'Payment status: %s → %s', $old_status, $new_status ), 'booking', $booking_id, array( 'note' => $note ) );
    }

    private static function request_timezone() {
        $timezone = isset( $_POST['client_timezone'] ) ? sanitize_text_field( wp_unslash( $_POST['client_timezone'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Missing -- callers are either the authorized booking hook or nonce-verified AJAX handler.
        return in_array( $timezone, timezone_identifiers_list(), true ) ? $timezone : '';
    }

    public static function mail_attempted( $args ) {
        $to = is_array( $args['to'] ?? '' ) ? implode( ',', $args['to'] ) : (string) ( $args['to'] ?? '' );
        self::$pending_mail = array(
            'recipient_hash' => hash( 'sha256', strtolower( trim( $to ) ) ),
            'subject'        => sanitize_text_field( (string) ( $args['subject'] ?? '' ) ),
        );
        self::record_mail( 'attempted' );
        return $args;
    }

    public static function mail_succeeded( $mail_data ) {
        self::record_mail( 'sent' );
    }

    public static function mail_failed( $error ) {
        self::record_mail( 'failed', is_wp_error( $error ) ? $error->get_error_message() : __( 'Unknown mail error', 'yatoon-booking-system' ) );
    }

    private static function record_mail( $status, $error = '' ) {
        global $wpdb;
        $wpdb->insert(
            $wpdb->prefix . 'yatoon_notification_log',
            array(
                'channel'        => 'email',
                'recipient_hash' => self::$pending_mail['recipient_hash'] ?? '',
                'subject'        => self::$pending_mail['subject'] ?? '',
                'status'         => sanitize_key( $status ),
                'error_message'  => $error ? sanitize_text_field( $error ) : null,
                'created_at'     => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%s', '%s', '%s', '%s' )
        );
    }

    public static function track_funnel() {
        check_ajax_referer( 'yatoon_frontend_nonce', 'nonce' );
        if ( function_exists( 'yatoon_public_rate_limit' ) ) {
            yatoon_public_rate_limit( 'funnel_event', 90, 10 );
        }
        $allowed = array( 'view_services', 'select_service', 'select_time', 'enter_details', 'payment_started', 'booking_success', 'booking_error' );
        $event = sanitize_key( wp_unslash( $_POST['event'] ?? '' ) );
        if ( ! in_array( $event, $allowed, true ) ) {
            wp_send_json_error( array( 'message' => __( 'Invalid funnel event.', 'yatoon-booking-system' ) ), 400 );
        }
        $session = preg_replace( '/[^a-f0-9]/', '', sanitize_key( wp_unslash( $_POST['session_hash'] ?? '' ) ) );
        if ( strlen( $session ) < 16 ) {
            wp_send_json_error( array( 'message' => __( 'Invalid session.', 'yatoon-booking-system' ) ), 400 );
        }
        global $wpdb;
        $wpdb->insert(
            $wpdb->prefix . 'yatoon_funnel_events',
            array(
                'session_hash'   => substr( $session, 0, 64 ),
                'event'          => $event,
                'service_id'     => absint( wp_unslash( $_POST['service_id'] ?? 0 ) ) ?: null,
                'staff_id'       => absint( wp_unslash( $_POST['staff_id'] ?? 0 ) ) ?: null,
                'location_id'    => absint( wp_unslash( $_POST['location_id'] ?? 0 ) ) ?: null,
                'error_code'     => sanitize_key( wp_unslash( $_POST['error_code'] ?? '' ) ),
                'client_timezone'=> self::request_timezone(),
                'created_at'     => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s' )
        );
        wp_send_json_success();
    }

    /** Keep aggregate journey reporting useful without retaining old events forever. */
    public static function prune_funnel_events() {
        global $wpdb;
        $cutoff = ( new DateTime( 'now', wp_timezone() ) )->modify( '-180 days' )->format( 'Y-m-d H:i:s' );
        $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}yatoon_funnel_events WHERE created_at < %s", $cutoff ) );
    }

    public static function add_menu() {
        add_submenu_page(
            'yatoon-booking-system',
            __( 'Insights & Reliability', 'yatoon-booking-system' ),
            __( 'Insights', 'yatoon-booking-system' ),
            'manage_options',
            'yatoon-insights',
            array( __CLASS__, 'render_admin' )
        );
    }

    public static function render_admin() {
        if ( ! current_user_can( 'manage_options' ) ) {
            return;
        }
        global $wpdb;
        $funnel_table = $wpdb->prefix . 'yatoon_funnel_events';
        $audit_table  = $wpdb->prefix . 'yatoon_audit_log';
        $mail_table   = $wpdb->prefix . 'yatoon_notification_log';
        $events = array( 'view_services', 'select_service', 'select_time', 'enter_details', 'payment_started', 'booking_success', 'booking_error' );
        $counts = array();
        foreach ( $events as $event ) {
            $counts[ $event ] = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT session_hash) FROM {$funnel_table} WHERE event=%s AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)", $event ) );
        }
        $journey = array( 'view_services', 'select_service', 'select_time', 'enter_details', 'payment_started', 'booking_success' );
        $funnel_rows = array();
        $previous = 0;
        foreach ( $journey as $index => $event ) {
            $count = $counts[ $event ];
            $base  = $counts['view_services'];
            $funnel_rows[] = array(
                'event'      => $event,
                'count'      => $count,
                'conversion' => $base ? round( ( $count / $base ) * 100, 1 ) : 0,
                'step_rate'   => $index && $previous ? round( ( $count / $previous ) * 100, 1 ) : 100,
                'dropoff'     => $index ? max( 0, $previous - $count ) : 0,
            );
            $previous = $count;
        }
        $errors = $wpdb->get_results( "SELECT COALESCE(NULLIF(error_code,''),'unspecified') AS error_code, COUNT(*) AS total FROM {$funnel_table} WHERE event='booking_error' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY error_code ORDER BY total DESC LIMIT 8" );
        $audits = $wpdb->get_results( "SELECT * FROM {$audit_table} ORDER BY id DESC LIMIT 50" );
        $mail = $wpdb->get_results( "SELECT * FROM {$mail_table} ORDER BY id DESC LIMIT 50" );
        $recall          = array();
        $commission_table = $wpdb->prefix . 'yatoon_commission_rules';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $commission_table ) ) ) === $commission_table ) {
            $recall = $wpdb->get_results( "SELECT MAX(b.customer_name) AS customer_name,b.customer_email,MAX(s.name) AS service_name,MAX(b.start_time) AS last_visit,MAX(cr.rebook_days) AS rebook_days,DATEDIFF(NOW(),MAX(b.start_time)) AS days_since FROM {$wpdb->prefix}yatoon_bookings b JOIN {$wpdb->prefix}yatoon_services s ON s.id=b.service_id JOIN {$commission_table} cr ON cr.service_id=b.service_id AND cr.staff_id=0 AND cr.rebook_days>0 WHERE b.status='completed' GROUP BY b.customer_email,b.service_id HAVING DATEDIFF(NOW(),MAX(b.start_time))>=MAX(cr.rebook_days) ORDER BY days_since DESC LIMIT 100" );
        }
        $token = get_option( self::FEED_TOKEN_OPTION, '' );
        $business_feed = add_query_arg( array( 'yatoon_ics_feed' => $token ), home_url( '/' ) );
        $staff = $wpdb->get_results( "SELECT id, name FROM {$wpdb->prefix}yatoon_staff WHERE is_active=1 ORDER BY name" );
        ?>
        <div class="wrap"><h1><?php esc_html_e( 'Insights & Reliability', 'yatoon-booking-system' ); ?></h1>
        <h2><?php esc_html_e( '30-day booking funnel', 'yatoon-booking-system' ); ?></h2>
        <p><?php esc_html_e( 'Counts are unique, privacy-safe booking sessions. Conversion is measured from the service page; step rate shows how many customers continued from the previous stage.', 'yatoon-booking-system' ); ?></p>
        <div class="yatoon-funnel-report" style="display:grid;gap:10px;max-width:920px">
            <?php foreach ( $funnel_rows as $row ) : ?>
            <div style="display:grid;grid-template-columns:minmax(150px,1fr) 90px 90px 90px;align-items:center;gap:12px;padding:14px 16px;background:#fff;border:1px solid #dcdcde;border-radius:12px">
                <div><strong><?php echo esc_html( ucwords( str_replace( '_', ' ', $row['event'] ) ) ); ?></strong><div style="height:7px;margin-top:8px;overflow:hidden;border-radius:99px;background:#f1f5f9"><span style="display:block;width:<?php echo esc_attr( min( 100, $row['conversion'] ) ); ?>%;height:100%;background:#ec4899"></span></div></div>
                <div><small><?php esc_html_e( 'Sessions', 'yatoon-booking-system' ); ?></small><br><strong><?php echo esc_html( $row['count'] ); ?></strong></div>
                <div><small><?php esc_html_e( 'Conversion', 'yatoon-booking-system' ); ?></small><br><strong><?php echo esc_html( $row['conversion'] ); ?>%</strong></div>
                <div><small><?php esc_html_e( 'Step rate', 'yatoon-booking-system' ); ?></small><br><strong><?php echo esc_html( $row['step_rate'] ); ?>%</strong><?php if ( $row['dropoff'] ) : ?><br><span style="color:#b42318;font-size:11px"><?php echo esc_html( sprintf( __( '%d dropped', 'yatoon-booking-system' ), $row['dropoff'] ) ); ?></span><?php endif; ?></div>
            </div>
            <?php endforeach; ?>
        </div>
        <?php if ( $errors ) : ?><h3><?php esc_html_e( 'Booking error reasons', 'yatoon-booking-system' ); ?></h3><div style="display:flex;flex-wrap:wrap;gap:8px"><?php foreach ( $errors as $error ) : ?><span style="padding:7px 10px;border:1px solid #fecaca;border-radius:999px;background:#fff7f7;color:#991b1b"><code><?php echo esc_html( $error->error_code ); ?></code> · <?php echo esc_html( $error->total ); ?></span><?php endforeach; ?></div><?php endif; ?>
        <h2><?php esc_html_e( 'Calendar subscription', 'yatoon-booking-system' ); ?></h2><p><?php esc_html_e( 'Paste a read-only feed URL into Apple Calendar, Google Calendar, Outlook, or a phone calendar. Regenerating the token immediately revokes every previous URL.', 'yatoon-booking-system' ); ?></p><p><label><?php esc_html_e( 'Business feed', 'yatoon-booking-system' ); ?><br><input class="large-text code" readonly value="<?php echo esc_attr( $business_feed ); ?>"></label></p>
        <?php foreach ( $staff as $person ) : $staff_token = hash_hmac( 'sha256', 'staff:' . $person->id, $token );
$url = add_query_arg( array( 'yatoon_ics_feed' => $staff_token, 'staff_id' => $person->id ), home_url( '/' ) ); ?><p><label><?php echo esc_html( $person->name ); ?><br><input class="large-text code" readonly value="<?php echo esc_attr( $url ); ?>"></label></p><?php endforeach; ?>
        <form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>"><?php wp_nonce_field( 'yatoon_regenerate_ics_token' ); ?><input type="hidden" name="action" value="yatoon_regenerate_ics_token"><button class="button"><?php esc_html_e( 'Revoke and regenerate calendar URLs', 'yatoon-booking-system' ); ?></button></form>
        <h2><?php esc_html_e( 'Due for a return visit', 'yatoon-booking-system' ); ?></h2><p><?php esc_html_e( 'Calculated from each service’s recommended rebook interval in Compensation & Stock.', 'yatoon-booking-system' ); ?></p><table class="widefat striped"><thead><tr><th><?php esc_html_e( 'Customer', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Service', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Last visit', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Days since', 'yatoon-booking-system' ); ?></th></tr></thead><tbody><?php foreach ( $recall as $row ) : ?><tr><td><?php echo esc_html( $row->customer_name ); ?><br><a href="mailto:<?php echo esc_attr( $row->customer_email ); ?>"><?php echo esc_html( $row->customer_email ); ?></a></td><td><?php echo esc_html( $row->service_name ); ?></td><td><?php echo esc_html( $row->last_visit ); ?></td><td><?php echo esc_html( $row->days_since ); ?></td></tr><?php endforeach; ?></tbody></table>
        <h2><?php esc_html_e( 'Recent audit activity', 'yatoon-booking-system' ); ?></h2><table class="widefat striped"><thead><tr><th><?php esc_html_e( 'Time', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Event', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Message', 'yatoon-booking-system' ); ?></th></tr></thead><tbody><?php foreach ( $audits as $row ) : ?><tr><td><?php echo esc_html( $row->created_at ); ?></td><td><code><?php echo esc_html( $row->event ); ?></code></td><td><?php echo esc_html( $row->message ); ?></td></tr><?php endforeach; ?></tbody></table>
        <h2><?php esc_html_e( 'Recent email delivery', 'yatoon-booking-system' ); ?></h2><table class="widefat striped"><thead><tr><th><?php esc_html_e( 'Time', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Status', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Subject', 'yatoon-booking-system' ); ?></th><th><?php esc_html_e( 'Error', 'yatoon-booking-system' ); ?></th></tr></thead><tbody><?php foreach ( $mail as $row ) : ?><tr><td><?php echo esc_html( $row->created_at ); ?></td><td><?php echo esc_html( $row->status ); ?></td><td><?php echo esc_html( $row->subject ); ?></td><td><?php echo esc_html( $row->error_message ); ?></td></tr><?php endforeach; ?></tbody></table></div>
        <?php
    }

    public static function regenerate_ics_token() {
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Permission denied.', 'yatoon-booking-system' ) );
        }
        check_admin_referer( 'yatoon_regenerate_ics_token' );
        update_option( self::FEED_TOKEN_OPTION, wp_generate_password( 40, false, false ), false );
        self::audit( 'ics_token_regenerated', __( 'Calendar subscription URLs were regenerated.', 'yatoon-booking-system' ) );
        wp_safe_redirect( admin_url( 'admin.php?page=yatoon-insights' ) );
        exit;
    }

    public static function maybe_render_ics() {
        if ( empty( $_GET['yatoon_ics_feed'] ) ) {
            return;
        }
        $provided = sanitize_text_field( wp_unslash( $_GET['yatoon_ics_feed'] ) );
        $staff_id = absint( $_GET['staff_id'] ?? 0 );
        $base = (string) get_option( self::FEED_TOKEN_OPTION, '' );
        $expected = $staff_id ? hash_hmac( 'sha256', 'staff:' . $staff_id, $base ) : $base;
        if ( ! $base || ! hash_equals( $expected, $provided ) ) {
            status_header( 403 );
            exit;
        }
        global $wpdb;
        $where = $staff_id ? $wpdb->prepare( ' AND b.staff_id=%d', $staff_id ) : '';
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Optional clause is prepared immediately above; table names use the WordPress prefix.
        $rows = $wpdb->get_results( "SELECT b.*, s.name AS service_name, st.name AS staff_name FROM {$wpdb->prefix}yatoon_bookings b LEFT JOIN {$wpdb->prefix}yatoon_services s ON s.id=b.service_id LEFT JOIN {$wpdb->prefix}yatoon_staff st ON st.id=b.staff_id WHERE b.start_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND b.status NOT IN ('cancelled','cancelled_by_customer','no_show') {$where} ORDER BY b.start_time ASC LIMIT 2000" );
        nocache_headers();
        header( 'Content-Type: text/calendar; charset=utf-8' );
        header( 'Content-Disposition: inline; filename="yatoon-calendar.ics"' );
        // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- This is a text/calendar response; every dynamic field is escaped with the dedicated RFC 5545 escaper.
        echo "BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-//Yatoon//Salon Booking " . self::ics_escape( YATOON_VERSION ) . "//EN\r\nCALSCALE:GREGORIAN\r\nMETHOD:PUBLISH\r\nX-WR-CALNAME:" . self::ics_escape( get_bloginfo( 'name' ) . ' Appointments' ) . "\r\n";
        $tz = wp_timezone_string() ?: 'UTC';
        try {
            $event_timezone = new DateTimeZone( $tz );
        } catch ( Exception $exception ) {
            $event_timezone = new DateTimeZone( 'UTC' );
            $tz             = 'UTC';
        }
        foreach ( $rows as $row ) {
            $uid = 'booking-' . absint( $row->id ) . '@' . wp_parse_url( home_url(), PHP_URL_HOST );
            $start = new DateTimeImmutable( $row->start_time, $event_timezone );
            $end   = new DateTimeImmutable( $row->end_time, $event_timezone );
            // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- This is a text/calendar response; every event field is escaped with the dedicated RFC 5545 escaper.
            echo "BEGIN:VEVENT\r\nUID:" . self::ics_escape( $uid ) . "\r\nDTSTAMP:" . gmdate( 'Ymd\THis\Z' ) . "\r\nDTSTART;TZID=" . self::ics_escape( $tz ) . ':' . $start->format( 'Ymd\THis' ) . "\r\nDTEND;TZID=" . self::ics_escape( $tz ) . ':' . $end->format( 'Ymd\THis' ) . "\r\nSUMMARY:" . self::ics_escape( ( $row->service_name ?: __( 'Appointment', 'yatoon-booking-system' ) ) . ' — ' . $row->customer_name ) . "\r\nDESCRIPTION:" . self::ics_escape( sprintf( /* translators: 1: staff name, 2: appointment status. */ __( 'Staff: %1$s | Status: %2$s', 'yatoon-booking-system' ), $row->staff_name, $row->status ) ) . "\r\nEND:VEVENT\r\n";
        }
        // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- Static RFC 5545 calendar terminator.
        echo "END:VCALENDAR\r\n";
        exit;
    }

    private static function ics_escape( $value ) {
        return str_replace( array( '\\', ';', ',', "\r\n", "\r", "\n" ), array( '\\\\', '\\;', '\\,', "\\n", "\\n", "\\n" ), (string) $value );
    }

    public static function cli_dst_test() {
        $zones = array_unique( array_filter( array( wp_timezone_string(), 'America/New_York', 'America/Los_Angeles', 'Europe/London' ) ) );
        foreach ( $zones as $zone ) {
            $tz = new DateTimeZone( $zone );
            $spring = new DateTimeImmutable( '2026-03-08 01:30:00', $tz );
            $fall   = new DateTimeImmutable( '2026-11-01 01:30:00', $tz );
            if ( $spring->modify( '+2 hours' )->getTimestamp() <= $spring->getTimestamp() || $fall->modify( '+2 hours' )->getTimestamp() <= $fall->getTimestamp() ) {
                \WP_CLI::error( 'DST arithmetic failed for ' . $zone );
            }
            \WP_CLI::log( 'PASS ' . $zone . ' offsets ' . $spring->format( 'P' ) . ' / ' . $fall->format( 'P' ) );
        }
        \WP_CLI::success( __( 'Yatoon DST regression checks completed.', 'yatoon-booking-system' ) );
    }
}
