<?php

namespace HelpGent\App\BackgroundServices;

defined( 'ABSPATH' ) || exit;

use HelpGent\App\BackgroundProcessors\BackgroundProcessor;
use HelpGent\App\Models\HelpGentUser;
use HelpGent\App\Models\Attachment;
use HelpGent\App\Models\Response;
use HelpGent\App\Repositories\AttachmentRepository;
use HelpGent\App\Utils\Repository;
use HelpGent\WpMVC\Database\Query\JoinClause;

class ScheduledBackgroundService extends BackgroundService {
    public function boot(): void {
        $this->background_processor = new BackgroundProcessor( 'scheduled', false );
    }

    public static function remove_users( array $data = [] ): bool {
        if ( ! empty( $data['guest_user_remove'] ) && 1 === $data['guest_user_remove'] ) {
            HelpGentUser::query()->where_null( 'registered_id' )->where( 'created_at', '<', helpgent_now()->sub_days( 30 )->format( helpgent_date_time_format() ) )->delete();
        }

        return false;
    }

    public static function remove_unverified_response( array $data = [] ): bool {
        Response::query()->where(
            function( $query ) {
                $query->where_not_null( 'created_by' )->where( 'created_by', '!=', 0 );
            } 
        )->where(
            function( $query ) {
                $query->where_null( 'is_verified' )->or_where( 'is_verified', 0 );
            }
        )->where( 'created_at', '<=', $data['created_at'] )->delete();

        Repository::delete_unused_forward_messages();

        return false;
    }

    public static function remove_unknown_attachment( array $data = [] ): bool {
        $attachment = Attachment::query( "attachment" )->select( "attachment.id, attachment.title, attachment.file_id, attachment.storage" )
        ->where( "attachment.created_at", "<", $data['created_at'] )

        /**
         * If attachment not found in answers table
         */
        ->left_join(
            "helpgent_element_answers", function( JoinClause $join ) {
                $join->on_column( "attachment.id", "=", "helpgent_element_answers.attachment_id" )
                ->on_in( "helpgent_element_answers.element_type", ['open_ended_video', 'open_ended_voice', 'open_ended_screen'] )
                ->or_on_raw( "(helpgent_element_answers.element_type = 'file_upload' and JSON_CONTAINS(helpgent_element_answers.value, JSON_ARRAY(attachment.id)))" );
            }
        )->where_null( "helpgent_element_answers.value" )->where(
            function( $query ) {
                $query->where_null( "helpgent_element_answers.attachment_id" )->or_where( "helpgent_element_answers.attachment_id", 0 );
            }
        )

        /**
         * If attachment not found in open ended text inner table
         */
        ->left_join( "helpgent_open_ended_text_inners", "helpgent_open_ended_text_inners.file", "=", "attachment.id" )->where_null( "helpgent_open_ended_text_inners.file" )

        /**
         * If attachment not found in messages table
         */
        ->left_join( "helpgent_messages", "helpgent_messages.attachment_id", "=", "attachment.id" )->where_null( "helpgent_messages.attachment_id" )

        /**
         * If attachment not found in message forward table
         */
        ->left_join( "helpgent_message_forwards", "helpgent_message_forwards.attachment_id", "=", "attachment.id" )->where_null( "helpgent_message_forwards.attachment_id" )
        ->first();

        return self::remove_attachment( $attachment );
    }

    public static function remove_expired_attachment( array $data = [] ): bool {
        $attachment = Attachment::query()->where( 'created_at', '<', $data['expired_date'] )->first();
        return self::remove_attachment( $attachment );
    }

    protected static function remove_attachment( $attachment ): bool {
        if ( ! $attachment ) {
            return false;
        }
        /**
         * @var AttachmentRepository $attachment_repository
         */
        $attachment_repository = helpgent_singleton( AttachmentRepository::class );

        $attachment_repository->delete_file( $attachment->title, $attachment->file_id, $attachment->storage );
        $attachment_repository->delete( $attachment->id );

        return true;
    }
}