<?php

declare(strict_types=1);

namespace Australcode\Rewards\Infrastructure\Database\Migrations;

use Australcode\Rewards\Infrastructure\Database\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * Creates the two foundational tables:
 *
 *  - wp_acrwd_balances: denormalized per-user balance for O(1) reads
 *    (replaces YITH's 17-usermeta-per-user model that doesn't scale).
 *  - wp_acrwd_transactions: immutable ledger of every points movement,
 *    indexed by (user_id, created_at) for history and (expires_at, status)
 *    for the expiry sweep.
 *
 * NOTE: the SQL deliberately follows dbDelta's strict format — fields indented
 * with single spaces (NOT tabs), lowercase types with explicit sizes, and two
 * spaces after "PRIMARY KEY". dbDelta silently fails to create tables when this
 * format is off.
 */
final class M0001CreateCoreTables implements Migration {

	public function version(): int {
		return 1;
	}

	public function description(): string {
		return 'Create core tables: balances + transactions';
	}

	public function up(): void {
		global $wpdb;
		$charset      = $wpdb->get_charset_collate();
		$balances     = Schema::table_name( Schema::TABLE_BALANCES );
		$transactions = Schema::table_name( Schema::TABLE_TRANSACTIONS );

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$balances_sql = "CREATE TABLE {$balances} (
 user_id bigint(20) unsigned NOT NULL,
 points_balance bigint(20) NOT NULL DEFAULT 0,
 lifetime_earned bigint(20) NOT NULL DEFAULT 0,
 lifetime_redeemed bigint(20) NOT NULL DEFAULT 0,
 tier_id varchar(64) NOT NULL DEFAULT '',
 updated_at datetime NOT NULL,
 PRIMARY KEY  (user_id),
 KEY tier_id (tier_id)
) {$charset};";

		$transactions_sql = "CREATE TABLE {$transactions} (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 user_id bigint(20) unsigned NOT NULL,
 type varchar(20) NOT NULL,
 points_delta bigint(20) NOT NULL,
 balance_after bigint(20) NOT NULL,
 order_id bigint(20) unsigned DEFAULT NULL,
 rule_id bigint(20) unsigned DEFAULT NULL,
 reason varchar(191) NOT NULL DEFAULT '',
 expires_at datetime DEFAULT NULL,
 status varchar(20) NOT NULL DEFAULT 'active',
 created_at datetime NOT NULL,
 PRIMARY KEY  (id),
 KEY user_created (user_id,created_at),
 KEY expiry_status (expires_at,status),
 KEY order_id (order_id)
) {$charset};";

		dbDelta( $balances_sql );
		dbDelta( $transactions_sql );
	}
}
