<?php
// app/database/ArticleStatusManager.php

if (!defined('ABSPATH')) {
  exit;
}

/**
 * Article Status Manager
 * 
 * Handles database operations for article status functionality
 */
class ContentCraftAI_ArticleStatusManager
{
  /**
   * Singleton instance of the class
   * @var ContentCraftAI_ArticleStatusManager|null
   */
  private static $instance = null;

  /**
   * Name of the generations database table
   * @var string
   */
  private $table_name;

  /**
   * Get the singleton instance of the class
   * 
   * @return ContentCraftAI_ArticleStatusManager The singleton instance
   */
  public static function get_instance()
  {
    if (self::$instance === null) {
      self::$instance = new self();
    }
    return self::$instance;
  }

  /**
   * Private constructor to prevent direct instantiation
   * Sets up table names
   */
  private function __construct()
  {
    global $wpdb;
    $this->table_name = $wpdb->prefix . 'content_craft_ai_generations';
  }

  /**
   * Get paginated generations with filters
   * 
   * @param int   $page      Current page number
   * @param int   $per_page  Number of items per page
   * @param array $filters   Array of filters to apply
   * 
   * @return array|bool Filtered generations data or false on failure
   */
  public function get_paginated_generations($page, $per_page, $filters = [])
  {
    global $wpdb;

    // Start building the query
    $query = "SELECT DISTINCT g.queue_id, g.*, 
                  c.name as category_name,
                  p.post_title as post_title,
                  p.post_status as post_status,
                  p.guid as post_url
                  FROM {$this->table_name} g
                  LEFT JOIN {$wpdb->terms} t ON g.category = t.term_id
                  LEFT JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id AND tt.taxonomy = 'category'
                  LEFT JOIN {$wpdb->term_relationships} tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
                  LEFT JOIN {$wpdb->posts} p ON g.post_id = p.ID
                  LEFT JOIN {$wpdb->term_taxonomy} tax ON tax.term_taxonomy_id = tr.term_taxonomy_id
                  LEFT JOIN {$wpdb->terms} c ON c.term_id = tax.term_id
                  WHERE 1=1";

    $count_query = "SELECT COUNT(DISTINCT g.queue_id) FROM {$this->table_name} g WHERE 1=1";

    $query_args = [];
    $count_args = [];

    // Apply filters
    if (!empty($filters['status'])) {
      $query .= " AND g.status = %s";
      $count_query .= " AND g.status = %s";
      $query_args[] = $filters['status'];
      $count_args[] = $filters['status'];
    }

    if (!empty($filters['postStatus'])) {
      // For normal articles with posts, filter by actual post status
      // For scheduled articles without posts, filter by the postStatus stored in parameters
      $query .= " AND (p.post_status = %s OR (g.post_id IS NULL AND JSON_EXTRACT(g.parameters, '$.postStatus') = %s))";
      $count_query .= " AND (g.post_id IN (SELECT ID FROM {$wpdb->posts} WHERE post_status = %s) OR (g.post_id IS NULL AND JSON_EXTRACT(g.parameters, '$.postStatus') = %s))";

      $query_args[] = $filters['postStatus'];
      $query_args[] = $filters['postStatus'];
      $count_args[] = $filters['postStatus'];
      $count_args[] = $filters['postStatus'];
    }

    if (!empty($filters['category'])) {
      $query .= " AND g.category = %d";
      $count_query .= " AND g.category = %d";
      $query_args[] = $filters['category'];
      $count_args[] = $filters['category'];
    }

    if (!empty($filters['dateFrom'])) {
      $query .= " AND g.created_at >= %s";
      $count_query .= " AND g.created_at >= %s";
      $query_args[] = $filters['dateFrom'] . ' 00:00:00';
      $count_args[] = $filters['dateFrom'] . ' 00:00:00';
    }

    if (!empty($filters['dateTo'])) {
      $query .= " AND g.created_at <= %s";
      $count_query .= " AND g.created_at <= %s";
      $query_args[] = $filters['dateTo'] . ' 23:59:59';
      $count_args[] = $filters['dateTo'] . ' 23:59:59';
    }

    if (!empty($filters['search'])) {
      $query .= " AND g.title LIKE %s";
      $count_query .= " AND g.title LIKE %s";
      $query_args[] = '%' . $wpdb->esc_like($filters['search']) . '%';
      $count_args[] = '%' . $wpdb->esc_like($filters['search']) . '%';
    }

    // Get total count for pagination
    $total_items = 0;
    if (!empty($count_args)) {
      // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $count_query is built dynamically but sanitized with $wpdb->prepare
      $count_result = $wpdb->get_var($wpdb->prepare($count_query, $count_args));
    } else {
      // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $count_query is built dynamically with known table names only, no user input
      $count_result = $wpdb->get_var($count_query);
    }

    if ($count_result !== null) {
      $total_items = (int) $count_result;
    }

    // Calculate total pages
    $total_pages = ceil($total_items / $per_page);

    // Ensure page is within bounds
    $page = max(1, min($page, $total_pages));

    // Add pagination to the query
    $offset = ($page - 1) * $per_page;
    $query .= " ORDER BY g.created_at DESC LIMIT %d OFFSET %d";
    $query_args[] = $per_page;
    $query_args[] = $offset;

    // Execute the query
    $generations = [];
    if (!empty($query_args)) {
      // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is built dynamically but sanitized with $wpdb->prepare
      $generations = $wpdb->get_results($wpdb->prepare($query, $query_args));
    } else {
      // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is built dynamically with known table names only, no user input
      $generations = $wpdb->get_results($query);
    }

    // Process the results
    $processed_generations = [];
    foreach ($generations as $generation) {
      // Add thumbnail URL if post has a featured image
      $thumbnail_url = '';
      if (!empty($generation->post_id)) {
        // phpcs:ignore WordPress.WP.DeprecatedFunctions -- These WordPress functions are available at runtime
        $thumbnail_id = get_post_thumbnail_id($generation->post_id);
        if ($thumbnail_id) {
          // phpcs:ignore WordPress.WP.DeprecatedFunctions -- These WordPress functions are available at runtime
          $thumbnail = wp_get_attachment_image_src($thumbnail_id, 'thumbnail');
          if ($thumbnail) {
            $thumbnail_url = $thumbnail[0];
          }
        }
      }

      // Decode parameters
      $parameters = !empty($generation->parameters) ? json_decode($generation->parameters, true) : [];

      // Add to processed array
      $processed_generations[] = [
        'id' => $generation->id,
        'queue_id' => $generation->queue_id,
        'title' => $generation->title,
        'status' => $generation->status,
        'word_count' => $generation->word_count,
        'created_at' => $generation->created_at,
        'completed_at' => $generation->completed_at,
        'scheduled_time' => $generation->scheduled_time,
        'is_scheduled' => (bool) $generation->is_scheduled,
        'post_id' => $generation->post_id,
        'post_title' => $generation->post_title,
        'post_status' => $generation->post_status,
        'post_url' => $generation->post_url,
        'category_name' => $generation->category_name,
        'thumbnail_url' => $thumbnail_url,
        'parameters' => $parameters
      ];
    }

    // Return the results with pagination info
    return [
      'generations' => $processed_generations,
      'total_items' => $total_items,
      'total_pages' => $total_pages,
      'current_page' => $page,
      'per_page' => $per_page
    ];
  }

  /**
   * Update the parameters of a scheduled article
   * 
   * @param string $queue_id   The queue ID to update
   * @param array  $parameters The new parameters
   * 
   * @return bool True on success, false on failure
   */
  public function update_generation_parameters($queue_id, $parameters)
  {
    global $wpdb;

    // Update the parameters
    $result = $wpdb->update(
      $this->table_name,
      [
        'parameters' => json_encode($parameters),
        'title' => $parameters['title'] ?? '',
        'word_count' => $parameters['wordCount'] ?? 0,
        'category' => $parameters['category'] ?? null
      ],
      ['queue_id' => $queue_id],
      ['%s', '%s', '%d', '%d'],
      ['%s']
    );

    return $result !== false;
  }
}
