<?php
/**
 * DESCRIPTION: Booking repository for CRUD operations on the nemtly_bookings custom table.
 * Handles query building with filtering, pagination, search, and date range support.
 *
 * @package NemtlyBooking\Repositories
 */

namespace NemtlyBooking\Repositories;

use NemtlyBooking\Models\Booking;

class Booking_Repository {
    /**
     * Table name
     *
     * @var string
     */
    private $table_name;

    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = nemtly_booking_get_bookings_table();
    }

    /**
     * Create a new booking
     *
     * @param Booking $booking The booking to create.
     *
     * @return Booking|false
     */
    public function create( Booking $booking ) {
        global $wpdb;
        $result = $wpdb->insert(
            $this->table_name,
            [
                'event_id'       => $booking->get_event_id(),
                'user_id'        => $booking->get_user_id(),
                'booking_uuid'   => $booking->get_booking_uuid(),
                'booking_status' => $booking->get_booking_status(),
                'attendee_name'  => $booking->get_attendee_name(),
                'attendee_email' => $booking->get_attendee_email(),
                'attendee_phone' => $booking->get_attendee_phone(),
                'booking_date'   => $booking->get_booking_date(),
                'booking_start'  => $booking->get_booking_start(),
                'booking_end'    => $booking->get_booking_end(),
                'booking_meta'   => wp_json_encode( $booking->get_booking_meta() ),
                'created_at'     => $booking->get_created_at(),
                'updated_at'     => $booking->get_updated_at(),
            ],
            [
                '%d', // event_id
                '%d', // user_id
                '%s', // booking_uuid
                '%s', // booking_status
                '%s', // attendee_name
                '%s', // attendee_email
                '%s', // attendee_phone
                '%s', // booking_date
                '%s', // booking_start
                '%s', // booking_end
                '%s', // booking_meta
                '%s', // created_at
                '%s', // updated_at
            ]
        );
        if ( false === $result ) {
            return false;
        }
        return $this->find( $wpdb->insert_id );
    }

    /**
     * Find booking by ID
     *
     * @param int $id The booking ID to find.
     *
     * @return Booking|null
     */
    public function find( int $id ): ?Booking {
        global $wpdb;
        $result = $wpdb->get_row(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT * FROM {$this->table_name} WHERE id = %d",
                $id
            ),
            ARRAY_A
        );
        if ( ! $result ) {
            return null;
        }
        return $this->map_to_booking( $result );
    }

    /**
     * Find booking by UUID
     *
     * @param string $uuid The booking UUID to find.
     *
     * @return Booking|null
     */
    public function find_by_uuid( string $uuid ): ?Booking {
        global $wpdb;
        $result = $wpdb->get_row(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT * FROM {$this->table_name} WHERE booking_uuid = %s",
                $uuid
            ),
            ARRAY_A
        );
        if ( ! $result ) {
            return null;
        }
        return $this->map_to_booking( $result );
    }

    /**
     * Update a booking
     *
     * @param Booking $booking The booking to update.
     *
     * @return Booking|false
     */
    public function update( Booking $booking ) {
        global $wpdb;
        $booking->touch(); // Update timestamp
        $result = $wpdb->update(
            $this->table_name,
            [
                'event_id'       => $booking->get_event_id(),
                'user_id'        => $booking->get_user_id(),
                'booking_status' => $booking->get_booking_status(),
                'attendee_name'  => $booking->get_attendee_name(),
                'attendee_email' => $booking->get_attendee_email(),
                'attendee_phone' => $booking->get_attendee_phone(),
                'booking_date'   => $booking->get_booking_date(),
                'booking_start'  => $booking->get_booking_start(),
                'booking_end'    => $booking->get_booking_end(),
                'booking_meta'   => wp_json_encode( $booking->get_booking_meta() ),
                'updated_at'     => $booking->get_updated_at(),
            ],
            [
                'id' => $booking->get_id(),
            ],
            [
                '%d', // event_id
                '%d', // user_id
                '%s', // booking_status
                '%s', // attendee_name
                '%s', // attendee_email
                '%s', // attendee_phone
                '%s', // booking_date
                '%s', // booking_start
                '%s', // booking_end
                '%s', // booking_meta
                '%s', // updated_at
            ],
            [ '%d' ] // WHERE format
        );
        if ( false === $result ) {
            return false;
        }
        return $this->find( $booking->get_id() );
    }

    /**
     * Delete a booking
     *
     * @param int $id The booking ID to delete.
     *
     * @return bool
     */
    public function delete( int $id ): bool {
        global $wpdb;
        $result = $wpdb->delete(
            $this->table_name,
            [ 'id' => $id ],
            [ '%d' ]
        );
        return false !== $result && $result > 0;
    }

    /**
     * Find all bookings
     *
     * @param array $args Query arguments.
     *
     * @return Booking[]
     */
    public function find_all( array $args = [] ): array {
        global $wpdb;
        $defaults = [
            'per_page'       => 10,
            'page'           => 1,
            'orderby'        => 'created_at',
            'order'          => 'DESC',
            'status'         => '',
            'event_id'       => 0,
            'user_id'        => 0,
            'search'         => '',
            'attendee_email' => '',
            'date_from'      => '',
            'date_to'        => '',
        ];
        $args     = wp_parse_args( $args, $defaults );
        // Build WHERE clause
        $where_conditions = [];
        $where_values     = [];
        if ( ! empty( $args['status'] ) ) {
            $where_conditions[] = 'booking_status = %s';
            $where_values[]     = $args['status'];
        }
        if ( ! empty( $args['event_id'] ) ) {
            $where_conditions[] = 'event_id = %d';
            $where_values[]     = (int) $args['event_id'];
        }
        if ( ! empty( $args['user_id'] ) ) {
            $where_conditions[] = 'user_id = %d';
            $where_values[]     = (int) $args['user_id'];
        }
        // Search functionality
        if ( ! empty( $args['search'] ) ) {
            $search_term        = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_conditions[] = '(attendee_name LIKE %s OR attendee_email LIKE %s OR booking_uuid LIKE %s)';
            $where_values[]     = $search_term;
            $where_values[]     = $search_term;
            $where_values[]     = $search_term;
        }
        if ( ! empty( $args['attendee_email'] ) ) {
            $where_conditions[] = 'attendee_email = %s';
            $where_values[]     = $args['attendee_email'];
        }
        // Date range filtering
        if ( ! empty( $args['date_from'] ) ) {
            $where_conditions[] = 'booking_date >= %s';
            $where_values[]     = $args['date_from'];
        }
        if ( ! empty( $args['date_to'] ) ) {
            $where_conditions[] = 'booking_date <= %s';
            $where_values[]     = $args['date_to'];
        }
        // Build ORDER BY clause
        $allowed_orderby = [ 'id', 'created_at', 'updated_at', 'booking_date', 'attendee_name' ];
        $orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
        $order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
        // Build LIMIT clause
        $limit  = absint( $args['per_page'] );
        $offset = ( absint( $args['page'] ) - 1 ) * $limit;
        // Construct the base query with proper preparation
        $where_clause = ! empty( $where_conditions ) ? 'WHERE ' . implode( ' AND ', $where_conditions ) : '';
        // Prepare the query with values - table name and column names are controlled by plugin
        if ( ! empty( $where_values ) ) {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and column names are controlled by plugin
                    "SELECT id, event_id, user_id, booking_uuid, booking_status, attendee_name, attendee_email,
                            attendee_phone, booking_date, booking_start, booking_end, booking_meta, created_at, updated_at
                            FROM {$this->table_name} {$where_clause} " // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    . "ORDER BY {$orderby} {$order} " // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    . 'LIMIT %d OFFSET %d',
                    array_merge( $where_values, [ $limit, $offset ] )
                ),
                ARRAY_A
            );
        } else {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, event_id, user_id, booking_uuid, booking_status, attendee_name, attendee_email,
                            attendee_phone, booking_date, booking_start, booking_end, booking_meta, created_at, updated_at
                            FROM {$this->table_name} {$where_clause} " // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    . "ORDER BY {$orderby} {$order} " // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    . 'LIMIT %d OFFSET %d',
                    [ $limit, $offset ]
                ),
                ARRAY_A
            );
        }
        $bookings = [];
        foreach ( $results as $result ) {
            $bookings[] = $this->map_to_booking( $result );
        }
        return $bookings;
    }

    /**
     * Find bookings by event ID
     *
     * @param int $event_id Event ID.
     *
     * @return Booking[]
     */
    public function find_by_event_id( int $event_id ): array {
        global $wpdb;
        $query = $wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
            "SELECT * FROM {$this->table_name} WHERE event_id = %d ORDER BY booking_date ASC",
            $event_id
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared above
        $results  = $wpdb->get_results( $query, ARRAY_A );
        $bookings = [];
        foreach ( $results as $result ) {
            $bookings[] = $this->map_to_booking( $result );
        }
        return $bookings;
    }

    /**
     * Find bookings by user ID
     *
     * @param int $user_id User ID.
     *
     * @return Booking[]
     */
    public function find_by_user_id( int $user_id ): array {
        global $wpdb;
        $query = $wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
            "SELECT * FROM {$this->table_name} WHERE user_id = %d ORDER BY booking_date DESC",
            $user_id
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared above
        $results  = $wpdb->get_results( $query, ARRAY_A );
        $bookings = [];
        foreach ( $results as $result ) {
            $bookings[] = $this->map_to_booking( $result );
        }
        return $bookings;
    }

    /**
     * Find bookings by date range
     *
     * @param string $start_date Start date (Y-m-d).
     * @param string $end_date   End date (Y-m-d).
     *
     * @return Booking[]
     */
    public function find_by_date_range( string $start_date, string $end_date ): array {
        global $wpdb;
        $results = $wpdb->get_results(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT * FROM {$this->table_name} WHERE booking_date BETWEEN %s AND %s ORDER BY booking_date ASC",
                $start_date,
                $end_date
            ),
            ARRAY_A
        );
        $bookings = [];
        foreach ( $results as $result ) {
            $bookings[] = $this->map_to_booking( $result );
        }
        return $bookings;
    }

    /**
     * Count active bookings for an event, optionally filtered by date.
     *
     * @since PBOOKING_SINCE
     *
     * @param int         $event_id     Event ID.
     * @param string|null $booking_date Optional booking date (Y-m-d) to filter by.
     *
     * @return int
     */
    public function count_bookings_for_event( int $event_id, ?string $booking_date = null ): int {
        global $wpdb;

        if ( null !== $booking_date ) {
            return (int) $wpdb->get_var(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE event_id = %d AND booking_date = %s AND booking_status IN ('pending', 'confirmed')",
                    $event_id,
                    $booking_date
                )
            );
        }

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT COUNT(*) FROM {$this->table_name} WHERE event_id = %d AND booking_status IN ('pending', 'confirmed')",
                $event_id
            )
        );
    }

    /**
     * Count active bookings for a specific time slot.
     *
     * @since PBOOKING_SINCE
     *
     * @param int    $event_id      Event ID.
     * @param string $booking_date  Booking date (Y-m-d).
     * @param string $booking_start Slot start time (H:i).
     *
     * @return int
     */
    public function count_bookings_for_slot( int $event_id, string $booking_date, string $booking_start ): int {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT COUNT(*) FROM {$this->table_name} WHERE event_id = %d AND booking_date = %s AND TIME(booking_start) = %s AND booking_status IN ('pending', 'confirmed')",
                $event_id,
                $booking_date,
                $booking_start
            )
        );
    }

    /**
     * Find all active bookings for an event on a specific date.
     *
     * Used by Slot_Service to batch-check slot availability in a single query.
     *
     * @since PBOOKING_SINCE
     *
     * @param int    $event_id     Event ID.
     * @param string $booking_date Booking date (Y-m-d).
     *
     * @return array Array of Booking objects.
     */
    public function find_bookings_for_date( int $event_id, string $booking_date ): array {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin, not user input
                "SELECT * FROM {$this->table_name} WHERE event_id = %d AND booking_date = %s AND booking_status IN ('pending', 'confirmed') ORDER BY booking_start ASC",
                $event_id,
                $booking_date
            ),
            ARRAY_A
        );

        $bookings = [];
        foreach ( $results as $result ) {
            $bookings[] = $this->map_to_booking( $result );
        }

        return $bookings;
    }

    /**
     * Count total bookings
     *
     * @param array $args Query arguments for filtering.
     *
     * @return int
     */
    public function count( array $args = [] ): int {
        global $wpdb;
        $where_conditions = [];
        $where_values     = [];
        if ( ! empty( $args['status'] ) ) {
            $where_conditions[] = 'booking_status = %s';
            $where_values[]     = $args['status'];
        }
        if ( ! empty( $args['event_id'] ) ) {
            $where_conditions[] = 'event_id = %d';
            $where_values[]     = (int) $args['event_id'];
        }
        if ( ! empty( $args['user_id'] ) ) {
            $where_conditions[] = 'user_id = %d';
            $where_values[]     = (int) $args['user_id'];
        }
        // Search functionality
        if ( ! empty( $args['search'] ) ) {
            $search_term        = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_conditions[] = '(attendee_name LIKE %s OR attendee_email LIKE %s OR booking_uuid LIKE %s)';
            $where_values[]     = $search_term;
            $where_values[]     = $search_term;
            $where_values[]     = $search_term;
        }
        // Date range filtering
        if ( ! empty( $args['date_from'] ) ) {
            $where_conditions[] = 'booking_date >= %s';
            $where_values[]     = $args['date_from'];
        }
        if ( ! empty( $args['date_to'] ) ) {
            $where_conditions[] = 'booking_date <= %s';
            $where_values[]     = $args['date_to'];
        }
        $where_clause = ! empty( $where_conditions ) ? 'WHERE ' . implode( ' AND ', $where_conditions ) : '';
        // Prepare the query with values - table name is controlled by plugin
        if ( ! empty( $where_values ) ) {
            return (int) $wpdb->get_var(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin
                    "SELECT COUNT(*) FROM {$this->table_name} {$where_clause}",
                    $where_values
                )
            );
        } else {
            return (int) $wpdb->get_var(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled by plugin
                "SELECT COUNT(*) FROM {$this->table_name} {$where_clause}"
            );
        }
    }

    /**
     * Map database result to Booking object
     *
     * @param array $result Database result.
     *
     * @return Booking
     */
    private function map_to_booking( array $result ): Booking {
        $booking_meta = [];
        if ( ! empty( $result['booking_meta'] ) ) {
            $decoded_meta = json_decode( $result['booking_meta'], true );
            $booking_meta = is_array( $decoded_meta ) ? $decoded_meta : [];
        }
        return new Booking(
            [
                'id'             => (int) $result['id'],
                'event_id'       => (int) $result['event_id'],
                'user_id'        => $result['user_id'] ? (int) $result['user_id'] : null,
                'booking_uuid'   => $result['booking_uuid'],
                'booking_status' => $result['booking_status'],
                'attendee_name'  => $result['attendee_name'],
                'attendee_email' => $result['attendee_email'],
                'attendee_phone' => $result['attendee_phone'],
                'booking_date'   => $result['booking_date'],
                'booking_start'  => $result['booking_start'],
                'booking_end'    => $result['booking_end'],
                'booking_meta'   => $booking_meta,
                'created_at'     => $result['created_at'],
                'updated_at'     => $result['updated_at'],
            ]
        );
    }

    /**
     * Create booking table if it doesn't exist
     *
     * @return bool
     */
    public function create_table(): bool {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        $sql             = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            event_id BIGINT(20) UNSIGNED NOT NULL,
            user_id BIGINT(20) UNSIGNED NULL,
            booking_uuid CHAR(36) NOT NULL,
            booking_status VARCHAR(20) NOT NULL DEFAULT 'pending',
            attendee_name VARCHAR(255) NULL,
            attendee_email VARCHAR(255) NULL,
            attendee_phone VARCHAR(50) NULL,
            booking_date DATE NOT NULL,
            booking_start TIME NULL,
            booking_end TIME NULL,
            booking_meta LONGTEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY booking_uuid (booking_uuid),
            KEY event_id (event_id),
            KEY user_id (user_id),
            KEY booking_status (booking_status),
            KEY booking_date (booking_date)
        ) {$charset_collate};";
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $this->table_name ) ) === $this->table_name;
    }
}
