<?php
/**
 * Keyword Manager
 *
 * @package AIContentGenerator
 */

namespace AICG;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Manages keyword queues linked to categories
 */
class Keyword_Manager
{

    /**
     * Get table name
     */
    private function get_table()
    {
        global $wpdb;
        return $wpdb->prefix . 'roidaico_keywords';
    }

    /**
     * Add keyword to queue
     */
    public function add_keyword($keyword, $category_id = 0, $language = '', $translation_of = null)
    {
        global $wpdb;

        $keyword = sanitize_text_field($keyword);

        if (empty($keyword)) {
            return new \WP_Error('empty_keyword', __('Keyword cannot be empty.', 'roidnet-ai-content-generator'));
        }

        // Check for duplicate
        if ($this->is_duplicate($keyword, $category_id)) {
            return new \WP_Error('duplicate_keyword', __('This keyword already exists in the queue.', 'roidnet-ai-content-generator'));
        }

        $language = sanitize_text_field($language);
        if (empty($language)) {
            $language = get_option('roidaico_default_language', 'en');
        }

        $translation_of = $translation_of ? absint($translation_of) : null;

        $result = $wpdb->insert(
            $this->get_table(),
            array(
                'keyword' => $keyword,
                'category_id' => $category_id,
                'status' => 'pending',
                'language' => $language,
                'translation_of' => $translation_of,
                'created_at' => current_time('mysql'),
            ),
            array('%s', '%d', '%s', '%s', '%d', '%s')
        );

        if ($result === false) {
            return new \WP_Error('db_error', __('Failed to add keyword.', 'roidnet-ai-content-generator'));
        }

        return $wpdb->insert_id;
    }

    /**
     * Add multiple keywords
     */
    public function add_keywords($keywords, $category_id = 0)
    {
        $added = array();
        $errors = array();

        foreach ($keywords as $keyword) {
            $result = $this->add_keyword($keyword, $category_id);

            if (is_wp_error($result)) {
                $errors[] = array(
                    'keyword' => $keyword,
                    'error' => $result->get_error_message(),
                );
            } else {
                $added[] = array(
                    'id' => $result,
                    'keyword' => $keyword,
                );
            }
        }

        return array(
            'added' => $added,
            'errors' => $errors,
        );
    }

    /**
     * Check if keyword is duplicate
     */
    public function is_duplicate($keyword, $category_id = null)
    {
        global $wpdb;
        $table = $this->get_table();

        // Check globally or per category
        if ($category_id !== null) {
            $count = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $table WHERE keyword = %s AND category_id = %d",
                $keyword,
                $category_id
            ));
        } else {
            $count = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $table WHERE keyword = %s",
                $keyword
            ));
        }

        return $count > 0;
    }

    /**
     * Get keyword by ID
     */
    public function get_keyword($id)
    {
        global $wpdb;
        $table = $this->get_table();

        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table WHERE id = %d",
            $id
        ));
    }

    /**
     * Get pending keywords queue
     */
    public function get_queue($category_id = null, $limit = 50)
    {
        global $wpdb;
        $table = $this->get_table();

        if ($category_id !== null) {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM $table 
                WHERE category_id = %d AND status = 'pending' 
                ORDER BY created_at ASC 
                LIMIT %d",
                $category_id,
                $limit
            ));
        } else {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM $table 
                WHERE status = 'pending' 
                ORDER BY created_at ASC 
                LIMIT %d",
                $limit
            ));
        }

        return $results;
    }

    /**
     * Get all keywords with pagination
     */
    public function get_all($args = array())
    {
        global $wpdb;
        $table = $this->get_table();

        $defaults = array(
            'category_id' => null,
            'status' => null,
            'language' => null,
            'per_page' => 20,
            'page' => 1,
            'orderby' => 'created_at',
            'order' => 'DESC',
        );

        $args = wp_parse_args($args, $defaults);
        $offset = ($args['page'] - 1) * $args['per_page'];

        $where = array('1=1');
        $values = array();

        if ($args['category_id'] !== null) {
            $where[] = 'category_id = %d';
            $values[] = $args['category_id'];
        }

        if ($args['status'] !== null) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }

        if (!empty($args['language'])) {
            $where[] = 'language = %s';
            $values[] = $args['language'];
        }

        $where_sql = implode(' AND ', $where);
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);

        $sql = "SELECT * FROM $table WHERE $where_sql ORDER BY $orderby LIMIT %d OFFSET %d";
        $values[] = $args['per_page'];
        $values[] = $offset;

        $results = $wpdb->get_results($wpdb->prepare($sql, $values));

        // Get total count
        $count_sql = "SELECT COUNT(*) FROM $table WHERE $where_sql";
        if (!empty(array_slice($values, 0, -2))) {
            $total = $wpdb->get_var($wpdb->prepare($count_sql, array_slice($values, 0, -2)));
        } else {
            $total = $wpdb->get_var($count_sql);
        }

        return array(
            'items' => $results,
            'total' => (int) $total,
            'total_pages' => ceil($total / $args['per_page']),
            'page' => $args['page'],
        );
    }

    /**
     * Get next pending keyword for a category
     */
    public function get_next_pending($category_id = null)
    {
        global $wpdb;
        $table = $this->get_table();

        if ($category_id !== null) {
            return $wpdb->get_row($wpdb->prepare(
                "SELECT * FROM $table 
                WHERE category_id = %d AND status = 'pending' 
                ORDER BY (CASE WHEN translation_of IS NULL OR translation_of = 0 THEN 0 ELSE 1 END) ASC, created_at ASC 
                LIMIT 1",
                $category_id
            ));
        }

        return $wpdb->get_row(
            "SELECT * FROM $table 
            WHERE status = 'pending' 
            ORDER BY (CASE WHEN translation_of IS NULL OR translation_of = 0 THEN 0 ELSE 1 END) ASC, created_at ASC 
            LIMIT 1"
        );
    }

    /**
     * Update keyword status
     */
    public function update_status($id, $status, $post_id = null, $error_message = null)
    {
        global $wpdb;
        $table = $this->get_table();

        $data = array(
            'status' => $status,
        );
        $format = array('%s');

        if ($status === 'completed' || $status === 'processing') {
            $data['processed_at'] = current_time('mysql');
            $format[] = '%s';
            $data['error_message'] = null;
            $format[] = '%s';
        } elseif ($status === 'pending') {
            $data['error_message'] = null;
            $format[] = '%s';
        } elseif ($status === 'failed') {
            $data['processed_at'] = current_time('mysql');
            $format[] = '%s';
            if ($error_message !== null) {
                $data['error_message'] = sanitize_textarea_field($error_message);
                $format[] = '%s';
            }
        }

        if ($post_id !== null) {
            $data['post_id'] = $post_id;
            $format[] = '%d';
        }

        return $wpdb->update(
            $table,
            $data,
            array('id' => $id),
            $format,
            array('%d')
        );
    }

    /**
     * Mark keyword as processing
     */
    public function mark_processing($id)
    {
        return $this->update_status($id, 'processing');
    }

    /**
     * Mark keyword as completed
     */
    public function mark_completed($id, $post_id = null)
    {
        return $this->update_status($id, 'completed', $post_id);
    }

    /**
     * Mark keyword as failed
     */
    public function mark_failed($id, $error_message = '')
    {
        return $this->update_status($id, 'failed', null, $error_message);
    }

    /**
     * Revert keyword status back to pending
     */
    public function revert_to_pending($id)
    {
        return $this->update_status($id, 'pending');
    }

    /**
     * Increment retry count for a keyword
     */
    public function increment_retries($id)
    {
        global $wpdb;
        $table = $this->get_table();
        return $wpdb->query($wpdb->prepare(
            "UPDATE $table SET retries = retries + 1 WHERE id = %d",
            $id
        ));
    }

    /**
     * Delete keyword
     */
    public function delete_keyword($id)
    {
        global $wpdb;
        return $wpdb->delete($this->get_table(), array('id' => $id), array('%d'));
    }

    /**
     * Delete multiple keywords
     */
    public function delete_keywords($ids)
    {
        global $wpdb;
        $table = $this->get_table();

        $ids = array_map('absint', $ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        return $wpdb->query($wpdb->prepare(
            "DELETE FROM $table WHERE id IN ($placeholders)",
            $ids
        ));
    }

    /**
     * Get queue count by category
     */
    public function get_pending_count($category_id = null)
    {
        global $wpdb;
        $table = $this->get_table();

        if ($category_id !== null) {
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT COALESCE(NULLIF(translation_of, 0), id)) FROM $table WHERE category_id = %d AND status = 'pending'",
                $category_id
            ));
        }

        return (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT COALESCE(NULLIF(translation_of, 0), id)) FROM $table WHERE status = 'pending'"
        );
    }

    /**
     * Check if queue is empty
     */
    public function is_queue_empty($category_id = null)
    {
        return $this->get_pending_count($category_id) === 0;
    }

    /**
     * Get statistics
     */
    public function get_stats()
    {
        global $wpdb;
        $table = $this->get_table();

        $stats = $wpdb->get_row(
            "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed
            FROM $table"
        );

        return $stats;
    }
}
