<?php
/**
 * Data access for the customers table.
 *
 * @package Cleannetic
 */

namespace Cleannetic\Database;

use Cleannetic\Booking\BookingStatus;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery -- Internal table names; all values go through prepare().

class CustomerRepository {

    private const SORTABLE_COLUMNS = [
        'first_name'     => 'c.first_name',
        'email'          => 'c.email',
        'created_at'     => 'c.created_at',
        'total_bookings' => 'total_bookings',
        'total_spent'    => 'total_spent',
        'last_booking'   => 'last_booking',
    ];

    private const EDITABLE_COLUMNS = [ 'wp_user_id', 'first_name', 'last_name', 'email', 'phone', 'address', 'property_notes', 'gate_code' ];

    public function find( int $customer_id ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE c.id = %d GROUP BY c.id', $this->stats_values( [ $customer_id ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- select_sql() is a literal with %i table placeholders.

        return $row ? $this->hydrate( $row ) : null;
    }

    public function find_id_by_email( string $email ): int {
        global $wpdb;

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE email = %s ORDER BY id ASC LIMIT 1', Schema::customers_table(), $email ) );
    }

    /**
     * Creates a customer or refreshes contact details of an existing one (matched by email).
     */
    public function upsert( array $customer_data ): int {
        $customer_data = $this->only_editable( $customer_data );
        $customer_id   = ! empty( $customer_data['email'] ) ? $this->find_id_by_email( $customer_data['email'] ) : 0;

        if ( $customer_id ) {
            $this->update( $customer_id, array_filter( $customer_data, static fn( $value ) => '' !== $value && null !== $value ) );
            return $customer_id;
        }

        return $this->insert( $customer_data );
    }

    public function insert( array $customer_data ): int {
        global $wpdb;

        $now                         = current_time( 'mysql' );
        $customer_data               = $this->only_editable( $customer_data );
        $customer_data['created_at'] = $now;
        $customer_data['updated_at'] = $now;

        return $wpdb->insert( Schema::customers_table(), $customer_data ) ? (int) $wpdb->insert_id : 0;
    }

    public function update( int $customer_id, array $customer_data ): bool {
        global $wpdb;

        $customer_data               = $this->only_editable( $customer_data );
        $customer_data['updated_at'] = current_time( 'mysql' );

        return false !== $wpdb->update( Schema::customers_table(), $customer_data, [ 'id' => $customer_id ] );
    }

    /**
     * Deletes a customer together with their bookings and payments.
     */
    public function delete( int $customer_id ): bool {
        global $wpdb;

        $bookings_table = Schema::bookings_table();

        $wpdb->query( $wpdb->prepare( 'DELETE p FROM %i p INNER JOIN %i b ON b.id = p.booking_id WHERE b.customer_id = %d', Schema::payments_table(), $bookings_table, $customer_id ) );
        $wpdb->delete( $bookings_table, [ 'customer_id' => $customer_id ], [ '%d' ] );

        return (bool) $wpdb->delete( Schema::customers_table(), [ 'id' => $customer_id ], [ '%d' ] );
    }

    /**
     * @return array{items: array[], total: int}
     */
    public function query( array $args = [] ): array {
        global $wpdb;

        $args = wp_parse_args(
            $args,
            [
                'search'   => '',
                'orderby'  => 'created_at',
                'order'    => 'DESC',
                'per_page' => 20,
                'page'     => 1,
            ]
        );

        $where_sql    = '1=1';
        $where_values = [];
        if ( $args['search'] ) {
            $like         = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_sql    = "(c.first_name LIKE %s OR c.last_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR CONCAT(c.first_name, ' ', c.last_name) LIKE %s)";
            $where_values = [ $like, $like, $like, $like, $like ];
        }

        $orderby  = self::SORTABLE_COLUMNS[ $args['orderby'] ] ?? 'c.created_at';
        $order    = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
        $per_page = max( 1, (int) $args['per_page'] );
        $offset   = max( 0, ( (int) $args['page'] - 1 ) * $per_page );

        $count_sql    = "SELECT COUNT(*) FROM %i c WHERE {$where_sql}";
        $count_values = array_merge( [ Schema::customers_table() ], $where_values );
        $items_sql    = $this->select_sql() . " WHERE {$where_sql} GROUP BY c.id ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
        $items_values = $this->stats_values( array_merge( $where_values, [ $per_page, $offset ] ) );

        // WHERE is a literal placeholder fragment and ORDER BY uses whitelisted columns.
        $total_count = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $count_values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows        = $wpdb->get_results( $wpdb->prepare( $items_sql, $items_values ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return [
            'items' => array_map( [ $this, 'hydrate' ], $rows ?: [] ),
            'total' => $total_count,
        ];
    }

    /**
     * Lightweight list for admin pickers.
     */
    public function all_for_select( int $limit = 500 ): array {
        global $wpdb;

        return $wpdb->get_results( $wpdb->prepare( 'SELECT id, first_name, last_name, email, phone, address FROM %i ORDER BY first_name ASC LIMIT %d', Schema::customers_table(), $limit ), ARRAY_A ) ?: [];
    }

    public function count_since( string $from_datetime ): int {
        global $wpdb;

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s', Schema::customers_table(), $from_datetime ) );
    }

    public function count(): int {
        global $wpdb;

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', Schema::customers_table() ) );
    }

    private function select_sql(): string {
        return 'SELECT c.*,
                COUNT(b.id) AS total_bookings,
                COALESCE(SUM(CASE WHEN b.status NOT IN (%s, %s) THEN b.total_amount ELSE 0 END), 0) AS total_spent,
                MAX(b.start_datetime) AS last_booking
            FROM %i c
            LEFT JOIN %i b ON b.customer_id = c.id';
    }

    /**
     * Prepends the status placeholders and table names used inside select_sql().
     */
    private function stats_values( array $values ): array {
        return array_merge( [ BookingStatus::CANCELLED, BookingStatus::QUOTE_REQUESTED, Schema::customers_table(), Schema::bookings_table() ], $values );
    }

    private function only_editable( array $customer_data ): array {
        return array_intersect_key( $customer_data, array_flip( self::EDITABLE_COLUMNS ) );
    }

    private function hydrate( array $row ): array {
        $row['id']             = (int) $row['id'];
        $row['total_bookings'] = (int) ( $row['total_bookings'] ?? 0 );
        $row['total_spent']    = (float) ( $row['total_spent'] ?? 0 );
        $row['full_name']      = trim( $row['first_name'] . ' ' . $row['last_name'] );

        return $row;
    }
}