<?php
/**
 * Add transaction-level fields to the Points table.
 *
 * Adds: transaction_type, source, reference_key, balance_after, meta,
 * created_by, status columns plus indexes for deduplication and querying.
 *
 * @package Ugcify\Database\Migrations
 */

namespace Ugcify\Database\Migrations;

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

/**
 * AlterPointsTableAddTransactionFields migration class.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration; all queries use $wpdb->prepare(); table/column names are trusted internal constants.
 */
class AlterPointsTableAddTransactionFields {

	/**
	 * WordPress database instance.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Constructor.
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 */
	public function __construct( \wpdb $wpdb ) {
		$this->wpdb = $wpdb;
	}

	/**
	 * Run migration.
	 *
	 * Uses individual ALTER statements to safely add columns and indexes
	 * only when they do not already exist.
	 *
	 * @return void
	 */
	public function run(): void {
		$table = $this->wpdb->prefix . 'ugcify_points';

		// Ensure prerequisite columns from AddActionToPointsTable exist
		// (ADD COLUMN IF NOT EXISTS is MariaDB-only; MySQL 5.7/8.0 does not support it).
		$this->add_column_if_missing( $table, 'action', "VARCHAR(64) NOT NULL DEFAULT 'manual' AFTER points" );
		$this->add_column_if_missing( $table, 'description', "VARCHAR(255) NOT NULL DEFAULT '' AFTER action" );

		$this->add_column_if_missing( $table, 'transaction_type', "VARCHAR(50) NOT NULL DEFAULT 'earn' AFTER description" );
		$this->add_column_if_missing( $table, 'source', "VARCHAR(100) NOT NULL DEFAULT '' AFTER transaction_type" );
		$this->add_column_if_missing( $table, 'reference_key', "VARCHAR(191) NULL AFTER source" );
		$this->add_column_if_missing( $table, 'balance_after', "INT(11) NOT NULL DEFAULT 0 AFTER reference_key" );
		$this->add_column_if_missing( $table, 'meta', "TEXT NULL AFTER balance_after" );
		$this->add_column_if_missing( $table, 'created_by', "BIGINT(20) UNSIGNED NULL AFTER meta" );
		$this->add_column_if_missing( $table, 'status', "VARCHAR(20) NOT NULL DEFAULT 'completed' AFTER created_by" );

		// Add unique index on reference_key for duplicate prevention.
		$this->add_index_if_missing( $table, 'reference_key', 'reference_key', true );

		// Add index on source for filtering by event type.
		$this->add_index_if_missing( $table, 'source', 'source' );

		// Add index on created_at for time-range queries.
		$this->add_index_if_missing( $table, 'created_at', 'created_at' );

		// Backfill transaction_type and source for existing rows that predate this migration.
		$this->backfill_existing_rows( $table );
	}

	/**
	 * Add a column to a table if it does not already exist.
	 *
	 * @param string $table       Table name.
	 * @param string $column_name Column name.
	 * @param string $definition  Column definition (type, default, position).
	 */
	private function add_column_if_missing( string $table, string $column_name, string $definition ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration; values are prepared.
		$row = $this->wpdb->get_results(
			$this->wpdb->prepare(
				'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s',
				$this->wpdb->dbname,
				$table,
				$column_name
			)
		);

		if ( empty( $row ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL migration; table/column names are trusted internal constants.
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN {$column_name} {$definition}" );
		}
	}

	/**
	 * Add an index to a table if it does not already exist.
	 *
	 * @param string $table      Table name.
	 * @param string $index_name Index name.
	 * @param string $column     Column(s) to index.
	 * @param bool   $unique     Whether the index should be unique.
	 */
	private function add_index_if_missing( string $table, string $index_name, string $column, bool $unique = false ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration; values are prepared.
		$index = $this->wpdb->get_results(
			$this->wpdb->prepare(
				'SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME = %s',
				$this->wpdb->dbname,
				$table,
				$index_name
			)
		);

		if ( empty( $index ) ) {
			$type = $unique ? 'UNIQUE INDEX' : 'INDEX';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL migration; table/column names are trusted internal constants.
			$this->wpdb->query( "ALTER TABLE {$table} ADD {$type} {$index_name} ({$column})" );
		}
	}

	/**
	 * Backfill transaction_type and source on pre-existing rows.
	 *
	 * Maps the existing `action` column values to the new schema:
	 * - positive points with a known action → earn / review_approved or review_featured
	 * - negative points → redeem / redemption
	 * - everything else → earn / manual_adjustment
	 *
	 * @param string $table Table name.
	 */
	private function backfill_existing_rows( string $table ): void {
		// Only backfill rows that haven't been populated yet.
		$this->wpdb->query(
			"UPDATE {$table}
			SET transaction_type = CASE
				WHEN points < 0 THEN 'redeem'
				ELSE 'earn'
			END,
			source = CASE
				WHEN action = 'pts_featured_bonus' THEN 'review_featured'
				WHEN action IN ('pts_text_review','pts_review_attachment','pts_video') THEN 'review_approved'
				WHEN action = 'deduction' THEN 'redemption'
				ELSE 'manual_adjustment'
			END
			WHERE source = '' AND transaction_type = 'earn'"
		);

		// Generate reference_key for existing earn rows linked to a review.
		$this->wpdb->query(
			"UPDATE {$table}
			SET reference_key = CONCAT(source, ':', review_id)
			WHERE reference_key IS NULL AND review_id IS NOT NULL AND review_id > 0 AND source != ''"
		);
	}
}
