}

    return [
        'local_start' => $local_start_dt->format( 'Y-m-d H:i:s' ),
        'local_end'   => $local_end_dt->format( 'Y-m-d H:i:s' ),
        'utc_start'   => $local_start_dt->setTimezone( $utc_tz )->format( 'Y-m-d H:i:s' ),
        'utc_end'     => $local_end_dt->setTimezone( $utc_tz )->format( 'Y-m-d H:i:s' ),
    ];
}

/**
 * Convert a raw Formidable UTC datetime to the site reporting timezone for health/status output.
 */
function tag_reporting_utc_to_local( string $value ) {
    if ( $value === '' ) {
        return '';
    }

    $utc_tz   = new DateTimeZone( 'UTC' );
    $local_tz = new DateTimeZone( 'America/Vancouver' );
    $dt       = DateTimeImmutable::createFromFormat( '!Y-m-d H:i:s', $value, $utc_tz );

    return $dt ? $dt->setTimezone( $local_tz )->format( 'Y-m-d H:i:s' ) : $value;
}

function tag_reporting_weekly_periods( string $report_end ) {
    $periods = [
        [ 'key'=>'AUG02_08',      'label'=>'Aug. 2–8',        'start'=>'2026-08-02', 'end'=>'2026-08-08' ],
        [ 'key'=>'AUG09_15',      'label'=>'Aug. 9–15',       'start'=>'2026-08-09', 'end'=>'2026-08-15' ],
        [ 'key'=>'AUG16_22',      'label'=>'Aug. 16–22',      'start'=>'2026-08-16', 'end'=>'2026-08-22' ],
        [ 'key'=>'AUG23_29',      'label'=>'Aug. 23–29',      'start'=>'2026-08-23', 'end'=>'2026-08-29' ],
        [ 'key'=>'AUG30_SEP05',   'label'=>'Aug. 30–Sept. 5', 'start'=>'2026-08-30', 'end'=>'2026-09-05' ],
        [ 'key'=>'SEP06_12',      'label'=>'Sept. 6–12',      'start'=>'2026-09-06', 'end'=>'2026-09-12' ],
        [ 'key'=>'SEP13_19',      'label'=>'Sept. 13–19',     'start'=>'2026-09-13', 'end'=>'2026-09-19' ],
        [ 'key'=>'SEP20_26',      'label'=>'Sept. 20–26',     'start'=>'2026-09-20', 'end'=>'2026-09-26' ],
        [ 'key'=>'SEP27_END',     'label'=>'Sept. 27–' . (int) substr( $report_end, 8, 2 ), 'start'=>'2026-09-27', 'end'=>$report_end ],
    ];

    $out = [];
    foreach ( $periods as $period ) {
        if ( $period['start'] > $report_end ) {
            continue;
        }
        if ( $period['end'] > $report_end ) {
            $period['end'] = $report_end;
        }
        $out[] = $period;
    }
    return $out;
}

function tag_reporting_monthly_periods( string $report_end ) {
    $out = [];
    for ( $month = 1; $month <= 12; $month++ ) {
        $start = sprintf( '2026-%02d-01', $month );
        if ( $start > $report_end ) {
            break;
        }
        $month_date = DateTimeImmutable::createFromFormat( '!Y-m-d', $start );
        $last_day = $month_date ? (int) $month_date->format( 't' ) : 28;
        $end = sprintf( '2026-%02d-%02d', $month, $last_day );
        if ( $end > $report_end ) {
            $end = $report_end;
        }
        $date = DateTimeImmutable::createFromFormat( '!Y-m-d', $start );
        $out[] = [
            'key'   => sprintf( '2026%02d', $month ),
            'label' => $date ? $date->format( 'F Y' ) : $start,
            'start' => $start,
            'end'   => $end,
        ];
    }
    return $out;
}

function tag_reporting_table_exists( string $table ) {
    global $wpdb;
    $like = $wpdb->esc_like( $table );
    return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) ) === $table;
}

function tag_reporting_columns( string $table ) {
    global $wpdb;
    $rows = $wpdb->get_results( "SHOW COLUMNS FROM `{$table}`", ARRAY_A );
    $out = [];
    foreach ( (array) $rows as $row ) {
        if ( isset( $row['Field'] ) ) {
            $out[ $row['Field'] ] = $row;
        }
    }
    return $out;
}

function tag_reporting_valid_date( string $value ) {
    if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
        return false;
    }
    $date = DateTimeImmutable::createFromFormat( '!Y-m-d', $value );
    return $date && $date->format( 'Y-m-d' ) === $value;
}

function tag_reporting_add_days( string $date, int $days ) {
    $dt = DateTimeImmutable::createFromFormat( '!Y-m-d', $date );
    if ( ! $dt ) {
        return $date;
    }
    return $dt->modify( sprintf( '%+d day', $days ) )->format( 'Y-m-d' );
}