<?php

namespace GemBoards;

use GemBoards\entities\BoardVisit;
use GemBoards\Entities\Role;
use GemBoards\Settings\Base;
use GemBoards\Utils\Helper;

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

class Migration {

	public static function run_migration( $gemboards_version ) {
		$self = new self();
		$self->migrate_0_1_2( $gemboards_version );
		$self->migrate_1_0_1( $gemboards_version );
		$self->migrate_1_0_2( $gemboards_version );
		$self->migrate_1_0_5( $gemboards_version );
	}

	public function migrate_0_1_2( $gemboards_version ) {
		if ( version_compare( $gemboards_version, '0.1.2', '<' ) ) {
			global $wpdb;
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Clearing cach through entity class's `clear_cache` method.
			$wpdb->query(
				"UPDATE {$wpdb->prefix}gemboards_roles SET `role` = 'manager' WHERE `role` = 'admin'"
			);
			// phpcs:enable
			( new Role() )->clear_cache();

			Base::save_settings();
		}
	}

	public function migrate_1_0_1( $gemboards_version ) {
		if ( version_compare( $gemboards_version, '1.0.1', '<' ) ) {
			global $wpdb;
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_results(
				"SELECT user_id, meta_value FROM $wpdb->usermeta WHERE meta_key = 'gemboards_recently_open'"
			);

			$board_data = [];
			foreach ( $results as $result ) {
				$meta_value = maybe_unserialize( $result->meta_value );
				$meta_value = is_array( $meta_value ) ? array_reverse($meta_value) : [];
				foreach ( $meta_value as $item ) {
					$board_data[] = [
						'user_id'  => absint( $result->user_id ),
						'board_id' => $item['board_id'],
					];
				}
			}

			foreach ( $board_data as $board_datum ) {
				$board_visit = new BoardVisit();
				$board_visit->set_user_id( $board_datum['user_id'] );
				$board_visit->set_board_id( $board_datum['board_id'] );
				$board_visit->save();
			}

			$wpdb->query( "DELETE FROM $wpdb->usermeta WHERE meta_key = 'gemboards_recently_open'" );
			// phpcs:enable

			$this->cleanup_invalid_priorities();
		}
	}

	public function migrate_1_0_2( $gemboards_version ) {
		if ( version_compare( $gemboards_version, '1.0.2', '<' ) ) {
			$this->cleanup_invalid_priorities();
		}
	}

	public function migrate_1_0_5( $gemboards_version ) {
		if ( version_compare( $gemboards_version, '1.0.5', '<' ) ) {
			$gemboards_settings = json_decode( get_option( GEMBOARDS_SETTINGS_NAME ), true );
			if ( ! $gemboards_settings || ! isset( $gemboards_settings['notifications']['status_changes'] ) ) {
				return;
			}
			$gemboards_settings['notifications']['stage_changes'] = $gemboards_settings['notifications']['status_changes'];
			unset($gemboards_settings['notifications']['status_changes']);
			update_option( GEMBOARDS_SETTINGS_NAME, wp_json_encode( $gemboards_settings ) );
		}
	}

	private function cleanup_invalid_priorities() {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( 'START TRANSACTION' );

		$queries = [
			// 1) Delete post → term relationships (priority only)
			"
			    DELETE tr
			    FROM {$wpdb->term_relationships} AS tr
			    INNER JOIN {$wpdb->term_taxonomy} AS tt
			        ON tt.term_taxonomy_id = tr.term_taxonomy_id
			    INNER JOIN {$wpdb->terms} AS t
			        ON t.term_id = tt.term_id
			    WHERE
			        tt.taxonomy = 'gemboards_priority'
			        AND t.slug NOT IN ('high', 'medium', 'low')
			",

			// 2) Delete term_taxonomy rows (priority only)
			"
			    DELETE tt
			    FROM {$wpdb->term_taxonomy} AS tt
			    INNER JOIN {$wpdb->terms} AS t
			        ON t.term_id = tt.term_id
			    WHERE
			        tt.taxonomy = 'gemboards_priority'
			        AND t.slug NOT IN ('high', 'medium', 'low')
			 ",

			// 3) Delete terms
			"
			    DELETE t
			    FROM {$wpdb->terms} AS t
			    LEFT JOIN {$wpdb->term_taxonomy} AS tt
			        ON tt.term_id = t.term_id
			    WHERE
			        tt.term_id IS NULL
			        AND t.slug NOT IN ('high', 'medium', 'low')
			",

			// 4) Recalculate counts (priority only)
			"
			    UPDATE {$wpdb->term_taxonomy} AS tt
			    SET tt.count = (
			        SELECT COUNT(*)
			        FROM {$wpdb->term_relationships} AS tr
			        WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
			    )
			    WHERE tt.taxonomy = 'gemboards_priority'
			",
		];

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $queries as $sql ) {
			if ( false === $wpdb->query( $sql ) ) {
				$wpdb->query( 'ROLLBACK' );

				Helper::error_log(
					'GemBoards priority cleanup failed: ' . $wpdb->last_error
				);

				break;
			}
		}

		$wpdb->query( 'COMMIT' );
		// phpcs:enable
	}

}
