<?php
// app/database/InternalLinksManager.php

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

/**
 * Manages the internal links database table
 * Handles storing, retrieving, and managing internal links for Content Craft AI
 */
class ContentCraftAI_InternalLinksManager
{
  private static $instance = null;
  private $internal_links_table;

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

  /**
   * Constructor
   */
  private function __construct()
  {
    global $wpdb;
    $this->internal_links_table = $wpdb->prefix . 'content_craft_ai_internal_links';
  }

  /**
   * Store or update internal links for a post
   *
   * @param int $post_id The post ID
   * @param string $focus_keyword Focus keyword for the post
   * @param array $tags Tags associated with the post
   * @return bool True on success, false on failure
   */
  public function store_post_links($post_id, $focus_keyword = '', $tags = [])
  {
    global $wpdb;

    // Check if post exists in the database
    $exists = $wpdb->get_var($wpdb->prepare(
      "SELECT COUNT(*) FROM {$this->internal_links_table} WHERE post_id = %d",
      $post_id
    ));

    // Get post permalink and title
    $permalink = get_permalink($post_id);
    $title = get_the_title($post_id);

    if (!$permalink || !$title) {
      // error_log("Content Craft AI: Failed to get permalink or title for post {$post_id}");
      return false;
    }

    // Convert tags array to JSON string for storage
    $tags_string = !empty($tags) ? json_encode($tags) : null;

    // If post exists, update it
    if ($exists) {
      $result = $wpdb->update(
        $this->internal_links_table,
        [
          'title' => $title,
          'link' => $permalink,
          'focus_keyword' => $focus_keyword,
          'tags' => $tags_string,
          'updated_at' => current_time('mysql')
        ],
        ['post_id' => $post_id],
        [
          '%s', // title
          '%s', // link
          '%s', // focus_keyword
          '%s', // tags
          '%s'  // updated_at
        ],
        ['%d'] // post_id
      );
    } else {
      // Insert new record
      $result = $wpdb->insert(
        $this->internal_links_table,
        [
          'post_id' => $post_id,
          'title' => $title,
          'link' => $permalink,
          'focus_keyword' => $focus_keyword,
          'tags' => $tags_string,
          'created_at' => current_time('mysql'),
          'updated_at' => current_time('mysql')
        ],
        [
          '%d', // post_id
          '%s', // title
          '%s', // link
          '%s', // focus_keyword
          '%s', // tags
          '%s', // created_at
          '%s'  // updated_at
        ]
      );
    }

    if ($result === false) {
      // error_log("Content Craft AI: Failed to store internal link data for post {$post_id} - " . $wpdb->last_error);
      return false;
    }

    return true;
  }

  /**
   * Remove internal links for a post
   *
   * @param int $post_id The post ID
   * @return bool True on success, false on failure
   */
  public function remove_post_links($post_id)
  {
    global $wpdb;

    $result = $wpdb->delete(
      $this->internal_links_table,
      ['post_id' => $post_id],
      ['%d']
    );

    if ($result === false) {
      // error_log("Content Craft AI: Failed to remove internal link data for post {$post_id} - " . $wpdb->last_error);
      return false;
    }

    return true;
  }

  /**
   * Find relevant internal links for an article based on focus keyword and category
   *
   * @param string $focus_keyword The keyword to find related content for
   * @param int|null $category_id Optional category ID to filter by
   * @param int $limit Number of links to return
   * @return array Array of matching links
   */
  public function find_relevant_internal_links($focus_keyword, $category_id = null, $limit = 1)
  {
    global $wpdb;

    // Try exact focus keyword match first
    if ($category_id) {
      // With category filter
      $exact_match = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT DISTINCT il.*, tr.term_taxonomy_id as category_id 
          FROM {$this->internal_links_table} il
          JOIN {$wpdb->posts} p ON il.post_id = p.ID
          LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
          LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
          WHERE p.post_status = 'publish'
          AND tt.taxonomy = 'category'
          AND tt.term_id = %d
          AND (
            il.focus_keyword = %s 
            OR il.title LIKE %s 
            OR il.tags LIKE %s
          )
          ORDER BY RAND()
          LIMIT %d",
          $category_id,
          $focus_keyword,
          '%' . $wpdb->esc_like($focus_keyword) . '%',
          '%' . $wpdb->esc_like($focus_keyword) . '%',
          $limit
        )
      );
    } else {
      // Without category filter
      $exact_match = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT DISTINCT il.*, tr.term_taxonomy_id as category_id 
          FROM {$this->internal_links_table} il
          JOIN {$wpdb->posts} p ON il.post_id = p.ID
          LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
          LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
          WHERE p.post_status = 'publish'
          AND tt.taxonomy = 'category'
          AND (
            il.focus_keyword = %s 
            OR il.title LIKE %s 
            OR il.tags LIKE %s
          )
          ORDER BY RAND()
          LIMIT %d",
          $focus_keyword,
          '%' . $wpdb->esc_like($focus_keyword) . '%',
          '%' . $wpdb->esc_like($focus_keyword) . '%',
          $limit
        )
      );
    }

    if (!empty($exact_match)) {
      return $exact_match;
    }

    // Split focus keyword into individual words
    $keywords = array_filter(explode(' ', strtolower($focus_keyword)));

    // If no exact match and we have multiple keywords, try combinations
    if (count($keywords) > 1) {
      $combinations = $this->generate_word_combinations($keywords);

      foreach ($combinations as $combination) {
        if ($category_id) {
          $matches = $wpdb->get_results(
            $wpdb->prepare(
              "SELECT DISTINCT il.*, tr.term_taxonomy_id as category_id 
              FROM {$this->internal_links_table} il
              JOIN {$wpdb->posts} p ON il.post_id = p.ID
              LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
              LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
              WHERE p.post_status = 'publish'
              AND tt.taxonomy = 'category'
              AND tt.term_id = %d
              AND (
                il.focus_keyword LIKE %s
                OR il.title LIKE %s
                OR il.tags LIKE %s
              )
              ORDER BY RAND()
              LIMIT %d",
              $category_id,
              '%' . $wpdb->esc_like($combination) . '%',
              '%' . $wpdb->esc_like($combination) . '%',
              '%' . $wpdb->esc_like($combination) . '%',
              $limit
            )
          );
        } else {
          $matches = $wpdb->get_results(
            $wpdb->prepare(
              "SELECT DISTINCT il.*, tr.term_taxonomy_id as category_id 
              FROM {$this->internal_links_table} il
              JOIN {$wpdb->posts} p ON il.post_id = p.ID
              LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
              LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
              WHERE p.post_status = 'publish'
              AND tt.taxonomy = 'category'
              AND (
                il.focus_keyword LIKE %s
                OR il.title LIKE %s
                OR il.tags LIKE %s
              )
              ORDER BY RAND()
              LIMIT %d",
              '%' . $wpdb->esc_like($combination) . '%',
              '%' . $wpdb->esc_like($combination) . '%',
              '%' . $wpdb->esc_like($combination) . '%',
              $limit
            )
          );
        }

        if (!empty($matches)) {
          return $matches;
        }
      }
    }

    // If no matches found even with word combinations, try random post from same category
    if ($category_id) {
      $random_matches = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT DISTINCT il.*, tr.term_taxonomy_id as category_id 
          FROM {$this->internal_links_table} il
          JOIN {$wpdb->posts} p ON il.post_id = p.ID
          LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
          LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
          WHERE p.post_status = 'publish'
          AND tt.taxonomy = 'category'
          AND tt.term_id = %d
          ORDER BY RAND() 
          LIMIT %d",
          $category_id,
          $limit
        )
      );

      if (!empty($random_matches)) {
        return $random_matches;
      }
    }

    // If no matches found at all, return empty array
    return [];
  }

  /**
   * Generate word combinations from keywords for better matching
   *
   * @param array $words Words to generate combinations from
   * @return array Array of combinations
   */
  private function generate_word_combinations($words)
  {
    $combinations = [];
    $count = count($words);

    // Generate combinations of consecutive words
    for ($i = 0; $i < $count; $i++) {
      $combination = '';
      for ($j = $i; $j < $count; $j++) {
        $combination .= ($j > $i ? ' ' : '') . $words[$j];
        if (strlen($combination) > 2) { // Only use combinations longer than 2 characters
          $combinations[] = $combination;
        }
      }
    }

    // Sort by length (longest first) to prioritize longer matches
    usort($combinations, function ($a, $b) {
      return strlen($b) - strlen($a);
    });

    return $combinations;
  }

  /**
   * Get internal links data for API
   *
   * @param string $focus_keyword Focus keyword
   * @param int|null $category_id Optional category ID
   * @return array Array of internal links with url, title and summary
   */
  public function get_internal_links_for_api($focus_keyword, $category_id = null)
  {
    $internal_links = [];

    if (empty($focus_keyword)) {
      return $internal_links;
    }

    $relevant_links = $this->find_relevant_internal_links(
      $focus_keyword,
      $category_id,
      1 // Limit to 1 relevant link
    );

    if (!empty($relevant_links)) {
      $internal_links = array_map(function ($link) {
        return [
          'url' => $link->link,
          'title' => $link->title,
          'summary' => wp_trim_words(
            wp_strip_all_tags(get_post_field('post_content', $link->post_id)),
            30,
            '...'
          )
        ];
      }, $relevant_links);
    }

    return $internal_links;
  }

  /**
   * Refresh all internal links
   * Scans all published posts and updates the internal links table
   *
   * @return array Stats about the operation
   */
  public function refresh_internal_links()
  {
    global $wpdb;

    try {
      // Get all existing post IDs in the internal_links table
      $existing_post_ids = $wpdb->get_col("SELECT post_id FROM {$this->internal_links_table}");

      // Track operations
      $inserted_count = 0;
      $updated_count = 0;
      $deleted_count = 0;

      // Get all published posts
      $args = [
        'post_type' => 'post',
        'post_status' => 'publish',
        'posts_per_page' => -1
      ];

      $posts = get_posts($args);
      $current_post_ids = []; // Track posts that still exist

      if (!empty($posts)) {
        foreach ($posts as $post) {
          $current_post_ids[] = $post->ID;

          // Get focus keyword from SEO plugin if available
          $focus_keyword = '';
          $seo_plugin = get_option('content_craft_ai_user_settings', [])['seoPlugin'] ?? '';

          if ($seo_plugin === 'yoast') {
            $focus_keyword = get_post_meta($post->ID, '_yoast_wpseo_focuskw', true);
          } elseif ($seo_plugin === 'rankmath') {
            $focus_keyword = get_post_meta($post->ID, 'rank_math_focus_keyword', true);
          }

          // Get post categories for tags
          $categories = get_the_category($post->ID);
          $tags = [];

          if (!empty($categories)) {
            foreach ($categories as $category) {
              $tags[] = $category->slug;
            }
          }

          // Check if post already exists in the table
          $already_exists = in_array($post->ID, $existing_post_ids);

          // Store/update the link
          $result = $this->store_post_links($post->ID, $focus_keyword, $tags);
          if ($result) {
            if ($already_exists) {
              $updated_count++;
            } else {
              $inserted_count++;
            }
          }
        }
      }

      // Find and remove links for posts that no longer exist or are not published
      $posts_to_remove = array_diff($existing_post_ids, $current_post_ids);
      if (!empty($posts_to_remove)) {
        foreach ($posts_to_remove as $post_id) {
          $deleted = $wpdb->delete(
            $this->internal_links_table,
            ['post_id' => $post_id],
            ['%d']
          );
          if ($deleted) {
            $deleted_count++;
          }
        }
      }

      // Update last refresh time
      update_option('content_craft_ai_internal_links_updated', current_time('mysql'));

      $total_count = $inserted_count + $updated_count;

      return [
        'inserted_count' => $inserted_count,
        'updated_count' => $updated_count,
        'deleted_count' => $deleted_count,
        'total_count' => $total_count
      ];
    } catch (Exception $e) {
      // error_log('Content Craft AI: Error in refresh_internal_links - ' . $e->getMessage());
      return ['error' => $e->getMessage()];
    }
  }
}
