<?php

namespace HelpGent\App\Repositories;

defined( 'ABSPATH' ) || exit;

use Exception;
use HelpGent\App\DTO\AdminEntryReadDTO;
use HelpGent\App\DTO\AdminResponseReadDTO;
use HelpGent\App\DTO\ResponseDTO;
use HelpGent\App\DTO\UserDTO;
use HelpGent\App\DTO\UserResponseReadDTO;
use HelpGent\App\Models\Attachment;
use HelpGent\App\Models\ContactInfo;
use HelpGent\App\Models\Form;
use HelpGent\App\Models\ChatMessage;
use HelpGent\App\Models\Message;
use HelpGent\App\Models\HelpGentUser;
use HelpGent\App\Models\UserMeta;
use HelpGent\App\Models\Response;
use HelpGent\App\Models\ResponseAttachment;
use HelpGent\App\Models\ResponseMeta;
use HelpGent\App\Models\ResponseTag;
use HelpGent\App\Models\ElementAnswers;
use HelpGent\App\Models\Review;
use HelpGent\App\Utils\DateTime;
use HelpGent\App\Utils\Repository;
use HelpGent\App\Support\Element\Fields\LiveChat as LiveChatField;
use HelpGent\App\Repositories\ChatMessageRepository;
use HelpGent\WpMVC\Database\Query\Builder;
use stdClass;

class ResponseRepository {
    protected const USER_ACTIVITY_META_KEY = 'last_user_activity_at';

    protected const ONLINE_ACTIVITY_WINDOW = 5 * MINUTE_IN_SECONDS;

    protected const AWAY_ACTIVITY_WINDOW = 30 * MINUTE_IN_SECONDS;

    protected const ENTRY_EXPORT_META_HEADERS = [
        'id',
        'form_id',
        'status',
        'is_read',
        'is_completed',
        'completed_at',
        'is_starred',
        'ip',
        'device',
        'browser',
        'browser_version',
        'created_by',
        'created_at',
        'updated_at',
    ];

    public function get_entries( AdminEntryReadDTO $entry_read_dto ): array {
        $base_query = $this->entries_base_query( $entry_read_dto );
        $counts     = $this->get_entries_status_counts( clone $base_query );
        $query      = clone $base_query;

        $this->apply_entries_status_filter( $query, $entry_read_dto->get_status() );

        $total_query = clone $query;
        $entries     = $query
            ->select( $this->get_entries_select_columns() );

        $this->apply_entries_sorting( $entries, $entry_read_dto );

        return [
            'total'   => intval( $total_query->count( 'response.id' ) ),
            'counts'  => $counts,
            'entries' => array_map(
                [ $this, 'prepare_entry' ],
                $entries->pagination( $entry_read_dto->get_page(), $entry_read_dto->get_per_page() )
            ),
        ];
    }

    public function export_entries( AdminEntryReadDTO $entry_read_dto ): array {
        $query = $this->entries_base_query( $entry_read_dto )
            ->select( $this->get_entries_select_columns() );

        $this->apply_entries_status_filter( $query, $entry_read_dto->get_status() );
        $this->apply_entries_sorting( $query, $entry_read_dto );

        return array_map( [ $this, 'prepare_entry' ], $query->get() );
    }

    public function export_entry_forms( AdminEntryReadDTO $entry_read_dto ): array {
        $query = $this->entries_base_query( $entry_read_dto )
            ->select( $this->get_entry_export_select_columns() );

        $this->apply_entries_status_filter( $query, $entry_read_dto->get_status() );
        $this->apply_entries_sorting( $query, $entry_read_dto );

        /**
         * @var AnswerRepository $answer_repository
         */
        $answer_repository = helpgent_singleton( AnswerRepository::class );
        $forms             = [];

        $entries       = $query->get();
        $contact_infos = $this->get_entry_export_contact_infos( $entries );

        foreach ( $entries as $entry ) {
            $form_id = intval( $entry->form_id );

            if ( ! isset( $forms[ $form_id ] ) ) {
                $forms[ $form_id ] = [
                    'form_id'   => $form_id,
                    'form_name' => ! empty( $entry->form_name ) ? $entry->form_name : sprintf( 'Form %d', $form_id ),
                    'headers'   => self::ENTRY_EXPORT_META_HEADERS,
                    'columns'   => [],
                    'rows'      => [],
                ];
            }

            $row          = $this->get_entry_export_base_row( $entry );
            $contact_info = $contact_infos[ intval( $entry->id ) ] ?? null;

            if ( $contact_info ) {
                foreach ( $this->get_contact_info_export_values( $contact_info ) as $field ) {
                    $this->add_entry_export_field(
                        $forms[ $form_id ],
                        $row,
                        'lead_contact:' . $field['key'],
                        $field
                    );
                }
            }

            try {
                $answers = $answer_repository->get( intval( $entry->id ) );
            } catch ( Exception $exception ) {
                $answers = [];
            }

            foreach ( $answers as $answer ) {
                foreach ( $this->get_answer_export_values( $answer ) as $field ) {
                    $this->add_entry_export_field(
                        $forms[ $form_id ],
                        $row,
                        (string) $answer->element_id . ':' . $field['key'],
                        $field
                    );
                }
            }

            $forms[ $form_id ]['rows'][] = $row;
        }

        return array_values(
            array_map(
                function( array $form ) {
                    unset( $form['columns'] );

                    $form['rows'] = array_map(
                        function( array $row ) use ( $form ) {
                            return array_reduce(
                                $form['headers'],
                                function( array $normalized_row, string $header ) use ( $row ) {
                                    $normalized_row[ $header ] = $row[ $header ] ?? '';
                                    return $normalized_row;
                                },
                                []
                            );
                        },
                        $form['rows']
                    );

                    return $form;
                },
                $forms
            )
        );
    }

    public function get_new_entries_count(): int {
        return intval(
            Response::query()
                ->where( 'is_archive', 0 )
                ->where( 'is_read', 0 )
                ->count()
        );
    }

    protected function entries_base_query( AdminEntryReadDTO $entry_read_dto ): Builder {
        $query = Response::query( 'response' )
            ->left_join( Form::get_table_name() . ' as form', 'response.form_id', '=', 'form.id' )
            ->left_join(
                HelpGentUser::get_table_name() . ' as user', function( $join ) {
                    $join->on_column( 'response.created_by', 'user.id' );
                }
            )
            ->where( 'response.is_archive', 0 );

        if ( $entry_read_dto->get_form_id() > 0 ) {
            $query->where( 'response.form_id', $entry_read_dto->get_form_id() );
        }

        $this->entries_search_query( $entry_read_dto, $query );
        $this->entries_date_query( $entry_read_dto, $query );

        return $query;
    }

    protected function get_entries_select_columns(): array {
        return [
            'response.id',
            'response.form_id',
            'response.is_read',
            'response.is_starred',
            'response.is_completed',
            'response.created_by',
            'response.submitted_at',
            'response.created_at',
            'response.updated_at',
            'form.title as form_name',
            'user.id as user_id',
            'user.user_type as user_type',
            'user.name as user_name',
            'user.email as user_email',
            'COALESCE(response.submitted_at, response.created_at) as submitted_on',
        ];
    }

    protected function get_entry_export_select_columns(): array {
        return [
            'response.id',
            'response.form_id',
            'response.is_read',
            'response.is_starred',
            'response.is_completed',
            'response.is_archive',
            'response.ip',
            'response.created_by',
            'response.submitted_at',
            'response.created_at',
            'response.updated_at',
            'form.title as form_name',
            'COALESCE(response.submitted_at, response.created_at) as submitted_on',
        ];
    }

    protected function get_entries_status_counts( Builder $query ): array {
        $result = $query
            ->select(
                [
                    'COUNT(response.id) as total',
                    'SUM(CASE WHEN response.is_read = 1 THEN 1 ELSE 0 END) as read_total',
                    'SUM(CASE WHEN response.is_read = 0 THEN 1 ELSE 0 END) as unread_total',
                    'SUM(CASE WHEN response.is_starred = 1 THEN 1 ELSE 0 END) as starred_total',
                ]
            )
            ->first();

        return [
            'all'     => intval( $result->total ?? 0 ),
            'read'    => intval( $result->read_total ?? 0 ),
            'unread'  => intval( $result->unread_total ?? 0 ),
            'starred' => intval( $result->starred_total ?? 0 ),
        ];
    }

    protected function entries_search_query( AdminEntryReadDTO $entry_read_dto, Builder $query ): Builder {
        global $wpdb;

        $search = trim( $entry_read_dto->get_search() );

        if ( empty( $search ) ) {
            return $query;
        }

        $like       = '%' . $wpdb->esc_like( $search ) . '%';
        $conditions = [
            $wpdb->prepare( '`user`.`name` LIKE %s', $like ),
            $wpdb->prepare( '`user`.`email` LIKE %s', $like ),
            $wpdb->prepare( '`form`.`title` LIKE %s', $like ),
        ];

        if ( preg_match( '/^(?:entry#?)?\s*(\d+)$/i', $search, $matches ) ) {
            $conditions[] = $wpdb->prepare( '`response`.`id` = %d', intval( $matches[1] ) );
        }

        return $query->where_raw( '(' . implode( ' OR ', $conditions ) . ')' );
    }

    protected function entries_date_query( AdminEntryReadDTO $entry_read_dto, Builder $query ): Builder {
        $date_type = $entry_read_dto->get_date_type();

        if ( 'all' === $date_type ) {
            return $query;
        }

        $now              = helpgent_now();
        $from_date_format = 'Y-m-d 00:00:01';
        $to_date_format   = 'Y-m-d 23:59:59';
        $date_column      = 'COALESCE(response.submitted_at, response.created_at)';

        if ( 'today' === $date_type ) {
            $from = $now->format( $from_date_format );
            $to   = helpgent_now()->format( $to_date_format );
        } elseif ( 'date_frame' === $date_type ) {
            $date_frame = $entry_read_dto->get_date_frame();

            if (
                empty( $date_frame['from'] ) ||
                ! is_string( $date_frame['from'] ) ||
                empty( $date_frame['to'] ) ||
                ! is_string( $date_frame['to'] ) ||
                ! helpgent_is_valid_date( $date_frame['from'], $from_date_format ) ||
                ! helpgent_is_valid_date( $date_frame['to'], $to_date_format )
            ) {
                return $query;
            }

            $from = sanitize_text_field( $date_frame['from'] );
            $to   = sanitize_text_field( $date_frame['to'] );
        } else {
            switch ( $date_type ) {
                case 'yesterday':
                    $date = $now->sub_days( 1 );
                    break;
                case 'last_week':
                    $date = $now->sub_days( 6 );
                    break;
                case 'last_month':
                    $date = $now->sub_days( 30 );
                    break;
                default:
                    return $query;
            }

            $from = $date->format( $from_date_format );
            $to   = helpgent_now()->format( $to_date_format );
        }

        return $query->where_raw( "({$date_column} >= '{$from}' AND {$date_column} <= '{$to}')" );
    }

    protected function apply_entries_status_filter( Builder $query, string $status ): Builder {
        switch ( $status ) {
            case 'read':
                return $query->where( 'response.is_read', 1 );
            case 'unread':
                return $query->where( 'response.is_read', 0 );
            case 'starred':
                return $query->where( 'response.is_starred', 1 );
            case 'all':
            default:
                return $query;
        }
    }

    protected function apply_entries_sorting( Builder $query, AdminEntryReadDTO $entry_read_dto ): Builder {
        $sort_order = $entry_read_dto->get_sort_order();

        switch ( $entry_read_dto->get_sort_by() ) {
            case 'form_name':
                $column = 'form.title';
                break;
            case 'status':
                $column = 'response.is_read';
                break;
            case 'submission':
                $column = 'response.is_completed';
                break;
            case 'submitted_on':
            default:
                $column = 'submitted_on';
                break;
        }

        if ( 'asc' === $sort_order ) {
            return $query->order_by( $column );
        }

        return $query->order_by_desc( $column );
    }

    public function prepare_entry( $entry ): array {
        $name  = $entry->user_name ?? '';
        $email = $entry->user_email ?? '';

        return [
            'id'            => intval( $entry->id ),
            'submission_id' => sprintf( 'Entry#%d', intval( $entry->id ) ),
            'form_id'       => intval( $entry->form_id ),
            'form_name'     => $entry->form_name ?? '',
            'submitted_by'  => [
                'type'  => empty( $name ) && empty( $email ) ? 'anonymous' : 'known',
                'name'  => $name,
                'email' => $email,
            ],
            'submitted_on'  => $entry->submitted_on ?? $entry->created_at ?? '',
            'is_read'       => intval( $entry->is_read ),
            'is_starred'    => intval( $entry->is_starred ),
            'is_completed'  => intval( $entry->is_completed ),
        ];
    }

    protected function get_entry_export_base_row( $entry ): array {
        return [
            'id'              => intval( $entry->id ),
            'form_id'         => intval( $entry->form_id ),
            'status'          => empty( $entry->is_archive ) ? 'publish' : 'trash',
            'is_read'         => intval( $entry->is_read ),
            'is_completed'    => intval( $entry->is_completed ),
            'completed_at'    => intval( $entry->is_completed ) ? ( $entry->submitted_at ?? '' ) : '',
            'is_starred'      => intval( $entry->is_starred ),
            'ip'              => $entry->ip ?? '',
            'device'          => '',
            'browser'         => '',
            'browser_version' => '',
            'created_by'      => $entry->created_by ?? '',
            'created_at'      => $entry->created_at ?? '',
            'updated_at'      => $entry->updated_at ?? '',
        ];
    }

    protected function get_entry_export_contact_infos( array $entries ): array {
        /**
         * @var ContactInfoRepository $contact_info_repository
         */
        $contact_info_repository = helpgent_singleton( ContactInfoRepository::class );

        return $contact_info_repository->get_response_contact_infos(
            array_map(
                function( $entry ) {
                    return intval( $entry->id ?? 0 );
                },
                $entries
            )
        );
    }

    protected function get_contact_info_export_values( $contact_info ): array {
        $name = trim( (string) ( $contact_info->name ?? '' ) );

        if ( '' === $name ) {
            $name = trim(
                implode(
                    ' ',
                    array_filter(
                        [
                            $contact_info->first_name ?? '',
                            $contact_info->last_name ?? '',
                        ]
                    )
                )
            );
        }

        $fields = [
            [
                'key'   => 'name',
                'label' => esc_html__( 'Contact Name', 'helpgent' ),
                'value' => $this->format_entry_export_value( $name ),
            ],
            [
                'key'   => 'email',
                'label' => esc_html__( 'Contact Email', 'helpgent' ),
                'value' => $this->format_entry_export_value( $contact_info->email ?? '' ),
            ],
            [
                'key'   => 'mobile_number',
                'label' => esc_html__( 'Contact Mobile Number', 'helpgent' ),
                'value' => $this->format_entry_export_value( $contact_info->mobile_number ?? '' ),
            ],
            [
                'key'   => 'company',
                'label' => esc_html__( 'Contact Company', 'helpgent' ),
                'value' => $this->format_entry_export_value( $contact_info->company ?? '' ),
            ],
        ];

        foreach ( $fields as $field ) {
            if ( '' !== trim( (string) $field['value'] ) ) {
                return $fields;
            }
        }

        return [];
    }

    protected function add_entry_export_field( array &$form, array &$row, string $field_key, array $field ): void {
        if ( ! isset( $form['columns'][ $field_key ] ) ) {
            $header = $this->get_unique_entry_export_header(
                $field['label'],
                $form['headers']
            );

            $form['columns'][ $field_key ] = $header;
            $form['headers'][]             = $header;
        }

        $row[ $form['columns'][ $field_key ] ] = $field['value'];
    }

    protected function get_answer_export_values( $answer ): array {
        $element_title = $this->normalize_entry_export_label(
            $answer->element_title ?? $answer->element_id ?? esc_html__( 'Answer', 'helpgent' )
        );

        if ( ! empty( $answer->is_unanswered ) ) {
            return [
                [
                    'key'   => 'value',
                    'label' => $element_title,
                    'value' => esc_html__( 'Unanswered', 'helpgent' ),
                ],
            ];
        }

        switch ( (string) ( $answer->element_type ?? '' ) ) {
            case 'address':
            case 'contact_info':
                return $this->get_structured_answer_export_values(
                    $answer->address ?? $answer->contact_info ?? null,
                    $answer->labels ?? [],
                    $element_title
                );
            case 'open_ended_text':
                return $this->get_structured_answer_export_values(
                    $answer->open_ended_text_inners ?? null,
                    $answer->labels ?? [],
                    $element_title
                );
            case 'file_upload':
                return [
                    [
                        'key'   => 'value',
                        'label' => $element_title,
                        'value' => $this->format_entry_export_files( $answer->attachments ?? [] ),
                    ],
                ];
            case 'single_select':
            case 'dropdown':
            case 'yes_no':
            case 'multi_select':
            case 'picture_select':
                return [
                    [
                        'key'   => 'value',
                        'label' => $element_title,
                        'value' => $this->format_entry_export_choice_value( $answer ),
                    ],
                ];
            default:
                $value = $answer->value ?? '';

                if ( ! empty( $answer->attachment ) ) {
                    $value = $this->format_entry_export_file( $answer->attachment );
                }

                return [
                    [
                        'key'   => 'value',
                        'label' => $element_title,
                        'value' => $this->format_entry_export_value( $value ),
                    ],
                ];
        }
    }

    protected function get_structured_answer_export_values( $answer_data, array $labels, string $fallback_label ): array {
        $data   = $this->entry_export_to_array( $answer_data );
        $fields = [];

        foreach ( $labels as $key => $label ) {
            if ( ! array_key_exists( $key, $data ) ) {
                continue;
            }

            $attachment = is_object( $answer_data ) && ! empty( $answer_data->attachment )
                ? $answer_data->attachment
                : null;

            $fields[] = [
                'key'   => $key,
                'label' => $this->normalize_entry_export_label( $label ?: $key ),
                'value' => 'file' === $key && $attachment
                    ? $this->format_entry_export_file( $attachment )
                    : $this->format_entry_export_value( $data[ $key ] ),
            ];
        }

        if ( ! empty( $fields ) ) {
            return $fields;
        }

        foreach ( $data as $key => $value ) {
            if ( in_array( $key, [ 'id', 'answer_id', 'form_id', 'response_id', 'form_data', 'created_at', 'updated_at', 'attachment' ], true ) ) {
                continue;
            }

            $attachment = is_object( $answer_data ) && ! empty( $answer_data->attachment )
                ? $answer_data->attachment
                : null;

            $fields[] = [
                'key'   => $key,
                'label' => $this->normalize_entry_export_label( $key ?: $fallback_label ),
                'value' => 'file' === $key && $attachment
                    ? $this->format_entry_export_file( $attachment )
                    : $this->format_entry_export_value( $value ),
            ];
        }

        if ( is_object( $answer_data ) && ! empty( $answer_data->attachment ) ) {
            $fields[] = [
                'key'   => 'attachment',
                'label' => $this->normalize_entry_export_label( $fallback_label . ' Attachment' ),
                'value' => $this->format_entry_export_file( $answer_data->attachment ),
            ];
        }

        return ! empty( $fields )
            ? $fields
            : [
                [
                    'key'   => 'value',
                    'label' => $fallback_label,
                    'value' => '',
                ],
            ];
    }

    protected function format_entry_export_choice_value( $answer ): string {
        $selected_values = $this->entry_export_normalize_list( $answer->value ?? '' );
        $options         = is_array( $answer->options ?? null ) ? $answer->options : [];
        $labels          = [];

        foreach ( $options as $option ) {
            $option = $this->entry_export_to_array( $option );

            $candidates = array_filter(
                [
                    $option['id'] ?? null,
                    $option['value'] ?? null,
                    $option['label'] ?? null,
                ],
                function( $value ) {
                    return null !== $value && '' !== $value;
                }
            );

            foreach ( $candidates as $candidate ) {
                if ( in_array( (string) $candidate, $selected_values, true ) ) {
                    $labels[] = $option['label'] ?? $option['value'] ?? $option['id'] ?? $candidate;
                    break;
                }
            }
        }

        return ! empty( $labels )
            ? implode( ', ', array_map( [ $this, 'format_entry_export_value' ], $labels ) )
            : $this->format_entry_export_value( $answer->value ?? '' );
    }

    protected function entry_export_normalize_list( $value ): array {
        if ( is_string( $value ) ) {
            $decoded = json_decode( $value, true );

            if ( JSON_ERROR_NONE === json_last_error() ) {
                $value = $decoded;
            }
        }

        if ( ! is_array( $value ) ) {
            $value = [ $value ];
        }

        return array_values(
            array_filter(
                array_map(
                    function( $item ) {
                        return is_scalar( $item ) ? (string) $item : wp_json_encode( $item );
                    },
                    $value
                ),
                function( $item ) {
                    return null !== $item && '' !== $item;
                }
            )
        );
    }

    protected function format_entry_export_files( array $files ): string {
        return implode(
            ' | ',
            array_filter(
                array_map(
                    [ $this, 'format_entry_export_file' ],
                    $files
                )
            )
        );
    }

    protected function format_entry_export_file( $file ): string {
        $file = $this->entry_export_to_array( $file );

        if ( empty( $file ) ) {
            return '';
        }

        $title = $file['title'] ?? $file['file_name'] ?? '';
        $url   = $file['url'] ?? '';

        if ( ! empty( $title ) && ! empty( $url ) ) {
            return sprintf( '%s (%s)', $title, $url );
        }

        return $title ?: $url;
    }

    protected function format_entry_export_value( $value ): string {
        if ( null === $value ) {
            return '';
        }

        if ( is_bool( $value ) ) {
            return $value ? '1' : '0';
        }

        if ( is_scalar( $value ) ) {
            $value = html_entity_decode( wp_strip_all_tags( (string) $value ), ENT_QUOTES, get_bloginfo( 'charset' ) );
            return trim( $value );
        }

        if ( is_array( $value ) ) {
            $flat_values = [];

            foreach ( $value as $item ) {
                $formatted_item = $this->format_entry_export_value( $item );

                if ( '' !== $formatted_item ) {
                    $flat_values[] = $formatted_item;
                }
            }

            return implode( ', ', $flat_values );
        }

        return wp_json_encode( $value );
    }

    protected function entry_export_to_array( $value ): array {
        if ( empty( $value ) ) {
            return [];
        }

        if ( is_array( $value ) ) {
            return $value;
        }

        if ( is_object( $value ) ) {
            return get_object_vars( $value );
        }

        if ( is_string( $value ) ) {
            $decoded = json_decode( $value, true );

            if ( JSON_ERROR_NONE === json_last_error() && is_array( $decoded ) ) {
                return $decoded;
            }
        }

        return [];
    }

    protected function normalize_entry_export_label( $label ): string {
        $label = trim( html_entity_decode( wp_strip_all_tags( (string) $label ), ENT_QUOTES, get_bloginfo( 'charset' ) ) );

        return '' !== $label ? $label : esc_html__( 'Answer', 'helpgent' );
    }

    protected function get_unique_entry_export_header( string $header, array $existing_headers ): string {
        $header          = $this->normalize_entry_export_label( $header );
        $candidate       = $header;
        $candidate_index = 2;

        while ( in_array( $candidate, $existing_headers, true ) ) {
            $candidate = sprintf( '%s (%d)', $header, $candidate_index );
            $candidate_index++;
        }

        return $candidate;
    }

    public function get( AdminResponseReadDTO $response_read_dto ) {
        $query = Response::query( 'response' )->left_join(
            HelpGentUser::get_table_name() . ' as user', function( $join ) {
                $join->on_column( 'response.created_by', 'user.id' );
            }
        )->where( 'response.form_id', $response_read_dto->get_form_id() )->where( 'response.is_archive', $response_read_dto->get_is_archive() );

        if ( $response_read_dto->is_initialized( "is_completed" ) ) {
            $query->where( 'response.is_completed', $response_read_dto->get_is_completed() );
        }

        if ( $response_read_dto->get_response_id() > 0 ) {
            $query->where( 'response.id', $response_read_dto->get_response_id() );
        }

        $tag_ids = $response_read_dto->get_tag_ids();

        // If find responses of certain tags
        if ( ! empty( $tag_ids ) ) {
            $tag_ids = map_deep( $tag_ids, 'intval' );
            $query->join(
                ResponseTag::get_table_name() . ' as response_tag', function( $join ) use( $tag_ids ) {
                    $join->on_column( 'response.id', 'response_tag.response_id' )->on_in( "response_tag.tag_id", $tag_ids );
                }
            );
        }

        $this->search_query( $response_read_dto, $query );

        $select = [
            'response.*',
            'user.id as user_id',
            'user.user_type as user_type',
            'user.name as user_name',
            'user.email as user_email'
        ];

        switch ( $response_read_dto->get_order_by() ) {
            case 'latest':
                $query->order_by_desc( 'response.updated_at' );
                break;
            case 'oldest':
                $query->order_by( 'response.updated_at' );
                break;
            case 'read':
                $this->bind_response_is_read_count_join_query( $query );

                $select[] = $this->get_response_is_read_count_query( 'unread', 'total_unread', true );

                $query->order_by( 'total_unread' );
                break;
            case 'unread':
                $this->bind_response_is_read_count_join_query( $query );

                $select[] = $this->get_response_is_read_count_query( 'unread', 'total_unread', true );

                $query->order_by_desc( 'total_unread' );
                break;
        }

        $form_date = $response_read_dto->get_from_date();

        if ( $form_date instanceof DateTime ) {
            $query->where( 'response.created_at', '>=', $form_date );
        }

        $to_date = $response_read_dto->get_to_date();

        if ( $to_date instanceof DateTime ) {
            $query->where( 'response.created_at', '<=', $to_date );
        }

        $query
            ->with(
                [
                    'tags',
                    'chat_message' => function ( Builder $query ) {
                        $query->select( 'helpgent_chat_messages.*', 'user.name as user_name', 'user.email as user_email' )->left_join(
                            HelpGentUser::get_table_name() . ' as user', function( $join ) {
                                $join->on_column( 'helpgent_chat_messages.created_by', 'user.id' );
                            }
                        )->order_by_desc( 'helpgent_chat_messages.id' );
                    },
                    'message'      => function ( Builder $query ) {
                        $query->select( 'helpgent_messages.*', 'user.name as user_name', 'user.email as user_email' )->left_join(
                            HelpGentUser::get_table_name() . ' as user', function( $join ) {
                                $join->on_column( 'helpgent_messages.created_by', 'user.id' );
                            }
                        )->order_by_desc( 'helpgent_messages.id' );
                    },
                    'message.forward',
                ]
            )
            ->select( $select )
            ->group_by( 'response.id' );

        return $this->prepare_response( $query->pagination( $response_read_dto->get_page(), $response_read_dto->get_per_page() ) );
    }

    public function user_get( UserResponseReadDTO $response_read_dto ) {
        $select = [
            'response.id',
            'response.form_id',
            'response.is_read',
            'response.created_by',
            'response.is_archive',
            'response.is_completed',
            'response.is_verified',
            'response.is_completed',
            'response.updated_at',
            'user.id as user_id',
            'user.user_type as user_type',
            'user.name as user_name',
            'user.email as user_email',
            'form.content as form_content',
        ];

        $query = Response::query( 'response' )
            ->select( $select )
            ->left_join(
                HelpGentUser::get_table_name() . ' as user', function( $join ) {
                    $join->on_column( 'response.created_by', 'user.id' );
                }
            )
            ->where( 'response.is_archive', 0 )
            ->where( 'response.created_by', $response_read_dto->get_created_by() );

        if ( $response_read_dto->is_initialized( 'is_completed' ) ) {
            $query->where( 'response.is_completed', $response_read_dto->get_is_completed() );
        }

        $query->left_join( 'helpgent_forms as form', 'response.form_id', '=', 'form.id' );

        $this->search_query( $response_read_dto, $query );

        switch ( $response_read_dto->get_order_by() ) {
            case 'latest':
                $query->order_by_desc( 'response.updated_at' );
                break;
            case 'oldest':
                $query->order_by( 'response.updated_at' );
                break;
            case 'read':
                $this->bind_response_is_read_count_join_query( $query );

                $select[] = $this->get_response_is_read_count_query( 'unread', 'total_unread', false );

                $query->order_by( 'total_unread' );
                break;
            case 'unread':
                $this->bind_response_is_read_count_join_query( $query );

                $select[] = $this->get_response_is_read_count_query( 'unread', 'total_unread', false );

                $query->order_by_desc( 'total_unread' );
                break;
        }

        $query
            ->with(
                [
                    'chat_message' => function ( Builder $query ) {
                        $query->select( 'helpgent_chat_messages.*', 'user.name as user_name', 'user.email as user_email' )->left_join(
                            HelpGentUser::get_table_name() . ' as user', function( $join ) {
                                $join->on_column( 'helpgent_chat_messages.created_by', 'user.id' );
                            }
                        )->order_by_desc( 'helpgent_chat_messages.id' );
                    },
                    'message'      => function ( Builder $query ) {
                        $query->select( 'helpgent_messages.*', 'user.name as user_name', 'user.email as user_email' )->left_join(
                            HelpGentUser::get_table_name() . ' as user', function( $join ) {
                                $join->on_column( 'helpgent_messages.created_by', 'user.id' );
                            }
                        )->order_by_desc( 'helpgent_messages.id' );
                    },
                    'message.forward'
                ]
            )
            ->select( $select )
            ->group_by( 'response.id' );

        return $this->prepare_response( $query->pagination( $response_read_dto->get_page(), $response_read_dto->get_per_page() ) );
    }

    public function bind_response_is_read_count_join_query( Builder $query ): void {
        $query
            ->left_join( 'helpgent_messages as message', 'message.response_id', 'response.id' )
            ->left_join( 'helpgent_chat_messages as chat_message', 'chat_message.response_id', 'response.id' )
            ->left_join( 'helpgent_users as message_user', 'message_user.id', 'message.created_by' )
            ->left_join(
                'usermeta as message_user_meta', function( $join ) {
                    global $wpdb;

                    $join
                    ->on_column( 'message_user.registered_id', '=', 'message_user_meta.user_id' )
                    ->on_column( 'message_user_meta.meta_key', '=', "'{$wpdb->prefix}capabilities'" );
                }
            )
            ->left_join( 'helpgent_users as chat_message_user', 'chat_message_user.id', 'chat_message.created_by' )
            ->left_join(
                'usermeta as chat_message_user_meta', function( $join ) {
                    global $wpdb;

                    $join
                    ->on_column( 'chat_message_user.registered_id', '=', 'chat_message_user_meta.user_id' )
                    ->on_column( 'chat_message_user_meta.meta_key', '=', "'{$wpdb->prefix}capabilities'" );
                }
            );
    }

    public function get_response_is_read_count_query( string $counter_type, string $label, bool $is_current_user_admin ): string {
        $is_read_value = $counter_type === 'read' ? '1' : '0';

        if ( $is_current_user_admin ) {
            return "SUM(
                CASE WHEN
                    (
                        message.is_read = {$is_read_value} AND
                        (
                            message.created_by = 0
                            OR message_user.registered_id IS NULL
                            OR ( message_user_meta.meta_value NOT LIKE '%administrator%' AND message_user_meta.meta_value NOT LIKE '%helpgent_agent%' )
                        )
                    ) OR
                    (
                        chat_message.is_read = {$is_read_value} AND
                        (
                            chat_message.created_by = 0
                            OR chat_message_user.registered_id IS NULL
                            OR ( chat_message_user_meta.meta_value NOT LIKE '%administrator%' AND chat_message_user_meta.meta_value NOT LIKE '%helpgent_agent%' )
                        )
                    )
                THEN 1 ELSE 0 END
            ) AS {$label}";
        }

        return "SUM(
            CASE WHEN
                (
                    message.is_read = {$is_read_value} AND
                    (
                        message_user_meta.meta_value LIKE '%administrator%' OR message_user_meta.meta_value LIKE '%helpgent_agent%'
                    )
                ) OR
                (
                    chat_message.is_read = {$is_read_value} AND
                    (
                        message_user_meta.meta_value LIKE '%administrator%' OR message_user_meta.meta_value LIKE '%helpgent_agent%'
                    )
                )
            THEN 1 ELSE 0 END
        ) AS {$label}";
    }

    protected function prepare_response( array $responses ) {
        return array_map(
            function( $response ) {
                $current_user  = helpgent_get_current_user();
                $is_user_admin = $current_user->is_administrator() || $current_user->is_agent();

                /**
                 * @var ResponseRepository
                 */
                $response_repository = helpgent_singleton( ResponseRepository::class );

                $form_content = $response_repository->get_meta_value( $response->id, 'form_content' );
                $form_content = ! empty( $form_content ) ? json_decode( $form_content, true ) : [];

                $response->form_elements                  = helpgent_get_form_elements( $form_content );
                $response->is_active_save_incomplete_data = helpgent_get_nested_value( 'global.is_active_save_incomplete_data', $form_content ) === '1';
                
                $response->total_unread_chat    = $this->get_total_unread_count( $response->id, $is_user_admin, 'chat' );
                $response->total_unread_message = $this->get_total_unread_count( $response->id, $is_user_admin, 'message' );
                $response->last_user_activity_at = $this->get_last_user_activity_at( $response );
                $response->activeStatus          = $this->get_user_activity_status( $response->last_user_activity_at );

                $response_token = helpgent_get_response_token( $response->id, $response->form_id );

                if ( ! empty( $response_token ) ) {
                    $response->response_token = $response_token;
                }

                if ( isset( $response->user_email ) ) {
                    $response->user_avatar_url = get_avatar_url( $response->user_email );
                }

                $message_items = [];

                if ( ! empty( $response->chat_message ) ) {
                    $response->chat_message->message_type = 'chat';

                    $message_items[] = $response->chat_message;
                }

                unset( $response->chat_message );

                if ( ! empty( $response->message ) ) {
                    $response->message->message_type = 'message';

                    $message_items[] = $response->message;
                }

                $response->message = array_reduce(
                    $message_items, function( $carry, $item ) {
                        if ( ! $carry ) {
                            return $item;
                        }

                        $carry_date = $carry->updated_at ? $carry->updated_at : $carry->created_at;
                        $item_date  = $item->updated_at ? $item->updated_at : $item->created_at;

                        return strtotime( $carry_date ) > strtotime( $item_date ) ? $carry : $item;
                    }
                );

                if ( ! empty( $response->message->attachment_id ) ) {
                    $attachment = Repository::attachment_query()->where( 'id', $response->message->attachment_id )->first();

                    if ( ! empty( $attachment ) ) {
                        $attachment = Repository::clean_file( Repository::bind_file_url( $attachment ) );
                    }

                    $response->message->attachment = $attachment;
                }

                if ( ! empty( $response->message->forward->attachment_id ) ) {
                    $attachment = Repository::attachment_query()->where( 'id', $response->message->forward->attachment_id )->first();

                    if ( ! empty( $attachment ) ) {
                        $attachment = Repository::clean_file( Repository::bind_file_url( $attachment ) );
                    }

                    $response->message->forward->attachment = $attachment;
                }

                if ( ! empty( $response->form_content ) ) {
                    $response_form = (object) [ 'content' => $response->form_content ];
                    Repository::bind_form_media_settings( $response_form );
                    $response->media_settings = $response_form->media_settings ?? null;
                    unset( $response->form_content );
                }

                $chat_answer = $this->get_response_answer( $response->id );

                if ( $chat_answer ) {
                    $response->chat_answer = $chat_answer;
                }

                return apply_filters( 'helpgent_response_item', $response );
            }, $responses
        );
    }

    public function get_response_answer( int $response_id ) {
        $answer = ElementAnswers::query( 'answer' )
            ->select(
                [
                    'answer.*',
                    'review.liked',
                    'review.comment',
                    'review.created_at as review_created_at',
                    'review.updated_at as review_updated_at',
                    "user.name as user_name",
                    "user.email as user_email",
                ]
            )
            ->left_join( Review::get_table_name() . ' as review', 'answer.id', 'review.answer_id' )
            ->left_join( Response::get_table_name() . ' as response', 'answer.response_id', 'response.id' )
            ->left_join( HelpGentUser::get_table_name() . ' as user', 'response.created_by', 'user.id' )
            ->where( 'answer.response_id', $response_id )
            ->where( 'answer.element_type', LiveChatField::get_key() )
            ->first();

        if ( empty( $answer ) ) {
            return null;
        }

        if ( $answer->user_email ) {
            $answer->user_avatar_url = get_avatar_url( $answer->user_email );
        }

        return $answer;
    }

    public function get_total_unread_count( int $response_id, bool $is_user_admin, string $message_type ): int {
        switch ( $message_type ) {
            case 'chat':
                $message_count_query = ChatMessage::query( 'message' );
                break;

            case 'message':
                $message_count_query = Message::query( 'message' );
                break;

            default:
                $message_count_query = ChatMessage::query( 'message' );
                break;
        }

        $message_count_query = $message_count_query->select( [ 'COUNT( message.id ) AS total_unread' ] )
            ->left_join( HelpGentUser::get_table_name() . ' as user', 'message.created_by', 'user.id' )
            ->left_join( UserMeta::get_table_name() . ' as user_meta', 'user.registered_id', 'user_meta.user_id' )
            ->where(
                function( $builder ) use( $is_user_admin ) {
                    global $wpdb;

                    if ( $is_user_admin ) {
                        $builder->where_null( 'user_meta.meta_key' )
                            ->or_where(
                                function( $builder ) {
                                    global $wpdb;

                                    $builder
                                    ->where( 'user_meta.meta_key', "{$wpdb->prefix}capabilities" )
                                    ->where_like( 'user_meta.meta_value', '%subscriber%' );
                                }
                            );
                    } else {
                        $builder->where( 'user_meta.meta_key', "{$wpdb->prefix}capabilities" )
                           ->where_not_like( 'user_meta.meta_value', '%subscriber%' );
                    }
                }
            )
            ->where( 'message.response_id', $response_id )
            ->where( 'message.is_read', '0' )
            ->group_by( 'message.response_id' )
            ->get();

            return ! empty( $message_count_query ) ? absint( $message_count_query[0]->total_unread ) : 0;
    }

    public function form( int $form_id ) {
        $columns = ['form.id', 'form.title', 'form.content'];

        $form_query = Form::query( 'form' )->select( $columns )->where( 'form.id', $form_id )->group_by( $columns );

        /**
         * @var Builder $form_query
         */
        $form_query     = apply_filters( 'helpgent_response_form_query', $form_query );
        $join_form_meta = apply_filters( 'helpgent_response_form_query_join_meta', false );

        if ( $join_form_meta ) {
            $form_query->left_join( "helpgent_form_meta as form_meta", "form_meta.form_id", "=", "form.id" );
        }

        $form = $form_query->first();

        if ( ! $form ) {
            throw new Exception( esc_html__( 'Form not found.', 'helpgent' ), 404 );
        }

        $form = Repository::bind_form_featured_image( $form );
        $form = Repository::bind_form_media_settings( $form );

        $form->has_live_chat      = Repository::form_has_element( LiveChatField::get_key(), $form );
        $form->has_only_live_chat = Repository::form_has_only_element( LiveChatField::get_key(), $form );

        unset( $form->content );

        $form->total_response = [
            'completed'   => Response::query()->where( 'form_id', $form_id )->where( 'is_completed', 1 )->where( 'is_archive', 0 )->count(),
            'incompleted' => Response::query()->where( 'form_id', $form_id )->where( 'is_completed', 0 )->where( 'is_archive', 0 )->count(),
            'archive'     => Response::query()->where( 'form_id', $form_id )->where( 'is_archive', 1 )->count(),
        ];

        return $form;
    }

    protected function search_query( $dto, Builder $query ) {
        if ( ! $dto->is_initialized( "search" ) ) {
            return $query;
        }

        $search = $dto->get_search();

        if ( empty( $search ) ) {
            return $query;
        }

        return $query->where(
            function( $query ) use( $search ) {
                $query->where_like( "user.name", "%{$search}%" )->or_where_like( "user.email", "%{$search}%" );
            }
        );
    }

    public function create( ResponseDTO $dto ) {
        $data = $dto->to_array();
        $now  = helpgent_now();

        $data['created_at'] = $now;
        $data['updated_at'] = $now;

        do_action( 'helpgent_before_response_created', $data );

        $id = Response::query()->insert_get_id( $data );

        do_action( 'helpgent_after_response_created', $id, $data );

        return $id;
    }

    public function update( ResponseDTO $dto ) {
        $data = $dto->to_array();

        $data[ 'updated_at' ] = helpgent_now();

        unset( $data['id'] );

        return Response::query()->where( 'id', $dto->get_id() )->update( $data );
    }

    public function create_and_get_token( ResponseDTO $response_dto ) {
        return $this->get_token( $response_dto->get_form_id(), $this->create( $response_dto ) );
    }

    public function get_token( int $form_id, int $response_id ): string {
        /**
         * Generating and storing token to identify the subsequent response on this response.
         */
        $token = 'response_token-' . helpgent_generate_token();

        /**
         * @var FormRepository $form_repository
         */
        $form_repository = helpgent_singleton( FormRepository::class );
        $form_repository->add_meta( $form_id, $token, $response_id );

        return $token;
    }

    public function verify( int $response_id ) {
        return Response::query()->where( 'id', $response_id )->update(
            [
                'is_verified' => 1,
                'updated_at'  => helpgent_now(),
            ]
        );
    }

    public function cancel( int $response_id ) {
        return Response::query()->where( 'id', $response_id )->update(
            [
                'is_verified' => 0,
                'updated_at'  => helpgent_now(),
            ]
        );
    }

    public function update_updated_at( int $id ) {
        return Response::query()->where( 'id', $id )->update(
            [
                'updated_at' => helpgent_now()
            ]
        );
    }

    public function update_user_activity( int $response_id, ?string $activity_at = null ) {
        return $this->update_meta(
            $response_id,
            static::USER_ACTIVITY_META_KEY,
            $activity_at ?: helpgent_now()->to_date_time_string()
        );
    }

    public function update_submitted_at( int $id ) {
        return Response::query()->where( 'id', $id )->update(
            [
                'submitted_at' => helpgent_now(),
                'updated_at'   => helpgent_now(),
            ]
        );
    }

    public function update_create_by( int $id, int $created_by ) {
        return Response::query()->where( 'id', $id )->update(
            [
                'created_by' => $created_by,
                'updated_at' => helpgent_now(),
            ]
        );
    }

    public function get_by_id( int $id ) {
        return Response::query()->where( 'id', $id )->first();
    }

    public function can_receive_staff_message( stdClass $response ): bool {
        $response_user = HelpGentUser::query( 'user' )
            ->select( 'user.email' )
            ->where( 'user.id', absint( $response->created_by ) )
            ->first();

        return ! empty( $response_user->email )
            && absint( $response->is_completed ) === 1;
    }

    public function can_receive_message( stdClass $response ): bool {
        return $this->can_receive_staff_message( $response )
            && absint( $response->is_verified ) === 1;
    }

    public function can_user_store_message( stdClass $response, ?UserDTO $user ): bool {
        if ( $user && ( $user->is_administrator() || $user->is_agent() ) ) {
            return $this->can_receive_staff_message( $response );
        }

        return $this->can_receive_message( $response );
    }

    public function has_content( int $response_id ): bool {
        if ( ElementAnswers::query()->where( 'response_id', $response_id )->first() ) {
            return true;
        }

        if ( ContactInfo::query()->where( 'response_id', $response_id )->first() ) {
            return true;
        }

        if ( Message::query()->where( 'response_id', $response_id )->first() ) {
            return true;
        }

        if ( ChatMessage::query()->where( 'response_id', $response_id )->first() ) {
            return true;
        }

        if ( ResponseAttachment::query()->where( 'response_id', $response_id )->first() ) {
            return true;
        }

        return false;
    }

    public function delete( int $id ) {
        $response = $this->get_by_id( $id );

        if ( ! $response ) {
            throw new Exception( esc_html__( 'Response not found.', 'helpgent' ), 404 );
        }

        $delete = Response::query()->where( 'id', $id )->delete();

        Repository::delete_unused_forward_messages();

        return $delete;
    }

    public function delete_bulk( array $ids ) {
        $ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

        if ( empty( $ids ) ) {
            throw new Exception( esc_html__( 'Responses not found.', 'helpgent' ), 404 );
        }

        $delete = Response::query()->where_in( 'id', $ids )->delete();

        Repository::delete_unused_forward_messages();

        return $delete;
    }

    public function mark_as_completed( int $id ) {
        return Response::query()->where( 'id', $id )->update(
            [
                'is_completed' => 1,
                'submitted_at' => helpgent_now(),
                'updated_at'   => helpgent_now(),
            ]
        );
    }

    public function update_read( int $id, int $is_read ) {
        $response = $this->get_by_id( $id );

        if ( ! $response ) {
            throw new Exception( esc_html__( 'Form response not found.', 'helpgent' ), 404 );
        }

        return Response::query()->where( 'id', $id )->update(
            [
                'is_read'    => $is_read,
                'updated_at' => helpgent_now(),
            ]
        );
    }

    public function update_bulk_read( array $ids, int $is_read ) {
        $ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

        if ( empty( $ids ) ) {
            throw new Exception( esc_html__( 'Responses not found.', 'helpgent' ), 404 );
        }

        return Response::query()->where_in( 'id', $ids )->update(
            [
                'is_read'    => $is_read,
                'updated_at' => helpgent_now(),
            ]
        );
    }

    public function update_starred( int $id, int $is_starred ) {
        $response = $this->get_by_id( $id );

        if ( ! $response ) {
            throw new Exception( esc_html__( 'Form response not found.', 'helpgent' ), 404 );
        }

        return Response::query()->where( 'id', $id )->update(
            [
                'is_starred' => $is_starred,
                'updated_at'  => helpgent_now(),
            ]
        );
    }

    public function update_archive_status( int $id, int $is_archive ) {
        $response = $this->get_by_id( $id );

        if ( ! $response ) {
            throw new Exception( esc_html__( 'Form response not found.', 'helpgent' ), 404 );
        }

        return Response::query()->where( 'id', $id )->update(
            [
                'is_archive' => $is_archive,
                'updated_at' => helpgent_now(),
            ]
        );
    }

    public function get_meta( int $response_id, string $meta_key ) {
        return ResponseMeta::query()->where( 'response_id', $response_id )->where( 'meta_key', $meta_key )->first();
    }

    protected function get_last_user_activity_at( stdClass $response ): ?string {
        $activity_at = $this->get_meta_value( $response->id, static::USER_ACTIVITY_META_KEY );

        if ( ! empty( $activity_at ) ) {
            return $activity_at;
        }

        $timestamps = array_filter(
            [
                $response->submitted_at ?? null,
                $response->created_at ?? null,
                $this->get_latest_non_staff_message_activity_at( $response->id, 'message' ),
                $this->get_latest_non_staff_message_activity_at( $response->id, 'chat' ),
            ]
        );

        if ( empty( $timestamps ) ) {
            return null;
        }

        usort(
            $timestamps,
            static function( string $left, string $right ): int {
                return strtotime( $right ) <=> strtotime( $left );
            }
        );

        return $timestamps[0];
    }

    protected function get_user_activity_status( ?string $activity_at ): string {
        $activity_timestamp = $activity_at ? strtotime( $activity_at ) : false;

        if ( ! $activity_timestamp ) {
            return 'offline';
        }

        $inactive_for = helpgent_now()->getTimestamp() - $activity_timestamp;

        if ( $inactive_for <= static::ONLINE_ACTIVITY_WINDOW ) {
            return 'online';
        }

        if ( $inactive_for <= static::AWAY_ACTIVITY_WINDOW ) {
            return 'away';
        }

        return 'offline';
    }

    protected function get_latest_non_staff_message_activity_at( int $response_id, string $message_type ): ?string {
        global $wpdb;

        $message_query = 'chat' === $message_type
            ? ChatMessage::query( 'message' )
            : Message::query( 'message' );

        $message = $message_query
            ->select( 'message.created_at', 'message.updated_at' )
            ->left_join( HelpGentUser::get_table_name() . ' as user', 'message.created_by', 'user.id' )
            ->left_join(
                UserMeta::get_table_name() . ' as user_meta',
                function( $join ) use ( $wpdb ) {
                    $join
                        ->on_column( 'user.registered_id', '=', 'user_meta.user_id' )
                        ->on_column( 'user_meta.meta_key', '=', "'{$wpdb->prefix}capabilities'" );
                }
            )
            ->where( 'message.response_id', $response_id )
            ->where(
                function( $builder ) {
                    $builder
                        ->where( 'message.created_by', 0 )
                        ->or_where_null( 'user.registered_id' )
                        ->or_where(
                            function( $builder ) {
                                $builder
                                    ->where_not_like( 'user_meta.meta_value', '%administrator%' )
                                    ->where_not_like( 'user_meta.meta_value', '%helpgent_agent%' );
                            }
                        );
                }
            )
            ->order_by_desc( 'message.updated_at' )
            ->order_by_desc( 'message.created_at' )
            ->first();

        if ( empty( $message ) ) {
            return null;
        }

        return ! empty( $message->updated_at ) ? $message->updated_at : $message->created_at;
    }

    public function get_meta_value( int $response_id, string $meta_key ) {
        $meta = $this->get_meta( $response_id, $meta_key );
        if ( ! $meta ) {
            return false;
        }
        return $meta->meta_value;
    }

    public function add_meta( int $response_id, string $meta_key, string $meta_value ) {
        $meta = $this->get_meta( $response_id, $meta_key );

        if ( $meta ) {
            return false;
        }

        return ResponseMeta::query()->insert(
            [
                'response_id' => $response_id,
                'meta_key'    => $meta_key,
                'meta_value'  => $meta_value,
            ]
        );
    }

    public function update_meta( int $response_id, string $meta_key, string $meta_value ) {
        $old_data = ResponseMeta::query()
            ->where( 'response_id', $response_id )
            ->where( 'meta_key', $meta_key )
            ->first();

        if ( ! $old_data ) {
            return ResponseMeta::query()->insert(
                [
                    'response_id' => $response_id,
                    'meta_key'    => $meta_key,
                    'meta_value'  => $meta_value,
                ]
            );
        }

        $update = ResponseMeta::query()
            ->where( 'response_id', $response_id )
            ->where( 'meta_key', $meta_key )
            ->update( [ 'meta_value' => $meta_value ] );

        return $update;
    }

    public function add_attachments( int $response_id, array $attachment_tokens ) {
        $attachment_ids = array_map(
            function( $id ) {
                return intval( $id );
            }, array_values( $attachment_tokens )
        );

        $attachment_tokens = array_map(
            function( $token ) {
                return is_string( $token ) ? $token : '';
            }, array_keys( $attachment_tokens )
        );

        $attachments = Attachment::query()->select( 'id' )->where_in( 'id', $attachment_ids )->where_in( 'upload_token', $attachment_tokens )->get();

        global $wpdb;

        if ( ! empty( $attachments ) ) {
            foreach ( $attachments as $attachment ) {
                $this->add_attachment( $response_id, $attachment->id );
            }
        }
    }

    public function add_attachment( int $response_id, int $attachment_id ) {
        global $wpdb;

        $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO {$wpdb->prefix}helpgent_response_attachment (response_id, attachment_id)
                        SELECT %d, %d FROM dual
                        WHERE NOT EXISTS (
                            SELECT 1
                            FROM {$wpdb->prefix}helpgent_response_attachment
                            WHERE response_id = %d AND attachment_id = %d
                        );",
                $response_id, $attachment_id, $response_id, $attachment_id
            )
        );
    }

    public function remove_attachment_permission( int $response_id, int $attachment_id ) {
        /**
         * Remove attachment read permission if attachment do not exist in this response message
         */
        $message = Message::query( "message" )->select( 1 )->where( 'response_id', $response_id )
            ->left_join( "helpgent_message_forwards", "message.forward_id", "=","helpgent_message_forwards.id" )
            ->where(
                function( $query ) use( $attachment_id ) {
                    $query->where( 'message.attachment_id', $attachment_id )->or_where( 'helpgent_message_forwards.attachment_id', $attachment_id );
                }
            )->first();

        if ( apply_filters( 'helpgent_remove_response_attachment_permission', empty( $message ), $response_id, $attachment_id ) ) {
            $this->delete_attachment( $response_id, $attachment_id );
            return;
        }
    }

    public function delete_attachment( int $response_id, int $attachment_id ) {
        ResponseAttachment::query()->where( 'response_id', $response_id )->where( 'attachment_id', $attachment_id )->delete();
    }

    public function delete_meta( int $response_id, string $meta_key ) {
        return ResponseMeta::query()->where( 'response_id', $response_id )->where( 'meta_key', $meta_key )->delete();
    }

    public function first_by_created_by( int $id, int $created_by, int $is_completed = 1 ) {
        return Response::query()->where( 'id', $id )->where( 'created_by', $created_by )->where( 'is_completed', $is_completed )->first();
    }
}
