<?php
/**
 * Data access for the bookings 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 BookingRepository {

    private const JSON_COLUMNS = [ 'pricing_breakdown', 'address_data' ];

    private const SORTABLE_COLUMNS = [ 'start_datetime', 'created_at', 'total_amount', 'status', 'booking_ref' ];

    public function insert( array $booking_data ): int {
        global $wpdb;

        $now                        = current_time( 'mysql' );
        $booking_data['created_at'] = $booking_data['created_at'] ?? $now;
        $booking_data['updated_at'] = $now;

        $inserted = $wpdb->insert( Schema::bookings_table(), $this->encode( $booking_data ) );

        return $inserted ? (int) $wpdb->insert_id : 0;
    }

    public function update( int $booking_id, array $booking_data ): bool {
        global $wpdb;

        $booking_data['updated_at'] = current_time( 'mysql' );

        return false !== $wpdb->update( Schema::bookings_table(), $this->encode( $booking_data ), [ 'id' => $booking_id ] );
    }

    public function delete( int $booking_id ): bool {
        global $wpdb;

        $wpdb->delete( Schema::payments_table(), [ 'booking_id' => $booking_id ], [ '%d' ] );
        return (bool) $wpdb->delete( Schema::bookings_table(), [ 'id' => $booking_id ], [ '%d' ] );
    }

    public function find( int $booking_id ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE b.id = %d', $this->select_values( [ $booking_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_by_ref( string $booking_ref ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE b.booking_ref = %s LIMIT 1', $this->select_values( [ $booking_ref ] ) ), 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 ref_exists( string $booking_ref ): bool {
        global $wpdb;

        return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE booking_ref = %s', Schema::bookings_table(), $booking_ref ) );
    }

    /**
     * Paginated, filterable booking list.
     *
     * @param array $args status, search, date_from, date_to, customer_id, staff_id, orderby, order, per_page, page.
     * @return array{items: array[], total: int}
     */
    public function query( array $args = [] ): array {
        global $wpdb;

        $args = wp_parse_args(
            $args,
            [
                'status'      => '',
                'search'      => '',
                'date_from'   => '',
                'date_to'     => '',
                'customer_id' => 0,
                'staff_id'    => 0,
                'orderby'     => 'start_datetime',
                'order'       => 'DESC',
                'per_page'    => 20,
                'page'        => 1,
            ]
        );

        $where_clauses = [ '1=1' ];
        $where_values  = [];

        if ( $args['status'] ) {
            $where_clauses[] = 'b.status = %s';
            $where_values[]  = $args['status'];
        }
        if ( $args['customer_id'] ) {
            $where_clauses[] = 'b.customer_id = %d';
            $where_values[]  = (int) $args['customer_id'];
        }
        if ( $args['staff_id'] ) {
            $where_clauses[] = 'b.staff_id = %d';
            $where_values[]  = (int) $args['staff_id'];
        }
        if ( $args['date_from'] ) {
            $where_clauses[] = 'b.start_datetime >= %s';
            $where_values[]  = $args['date_from'] . ' 00:00:00';
        }
        if ( $args['date_to'] ) {
            $where_clauses[] = 'b.start_datetime <= %s';
            $where_values[]  = $args['date_to'] . ' 23:59:59';
        }
        if ( $args['search'] ) {
            $like            = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_clauses[] = "(b.booking_ref LIKE %s OR 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)";
            array_push( $where_values, $like, $like, $like, $like, $like, $like );
        }

        $orderby   = in_array( $args['orderby'], self::SORTABLE_COLUMNS, true ) ? $args['orderby'] : 'start_datetime';
        $order     = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
        $per_page  = max( 1, (int) $args['per_page'] );
        $offset    = max( 0, ( (int) $args['page'] - 1 ) * $per_page );
        $where_sql = implode( ' AND ', $where_clauses );

        $count_sql    = "SELECT COUNT(*) FROM %i b LEFT JOIN %i c ON c.id = b.customer_id WHERE {$where_sql}";
        $count_values = array_merge( [ Schema::bookings_table(), Schema::customers_table() ], $where_values );
        $items_sql    = $this->select_sql() . " WHERE {$where_sql} ORDER BY b.%i {$order} LIMIT %d OFFSET %d";
        $items_values = $this->select_values( array_merge( $where_values, [ $orderby, $per_page, $offset ] ) );

        // WHERE is built from literal placeholder fragments and the ORDER direction is whitelisted.
        $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,
        ];
    }

    /**
     * Bookings between two site-local datetimes, ordered by start time.
     */
    public function between( string $from_datetime, string $to_datetime, array $exclude_statuses = BookingStatus::NON_BLOCKING ): array {
        global $wpdb;

        $placeholders = implode( ',', array_fill( 0, count( $exclude_statuses ), '%s' ) );
        $sql          = $this->select_sql() . " WHERE b.start_datetime BETWEEN %s AND %s AND b.status NOT IN ({$placeholders}) ORDER BY b.start_datetime ASC";
        $rows         = $wpdb->get_results( $wpdb->prepare( $sql, $this->select_values( array_merge( [ $from_datetime, $to_datetime ], $exclude_statuses ) ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- select_sql() is a literal with %i table placeholders.

        return array_map( [ $this, 'hydrate' ], $rows ?: [] );
    }

    /**
     * Number of slot-occupying bookings overlapping a time window.
     */
    public function count_overlapping( string $window_start, string $window_end, int $exclude_booking_id = 0 ): int {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE start_datetime < %s AND end_datetime > %s AND id != %d AND status NOT IN (' . implode( ',', array_fill( 0, count( BookingStatus::NON_BLOCKING ), '%s' ) ) . ')',
                array_merge( [ Schema::bookings_table(), $window_end, $window_start, $exclude_booking_id ], BookingStatus::NON_BLOCKING )
            )
        );
    }

    /**
     * Occupying bookings for a whole day, used to compute slot capacity in one query.
     *
     * @return array<int, array{start: string, end: string}>
     */
    public function occupied_windows( string $day_start, string $day_end ): array {
        global $wpdb;

        return $wpdb->get_results(
            $wpdb->prepare(
                'SELECT start_datetime AS start, end_datetime AS end FROM %i WHERE start_datetime < %s AND end_datetime > %s AND status NOT IN (' . implode( ',', array_fill( 0, count( BookingStatus::NON_BLOCKING ), '%s' ) ) . ')',
                array_merge( [ Schema::bookings_table(), $day_end, $day_start ], BookingStatus::NON_BLOCKING )
            ),
            ARRAY_A
        ) ?: [];
    }

    /**
     * Find bookings overlapping with a given datetime window, taking buffer into account.
     * Excludes cancelled bookings.
     *
     * @param string $start_datetime Proposed start datetime (Y-m-d H:i:s).
     * @param string $end_datetime   Proposed end datetime (Y-m-d H:i:s).
     * @param int    $buffer_minutes Buffer time in minutes between appointments.
     * @param int    $exclude_id     Optional booking ID to exclude.
     * @return array<int, array{id: int, booking_ref: string, start_datetime: string, end_datetime: string, duration_minutes: int, status: string}>
     */
    public function find_conflicts( string $start_datetime, string $end_datetime, int $buffer_minutes = 30, int $exclude_id = 0 ): array {
        global $wpdb;

        $start_dt = new \DateTimeImmutable( $start_datetime, wp_timezone() );
        $end_dt   = new \DateTimeImmutable( $end_datetime, wp_timezone() );

        $query_start = $start_dt->modify( "-{$buffer_minutes} minutes" )->format( 'Y-m-d H:i:s' );
        $query_end   = $end_dt->modify( "+{$buffer_minutes} minutes" )->format( 'Y-m-d H:i:s' );

        $candidates = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, booking_ref, start_datetime, end_datetime, duration_minutes, status
                FROM %i
                WHERE status != %s
                  AND start_datetime < %s
                  AND end_datetime > %s
                  AND id != %d',
                Schema::bookings_table(),
                BookingStatus::CANCELLED,
                $query_end,
                $query_start,
                $exclude_id
            ),
            ARRAY_A
        ) ?: [];
        $conflicts  = [];

        $proposed_window_end = $end_dt->modify( "+{$buffer_minutes} minutes" );

        foreach ( $candidates as $candidate ) {
            $cand_start = new \DateTimeImmutable( $candidate['start_datetime'], wp_timezone() );
            $duration   = (int) ( $candidate['duration_minutes'] ?: 60 );
            $cand_end   = ( ! empty( $candidate['end_datetime'] ) && '0000-00-00 00:00:00' !== $candidate['end_datetime'] )
                ? new \DateTimeImmutable( $candidate['end_datetime'], wp_timezone() )
                : $cand_start->modify( "+{$duration} minutes" );

            $cand_window_end = $cand_end->modify( "+{$buffer_minutes} minutes" );

            if ( $start_dt < $cand_window_end && $cand_start < $proposed_window_end ) {
                $conflicts[] = $candidate;
            }
        }

        return $conflicts;
    }

    public function count_by_status(): array {
        global $wpdb;

        $rows   = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS total FROM %i GROUP BY status', Schema::bookings_table() ), ARRAY_A ) ?: [];
        $counts = array_fill_keys( array_keys( BookingStatus::labels() ), 0 );
        foreach ( $rows as $row ) {
            $counts[ $row['status'] ] = (int) $row['total'];
        }
        return $counts;
    }

    public function count_between( string $from_datetime, string $to_datetime ): int {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i WHERE start_datetime BETWEEN %s AND %s AND status NOT IN (' . implode( ',', array_fill( 0, count( BookingStatus::NON_BLOCKING ), '%s' ) ) . ')',
                array_merge( [ Schema::bookings_table(), $from_datetime, $to_datetime ], BookingStatus::NON_BLOCKING )
            )
        );
    }

    public function revenue_between( string $from_datetime, string $to_datetime ): float {
        global $wpdb;

        return (float) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COALESCE(SUM(total_amount), 0) FROM %i
                WHERE start_datetime BETWEEN %s AND %s
                  AND status != %s
                  AND ( payment_status = %s OR status = %s )',
                Schema::bookings_table(),
                $from_datetime,
                $to_datetime,
                BookingStatus::CANCELLED,
                BookingStatus::PAYMENT_PAID,
                BookingStatus::COMPLETED
            )
        );
    }

    public function scheduled_revenue_between( string $from_datetime, string $to_datetime ): float {
        global $wpdb;

        return (float) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COALESCE(SUM(total_amount), 0) FROM %i
                WHERE start_datetime BETWEEN %s AND %s
                  AND status != %s
                  AND payment_status != %s
                  AND status != %s',
                Schema::bookings_table(),
                $from_datetime,
                $to_datetime,
                BookingStatus::CANCELLED,
                BookingStatus::PAYMENT_PAID,
                BookingStatus::COMPLETED
            )
        );
    }

    /**
     * Booking counts per day (Y-m-d => count) for sparklines.
     */
    public function daily_counts( string $from_date, string $to_date ): array {
        global $wpdb;

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT DATE(start_datetime) AS booking_day, COUNT(*) AS total FROM %i WHERE start_datetime BETWEEN %s AND %s AND status != %s GROUP BY booking_day',
                Schema::bookings_table(),
                $from_date . ' 00:00:00',
                $to_date . ' 23:59:59',
                BookingStatus::CANCELLED
            ),
            ARRAY_A
        ) ?: [];

        $counts = [];
        foreach ( $rows as $row ) {
            $counts[ $row['booking_day'] ] = (int) $row['total'];
        }
        return $counts;
    }

    public function count_for_staff( int $staff_id ): int {
        global $wpdb;

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE staff_id = %d AND status != %s', Schema::bookings_table(), $staff_id, BookingStatus::CANCELLED ) );
    }

    public function unassign_staff( int $staff_id ): void {
        global $wpdb;

        $wpdb->query( $wpdb->prepare( 'UPDATE %i SET staff_id = NULL WHERE staff_id = %d', Schema::bookings_table(), $staff_id ) );
    }

    /**
     * Shared SELECT with table placeholders; pair it with select_values().
     */
    private function select_sql(): string {
        return 'SELECT b.*, c.first_name, c.last_name, c.email AS customer_email, c.phone AS customer_phone, s.title AS joined_service_name
            FROM %i b
            LEFT JOIN %i c ON c.id = b.customer_id
            LEFT JOIN %i s ON s.id = b.service_id';
    }

    /**
     * Prepends the table names used inside select_sql().
     */
    private function select_values( array $values ): array {
        return array_merge( [ Schema::bookings_table(), Schema::customers_table(), Schema::services_table() ], $values );
    }

    private function encode( array $booking_data ): array {
        foreach ( self::JSON_COLUMNS as $column ) {
            if ( isset( $booking_data[ $column ] ) && is_array( $booking_data[ $column ] ) ) {
                $booking_data[ $column ] = wp_json_encode( $booking_data[ $column ] );
            }
        }
        return $booking_data;
    }

    private function hydrate( array $row ): array {
        foreach ( self::JSON_COLUMNS as $column ) {
            $decoded        = json_decode( (string) ( $row[ $column ] ?? '' ), true );
            $row[ $column ] = is_array( $decoded ) ? $decoded : [];
        }

        $row['id']               = (int) $row['id'];
        $row['customer_id']      = (int) $row['customer_id'];
        $row['service_id']       = (int) $row['service_id'];
        $row['staff_id']         = $row['staff_id'] ? (int) $row['staff_id'] : 0;
        $row['duration_minutes'] = (int) $row['duration_minutes'];
        $row['total_amount']     = (float) $row['total_amount'];
        $row['deposit_paid']     = (float) $row['deposit_paid'];
        $row['customer_name']    = html_entity_decode( trim( ( $row['first_name'] ?? '' ) . ' ' . ( $row['last_name'] ?? '' ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );

        $saved_service_name = ! empty( $row['pricing_breakdown']['service_name'] )
            ? (string) $row['pricing_breakdown']['service_name']
            : (string) ( $row['joined_service_name'] ?: __( 'Service', 'cleannetic' ) );
        $saved_service_name = html_entity_decode( $saved_service_name, ENT_QUOTES | ENT_HTML5, 'UTF-8' );

        if ( ! empty( $row['service_id'] ) && empty( $row['joined_service_name'] ) ) {
            /* translators: %s: service name. */
            $row['service_name']    = sprintf( __( '%s (service deleted)', 'cleannetic' ), $saved_service_name );
            $row['service_deleted'] = true;
        } else {
            $row['service_name']    = $saved_service_name;
            $row['service_deleted'] = false;
        }
        unset( $row['joined_service_name'] );

        if ( ! empty( $row['pricing_breakdown']['line_items'] ) && is_array( $row['pricing_breakdown']['line_items'] ) ) {
            foreach ( $row['pricing_breakdown']['line_items'] as &$item ) {
                if ( isset( $item['name'] ) ) {
                    $item['name'] = html_entity_decode( (string) $item['name'], ENT_QUOTES | ENT_HTML5, 'UTF-8' );
                }
            }
            unset( $item );
        }
        if ( ! empty( $row['pricing_breakdown']['service_name'] ) ) {
            $row['pricing_breakdown']['service_name'] = $saved_service_name;
        }

        return $row;
    }
}