<?php

declare(strict_types=1);

namespace KimaAI\Models;

defined('ABSPATH') || exit;

class LinkSuggestionModel {
  use HasSchema;

  /**
   * The table name.
   *
   * @var string
   */
  public const TABLE = 'kimaai_link_suggestions';

  /**
   * The table primary key.
   *
   * @var string
   */
  public const PRIMARY_KEY = 'id';

  /**
   * Columns.
   *
   * @var array
   */
  public const COLUMNS = [
    'id' => 'BIGINT(20) NOT NULL AUTO_INCREMENT',
    'object_id' => 'BIGINT(20) NOT NULL',
    'incoming_link_count' => 'INT(11) NULL',
    'outgoing_link_count' => 'INT(11) NULL', // internal outgoing count
    'outgoing_external_link_count' => 'INT(11) NULL',
    'incoming_internal_ids' => 'LONGTEXT NULL', // JSON array (unique post IDs that link TO this post)
    'outgoing_internal_ids' => 'LONGTEXT NULL', // JSON array (unique post IDs this post links TO)
    'outgoing_external_urls' => 'LONGTEXT NULL', // JSON array (unique external URLs this post links TO)
    'suggestion_ids' => 'LONGTEXT NULL', // JSON array of post IDs (top-7 suggestions)
    'last_updated' => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
  ];

  /**
   * Indexes.
   *
   * @var string[]
   */
  public const INDEXES = [
    'UNIQUE KEY object_id (object_id)',
    'KEY last_updated (last_updated)',
  ];

  /**
   * Returns the fully-qualified table name.
   *
   * @return string
   */
  public function table(): string {
    global $wpdb;
    return $wpdb->prefix . self::TABLE;
  }

  /**
   * Upserts a link suggestion row for the given object.
   *
   * @param int $object_id The post/object ID.
   * @param int|null $incoming_link_count Incoming link count.
   * @param int|null $outgoing_internal_link_count Outgoing internal link count.
   * @param int|null $outgoing_external_link_count Outgoing external link count.
   * @param array $incoming_internal_ids Incoming internal post IDs.
   * @param array $outgoing_internal_ids Outgoing internal post IDs.
   * @param array $outgoing_external_urls Outgoing external URLs.
   * @param array $suggestion_ids Suggested post IDs.
   * @return void
   */
  public function upsert(
    int $object_id,
    ?int $incoming_link_count,
    ?int $outgoing_internal_link_count,
    ?int $outgoing_external_link_count,
    array $incoming_internal_ids,
    array $outgoing_internal_ids,
    array $outgoing_external_urls,
    array $suggestion_ids
  ): void {
    global $wpdb;

    $table = $this->table();

    $to_json_ids = static function (array $ids): string {
      $ids = array_values(array_unique(array_map('intval', $ids)));
      $ids = array_values(array_filter($ids, fn($id) => $id > 0));
      return wp_json_encode($ids);
    };

    $to_json_urls = static function (array $urls): string {
      $urls = array_values(array_unique(array_filter(array_map('strval', $urls), fn($url) => $url !== '')));
      return wp_json_encode($urls);
    };

    $data = [
      'object_id' => $object_id,
      'incoming_link_count' => $incoming_link_count,
      'outgoing_link_count' => $outgoing_internal_link_count,
      'outgoing_external_link_count' => $outgoing_external_link_count,
      'incoming_internal_ids' => $to_json_ids($incoming_internal_ids),
      'outgoing_internal_ids' => $to_json_ids($outgoing_internal_ids),
      // ✅ store as JSON (matches comment + read logic)
      'outgoing_external_urls' => $to_json_urls($outgoing_external_urls),
      'suggestion_ids' => $to_json_ids(array_slice($suggestion_ids, 0, 7)),
      'last_updated' => current_time('mysql'),
    ];

    $format = ['%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s'];

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
    $wpdb->replace($table, $data, $format);
  }

  /**
   * Returns a single link suggestion row for the given object ID, or null if not found.
   *
   * @param int $object_id The post/object ID.
   * @return array|null
   */
  public function get(int $object_id): ?array {
    global $wpdb;

    $table = $this->table();

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
    $row = $wpdb->get_row(
      // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
      $wpdb->prepare("SELECT * FROM {$table} WHERE object_id = %d LIMIT 1", $object_id),
      ARRAY_A
    );

    if (!$row) {
      return null;
    }

    // Decode JSON arrays; tolerate legacy serialized rows for outgoing_external_urls.
    foreach (['incoming_internal_ids', 'outgoing_internal_ids', 'suggestion_ids'] as $k) {
      $row[$k] = isset($row[$k]) ? (json_decode((string) $row[$k], true) ?: []) : [];
    }

    // outgoing_external_urls: JSON now, but allow fallback for old serialized data.
    $urls_raw = (string) ($row['outgoing_external_urls'] ?? '');
    $urls = $urls_raw !== '' ? json_decode($urls_raw, true) : null;
    if (is_array($urls)) {
      $row['outgoing_external_urls'] = $urls;
    }
    else {
      $legacy = $urls_raw !== '' ? maybe_unserialize($urls_raw) : [];
      $row['outgoing_external_urls'] = is_array($legacy) ? array_values($legacy) : [];
    }

    return $row;
  }
}
