<?php
/**
 * Migration 026: Add RSS Calendar Columns
 *
 * Adds rss_feed_id and source_metadata columns to the editorial calendar table,
 * and adds 'rss' to the keyword_source ENUM. These columns enable RSS feed name,
 * source color, and feed tracking in the editorial calendar.
 *
 * @package Wisewand
 * @subpackage Database\Migrations
 * @since 2.8.2
 * @license GPL-2.0-or-later
 */

namespace Wisewand\Database\Migrations;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

/**
 * Migration 026 Class
 *
 * Adds rss_feed_id, source_metadata columns and 'rss' ENUM value
 * to the editorial calendar table for RSS calendar integration.
 *
 * @since 2.8.2
 */
class Migration_026_Add_RSS_Calendar_Columns {

	/**
	 * Run the migration
	 *
	 * Idempotent — safe to run multiple times.
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 * @return bool True on success, false on failure.
	 */
	public static function up( $wpdb ) {
		$table_name = $wpdb->prefix . 'wisewand_editorial_calendar';

		// 1. Add rss_feed_id column if not exists
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$rss_feed_id_exists = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW COLUMNS FROM %i LIKE %s',
				$table_name,
				'rss_feed_id'
			)
		);

		if ( empty( $rss_feed_id_exists ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$result = $wpdb->query(
				$wpdb->prepare(
					'ALTER TABLE %i ADD COLUMN rss_feed_id bigint(20) unsigned DEFAULT NULL COMMENT %s AFTER rss_article_id',
					$table_name,
					'Link to RSS feed for calendar tracking'
				)
			);

			if ( false === $result ) {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					error_log( 'Wisewand Migration 026: Failed to add rss_feed_id column — ' . $wpdb->last_error );
				}
				return false;
			}

			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 026: Added rss_feed_id column' );
			}
		}

		// 2. Add source_metadata column if not exists
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$source_metadata_exists = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW COLUMNS FROM %i LIKE %s',
				$table_name,
				'source_metadata'
			)
		);

		if ( empty( $source_metadata_exists ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$result = $wpdb->query(
				$wpdb->prepare(
					'ALTER TABLE %i ADD COLUMN source_metadata longtext DEFAULT NULL COMMENT %s AFTER rss_feed_id',
					$table_name,
					'JSON metadata from RSS source (feed_name, feed_color)'
				)
			);

			if ( false === $result ) {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					error_log( 'Wisewand Migration 026: Failed to add source_metadata column — ' . $wpdb->last_error );
				}
				return false;
			}

			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 026: Added source_metadata column' );
			}
		}

		// 3. Check if keyword_source ENUM includes 'rss'
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$column_info = $wpdb->get_row(
			$wpdb->prepare(
				'SHOW COLUMNS FROM %i LIKE %s',
				$table_name,
				'keyword_source'
			)
		);

		if ( $column_info && false === strpos( $column_info->Type, "'rss'" ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$result = $wpdb->query(
				$wpdb->prepare(
					"ALTER TABLE %i MODIFY COLUMN keyword_source enum('current_ranking','manual','discovery','saved','rss') DEFAULT 'manual'",
					$table_name
				)
			);

			if ( false === $result ) {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					error_log( 'Wisewand Migration 026: Failed to modify keyword_source ENUM — ' . $wpdb->last_error );
				}
				return false;
			}

			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 026: Added rss value to keyword_source ENUM' );
			}
		}

		// 4. Add index on rss_feed_id if not exists
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
		$index_exists = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW INDEX FROM %i WHERE Key_name = %s',
				$table_name,
				'rss_feed_id'
			)
		);

		if ( empty( $index_exists ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
			$result = $wpdb->query(
				$wpdb->prepare(
					'ALTER TABLE %i ADD KEY rss_feed_id (rss_feed_id)',
					$table_name
				)
			);

			if ( false === $result ) {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					error_log( 'Wisewand Migration 026: Failed to add rss_feed_id index — ' . $wpdb->last_error );
				}
				// Non-fatal — index is nice to have but not critical
			} else {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					error_log( 'Wisewand Migration 026: Added rss_feed_id index' );
				}
			}
		}

		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 026: Completed — RSS calendar columns ready' );
		}

		return true;
	}

	/**
	 * Rollback the migration
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 * @return bool True on success.
	 */
	public static function down( $wpdb ) {
		return true;
	}
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
