<?php

namespace GriffinForms\Admin\Sql;

class Forms extends \GriffinForms\Admin\Sql\Lists
{
    public function getTemplateCategories()
    {
        global $wpdb;
        $table = $this->config->getTable('template');

        // Query to get categories and template counts
        $results = $wpdb->get_results(
            $wpdb->prepare(
            'SELECT category, COUNT(*) as template_count 
            FROM %i
            GROUP BY category',
            $table)
        );

        return $results;
    }

    public function getFormTemplates($category_name = '', $tag_name = '', $offset = 0, $limit = 10)
    {
        global $wpdb;

        $table = esc_sql($this->config->getTable('template'));
        $like_tag = '%' . $wpdb->esc_like($tag_name) . '%';

        if (!empty($category_name) && $category_name !== 'All' && !empty($tag_name)) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE category = %s AND tags LIKE %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $table, $category_name, $like_tag, $limit, $offset
                ),
                ARRAY_A
            );
        }

        if (!empty($category_name) && $category_name !== 'All') {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE category = %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $table, $category_name, $limit, $offset
                ),
                ARRAY_A
            );
        }

        if (!empty($tag_name)) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE tags LIKE %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $table, $like_tag, $limit, $offset
                ),
                ARRAY_A
            );
        }

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM %i ORDER BY created_at DESC LIMIT %d OFFSET %d",
                $table, $limit, $offset
            ),
            ARRAY_A
        );
    }

    public function getTotalTemplateCount() {
        global $wpdb;
        $table = $this->config->getTable('template');
        
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $table));
        return $count;
    }

    public function getNextFormNumber(): int
    {
        global $wpdb;
        $table = $this->config->getTable('form');
        $next = $wpdb->get_var($wpdb->prepare("SELECT COALESCE(MAX(id), 0) + 1 FROM %i", $table));

        return max(1, absint($next));
    }

    public function getFormTemplatesBySearch($search_term) {
        global $wpdb;
        $table = $this->config->getTable('template');
        $like_search_term = '%' . $wpdb->esc_like($search_term) . '%';
        return $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE name LIKE %s ORDER BY created_at DESC", $table, $like_search_term), ARRAY_A);
    }

    public function getTemplateById($template_id) {
        global $wpdb;
        $table = $this->config->getTable('template');
    
        // Prepare and execute the SQL query to fetch the template data by ID
        $template = $wpdb->get_row($wpdb->prepare('SELECT * FROM %i WHERE id = %d', $table, $template_id), ARRAY_A);
    
        return $template ? $template : false;
    }

    protected function folderIdQuery($folder_id, &$params)
    {
        if ($folder_id === '-1' || $folder_id === -1 || $folder_id === null) {
            return '1=1'; // No-op condition
        }
    
        if ($folder_id === 'root') {
            $folder_id = 0;
        }
    
        $params[] = absint($folder_id);
        return 'folder = %d';
    }

    protected function workflowQuery($workflow, &$params)
    {
        $workflow = sanitize_key((string) $workflow);
        if ($workflow !== 'user_registration') {
            return '1=1';
        }

        $params[] = '%"wordpress_accounts"%';
        $params[] = '%"enabled":true%';

        return '(integration_settings LIKE %s AND integration_settings LIKE %s)';
    }

    public function insertTemplate($template_data) {
        global $wpdb;
        $table = $this->config->getTable('template'); 
        $data = [
            'name' => sanitize_text_field($template_data['name']),
            'description' => sanitize_textarea_field($template_data['description']),
            'category' => sanitize_text_field($template_data['category']),
            'tags' => maybe_serialize($template_data['tags'] ?? []),
            'form_data' => maybe_serialize($template_data['form_data']),
            'origin' => maybe_serialize($template_data['origin']),
            'created_at' => current_time('mysql', true),
        ];
        $format = ['%s', '%s', '%s', '%s', '%s', '%s', '%s'];
    
        $inserted = $wpdb->insert($table, $data, $format);
    
        return $inserted ? $wpdb->insert_id : false;
    }

    public function deleteSubmissionsByFormId($form_id)
    {
        global $wpdb;
        $submissions_table = $this->config->getTable('submission');
    
        // First, check if there are any submissions associated with this form ID
        $submission_count = $wpdb->get_var(
            $wpdb->prepare("SELECT COUNT(*) FROM %i WHERE form_id = %d", $submissions_table, $form_id)
        );
    
        if ($submission_count === '0') {
            // No submissions to delete; return true to indicate no error
            return true;
        }
    
        // Proceed with deletion if submissions exist
        return (bool) $wpdb->delete($submissions_table, ['form_id' => $form_id], ['%d']);
    }
}
