<?php
/**
 * Migration 024: Add 'imported_scheduled' to wordpress_status ENUM
 *
 * Adds 'imported_scheduled' status value to editorial calendar wordpress_status ENUM.
 * Fixes calendar display issue where scheduled imported articles show blue instead of green.
 *
 * Root cause: Code uses 'imported_scheduled' since Nov 2, 2025, but database ENUM was missing this value.
 * Result: MySQL silently rejected value, stored 'scheduled' instead, calendar showed blue not green.
 *
 * @package Wisewand
 * @subpackage Database\Migrations
 * @since 2.7.1
 * @license GPL-2.0-or-later
 */

namespace Wisewand\Database\Migrations;

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

/**
 * Migration 024 Class
 *
 * Adds 'imported_scheduled' to wordpress_status ENUM and fixes existing mismatched entries.
 *
 * @since 2.7.1
 */
class Migration_024_Add_Imported_Scheduled_Status {

	/**
	 * Run the migration
	 *
	 * Adds 'imported_scheduled' to wordpress_status ENUM and updates existing entries.
	 * 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 correct ENUM
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 024: Table does not exist yet, skipping migration' );
			}
			return true;
		}

		// Check if 'imported_scheduled' already in ENUM
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$column_info = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = %s
				AND TABLE_NAME = %s
				AND COLUMN_NAME = %s",
				DB_NAME,
				$table_name,
				'wordpress_status'
			),
			ARRAY_A
		);

		if ( ! $column_info ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 024: wordpress_status column not found' );
			}
			return false;
		}

		// Check if 'imported_scheduled' already in ENUM
		if ( strpos( $column_info['COLUMN_TYPE'], "'imported_scheduled'" ) !== false ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 024: imported_scheduled already in ENUM, skipping' );
			}
			return true;
		}

		// Migration needed - add 'imported_scheduled' to ENUM
		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 024: Adding imported_scheduled to wordpress_status ENUM' );
		}

		// Update ENUM to include 'imported_scheduled'
		// 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}`
			MODIFY COLUMN wordpress_status
			enum('scheduled','importing','imported_draft','imported_published','imported_scheduled','failed')
			DEFAULT 'scheduled'
			COMMENT 'WordPress post import status with sub-statuses'"
		);

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

		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 024: Successfully added imported_scheduled to ENUM' );
		}

		// Fix existing entries with incorrect status
		// Criteria: wordpress_status = 'scheduled' BUT wordpress_post_id IS NOT NULL AND published_at IS NOT NULL
		// These should be 'imported_scheduled' not 'scheduled'
		// Transaction wraps only DML — ALTER TABLE above causes implicit commit in MySQL.
		$wpdb->query( 'START TRANSACTION' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$updated_count = $wpdb->query(
			"UPDATE `{$table_name}`
			SET wordpress_status = 'imported_scheduled'
			WHERE wordpress_status = 'scheduled'
			AND wordpress_post_id IS NOT NULL
			AND published_at IS NOT NULL"
		);

		if ( $wpdb->last_error ) {
			$wpdb->query( 'ROLLBACK' );
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'Wisewand Migration 024: ' . $wpdb->last_error );
			}
			return false;
		}

		$wpdb->query( 'COMMIT' );

		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log(
				sprintf(
					'Wisewand Migration 024: Updated %d calendar entries from scheduled to imported_scheduled',
					$updated_count
				)
			);
		}

		return true;
	}

	/**
	 * Rollback the migration
	 *
	 * Removes 'imported_scheduled' from ENUM and reverts entries to 'scheduled'.
	 * Use with caution - will lose imported_scheduled status distinction.
	 *
	 * @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';

		// Revert entries to 'scheduled'
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"UPDATE `{$table_name}`
			SET wordpress_status = 'scheduled'
			WHERE wordpress_status = 'imported_scheduled'"
		);

		// Remove 'imported_scheduled' from ENUM (back to original 5 values)
		// 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}`
			MODIFY COLUMN wordpress_status
			enum('scheduled','importing','imported_draft','imported_published','failed')
			DEFAULT 'scheduled'"
		);

		if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
			error_log( 'Wisewand Migration 024 Rollback: Removed imported_scheduled from ENUM' );
		}

		return $result !== false;
	}
}
