<?php

namespace AIPT\Api\BulkGenerator\Common;

class DatabaseManager {
    private $wpdb;
    private $history_table;

    /**
     * Cache key prefix for history items
     */
    private const CACHE_PREFIX = 'aipt_history_';

    /**
     * Cache TTL in seconds (60 seconds for frequently changing data)
     */
    private const CACHE_TTL = 60;

    /**
     * Allowed columns for WHERE clauses (whitelist for SQL injection prevention)
     */
    private const ALLOWED_COLUMNS = [
        'id',
        'product_id',
        'description_type',
        'status',
        'created_at',
        'updated_at'
    ];

    /**
     * Allowed ORDER BY clauses (whitelist for SQL injection prevention)
     */
    private const ALLOWED_ORDER_BY = [
        'created_at DESC',
        'created_at ASC',
        'updated_at DESC',
        'updated_at ASC',
        'id DESC',
        'id ASC',
        'product_id ASC',
        'product_id DESC',
        'h.created_at DESC',
        'h.created_at ASC',
        'h.id DESC',
        'h.id ASC'
    ];

    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->history_table = $wpdb->prefix . 'aipt_bulk_generator_history';
    }

    /**
     * Generate a cache key for history queries.
     *
     * @param array  $conditions Query conditions
     * @param string $order_by   Order by clause
     * @return string Cache key
     */
    private function get_cache_key(array $conditions, string $order_by): string {
        $hash = md5(wp_json_encode($conditions) . $order_by);
        return self::CACHE_PREFIX . $hash;
    }

    /**
     * Invalidate all history caches.
     * Called after any write operation to ensure fresh data.
     *
     * @return void
     */
    private function invalidate_cache(): void {

        $version = get_transient(self::CACHE_PREFIX . 'version');
        $new_version = ($version ? (int) $version : 0) + 1;
        set_transient(self::CACHE_PREFIX . 'version', $new_version, DAY_IN_SECONDS);
    }

    /**
     * Get the current cache version for key generation.
     *
     * @return int Cache version
     */
    private function get_cache_version(): int {
        $version = get_transient(self::CACHE_PREFIX . 'version');
        return $version ? (int) $version : 0;
    }

    /**
     * Validates and returns a safe ORDER BY clause
     *
     * @param string $order_by The ORDER BY clause to validate
     * @return string A safe ORDER BY clause from the whitelist
     */
    private function validate_order_by(string $order_by): string {
        $order_by = trim($order_by);
        
        if (in_array($order_by, self::ALLOWED_ORDER_BY, true)) {
            return $order_by;
        }
        

        return 'created_at DESC';
    }

    /**
     * Validates a column name against the whitelist
     *
     * @param string $column The column name to validate
     * @return bool True if the column is allowed
     * @throws \InvalidArgumentException If column is not in whitelist
     */
    private function validate_column(string $column): bool {

        $column_name = preg_replace('/^[a-z]+\./', '', $column);
        
        if (!in_array($column_name, self::ALLOWED_COLUMNS, true)) {
            throw new \InvalidArgumentException(
                sprintf('Invalid column name: %s', esc_html($column_name))
            );
        }
        
        return true;
    }

    public function insert_history_item($product_id, $description_type, $generated_text, $status = 'pending') {

        if (!in_array($description_type, ['short', 'full', 'tags'], true)) {
            throw new \InvalidArgumentException('Invalid description type. Must be one of: short, full, tags');
        }

        if ($status === 'approved') {
            return false;
        }

        $existing = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT id, status FROM {$this->history_table}
            WHERE product_id = %d
            AND description_type = %s
            AND status IN ('pending', 'rejected')
            ORDER BY created_at DESC
            LIMIT 1",
            $product_id,
            $description_type
        ));

        if ($existing) {

            $result = $this->wpdb->update(
                $this->history_table,
                [
                    'generated_text' => $generated_text,
                    'status' => $status,
                    'updated_at' => current_time('mysql')
                ],
                ['id' => $existing->id],
                ['%s', '%s', '%s'],
                ['%d']
            );
            $this->invalidate_cache();
            return $result;
        }

        $result = $this->wpdb->insert(
            $this->history_table,
            [
                'product_id' => $product_id,
                'description_type' => $description_type,
                'generated_text' => $generated_text,
                'status' => $status,
                'created_at' => current_time('mysql')
            ],
            [
                '%d', 
                '%s', 
                '%s', 
                '%s', 
                '%s'  
            ]
        );
        $this->invalidate_cache();
        return $result;
    }

    public function update_history_status($conditions, $new_status) {

        if (!isset($conditions['id']) && isset($conditions['product_id'])) {

            if (isset($conditions['description_type'])) {
                $latest_record = $this->wpdb->get_row($this->wpdb->prepare(
                    "SELECT id FROM {$this->history_table}
                    WHERE product_id = %d
                    AND description_type = %s
                    ORDER BY created_at DESC
                    LIMIT 1",
                    $conditions['product_id'],
                    $conditions['description_type']
                ));
            } else {

                $latest_record = $this->wpdb->get_row($this->wpdb->prepare(
                    "SELECT id FROM {$this->history_table}
                    WHERE product_id = %d
                    ORDER BY created_at DESC
                    LIMIT 1",
                    $conditions['product_id']
                ));
            }

            if ($latest_record) {
                $conditions = ['id' => $latest_record->id];
            }
        }

        $condition_formats = [];
        foreach ($conditions as $value) {
            if (is_numeric($value)) {
                $condition_formats[] = '%d';
            } else {
                $condition_formats[] = '%s';
            }
        }

        $result = $this->wpdb->update(
            $this->history_table,
            [
                'status' => $new_status,
                'updated_at' => current_time('mysql')
            ],
            $conditions,
            ['%s', '%s'],
            $condition_formats
        );
        $this->invalidate_cache();
        return $result;
    }

    public function get_history_items($conditions = [], $order_by = 'created_at DESC') {

        $use_cache = empty($conditions) || (count($conditions) === 1 && isset($conditions['status']));
        $cache_key = '';
        
        if ($use_cache) {
            $cache_key = $this->get_cache_key($conditions, $order_by) . '_v' . $this->get_cache_version();
            $cached = get_transient($cache_key);
            if ($cached !== false) {
                return $cached;
            }
        }

        $query = "SELECT h.*, p.post_title as product_name 
                 FROM {$this->history_table} h
                 LEFT JOIN {$this->wpdb->posts} p ON h.product_id = p.ID
                 WHERE p.post_type = 'product'";
        

        if (!isset($conditions['status'])) {
            $query .= " AND h.status IN ('pending', 'rejected')";
        }
        
        $values = [];
        if (!empty($conditions)) {
            $where_clauses = [];
            foreach ($conditions as $key => $value) {

                $column_key = str_replace(' IN', '', $key);
                $this->validate_column($column_key);
                
                if (is_array($value)) {
                    $element_placeholders = array_map(
                        function ($element) {
                            return is_int($element) ? '%d' : '%s';
                        },
                        $value
                    );
                    $placeholders = implode(',', $element_placeholders);
                    $where_clauses[] = "h.{$column_key} IN ($placeholders)";
                    $values = array_merge($values, $value);
                } else {
                    $placeholder = is_int($value) ? '%d' : '%s';
                    $where_clauses[] = "h.{$column_key} = $placeholder";
                    $values[] = $value;
                }
            }
            $query .= " AND " . implode(' AND ', $where_clauses);
        }
        

        $safe_order_by = $this->validate_order_by($order_by);
        $query .= " ORDER BY " . $safe_order_by;

        $results = !empty($values) ?
            $this->wpdb->get_results($this->wpdb->prepare($query, $values), ARRAY_A) :
            $this->wpdb->get_results($query, ARRAY_A);

        if ($use_cache && !empty($cache_key)) {
            set_transient($cache_key, $results, self::CACHE_TTL);
        }

        return $results;
    }

    public function delete_history_items($conditions) {
        if (empty($conditions)) {
            return false;
        }

        if (isset($conditions['product_id']) && !isset($conditions['status'])) {
            $conditions['status IN'] = ['pending', 'rejected'];
        }

        $where_clauses = [];
        $values = [];
        foreach ($conditions as $key => $value) {

            $column_key = str_replace(' IN', '', $key);
            $this->validate_column($column_key);
            
            if (strpos($key, ' IN') !== false) {
                $element_placeholders = array_map(
                    function ($element) {
                        return is_int($element) ? '%d' : '%s';
                    },
                    $value
                );
                $placeholders = implode(',', $element_placeholders);
                $where_clauses[] = "{$column_key} IN ($placeholders)";
                $values = array_merge($values, $value);
            } else {
                $placeholder = is_int($value) ? '%d' : '%s';
                $where_clauses[] = "{$column_key} = $placeholder";
                $values[] = $value;
            }
        }

        $query = "DELETE FROM {$this->history_table} WHERE " . implode(' AND ', $where_clauses);

        $result = $this->wpdb->query($this->wpdb->prepare($query, $values));
        $this->invalidate_cache();
        return $result;
    }

    public function truncate_history_table() {

        $result = $this->wpdb->query("TRUNCATE TABLE {$this->history_table}");
        $this->invalidate_cache();
        return $result;
    }

    public function get_product_history($product_id) {
        return $this->get_history_items(['product_id' => $product_id]);
    }

    public function get_pending_history() {
        return $this->get_history_items(['status' => 'pending']);
    }

    public function get_rejected_history() {
        return $this->get_history_items(['status' => 'rejected']);
    }

    public function get_approved_history() {
        return $this->get_history_items(['status' => 'approved']);
    }

    /**
     * Batch update history status for multiple IDs at once.
     * Reduces N individual UPDATE queries to a single query.
     *
     * @param array  $ids       Array of history item IDs to update
     * @param string $new_status The new status value
     * @return int|false Number of rows affected or false on failure
     */
    public function batch_update_history_status(array $ids, string $new_status) {
        if (empty($ids)) {
            return 0;
        }

        $ids = array_map('absint', $ids);
        $ids = array_filter($ids); 

        if (empty($ids)) {
            return 0;
        }

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

        $query = $this->wpdb->prepare(
            "UPDATE {$this->history_table} 
             SET status = %s, updated_at = %s 
             WHERE id IN ($placeholders)",
            array_merge(
                [$new_status, current_time('mysql')],
                $ids
            )
        );

        $result = $this->wpdb->query($query);
        $this->invalidate_cache();
        return $result;

    }
}