<?php
/**
 * Migration: Create the custom-field values table.
 *
 * Each row is a single (post, field_key) pairing. Values are split across
 * `value_text` / `value_number` / `value_date` so simple numeric or date-range
 * filters can be done in SQL without parsing strings. Multi-select fields
 * store their value as a JSON array in `value_text` (parsed by the renderer).
 *
 * @package ThemeREX\AutoDealer
 */

declare(strict_types=1);

namespace ThemeREX\AutoDealer\Database\Migrations;

use ThemeREX\AutoDealer\Database\MigrationInterface;

class CreateCustomFieldValuesTable implements MigrationInterface {

	public function up( \wpdb $wpdb ): void {
		$table          = $wpdb->prefix . 'trx_ad_custom_field_values';
		$charsetCollate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			field_key varchar(64) NOT NULL,
			value_text text DEFAULT NULL,
			value_number decimal(18,4) DEFAULT NULL,
			value_date date DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY post_field (post_id, field_key),
			KEY field_key (field_key),
			KEY value_number (value_number),
			KEY value_date (value_date)
		) {$charsetCollate};";

		dbDelta( $sql );
	}

	public function getVersion(): int {
		return 4;
	}
}
