<?php


namespace Vizyt\Models;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Vizyt's own tables ({prefix}vizyt_*); bookings and availability must be read fresh on every request.

use WP_Error;

class Customer {

    /** Status of a customer deleted while they had bookings (see remove()). */
    const STATUS_ERASED = 'erased';

    /** Statuses the admin can set. */
    const STATUSES = [ 'active', 'inactive' ];

    /** Tags per customer, characters per tag (the column holds "a,b" in 500). */
    const TAGS_MAX       = 10;
    const TAG_MAX_LENGTH = 32;

    /** Notes length cap (characters). */
    const NOTES_MAX = 5000;

    /** Sort keys of the list (the first five are the 5c sort select). */
    const ORDERBY = [ 'last_visit', 'name', 'visits', 'spent', 'created_at', 'id', 'first_name', 'last_name', 'email' ];

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'vizyt_customers';
    }

    // -------------------------------------------------------------------------
    // Tags: stored as "постійна,VIP" — no spaces around commas, no empty or
    // repeated tags (case-insensitive), at most TAGS_MAX of TAG_MAX_LENGTH.
    // -------------------------------------------------------------------------

    /**
     * Clean tags from a list or a "a, b; c" string: whitespace collapsed,
     * commas and semicolons are separators, the first spelling of a repeated
     * tag wins, longer tags are cut, the list is cut at TAGS_MAX.
     *
     * @param mixed $value
     * @return string[]
     */
    public static function normalizeTags( $value ): array {
        if ( null === $value || '' === $value ) {
            return [];
        }
        $list = is_array( $value ) ? $value : preg_split( '/[,;]/u', (string) $value );
        $out  = [];
        $seen = [];
        foreach ( $list as $tag ) {
            if ( ! is_scalar( $tag ) ) {
                continue;
            }
            $tag = sanitize_text_field( str_replace( [ ',', ';' ], ' ', (string) $tag ) );
            $tag = trim( mb_substr( trim( (string) preg_replace( '/\s+/u', ' ', $tag ) ), 0, self::TAG_MAX_LENGTH ) );
            $key = mb_strtolower( $tag );
            if ( '' === $tag || isset( $seen[ $key ] ) ) {
                continue;
            }
            $seen[ $key ] = true;
            $out[]        = $tag;
            if ( count( $out ) >= self::TAGS_MAX ) {
                break;
            }
        }

        return $out;
    }

    /**
     * Like normalizeTags(), but a list the admin sent: too many tags or a
     * too long tag is a 400 (data.field = tags), not silently cut.
     *
     * @param mixed $value Array of strings, or a comma-separated string.
     * @return string[]|WP_Error
     */
    public static function validateTags( $value ) {
        $error = static fn( string $message ) => new WP_Error( 'vizyt_invalid_param', $message, [ 'status' => 400, 'field' => 'tags' ] );

        if ( null === $value || '' === $value ) {
            return [];
        }
        if ( ! is_array( $value ) && ! is_string( $value ) ) {
            return $error( __( 'Tags must be a list of words.', 'vizyt' ) );
        }
        $raw  = is_array( $value ) ? $value : preg_split( '/[,;]/u', $value );
        $keys = [];
        foreach ( $raw as $tag ) {
            if ( ! is_scalar( $tag ) ) {
                return $error( __( 'Tags must be a list of words.', 'vizyt' ) );
            }
            $tag = trim( (string) preg_replace( '/\s+/u', ' ', sanitize_text_field( str_replace( [ ',', ';' ], ' ', (string) $tag ) ) ) );
            if ( '' === $tag ) {
                continue;
            }
            if ( mb_strlen( $tag ) > self::TAG_MAX_LENGTH ) {
                /* translators: %d: maximum characters in a tag */
                return $error( sprintf( __( 'A tag can be up to %d characters long.', 'vizyt' ), self::TAG_MAX_LENGTH ) );
            }
            $keys[ mb_strtolower( $tag ) ] = true;
        }
        if ( count( $keys ) > self::TAGS_MAX ) {
            /* translators: %d: maximum number of tags */
            return $error( sprintf( __( 'A customer can have up to %d tags.', 'vizyt' ), self::TAGS_MAX ) );
        }

        return self::normalizeTags( $raw );
    }

    /** @param string[] $tags */
    public static function tagString( array $tags ): string {
        return implode( ',', $tags );
    }

    /** @return string[] */
    public static function tagList( $stored ): array {
        return '' === (string) $stored ? [] : array_values( array_filter( explode( ',', (string) $stored ), static fn( $t ) => '' !== $t ) );
    }

    /**
     * Tags in use with their customer counts, most used first (5c chips,
     * the tag suggestions of 5d). Erased customers are not counted.
     *
     * @return array<int, array{name: string, count: int}>
     */
    public static function allTags( int $limit = 50 ): array {
        global $wpdb;

        $counts = [];
        $names  = [];
        $rows   = (array) $wpdb->get_col(
            $wpdb->prepare( "SELECT tags FROM %i WHERE tags IS NOT NULL AND tags <> '' AND status <> %s", self::table(), self::STATUS_ERASED )
        );
        foreach ( $rows as $stored ) {
            foreach ( self::tagList( $stored ) as $tag ) {
                $key            = mb_strtolower( $tag );
                $names[ $key ]  = $names[ $key ] ?? $tag;
                $counts[ $key ] = ( $counts[ $key ] ?? 0 ) + 1;
            }
        }

        $list = [];
        foreach ( $counts as $key => $count ) {
            $list[] = [ 'name' => $names[ $key ], 'count' => $count ];
        }
        usort( $list, static fn( $a, $b ) => [ $b['count'], mb_strtolower( $a['name'] ) ] <=> [ $a['count'], mb_strtolower( $b['name'] ) ] );

        return array_slice( $list, 0, max( 1, $limit ) );
    }

    // -------------------------------------------------------------------------
    // List with booking aggregates (5c)
    // -------------------------------------------------------------------------

    /**
     * Booking aggregates per customer, as a derived table "a":
     *   visits     — bookings not cancelled and not a no-show (upcoming ones too);
     *   last_visit — start of the latest visit that happened: completed, or
     *                confirmed with a start before now (business time);
     *   cancelled, no_shows — bookings in those statuses;
     *   spent_main — Σ price of the visits that happened, in $currency (for sorting);
     *   spent_list — "UAH:4300.00,EUR:50.00" every currency, biggest first.
     *
     * @param int[]|null $ids Only these customers (null = everybody).
     * @return array{0: string, 1: array} SQL and its values.
     */
    private static function aggregateSql( string $currency, ?array $ids = null ): array {
        global $wpdb;

        $now    = ( new \DateTimeImmutable( 'now', vizyt_timezone() ) )->format( 'Y-m-d H:i:s' );
        $where  = '';
        $values = [ $currency, $now, $now ];
        if ( null !== $ids ) {
            $ids    = $ids ?: [ 0 ];
            $where  = 'WHERE customer_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')';
            $values = array_merge( $values, $ids );
        }
        $happened = "(status = 'completed' OR (status = 'confirmed' AND start_datetime <= %s))";

        $sql = "SELECT customer_id,
                       SUM(visits) AS visits,
                       MAX(last_visit) AS last_visit,
                       SUM(cancelled) AS cancelled,
                       SUM(no_shows) AS no_shows,
                       SUM(CASE WHEN currency = %s THEN spent ELSE 0 END) AS spent_main,
                       GROUP_CONCAT(CASE WHEN spent > 0 THEN CONCAT(currency, ':', spent) END ORDER BY spent DESC, currency ASC SEPARATOR ',') AS spent_list
                  FROM (
                        SELECT customer_id, currency,
                               SUM(status NOT IN ('cancelled', 'no_show')) AS visits,
                               MAX(CASE WHEN {$happened} THEN start_datetime END) AS last_visit,
                               SUM(status = 'cancelled') AS cancelled,
                               SUM(status = 'no_show') AS no_shows,
                               SUM(CASE WHEN {$happened} THEN price ELSE 0 END) AS spent
                          FROM {$wpdb->prefix}vizyt_bookings
                          {$where}
                         GROUP BY customer_id, currency
                       ) g
                 GROUP BY customer_id";

        return [ $sql, $values ];
    }

    /**
     * WHERE on customers "c": status (active|inactive; default: everybody
     * but erased), search (first / last / full name, email, phone — also
     * digits only), tag (exact, case-insensitive).
     *
     * @return array{0: string, 1: array}
     */
    private static function where( array $args ): array {
        global $wpdb;

        $where  = [];
        $values = [];

        if ( isset( $args['status'] ) && '' !== $args['status'] ) {
            $where[]  = 'c.status = %s';
            $values[] = (string) $args['status'];
        } else {
            $where[]  = 'c.status <> %s';
            $values[] = self::STATUS_ERASED;
        }

        $search = trim( (string) ( $args['search'] ?? '' ) );
        if ( '' !== $search ) {
            $like  = '%' . $wpdb->esc_like( $search ) . '%';
            $parts = [ 'c.first_name LIKE %s', 'c.last_name LIKE %s', "CONCAT_WS(' ', c.first_name, c.last_name) LIKE %s", 'c.email LIKE %s', 'c.phone LIKE %s' ];
            array_push( $values, $like, $like, $like, $like, $like );
            $digits = preg_replace( '/\D+/', '', $search );
            if ( strlen( $digits ) >= 5 ) {
                $parts[]  = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.phone, ' ', ''), '-', ''), '(', ''), ')', ''), '+', '') LIKE %s";
                $values[] = '%' . $digits . '%';
            }
            $where[] = '(' . implode( ' OR ', $parts ) . ')';
        }

        $tag = self::normalizeTags( (string) ( $args['tag'] ?? '' ) );
        if ( $tag ) {
            // LOWER() on both sides: case-insensitive whatever the collation.
            $where[]  = 'FIND_IN_SET(LOWER(%s), LOWER(c.tags)) > 0';
            $values[] = $tag[0];
        }

        return [ 'WHERE ' . implode( ' AND ', $where ), $values ];
    }

    /**
     * List for the admin (5c) with the booking aggregates on every row:
     * visits, last_visit, cancelled, no_shows, spent_main, spent_list
     * (see aggregateSql()). Sorted by an aggregate (last_visit, visits,
     * spent): one query for the page, one for the total. Sorted by a
     * customer column: the total, the page of customers, then the
     * aggregates of that page only (no aggregate over every customer).
     *
     * Args: page, per_page, status, search, tag, orderby (ORDERBY; last_visit
     * puts customers without a visit last), order, currency (of spent_main;
     * default: Settings default currency).
     *
     * @return array{items: object[], total: int, pages: int}
     */
    public static function getAll( array $args = [] ): array {
        global $wpdb;

        $page     = max( 1, (int) ( $args['page'] ?? 1 ) );
        $per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
        $orderby  = in_array( $args['orderby'] ?? '', self::ORDERBY, true ) ? $args['orderby'] : 'created_at';
        $order    = 'ASC' === strtoupper( (string) ( $args['order'] ?? 'DESC' ) ) ? 'ASC' : 'DESC';
        $currency = (string) ( $args['currency'] ?? \Vizyt\Admin\Settings::get_default_currency() );

        [ $where_sql, $where_values ] = self::where( $args );

        switch ( $orderby ) {
            case 'last_visit':
                $order_sql = "a.last_visit IS NULL, a.last_visit {$order}, c.id {$order}";
                break;
            case 'visits':
                $order_sql = "COALESCE(a.visits, 0) {$order}, c.id {$order}";
                break;
            case 'spent':
                $order_sql = "COALESCE(a.spent_main, 0) {$order}, c.id {$order}";
                break;
            case 'name':
                $order_sql = "c.first_name {$order}, c.last_name {$order}, c.id {$order}";
                break;
            default:
                $order_sql = "c.{$orderby} {$order}, c.id {$order}";
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where_sql (where()) and $agg_sql (aggregateSql()) are fixed SQL with placeholders, every value goes through prepare(); $order_sql is one of the fixed orders above with a whitelisted column and direction.
        $table = self::table();
        $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} c {$where_sql}", ...$where_values ) );

        if ( ! in_array( $orderby, [ 'last_visit', 'visits', 'spent' ], true ) ) {
            $items = (array) $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT c.* FROM {$table} c {$where_sql} ORDER BY {$order_sql} LIMIT %d OFFSET %d",
                    ...array_merge( $where_values, [ $per_page, ( $page - 1 ) * $per_page ] )
                )
            );
            $agg = [];
            if ( $items ) {
                [ $agg_sql, $agg_values ] = self::aggregateSql( $currency, array_map( static fn( $r ) => (int) $r->id, $items ) );
                foreach ( (array) $wpdb->get_results( $wpdb->prepare( $agg_sql, ...$agg_values ) ) as $row ) {
                    $agg[ (int) $row->customer_id ] = $row;
                }
            }
            foreach ( $items as $item ) {
                $row              = $agg[ (int) $item->id ] ?? null;
                $item->visits     = $row ? (int) $row->visits : 0;
                $item->last_visit = $row ? $row->last_visit : null;
                $item->cancelled  = $row ? (int) $row->cancelled : 0;
                $item->no_shows   = $row ? (int) $row->no_shows : 0;
                $item->spent_main = $row ? $row->spent_main : 0;
                $item->spent_list = $row ? $row->spent_list : null;
            }

            return [
                'items' => $items,
                'total' => $total,
                'pages' => (int) ceil( $total / $per_page ),
            ];
        }

        [ $agg_sql, $agg_values ] = self::aggregateSql( $currency );
        $items = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT c.*, COALESCE(a.visits, 0) AS visits, a.last_visit, COALESCE(a.cancelled, 0) AS cancelled,
                        COALESCE(a.no_shows, 0) AS no_shows, COALESCE(a.spent_main, 0) AS spent_main, a.spent_list
                   FROM {$table} c LEFT JOIN ({$agg_sql}) a ON a.customer_id = c.id
                   {$where_sql}
                  ORDER BY {$order_sql}
                  LIMIT %d OFFSET %d",
                ...array_merge( $agg_values, $where_values, [ $per_page, ( $page - 1 ) * $per_page ] )
            )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return [
            'items' => $items,
            'total' => $total,
            'pages' => (int) ceil( $total / $per_page ),
        ];
    }

    /**
     * The aggregates of some customers (profile 5d): id => visits, last_visit,
     * cancelled, no_shows, spent (list of {currency, amount}).
     *
     * @param int[] $ids
     * @return array<int, array{visits: int, last_visit: ?string, cancelled: int, no_shows: int, spent: array<int, array{currency: string, amount: float}>}>
     */
    public static function aggregates( array $ids ): array {
        global $wpdb;

        $ids = array_values( array_unique( array_filter( array_map( 'absint', $ids ) ) ) );
        if ( ! $ids ) {
            return [];
        }
        [ $sql, $values ] = self::aggregateSql( \Vizyt\Admin\Settings::get_default_currency(), $ids );
        $out              = array_fill_keys( $ids, [ 'visits' => 0, 'last_visit' => null, 'cancelled' => 0, 'no_shows' => 0, 'spent' => [] ] );
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- aggregateSql() returns fixed SQL with placeholders and its values.
        $rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, ...$values ) );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        foreach ( $rows as $row ) {
            $out[ (int) $row->customer_id ] = self::aggregateOf( $row );
        }

        return $out;
    }

    /**
     * The aggregate columns of a row (getAll() item or aggregateSql() row) as typed values.
     *
     * @return array{visits: int, last_visit: ?string, cancelled: int, no_shows: int, spent: array<int, array{currency: string, amount: float}>}
     */
    public static function aggregateOf( object $row ): array {
        $spent = [];
        foreach ( array_filter( explode( ',', (string) ( $row->spent_list ?? '' ) ) ) as $pair ) {
            [ $currency, $amount ] = array_pad( explode( ':', $pair, 2 ), 2, '0' );
            $spent[]               = [ 'currency' => (string) $currency, 'amount' => round( (float) $amount, 2 ) ];
        }

        return [
            'visits'     => (int) ( $row->visits ?? 0 ),
            'last_visit' => empty( $row->last_visit ) ? null : (string) $row->last_visit,
            'cancelled'  => (int) ( $row->cancelled ?? 0 ),
            'no_shows'   => (int) ( $row->no_shows ?? 0 ),
            'spent'      => $spent,
        ];
    }

    public static function getById( int $id ): ?object {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), $id )
        ) ?: null;
    }

    // -------------------------------------------------------------------------
    // Writes
    // -------------------------------------------------------------------------

    /**
     * Check what the admin sent for a create ($create = true: first_name
     * required) or an update (only the keys present). Errors are 400 with
     * data.field. Returns the clean values: first_name, last_name, email,
     * phone, date_of_birth ('' clears), status, notes, tags (list).
     *
     * @return array<string, mixed>|WP_Error
     */
    public static function validate( array $params, bool $create ) {
        $error = static fn( string $field, string $message ) => new WP_Error( 'vizyt_invalid_param', $message, [ 'status' => 400, 'field' => $field ] );
        $out   = [];

        if ( $create || array_key_exists( 'first_name', $params ) ) {
            $first = isset( $params['first_name'] ) && is_scalar( $params['first_name'] ) ? sanitize_text_field( (string) $params['first_name'] ) : '';
            if ( '' === $first ) {
                return new WP_Error( 'vizyt_missing_required_fields', __( 'Enter the customer’s name.', 'vizyt' ), [ 'status' => 400, 'field' => 'first_name' ] );
            }
            $out['first_name'] = mb_substr( $first, 0, 100 );
        }

        if ( array_key_exists( 'last_name', $params ) ) {
            $out['last_name'] = mb_substr( is_scalar( $params['last_name'] ) ? sanitize_text_field( (string) $params['last_name'] ) : '', 0, 100 );
        }

        if ( array_key_exists( 'email', $params ) ) {
            $email = is_scalar( $params['email'] ) ? trim( (string) $params['email'] ) : '';
            if ( '' !== $email && ! is_email( $email ) ) {
                return $error( 'email', __( 'Enter a valid email address.', 'vizyt' ) );
            }
            $out['email'] = '' === $email ? '' : sanitize_email( $email );
        }

        if ( array_key_exists( 'phone', $params ) ) {
            $phone = is_scalar( $params['phone'] ) ? sanitize_text_field( (string) $params['phone'] ) : '';
            if ( mb_strlen( $phone ) > 20 ) {
                return $error( 'phone', __( 'A phone number can be up to 20 characters long.', 'vizyt' ) );
            }
            $out['phone'] = $phone;
        }

        if ( array_key_exists( 'date_of_birth', $params ) ) {
            $dob = is_scalar( $params['date_of_birth'] ) ? trim( (string) $params['date_of_birth'] ) : '';
            if ( '' !== $dob ) {
                $today = ( new \DateTimeImmutable( 'now', vizyt_timezone() ) )->format( 'Y-m-d' );
                if ( ! \Vizyt\Rest\Params::is_date( $dob ) || $dob < '1900-01-01' || $dob > $today ) {
                    return $error( 'date_of_birth', __( 'Enter a real date of birth.', 'vizyt' ) );
                }
            }
            $out['date_of_birth'] = $dob;
        }

        if ( array_key_exists( 'status', $params ) ) {
            if ( ! in_array( $params['status'], self::STATUSES, true ) ) {
                return $error( 'status', __( 'Unknown customer status.', 'vizyt' ) );
            }
            $out['status'] = $params['status'];
        }

        if ( array_key_exists( 'notes', $params ) ) {
            $notes = is_scalar( $params['notes'] ) ? (string) $params['notes'] : '';
            if ( mb_strlen( $notes ) > self::NOTES_MAX ) {
                /* translators: %d: maximum characters */
                return $error( 'notes', sprintf( __( 'Notes can be up to %d characters long.', 'vizyt' ), self::NOTES_MAX ) );
            }
            $out['notes'] = $notes;
        }

        if ( array_key_exists( 'tags', $params ) ) {
            $tags = self::validateTags( $params['tags'] );
            if ( is_wp_error( $tags ) ) {
                return $tags;
            }
            $out['tags'] = $tags;
        }

        return $out;
    }

    /**
     * Columns from create/update data (only the keys present). Empty email,
     * phone, date of birth, notes or tags are stored as NULL.
     *
     * @return array<string, mixed>
     */
    private static function fields( array $data ): array {
        $fields = [];

        if ( isset( $data['first_name'] ) ) {
            $first = mb_substr( sanitize_text_field( (string) $data['first_name'] ), 0, 100 );
            if ( '' !== $first ) {
                $fields['first_name'] = $first;
            }
        }

        if ( isset( $data['last_name'] ) ) {
            $fields['last_name'] = mb_substr( sanitize_text_field( (string) $data['last_name'] ), 0, 100 );
        }

        if ( array_key_exists( 'email', $data ) ) {
            $email           = sanitize_email( (string) $data['email'] );
            $fields['email'] = '' === $email ? null : $email;
        }

        if ( array_key_exists( 'phone', $data ) ) {
            $phone           = mb_substr( sanitize_text_field( (string) $data['phone'] ), 0, 20 );
            $fields['phone'] = '' === $phone ? null : $phone;
        }

        if ( array_key_exists( 'date_of_birth', $data ) ) {
            $dob = sanitize_text_field( (string) $data['date_of_birth'] );
            if ( '' === $dob ) {
                $fields['date_of_birth'] = null;
            } elseif ( \Vizyt\Rest\Params::is_date( $dob ) ) {
                $fields['date_of_birth'] = $dob;
            }
        }

        if ( isset( $data['status'] ) ) {
            $fields['status'] = in_array( $data['status'], array_merge( self::STATUSES, [ self::STATUS_ERASED ] ), true )
                ? $data['status']
                : 'active';
        }

        if ( array_key_exists( 'notes', $data ) ) {
            $notes           = wp_kses_post( mb_substr( (string) $data['notes'], 0, self::NOTES_MAX ) );
            $fields['notes'] = '' === $notes ? null : $notes;
        }

        if ( array_key_exists( 'tags', $data ) ) {
            $tags           = self::tagString( self::normalizeTags( $data['tags'] ) );
            $fields['tags'] = '' === $tags ? null : $tags;
        }

        return $fields;
    }

    public static function create( array $data ): int|false {
        global $wpdb;

        $fields = self::fields( $data );
        if ( empty( $fields['first_name'] ) ) {
            return false;
        }

        $now                  = current_time( 'mysql', true );
        $fields['created_at'] = $now;
        $fields['updated_at'] = $now;

        $result = $wpdb->insert( self::table(), $fields );

        return $result ? (int) $wpdb->insert_id : false;
    }

    public static function update( int $id, array $data ): bool {
        global $wpdb;

        $fields = self::fields( $data );
        if ( empty( $fields ) ) {
            return false;
        }

        $fields['updated_at'] = current_time( 'mysql', true );

        return $wpdb->update( self::table(), $fields, [ 'id' => $id ] ) !== false;
    }

    public static function delete( int $id ): bool {
        global $wpdb;
        $wpdb->delete( $wpdb->prefix . 'vizyt_customer_notes', [ 'customer_id' => $id ] );
        return $wpdb->delete( self::table(), [ 'id' => $id ] ) !== false;
    }

    /**
     * Delete from the admin (5d ⋯ → Видалити): a customer without bookings
     * is deleted; one with bookings is anonymised like the privacy eraser
     * does (the bookings stay for the statistics and the calendars) and
     * hidden from the lists. "Without bookings" is checked by the DELETE
     * itself, so a booking made meanwhile never loses its customer row.
     *
     * @return string 'deleted' | 'anonymised' | '' (failed)
     */
    public static function remove( int $id ): string {
        global $wpdb;

        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->prefix}vizyt_customers WHERE id = %d AND NOT EXISTS (SELECT 1 FROM {$wpdb->prefix}vizyt_bookings WHERE customer_id = %d)",
                $id,
                $id
            )
        );
        if ( false === $deleted ) {
            return '';
        }
        if ( $deleted > 0 ) {
            $wpdb->delete( $wpdb->prefix . 'vizyt_customer_notes', [ 'customer_id' => $id ] );
            return 'deleted';
        }

        return false !== self::anonymise( $id ) ? 'anonymised' : '';
    }

    /**
     * Remove a customer's personal data and keep their bookings: the row is
     * renamed "Deleted customer" with every other field emptied and status
     * "erased"; the bookings' notes and cancel reasons (which can hold what
     * the customer said) and the reasons in their history are cleared.
     * Used by the privacy eraser and by remove(). All or nothing (Tx).
     *
     * @return int|false Bookings kept, false when a write failed (nothing changed).
     */
    public static function anonymise( int $id ) {
        return \Vizyt\Support\Tx::run( static fn() => self::anonymiseWrites( $id ) );
    }

    /**
     * The writes of anonymise(); false as soon as one fails.
     *
     * @return int|false
     */
    private static function anonymiseWrites( int $id ) {
        global $wpdb;

        $ok = false !== $wpdb->update(
            self::table(),
            [
                'first_name'    => __( 'Deleted customer', 'vizyt' ),
                'last_name'     => null,
                'email'         => null,
                'phone'         => null,
                'date_of_birth' => null,
                'notes'         => null,
                'tags'          => null,
                'status'        => self::STATUS_ERASED,
                'updated_at'    => current_time( 'mysql', true ),
            ],
            [ 'id' => $id ]
        );
        if ( ! $ok || false === $wpdb->delete( $wpdb->prefix . 'vizyt_customer_notes', [ 'customer_id' => $id ] ) ) {
            return false;
        }

        $booking_ids = array_map( 'intval', (array) $wpdb->get_col(
            $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}vizyt_bookings WHERE customer_id = %d", $id )
        ) );

        if ( $booking_ids ) {
            $scrubbed = $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$wpdb->prefix}vizyt_bookings SET cancel_reason = NULL, internal_note = NULL WHERE id IN (" . implode( ',', array_fill( 0, count( $booking_ids ), '%d' ) ) . ') AND (cancel_reason IS NOT NULL OR internal_note IS NOT NULL)',
                    $booking_ids
                )
            );
            if ( false === $scrubbed ) {
                return false;
            }

            $events = (array) $wpdb->get_results(
                $wpdb->prepare(
                    'SELECT id, meta FROM %i WHERE booking_id IN (' . implode( ',', array_fill( 0, count( $booking_ids ), '%d' ) ) . ') AND type = %s AND meta IS NOT NULL',
                    array_merge( [ BookingEvent::table() ], $booking_ids, [ 'cancelled' ] )
                )
            );
            foreach ( $events as $event ) {
                $meta = json_decode( (string) $event->meta, true );
                if ( is_array( $meta ) && isset( $meta['reason'] ) ) {
                    $meta['reason'] = '';
                    if ( false === $wpdb->update( BookingEvent::table(), [ 'meta' => wp_json_encode( $meta ) ], [ 'id' => (int) $event->id ] ) ) {
                        return false;
                    }
                }
            }
        }

        return count( $booking_ids );
    }

    /**
     * Visits per customer: bookings that were not cancelled and not a no-show
     * (shown in the admin customer lookup, "6 візитів").
     *
     * @param int[] $ids
     * @return array<int, int> Every requested id has a key.
     */
    public static function visitCounts( array $ids ): array {
        global $wpdb;

        $ids = array_values( array_unique( array_filter( array_map( 'absint', $ids ) ) ) );
        if ( ! $ids ) {
            return [];
        }

        $out  = array_fill_keys( $ids, 0 );
        $rows = (array) $wpdb->get_results(
            $wpdb->prepare(
                "SELECT customer_id, COUNT(*) AS n FROM {$wpdb->prefix}vizyt_bookings WHERE customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") AND status NOT IN ('cancelled', 'no_show') GROUP BY customer_id",
                $ids
            )
        );
        foreach ( $rows as $row ) {
            $out[ (int) $row->customer_id ] = (int) $row->n;
        }

        return $out;
    }

    /**
     * Customers, without the erased ones (status filter optional).
     */
    public static function count( array $args = [] ): int {
        global $wpdb;
        $table = self::table();

        if ( ! isset( $args['status'] ) ) {
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status <> %s', $table, self::STATUS_ERASED ) );
        }

        return (int) $wpdb->get_var(
            $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $table, $args['status'] )
        );
    }
}