Longman\TelegramBot\Commands\AdminCommands\CleanupCommand::getQueries()

Complexity

Conditions	11
Paths	257

Size

Total Lines	181
Code Lines	59

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	132

Importance

Changes	1
Bugs	0	Features	0

<?php
    private function getQueries($settings)
    {
        if (empty($settings) || !is_array($settings)) {
            throw new TelegramException('Settings variable is not an array or is empty!');
        }

        // Convert all clean_older_than times to correct format.
        $clean_older_than = $settings['clean_older_than'];
        foreach ($clean_older_than as $table => $time) {
            $clean_older_than[$table] = date('Y-m-d H:i:s', strtotime('-' . $time));
        }
        $tables_to_clean = $settings['tables_to_clean'];

        $queries = [];

        if (in_array('telegram_update', $tables_to_clean, true)) {
            $queries[] = sprintf('
              DELETE FROM `%3$s`
              WHERE `id` != \'%1$s\'
                AND `chat_id` NOT IN (
                  SELECT `id`
                  FROM `%4$s` 
                  WHERE `chat_id` = `%4$s`.`id`
                )
                AND (
                  `message_id` IS NOT NULL
                  AND `message_id` IN (
                    SELECT f.id
                    FROM `%5$s` f 
                    WHERE `date` < \'%2$s\'
                  )
                ) 
                OR (
                  `edited_message_id` IS NOT NULL
                  AND `edited_message_id` IN (
                    SELECT f.id
                    FROM `%6$s` f
                    WHERE `edit_date` < \'%2$s\'
                  )
                )
                OR (
                  `inline_query_id` IS NOT NULL 
                  AND `inline_query_id` IN (
                    SELECT f.id 
                    FROM `%7$s` f 
                    WHERE `created_at` < \'%2$s\'
                  )
                ) 
                OR (
                  `chosen_inline_result_id` IS NOT NULL 
                  AND `chosen_inline_result_id` IN (
                    SELECT f.id 
                    FROM `%8$s` f 
                    WHERE `created_at` < \'%2$s\'
                  )
                ) 
                OR (
                  `callback_query_id` IS NOT NULL 
                  AND `callback_query_id` IN (
                    SELECT f.id 
                    FROM `%9$s` f 
                    WHERE `created_at` < \'%2$s\'
                  )
                )
            ',
                $this->getUpdate()->getUpdateId(),
                $clean_older_than['telegram_update'],
                TB_TELEGRAM_UPDATE,
                TB_CHAT,
                TB_MESSAGE,
                TB_EDITED_MESSAGE,
                TB_INLINE_QUERY,
                TB_CHOSEN_INLINE_RESULT,
                TB_CALLBACK_QUERY
            );
        }

        if (in_array('user_chat', $tables_to_clean, true)) {
            $queries[] = sprintf('
                DELETE FROM `%1$s` 
                WHERE `user_id` IN (
                  SELECT f.id
                  FROM `%2$s` f 
                  WHERE `updated_at` < \'%3$s\'
                )
            ',
                TB_USER_CHAT,
                TB_USER,
                $clean_older_than['chat']
            );
        }

        // Simple.
        $simple_tables = [
            'user'            => ['table' => TB_USER, 'field' => 'updated_at'],
            'chat'            => ['table' => TB_CHAT, 'field' => 'updated_at'],
            'conversation'    => ['table' => TB_CONVERSATION, 'field' => 'updated_at'],
            'request_limiter' => ['table' => TB_REQUEST_LIMITER, 'field' => 'created_at'],
        ];

        // Botan table is only available if enabled.
        if (defined('TB_BOTAN_SHORTENER')) {
            $simple_tables['botan_shortener'] = ['table' => TB_BOTAN_SHORTENER, 'field' => 'created_at'];
        }

        foreach (array_intersect(array_keys($simple_tables), $tables_to_clean) as $table_to_clean) {
            $queries[] = sprintf('
                DELETE FROM `%1$s` 
                WHERE `%2$s` < \'%3$s\'
            ',
                $simple_tables[$table_to_clean]['table'],
                $simple_tables[$table_to_clean]['field'],
                $clean_older_than[$table_to_clean]
            );
        }

        // Queries.
        $query_tables = [
            'inline_query'         => ['table' => TB_INLINE_QUERY, 'field' => 'created_at'],
            'chosen_inline_result' => ['table' => TB_CHOSEN_INLINE_RESULT, 'field' => 'created_at'],
            'callback_query'       => ['table' => TB_CALLBACK_QUERY, 'field' => 'created_at'],
        ];
        foreach (array_intersect(array_keys($query_tables), $tables_to_clean) as $table_to_clean) {
            $queries[] = sprintf('
                DELETE FROM `%1$s` 
                WHERE `%2$s` < \'%3$s\'
                  AND `id` NOT IN (
                    SELECT `%4$s` 
                    FROM `%5$s` 
                    WHERE `%4$s` = `%1$s`.`id`
                  )
            ',
                $query_tables[$table_to_clean]['table'],
                $query_tables[$table_to_clean]['field'],
                $clean_older_than[$table_to_clean],
                $table_to_clean . '_id',
                TB_TELEGRAM_UPDATE
            );
        }

        // Messages
        if (in_array('edited_message', $tables_to_clean, true)) {
            $queries[] = sprintf('
                DELETE FROM `%1$s` 
                WHERE `edit_date` < \'%2$s\'
                  AND `id` NOT IN (
                    SELECT `message_id`
                    FROM `%3$s`
                    WHERE `edited_message_id` = `%1$s`.`id`
                  )
            ',
                TB_EDITED_MESSAGE,
                $clean_older_than['edited_message'],
                TB_TELEGRAM_UPDATE
            );
        }

        if (in_array('message', $tables_to_clean, true)) {
            $queries[] = sprintf('
                DELETE FROM `%1$s` 
                WHERE `date` < \'%2$s\'
                  AND `id` NOT IN (
                    SELECT `message_id`
                    FROM `%3$s`
                    WHERE `message_id` = `%1$s`.`id`
                  )
                  AND `id` NOT IN (
                    SELECT `message_id`
                    FROM `%4$s`
                    WHERE `message_id` = `%1$s`.`id`
                  )
            ',
                TB_MESSAGE,
                $clean_older_than['message'],
                TB_TELEGRAM_UPDATE,
                TB_CALLBACK_QUERY
            );
        }

        return $queries;
    }