<?php
/**
 * This file is part of the Wisewand WordPress plugin.
 *
 * @package Wisewand
 * @license GPL-2.0-or-later
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 */

/**
 * Migration 023: Add RSS Article ID to Calendar
 *
 * Adds rss_article_id column to editorial calendar table to fix SQL errors
 * when linking RSS articles to calendar entries.
 *
 * Fixes error: Unknown column 'rss_article_id' in 'WHERE' clause
 *
 * @package Wisewand
 * @subpackage Database\Migrations
 * @since 2.5.0
 */

namespace Wisewand\Database\Migrations;

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

/**
 * Migration 023 Class
 *
 * @since 2.5.0
 */
class Migration_023_Add_RSS_Article_ID_To_Calendar {

	/**
	 * Run the migration
	 *
	 * Adds rss_article_id column and index to editorial calendar table.
	 * Migration is 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';

		// Check if table exists
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table_exists = $wpdb->get_var(
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
		);

		if ( ! $table_exists ) {
			// Table doesn't exist - fresh install will create it with column
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023: Table does not exist yet, skipping migration' );
			}
			return true;
		}

		// Check if column already exists
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = %s
				AND TABLE_NAME = %s
				AND COLUMN_NAME = %s",
				DB_NAME,
				$table_name,
				'rss_article_id'
			)
		);

		if ( ! empty( $column_exists ) ) {
			// Column already exists - migration already completed
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023: Column rss_article_id already exists, skipping' );
			}
			return true;
		}

		// Migration needed - add column
		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 023: Adding rss_article_id column to editorial calendar' );
		}

		// Add column after keyword_source (related fields together)
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$result = $wpdb->query(
			"ALTER TABLE `{$table_name}`
			ADD COLUMN rss_article_id bigint(20) unsigned DEFAULT NULL
			COMMENT 'Link to RSS article if source is RSS'
			AFTER keyword_source"
		);

		// Check for errors
		if ( false === $result ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023: Failed to add column - ' . $wpdb->last_error );
			}
			return false;
		}

		// Add index for query performance (used in WHERE clauses)
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$index_result = $wpdb->query(
			"ALTER TABLE `{$table_name}` ADD KEY rss_article_id (rss_article_id)"
		);

		// Index creation failure is not fatal (column still added)
		if ( false === $index_result ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023: Column added but index creation failed - ' . $wpdb->last_error );
			}
			// Still return true - column is added which fixes the error
			return true;
		}

		// Success
		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 023: Successfully added rss_article_id column and index' );
		}

		return true;
	}

	/**
	 * Rollback the migration
	 *
	 * Removes rss_article_id column and index from editorial calendar table.
	 * Use with caution - will lose existing RSS article links.
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 * @return bool True on success, false on failure.
	 */
	public static function down( $wpdb ) {
		$table_name = $wpdb->prefix . 'wisewand_editorial_calendar';

		// Check if column exists before attempting removal
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = %s
				AND TABLE_NAME = %s
				AND COLUMN_NAME = %s",
				DB_NAME,
				$table_name,
				'rss_article_id'
			)
		);

		if ( empty( $column_exists ) ) {
			// Column doesn't exist - nothing to rollback
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023 Rollback: Column does not exist, nothing to remove' );
			}
			return true;
		}

		// Remove column (index will be dropped automatically)
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$result = $wpdb->query(
			"ALTER TABLE `{$table_name}` DROP COLUMN rss_article_id"
		);

		if ( false === $result ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 023 Rollback: Failed to remove column - ' . $wpdb->last_error );
			}
			return false;
		}

		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 023 Rollback: Successfully removed rss_article_id column' );
		}

		return true;
	}
}
