<?php

namespace AIPT\Api\AutomationJobs;

use AIPT\Api\AutomationJobs\Models\AutomationJob;

/**
 * Automation Job Manager
 * 
 * Handles CRUD operations for automation jobs
 * Following Single Responsibility Principle
 * 
 * @since 2.3.0
 */
class AutomationJobManager {
    
    /**
     * WordPress database instance
     * @var \wpdb
     */
    private $wpdb;
    
    /**
     * Jobs table name
     * @var string
     */
    private $jobs_table;
    
    /**
     * Job products table name
     * @var string
     */
    private $job_products_table;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->jobs_table = $wpdb->prefix . 'aipt_automation_jobs';
        $this->job_products_table = $wpdb->prefix . 'aipt_job_products';
    }
    
    /**
     * Create a new job
     * 
     * @param AutomationJob $job Job to create
     * @return int|false Job ID on success, false on failure
     * @throws \Exception On validation or database errors
     */
    public function createJob(AutomationJob $job) {

        $validation_errors = $job->validate();
        if (!empty($validation_errors)) {
            throw new \Exception('Job validation failed: ' . wp_json_encode($validation_errors));
        }
        

        $job->created_at = current_time('mysql');
        $job->updated_at = current_time('mysql');

        $job->status = 'preparing';
        $job->progress = 0;

        $data = $job->toArray();
        unset($data['id']); 
        

        $result = $this->wpdb->insert(
            $this->jobs_table,
            $data,
            [
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%d', 
                '%d', 
                '%d', 
                '%d', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%d', 
                '%s', 
                '%s'  
            ]
        );
        
        if ($result === false) {

            throw new \Exception('Failed to create job: ' . esc_html($this->wpdb->last_error));        }
        
        $job_id = $this->wpdb->insert_id;

        try {
            $pool_builder = new \AIPT\Api\AutomationJobs\PoolBuilder();

            $filter = is_array($job->product_filter) ? $job->product_filter : [];
            $pool_result = $pool_builder->buildPool($job_id, $filter, 2000, 5);

            $target_total = isset($pool_result['total']) ? (int) $pool_result['total'] : 0;

            $current_total = (int) $this->wpdb->get_var(
                $this->wpdb->prepare("SELECT COUNT(*) FROM {$this->job_products_table} WHERE job_id = %d", $job_id)
            );

            $update_data = [
                'total_products' => $target_total,
                'updated_at' => current_time('mysql')
            ];
            $update_formats = ['%d','%s'];

            if ($target_total === 0) {

                $update_data['status'] = 'completed';
                $update_data['progress'] = 100;
                $update_data['completed_at'] = current_time('mysql');
                $update_formats = array_merge($update_formats, ['%s','%d','%s']);
            } elseif ($current_total >= $target_total) {

                $update_data['status'] = 'ready';
                $update_formats[] = '%s';
            } else {

                $update_data['status'] = 'preparing';
                $update_formats[] = '%s';
            }

            $this->wpdb->update(
                $this->jobs_table,
                $update_data,
                ['id' => $job_id],
                $update_formats,
                ['%d']
            );
        } catch (\Exception $e) {
        }

        return $job_id;
    }
    
    /**
     * Get a job by ID
     * 
     * @param int $job_id Job ID
     * @return AutomationJob|null Job object or null if not found
     */
    public function getJob($job_id) {

        $result = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT * FROM {$this->jobs_table} WHERE id = %d",
                $job_id
            ),
            ARRAY_A
        );

        
        if (!$result) {
            return null;
        }
        
        return new AutomationJob($result);
    }
    
    /**
     * Update a job
     * 
     * @param AutomationJob $job Job to update
     * @return bool Success status
     * @throws \Exception On validation or database errors
     */
    public function updateJob(AutomationJob $job) {
        if (empty($job->id)) {
            throw new \Exception('Job ID is required for update');
        }
        

        $validation_errors = $job->validate();
        if (!empty($validation_errors)) {
            throw new \Exception('Job validation failed: ' . wp_json_encode($validation_errors));
        }
        

        $job->updated_at = current_time('mysql');
        

        $data = $job->toArray();
        $job_id = $data['id'];
        unset($data['id'], $data['created_at']); 
        

        $result = $this->wpdb->update(
            $this->jobs_table,
            $data,
            ['id' => $job_id],
            [
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%d', 
                '%d', 
                '%d', 
                '%d', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%s', 
                '%d', 
                '%s'  
            ],
            ['%d']
        );
        
        if ($result === false) {

            throw new \Exception('Failed to update job: ' . esc_html($this->wpdb->last_error));
        }
        
        return true;
    }
    
    /**
     * Delete a job and all related data
     * 
     * Deletes job record and all associated data from:
     * - aipt_draft_content (generated content awaiting review)
     * - aipt_job_logs (execution logs)
     * - aipt_job_products (product queue)
     * - aipt_automation_jobs (job record itself)
     * 
     * @param int $job_id Job ID to delete
     * @return bool Success status
     * @throws \Exception On database errors
     */
    public function deleteJob($job_id) {
        $job_id = absint($job_id);
        if ($job_id <= 0) {
            return false;
        }

        $this->wpdb->query('START TRANSACTION');

        try {

            $draft_content_table = $this->wpdb->prefix . 'aipt_draft_content';
            $this->wpdb->delete(
                $draft_content_table,
                ['job_id' => $job_id],
                ['%d']
            );

            $job_logs_table = $this->wpdb->prefix . 'aipt_job_logs';
            $this->wpdb->delete(
                $job_logs_table,
                ['job_id' => $job_id],
                ['%d']
            );

            $this->wpdb->delete(
                $this->job_products_table,
                ['job_id' => $job_id],
                ['%d']
            );

            $result = $this->wpdb->delete(
                $this->jobs_table,
                ['id' => $job_id],
                ['%d']
            );

            if ($result === false) {
                throw new \Exception('Failed to delete job: ' . esc_html($this->wpdb->last_error));
            }

            if ($this->wpdb->query('COMMIT') === false) {
                throw new \RuntimeException('COMMIT failed: ' . $this->wpdb->last_error);
            }
            return $result > 0;

        } catch (\Throwable $e) {
            $rollback = $this->wpdb->query('ROLLBACK');
            if ($rollback === false) {

            }

            throw new \Exception('Failed to delete job', 0, $e);
        }
    }
    
    /**
     * Get jobs with filters
     * 
     * @param array $filters Filters (status, tool_type, created_by, etc.)
     * @param int $limit Limit
     * @param int $offset Offset
     * @param string $order_by Order by field
     * @param string $order Order direction (ASC/DESC)
     * @return array Jobs
     */
    public function getJobs($filters = [], $limit = 20, $offset = 0, $order_by = 'created_at', $order = 'DESC') {
        $where_conditions = [];
        $where_values = [];

        if (!empty($filters['status'])) {
            if (is_array($filters['status'])) {

                $placeholders = implode(',', array_fill(0, count($filters['status']), '%s'));
                $where_conditions[] = "status IN ({$placeholders})";
                $where_values = array_merge($where_values, $filters['status']);
            } else {
                $where_conditions[] = 'status = %s';
                $where_values[] = $filters['status'];
            }
        }

        if (!empty($filters['tool_type'])) {
            $where_conditions[] = 'tool_type = %s';
            $where_values[] = $filters['tool_type'];
        }

        if (!empty($filters['created_by'])) {
            $where_conditions[] = 'created_by = %d';
            $where_values[] = $filters['created_by'];
        }

        $query = "SELECT * FROM {$this->jobs_table}";

        if (!empty($where_conditions)) {
            $query .= ' WHERE ' . implode(' AND ', $where_conditions);
        }

        $allowed_order_by = ['id', 'name', 'status', 'tool_type', 'progress', 'created_at', 'updated_at', 'next_run'];
        if (!in_array($order_by, $allowed_order_by)) {
            $order_by = 'created_at';
        }

        $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC';

        $query .= " ORDER BY {$order_by} {$order}";
        $query .= " LIMIT %d OFFSET %d";

        $where_values[] = $limit;
        $where_values[] = $offset;

        if (!empty($where_values)) {
            $results = $this->wpdb->get_results(
                $this->wpdb->prepare($query, $where_values),
                ARRAY_A
            );
        } else {
            $results = $this->wpdb->get_results($query, ARRAY_A);
        }

        $jobs = [];
        foreach ($results as $result) {
            $jobs[] = new AutomationJob($result);
        }

        return $jobs;
    }
    
    /**
     * Get total job count with filters
     *
     * @param array $filters Filters
     * @return int Total count
     */
    public function getJobCount($filters = []) {
        $where_conditions = [];
        $where_values = [];

        if (!empty($filters['status'])) {
            if (is_array($filters['status'])) {

                $placeholders = implode(',', array_fill(0, count($filters['status']), '%s'));
                $where_conditions[] = "status IN ({$placeholders})";
                $where_values = array_merge($where_values, $filters['status']);
            } else {
                $where_conditions[] = 'status = %s';
                $where_values[] = $filters['status'];
            }
        }

        if (!empty($filters['tool_type'])) {
            $where_conditions[] = 'tool_type = %s';
            $where_values[] = $filters['tool_type'];
        }

        if (!empty($filters['created_by'])) {
            $where_conditions[] = 'created_by = %d';
            $where_values[] = $filters['created_by'];
        }

        $query = "SELECT COUNT(*) FROM {$this->jobs_table}";

        if (!empty($where_conditions)) {
            $query .= ' WHERE ' . implode(' AND ', $where_conditions);
        }

        if (!empty($where_values)) {
            return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $where_values));
        } else {
            return (int) $this->wpdb->get_var($query);
        }

    }
    
    /**
     * Update job status
     * 
     * @param int $job_id Job ID
     * @param string $status New status
     * @param string|null $error_message Optional error message
     * @return bool Success status
     */
    public function updateJobStatus($job_id, $status, $error_message = null) {
        $data = [
            'status' => $status,
            'updated_at' => current_time('mysql')
        ];

        $formats = ['%s', '%s']; 

        if ($status === 'running') {

            $data['started_at'] = current_time('mysql');
            $formats[] = '%s';
        } elseif (in_array($status, ['completed', 'failed'])) {
            $data['completed_at'] = current_time('mysql');
            $formats[] = '%s';
        }

        if ($error_message !== null) {
            $data['error_message'] = $error_message;
            $formats[] = '%s';
        } elseif (in_array($status, ['running', 'ready', 'scheduled', 'preparing', 'completed'], true)) {
            $data['error_message'] = null;
            $formats[] = '%s';
        }

        $result = $this->wpdb->update(
            $this->jobs_table,
            $data,
            ['id' => $job_id],
            $formats,
            ['%d']
        );

        return $result !== false;
    }
    
    /**
     * Update job progress
     * 
     * @param int $job_id Job ID
     * @param int $processed_products Number of processed products
     * @param int $failed_products Number of failed products
     * @return bool Success status
     */
    public function updateJobProgress($job_id, $processed_products, $failed_products = 0) {
        $job = $this->getJob($job_id);
        if (!$job) {
            return false;
        }
        
        $progress = $job->total_products > 0 ? 
            round(($processed_products / $job->total_products) * 100) : 0;
        
        $result = $this->wpdb->update(
            $this->jobs_table,
            [
                'progress' => min(100, max(0, $progress)),
                'processed_products' => $processed_products,
                'failed_products' => $failed_products,
                'updated_at' => current_time('mysql'),
                'last_progress_logged_at' => current_time('mysql')
            ],
            ['id' => $job_id],
            ['%d', '%d', '%d', '%s', '%s'],
            ['%d']
        );
        
        return $result !== false;
    }
    
    /**
     * Create job products entries
     *
     * @param int $job_id Job ID
     * @param array $product_filter Product filter configuration
     * @return bool Success status
     */
    private function createJobProducts($job_id, $product_filter) {
        global $wpdb;

        try {

            $product_ids = $this->getFilteredProductIds($product_filter);

            if (empty($product_ids)) {
                return true; 
            }

            $batch_size = 100;
            $batches = array_chunk($product_ids, $batch_size);

            foreach ($batches as $batch) {
                $values = [];
                $placeholders = [];

                foreach ($batch as $product_id) {
                    $values[] = $job_id;
                    $values[] = $product_id;
                    $values[] = 'pending';
                    $values[] = current_time('mysql');
                    $placeholders[] = '(%d, %d, %s, %s)';
                }

                $query = "INSERT INTO {$this->job_products_table}
                         (job_id, product_id, status, created_at)
                         VALUES " . implode(', ', $placeholders);

                $result = $wpdb->query($wpdb->prepare($query, $values));

                if ($result === false) {
                    throw new \Exception('Failed to create job products: ' . $wpdb->last_error);
                }
            }

            return true;

        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Get filtered product IDs based on filter configuration
     *
     * @param array $product_filter Product filter configuration
     * @return array Product IDs
     */
    private function getFilteredProductIds($product_filter) {
        global $wpdb;

        $query = "
            SELECT DISTINCT p.ID
            FROM {$wpdb->posts} p
            WHERE p.post_type = 'product'
            AND p.post_status = 'publish'
        ";

        $query_params = [];

        if ( class_exists( '\\AIPT\\Premium\\AutomationJobs\\Services\\ProductFilterAugmenter' ) ) {
            \AIPT\Premium\AutomationJobs\Services\ProductFilterAugmenter::addManagerFilters( $query, $query_params, $product_filter );
        }

        if (!empty($product_filter['priceRange']['min']) || !empty($product_filter['priceRange']['max'])) {
            $price_conditions = [];

            if (!empty($product_filter['priceRange']['min'])) {
                $price_conditions[] = "CAST(pm.meta_value AS DECIMAL(10,2)) >= %f";
                $query_params[] = (float) $product_filter['priceRange']['min'];
            }

            if (!empty($product_filter['priceRange']['max'])) {
                $price_conditions[] = "CAST(pm.meta_value AS DECIMAL(10,2)) <= %f";
                $query_params[] = (float) $product_filter['priceRange']['max'];
            }

            if (!empty($price_conditions)) {
                $query .= " AND p.ID IN (
                    SELECT post_id FROM {$wpdb->postmeta} pm
                    WHERE pm.meta_key = '_price'
                    AND " . implode(' AND ', $price_conditions) . "
                )";
            }
        }

        if (!empty($product_filter['includeIds'])) {
            $include_ids = array_map('intval', explode(',', $product_filter['includeIds']));
            if (!empty($include_ids)) {
                $include_placeholders = implode(',', array_fill(0, count($include_ids), '%d'));
                $query .= " AND p.ID IN ({$include_placeholders})";
                $query_params = array_merge($query_params, $include_ids);
            }
        }

        if (!empty($product_filter['excludeIds'])) {
            $exclude_ids = array_map('intval', explode(',', $product_filter['excludeIds']));
            if (!empty($exclude_ids)) {
                $exclude_placeholders = implode(',', array_fill(0, count($exclude_ids), '%d'));
                $query .= " AND p.ID NOT IN ({$exclude_placeholders})";
                $query_params = array_merge($query_params, $exclude_ids);
            }
        }

        $query .= " ORDER BY p.ID ASC LIMIT 1000";

        if (!empty($query_params)) {
            $results = $wpdb->get_col($wpdb->prepare($query, $query_params));
        } else {
            $results = $wpdb->get_col($query);
        }

        return array_map('intval', $results);
    }
    
    /**
     * Get failed product IDs by job ID
     * 
     * @param int $jobId Job ID
     * @return array Array of failed product IDs
     */
    public function getFailedProductIdsByJobId(int $jobId): array {
        $table = $this->wpdb->prefix . 'aipt_job_products';

        $result = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT product_id FROM {$table} WHERE job_id = %d AND status = 'failed' ORDER BY product_id ASC",
                $jobId
            )
        );

        return $result;
    }
}
