<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * ACSIL_Database — カスタムテーブルの作成・CRUD・集計クエリ
 */
class ACSIL_Database {

	private string $table_name;

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

	public function get_table_name(): string {
		return $this->table_name;
	}

	// ── テーブル作成・削除 ────────────────────────────────────────────────

	public function create_table(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$this->table_name} (
			id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			source_post_id  BIGINT UNSIGNED NOT NULL,
			target_post_id  BIGINT UNSIGNED NOT NULL,
			anchor_text     VARCHAR(500)    NOT NULL DEFAULT '',
			link_url        VARCHAR(2083)   NOT NULL DEFAULT '',
			created_at      DATETIME        NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at      DATETIME        NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			INDEX idx_source (source_post_id),
			INDEX idx_target (target_post_id),
			UNIQUE KEY unique_link (source_post_id, target_post_id, anchor_text(191))
		) {$charset_collate};";

		dbDelta( $sql );
	}

	public function drop_table(): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Drops this plugin's own table; the name is $wpdb->prefix . 'acsil_links'.
		$wpdb->query( "DROP TABLE IF EXISTS {$this->table_name}" );
	}

	// ── リンクデータ保存（DELETE → INSERT） ──────────────────────────────

	/**
	 * @param int   $source_post_id スキャン元記事ID
	 * @param array $links          [ ['target_id'=>int, 'anchor'=>string, 'url'=>string], ... ]
	 */
	public function save_links( int $source_post_id, array $links ): void {
		global $wpdb;
		$now = current_time( 'mysql' );

		// 既存データを削除
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->table_name,
			array( 'source_post_id' => $source_post_id ),
			array( '%d' )
		);

		if ( empty( $links ) ) {
			return;
		}

		foreach ( $links as $link ) {
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$this->table_name,
				array(
					'source_post_id' => $source_post_id,
					'target_post_id' => (int) $link['target_id'],
					'anchor_text'    => substr( (string) $link['anchor'], 0, 500 ),
					'link_url'       => substr( (string) $link['url'], 0, 2083 ),
					'created_at'     => $now,
					'updated_at'     => $now,
				),
				array( '%d', '%d', '%s', '%s', '%s', '%s' )
			);
		}
	}

	// ── 投稿削除時のクリーンアップ ────────────────────────────────────────

	public function delete_post_links( int $post_id ): void {
		global $wpdb;

		// source 側を削除
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->table_name,
			array( 'source_post_id' => $post_id ),
			array( '%d' )
		);

		// target 側を削除
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->table_name,
			array( 'target_post_id' => $post_id ),
			array( '%d' )
		);

		// ダッシュボードキャッシュをクリア
		delete_transient( 'acsil_dashboard_data' );
	}

	// ── 発リンク・被リンク取得 ────────────────────────────────────────────

	// The queries below interpolate only the plugin's own table name
	// ($wpdb->prefix . 'acsil_links'), core table names from $wpdb, and SQL
	// fragments that were built with $wpdb->prepare() or are constant strings.
	// Every user supplied value is bound through $wpdb->prepare().
	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- See the comment above.

	public function get_outgoing_links( int $post_id ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT l.target_post_id, l.anchor_text, l.link_url, p.post_title
				 FROM {$this->table_name} l
				 INNER JOIN {$wpdb->posts} p ON p.ID = l.target_post_id
				 WHERE l.source_post_id = %d
				 ORDER BY p.post_title ASC",
				$post_id
			),
			ARRAY_A
		);
	}

	public function get_incoming_links( int $post_id ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT l.source_post_id, l.anchor_text, l.link_url, p.post_title
				 FROM {$this->table_name} l
				 INNER JOIN {$wpdb->posts} p ON p.ID = l.source_post_id
				 WHERE l.target_post_id = %d
				 ORDER BY p.post_title ASC",
				$post_id
			),
			ARRAY_A
		);
	}

	// ── リンクマップ用データ取得 ──────────────────────────────────────────

	public function get_all_links( array $args = array() ): array {
		global $wpdb;

		// ── ステータス条件 ──────────────────────────────────────────────────
		$status_val = ( ! empty( $args['status'] ) && 'any' !== $args['status'] )
			? $args['status']
			: null;

		if ( $status_val ) {
			// エッジ用（ps = ソース, pt = ターゲット）
			$src_status_sql = $wpdb->prepare( ' AND ps.post_status = %s', $status_val );
			$tgt_status_sql = $wpdb->prepare( ' AND pt.post_status = %s', $status_val );
			// ノード用（p エイリアス）
			$node_status_sql = $wpdb->prepare( ' AND p.post_status = %s', $status_val );
		} else {
			$src_status_sql  = " AND ps.post_status IN ('publish','draft')";
			$tgt_status_sql  = " AND pt.post_status IN ('publish','draft')";
			$node_status_sql = " AND p.post_status IN ('publish','draft')";
		}

		// ── カテゴリー条件 ─────────────────────────────────────────────────
		$edge_category_sql = '';
		$node_category_sql = '';
		if ( ! empty( $args['category'] ) ) {
			$cat_id            = absint( $args['category'] );
			$edge_category_sql = $wpdb->prepare(
				" AND EXISTS (
					SELECT 1 FROM {$wpdb->term_relationships} tr
					INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
					WHERE tr.object_id = ps.ID AND tt.taxonomy = 'category' AND tt.term_id = %d
				)",
				$cat_id
			);
			$node_category_sql = $wpdb->prepare(
				" AND EXISTS (
					SELECT 1 FROM {$wpdb->term_relationships} tr
					INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
					WHERE tr.object_id = p.ID AND tt.taxonomy = 'category' AND tt.term_id = %d
				)",
				$cat_id
			);
		}

		// ── エッジ（ソース・ターゲット両方の有効性をチェック） ────────────
		// ターゲット側も INNER JOIN して「無効な投稿を指すエッジ」を除外する。
		// これにより D3 forceLink の "node not found" エラーを防ぐ。
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$edges = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT DISTINCT l.source_post_id AS source, l.target_post_id AS target, l.anchor_text
			 FROM {$this->table_name} l
			 INNER JOIN {$wpdb->posts} ps ON ps.ID = l.source_post_id
			 INNER JOIN {$wpdb->posts} pt ON pt.ID = l.target_post_id
			 WHERE 1=1 {$src_status_sql} {$tgt_status_sql} {$edge_category_sql}",
			ARRAY_A
		);

		// ── ノード ──────────────────────────────────────────────────────────
		$settings  = ACSIL_Core::get_settings();
		$limit     = ! empty( $args['limit'] ) ? absint( $args['limit'] ) : absint( $settings['default_limit'] );
		$limit_sql = $limit > 0 ? $wpdb->prepare( ' LIMIT %d', $limit ) : '';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$posts = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT p.ID, p.post_title, p.post_status, p.post_date
			 FROM {$wpdb->posts} p
			 WHERE p.post_type IN ('post','page') {$node_status_sql} {$node_category_sql}
			 ORDER BY p.ID ASC {$limit_sql}",
			ARRAY_A
		);

		return array(
			'edges' => $edges ?: array(),
			'posts' => $posts ?: array(),
		);
	}

	// ── 全投稿のリンク数を一括取得（N+1問題回避） ─────────────────────────

	public function get_all_link_counts(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$outgoing = $wpdb->get_results(
			"SELECT source_post_id AS post_id, COUNT(*) AS cnt
			 FROM {$this->table_name}
			 GROUP BY source_post_id",
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$incoming = $wpdb->get_results(
			"SELECT target_post_id AS post_id, COUNT(*) AS cnt
			 FROM {$this->table_name}
			 GROUP BY target_post_id",
			ARRAY_A
		);

		$result = array();

		foreach ( $outgoing as $row ) {
			$result[ (int) $row['post_id'] ]['outgoing'] = (int) $row['cnt'];
		}
		foreach ( $incoming as $row ) {
			$id = (int) $row['post_id'];
			if ( ! isset( $result[ $id ] ) ) {
				$result[ $id ] = array();
			}
			$result[ $id ]['incoming'] = (int) $row['cnt'];
		}

		return $result;
	}

	// ── 孤立記事取得 ─────────────────────────────────────────────────────

	/**
	 * @param string $type  'no_incoming' | 'no_outgoing'
	 * @param array  $post_types
	 */
	public function get_orphan_posts( string $type, array $post_types = array( 'post', 'page' ) ): array {
		global $wpdb;

		$post_types_escaped = implode(
			',',
			array_map(
				function ( $pt ) use ( $wpdb ) {
					return $wpdb->prepare( '%s', $pt ); },
				$post_types
			)
		);

		if ( 'no_incoming' === $type ) {
			$subquery = "SELECT DISTINCT target_post_id FROM {$this->table_name}";
			$col      = 'ID';
		} else {
			$subquery = "SELECT DISTINCT source_post_id FROM {$this->table_name}";
			$col      = 'ID';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results(
			"SELECT p.ID, p.post_title, p.post_status, p.post_date, p.post_type
			 FROM {$wpdb->posts} p
			 WHERE p.post_type IN ({$post_types_escaped})
			   AND p.post_status IN ('publish','draft')
			   AND p.ID NOT IN ({$subquery})
			 ORDER BY p.post_date DESC",
			ARRAY_A
		) ?: array();
	}

	// ── ダッシュボード集計 ────────────────────────────────────────────────

	public function get_dashboard_stats(): array {
		$cached = get_transient( 'acsil_dashboard_data' );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$settings   = ACSIL_Core::get_settings();
		$post_types = $settings['post_types'];

		$post_types_escaped = implode(
			',',
			array_map(
				function ( $pt ) use ( $wpdb ) {
					return $wpdb->prepare( '%s', $pt ); },
				$post_types
			)
		);

		// 総記事数
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$total_posts = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts}
			 WHERE post_type IN ({$post_types_escaped})
			   AND post_status IN ('publish','draft')"
		);

		// 内部リンク総数
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$total_links = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->table_name}"
		);

		// 孤立記事数（被リンクなし）
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$orphan_no_incoming = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 WHERE p.post_type IN ({$post_types_escaped})
			   AND p.post_status IN ('publish','draft')
			   AND p.ID NOT IN (SELECT DISTINCT target_post_id FROM {$this->table_name})"
		);

		// 発リンクなし記事数
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$orphan_no_outgoing = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 WHERE p.post_type IN ({$post_types_escaped})
			   AND p.post_status IN ('publish','draft')
			   AND p.ID NOT IN (SELECT DISTINCT source_post_id FROM {$this->table_name})"
		);

		// 被リンク TOP3
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$top3 = $wpdb->get_results(
			"SELECT l.target_post_id AS post_id, p.post_title, COUNT(*) AS cnt
			 FROM {$this->table_name} l
			 INNER JOIN {$wpdb->posts} p ON p.ID = l.target_post_id
			 GROUP BY l.target_post_id
			 ORDER BY cnt DESC
			 LIMIT 3",
			ARRAY_A
		) ?: array();

		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// 平均
		$avg = $total_posts > 0 ? round( $total_links / $total_posts, 1 ) : 0;

		$stats = array(
			'total_posts'        => $total_posts,
			'total_links'        => $total_links,
			'avg_links'          => $avg,
			'orphan_no_incoming' => $orphan_no_incoming,
			'orphan_no_outgoing' => $orphan_no_outgoing,
			'top3'               => $top3,
			'last_scan'          => get_option( 'acsil_last_scan_time', '' ),
		);

		set_transient( 'acsil_dashboard_data', $stats, HOUR_IN_SECONDS );

		return $stats;
	}

	// ── Ajax: データ全削除 ────────────────────────────────────────────────

	public function ajax_delete_data(): void {
		check_ajax_referer( 'acsil_delete_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Sorry, you are not allowed to access this page.', 'acs-internal-links' ) ) );
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->query( "TRUNCATE TABLE {$this->table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		delete_transient( 'acsil_dashboard_data' );

		wp_send_json_success( array( 'message' => __( 'Link data deleted.', 'acs-internal-links' ) ) );
	}
}