<?php
/**
 * CustomerRepository — persistence layer for tc_customers.
 *
 * Responsible for all reads and writes to the tc_customers table.
 * The AccountManager coordinates this repository with WP user
 * operations and business-logic validation.
 *
 * Table columns (from Schema::customers()):
 *   id, wp_user_id, email, first_name, last_name, phone,
 *   language, currency, total_orders, total_spent, last_ordered_at,
 *   status, notes, created_at, updated_at, deleted_at
 *
 * @package TitanCart\Modules\Accounts
 */

namespace TitanCart\Modules\Accounts;

defined( 'ABSPATH' ) || exit;

class CustomerRepository {

    // ══════════════════════════════════════════════════════════════════════════
    // FINDERS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Find a customer record by primary key.
     *
     * @param  int $id  tc_customers.id
     * @return array|null
     */
    public function find( int $id ): ?array {
        $db  = titancart_db();
        $row = $db->getRow(
            "SELECT * FROM {$db->table('customers')} WHERE id = ?i AND deleted_at IS NULL LIMIT 1",
            $id
        );
        return $row ?: null;
    }

    /**
     * Find by WordPress user ID.
     *
     * @param  int $wp_user_id
     * @return array|null
     */
    public function find_by_wp_user( int $wp_user_id ): ?array {
        $db  = titancart_db();
        $row = $db->getRow(
            "SELECT * FROM {$db->table('customers')} WHERE wp_user_id = ?i AND deleted_at IS NULL LIMIT 1",
            $wp_user_id
        );
        return $row ?: null;
    }

    /**
     * Find by email address.
     *
     * @param  string $email
     * @return array|null
     */
    public function find_by_email( string $email ): ?array {
        $db  = titancart_db();
        $row = $db->getRow(
            "SELECT * FROM {$db->table('customers')} WHERE email = ?s AND deleted_at IS NULL LIMIT 1",
            strtolower( trim( $email ) )
        );
        return $row ?: null;
    }

    /**
     * List customers with optional filters.
     *
     * @param  array $args {
     *   @type string $search     Search email / name.
     *   @type string $status     'active'|'disabled'|'banned'
     *   @type string $sort       Column name, default 'created_at'.
     *   @type string $order      'ASC'|'DESC', default 'DESC'.
     *   @type int    $page       1-based page number.
     *   @type int    $per_page   Rows per page, default 20.
     * }
     * @return array{ items: array, total: int, pages: int }
     */
    public function get_list( array $args = [] ): array {
        $db       = titancart_db();
        $table    = $db->table( 'customers' );
        $per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
        $page     = max( 1, (int) ( $args['page']     ?? 1  ) );
        $offset   = ( $page - 1 ) * $per_page;

        $where  = 'WHERE deleted_at IS NULL';
        $params = [];

        if ( ! empty( $args['status'] ) ) {
            $where   .= ' AND status = ?s';
            $params[] = $args['status'];
        }

        if ( ! empty( $args['search'] ) ) {
            // Database::escape() is private; escape_like() is the public API for LIKE escaping.
            // Calling $db->escape() here used to fatal on every customer search.
            $like     = '%' . $db->escape_like( $args['search'] ) . '%';
            $where   .= " AND (email LIKE ?p OR first_name LIKE ?p OR last_name LIKE ?p)";
            $params[] = $like;
            $params[] = $like;
            $params[] = $like;
        }

        $allowed_sort = [ 'created_at', 'email', 'first_name', 'last_name', 'total_orders', 'total_spent' ];
        $sort         = in_array( $args['sort'] ?? '', $allowed_sort, true ) ? $args['sort'] : 'created_at';
        $order        = strtoupper( $args['order'] ?? 'DESC' ) === 'ASC' ? 'ASC' : 'DESC';

        $total = (int) $db->getField(
            "SELECT COUNT(*) FROM {$table} {$where}",
            ...$params
        );

        $items = $db->getArray(
            "SELECT * FROM {$table} {$where} ORDER BY {$sort} {$order} LIMIT ?i OFFSET ?i",
            ...array_merge( $params, [ $per_page, $offset ] )
        );

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

    // ══════════════════════════════════════════════════════════════════════════
    // WRITES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Insert a new customer record.
     *
     * Before inserting, this method auto-anonymizes any *soft-deleted* row
     * still holding the same email. The UNIQUE(email) constraint on
     * tc_customers does not respect soft deletes, so a stale row with
     * deleted_at IS NOT NULL but the original email intact will silently
     * block any subsequent INSERT for that email — $wpdb->insert returns
     * false, $wpdb->insert_id stays at 0, and callers walk away with a
     * customer_id of 0 (a broken account state). Active rows (deleted_at
     * IS NULL) are intentionally NOT touched here — that's a real conflict
     * that should be surfaced to the caller via insert_id=0.
     *
     * Stale rows arrive in this state from two paths:
     *
     *   1. Admin deletion via wp-admin → Users → Delete fires the
     *      `delete_user` action, AccountsModule::on_user_deleted handles it,
     *      and (since the soft_delete fix) anonymization now runs there too.
     *      But pre-fix data may still carry the original email.
     *
     *   2. Any historical code path that set deleted_at directly without
     *      going through CustomerRepository::soft_delete().
     *
     * @param  array $data  Map of column => value.
     * @return int   Newly inserted ID, or 0 if INSERT failed.
     */
    public function create( array $data ): int {
        global $wpdb;

        $email = isset( $data['email'] ) ? (string) $data['email'] : '';
        if ( $email !== '' ) {
            $db    = titancart_db();
            $stale = $db->getRow(
                "SELECT id FROM {$db->table('customers')}
                 WHERE email = ?s AND deleted_at IS NOT NULL
                 LIMIT 1",
                $email
            );
            if ( $stale ) {
                $now = current_time( 'mysql', true );
                $db->query(
                    "UPDATE {$db->table('customers')}
                     SET email      = CONCAT('deleted_', id, '@removed.invalid'),
                         first_name = 'Deleted',
                         last_name  = 'User',
                         phone      = NULL,
                         status     = 'disabled',
                         updated_at = ?s
                     WHERE id = ?i",
                    $now,
                    (int) $stale['id']
                );
            }
        }

        $wpdb->insert( $wpdb->prefix . 'tc_customers', $data );
        return (int) $wpdb->insert_id;
    }

    /**
     * Update a customer record.
     *
     * @param  int   $id
     * @param  array $data  Map of column => value.
     * @return bool
     */
    public function update( int $id, array $data ): bool {
        global $wpdb;
        $result = $wpdb->update(
            $wpdb->prefix . 'tc_customers',
            $data,
            [ 'id' => $id ]
        );
        return $result !== false;
    }

    /**
     * Soft-delete a customer (sets deleted_at) AND anonymize PII fields.
     *
     * Anonymization is intentionally performed here rather than only inside
     * the storefront's Delete-My-Account flow. Reasons:
     *
     *   1. The `delete_user` WP action fires whenever an admin deletes a WP
     *      user via wp-admin → Users → Delete. AccountsModule listens for
     *      that and calls this method. If we *only* set deleted_at, the
     *      tc_customers row keeps its original email, the UNIQUE(email)
     *      constraint then blocks anyone (including the same person)
     *      registering with that email later — INSERT fails silently and
     *      the new account ends up with no tc_customers row at all.
     *
     *   2. GDPR-style "right to be forgotten" expectations are uniform
     *      regardless of which UI triggered the delete.
     *
     * The anonymization shape mirrors AccountController::delete_account so
     * both paths converge on identical row state. The CONCAT in SQL
     * guarantees per-row uniqueness for the placeholder email.
     *
     * Idempotent — running twice on the same row produces the same result
     * (first_name/last_name stay 'Deleted'/'User', email already starts
     * with 'deleted_<id>@removed.invalid' but the CONCAT regenerates the
     * same string).
     *
     * @param  int $id  tc_customers.id
     * @return bool     True on success.
     */
    public function soft_delete( int $id ): bool {
        $db  = titancart_db();
        $now = current_time( 'mysql', true );
        $result = $db->query(
            "UPDATE {$db->table('customers')}
             SET deleted_at  = ?s,
                 email       = CONCAT('deleted_', id, '@removed.invalid'),
                 first_name  = 'Deleted',
                 last_name   = 'User',
                 phone       = NULL,
                 status      = 'disabled',
                 updated_at  = ?s
             WHERE id = ?i",
            $now,
            $now,
            $id
        );
        return $result !== false;
    }

    /**
     * Increment order stats after a new order is placed.
     *
     * @param  int   $customer_id
     * @param  float $order_total
     * @return void
     */
    public function increment_order_stats( int $customer_id, float $order_total ): void {
        $db = titancart_db();
        $db->query(
            "UPDATE {$db->table('customers')}
             SET total_orders = total_orders + 1,
                 total_spent  = total_spent + ?s,
                 last_ordered_at = ?s
             WHERE id = ?i",
            (string) $order_total,
            titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
            $customer_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // ADDRESSES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all saved addresses for a customer.
     *
     * @param  int         $customer_id
     * @param  string|null $type  'billing'|'shipping'|null (all)
     * @return array
     */
    public function get_addresses( int $customer_id, ?string $type = null ): array {
        $db    = titancart_db();
        $where = 'WHERE customer_id = ?i';
        $args  = [ $customer_id ];

        if ( $type && in_array( $type, [ 'billing', 'shipping' ], true ) ) {
            $where  .= ' AND type = ?s';
            $args[]  = $type;
        }

        return $db->getArray(
            "SELECT * FROM {$db->table('customer_addresses')} {$where} ORDER BY is_default DESC, id ASC",
            ...$args
        ) ?: [];
    }

    /**
     * Get a single address, ensuring it belongs to the customer.
     *
     * @param  int $address_id
     * @param  int $customer_id
     * @return array|null
     */
    public function get_address( int $address_id, int $customer_id ): ?array {
        $db  = titancart_db();
        $row = $db->getRow(
            "SELECT * FROM {$db->table('customer_addresses')} WHERE id = ?i AND customer_id = ?i LIMIT 1",
            $address_id,
            $customer_id
        );
        return $row ?: null;
    }

    /**
     * Insert an address row. Returns new address ID.
     *
     * @param  array $data
     * @return int
     */
    public function create_address( array $data ): int {
        global $wpdb;
        $wpdb->insert( $wpdb->prefix . 'tc_customer_addresses', $data );
        return (int) $wpdb->insert_id;
    }

    /**
     * Update an address row.
     *
     * @param  int   $address_id
     * @param  int   $customer_id  Ownership check.
     * @param  array $data
     * @return bool
     */
    public function update_address( int $address_id, int $customer_id, array $data ): bool {
        global $wpdb;
        $result = $wpdb->update(
            $wpdb->prefix . 'tc_customer_addresses',
            $data,
            [ 'id' => $address_id, 'customer_id' => $customer_id ]
        );
        return $result !== false;
    }

    /**
     * Delete an address.
     *
     * @param  int $address_id
     * @param  int $customer_id
     * @return bool
     */
    public function delete_address( int $address_id, int $customer_id ): bool {
        global $wpdb;
        $result = $wpdb->delete(
            $wpdb->prefix . 'tc_customer_addresses',
            [ 'id' => $address_id, 'customer_id' => $customer_id ]
        );
        return (bool) $result;
    }

    /**
     * Clear is_default for all addresses of a customer, then set one as default.
     *
     * @param  int $customer_id
     * @param  int $address_id
     * @return void
     */
    public function set_default_address( int $customer_id, int $address_id ): void {
        global $wpdb;
        $table = $wpdb->prefix . 'tc_customer_addresses';
        $wpdb->update( $table, [ 'is_default' => 0 ], [ 'customer_id' => $customer_id ] );
        $wpdb->update( $table, [ 'is_default' => 1 ], [ 'id' => $address_id, 'customer_id' => $customer_id ] );
    }
}
