<?php
/**
 * Migration: create click tracking tables.
 *
 * @package Affilun\Infrastructure\Persistence\Migrations
 */

declare(strict_types=1);

namespace Affilun\Infrastructure\Persistence\Migrations;

use Affilun\Infrastructure\Persistence\Migration;
use Affilun\Infrastructure\Persistence\SchemaInstaller;
use Affilun\Infrastructure\Persistence\SchemaVersion;
use Affilun\Infrastructure\Persistence\TableNameResolver;

/**
 * Creates bounded custom tables for click events and daily rollups.
 */
final class CreateClickTables implements Migration {

	/**
	 * Schema installer.
	 */
	private SchemaInstaller $installer;

	/**
	 * Table resolver.
	 */
	private TableNameResolver $tables;

	/**
	 * Charset / collation clause.
	 */
	private string $charsetCollate;

	/**
	 * Construct the migration.
	 *
	 * @param SchemaInstaller   $installer      Installer.
	 * @param TableNameResolver $tables         Table resolver.
	 * @param string            $charsetCollate Charset / collation clause.
	 */
	public function __construct( SchemaInstaller $installer, TableNameResolver $tables, string $charsetCollate ) {
		$this->installer      = $installer;
		$this->tables         = $tables;
		$this->charsetCollate = trim( $charsetCollate ) !== '' ? trim( $charsetCollate ) : 'DEFAULT CHARSET=utf8mb4';
	}

	/**
	 * Target schema version.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 10 );
	}

	/**
	 * Human-readable migration description.
	 */
	public function description(): string {
		return 'create click tracking event and rollup tables';
	}

	/**
	 * Apply the schema changes.
	 */
	public function up(): void {
		$this->installer->install( $this->eventsSql() );
		$this->installer->install( $this->rollupsSql() );
	}

	/**
	 * Build click events DDL.
	 */
	private function eventsSql(): string {
		$table = $this->tables->resolve( 'affilun_click_events' );
		return "CREATE TABLE {$table} (\n"
			. "  id bigint unsigned NOT NULL AUTO_INCREMENT,\n"
			. "  provider varchar(64) NOT NULL,\n"
			. "  button_key varchar(64) NOT NULL,\n"
			. "  target_url_hash char(64) NOT NULL,\n"
			. "  product_id bigint unsigned NULL,\n"
			. "  post_id bigint unsigned NULL,\n"
			. "  variant varchar(64) NOT NULL DEFAULT '',\n"
			. "  user_agent_hash char(64) NULL,\n"
			. "  referer_host varchar(255) NULL,\n"
			. "  created_at datetime NOT NULL,\n"
			. "  PRIMARY KEY  (id),\n"
			. "  KEY idx_created_at (created_at),\n"
			. "  KEY idx_product_id (product_id),\n"
			. "  KEY idx_post_id (post_id),\n"
			. "  KEY idx_provider_button (provider, button_key)\n"
			. ") ENGINE=InnoDB {$this->charsetCollate};";
	}

	/**
	 * Build click rollups DDL.
	 */
	private function rollupsSql(): string {
		$table = $this->tables->resolve( 'affilun_click_rollups' );
		return "CREATE TABLE {$table} (\n"
			. "  id bigint unsigned NOT NULL AUTO_INCREMENT,\n"
			. "  rollup_date date NOT NULL,\n"
			. "  provider varchar(64) NOT NULL,\n"
			. "  button_key varchar(64) NOT NULL,\n"
			. "  variant varchar(64) NOT NULL DEFAULT '',\n"
			. "  product_id bigint unsigned NOT NULL DEFAULT 0,\n"
			. "  post_id bigint unsigned NOT NULL DEFAULT 0,\n"
			. "  clicks bigint unsigned NOT NULL DEFAULT 0,\n"
			. "  updated_at datetime NOT NULL,\n"
			. "  PRIMARY KEY  (id),\n"
			. "  UNIQUE KEY uq_rollup_dimensions (rollup_date, provider, button_key, variant, product_id, post_id),\n"
			. "  KEY idx_rollup_date (rollup_date),\n"
			. "  KEY idx_product_id (product_id),\n"
			. "  KEY idx_post_id (post_id)\n"
			. ") ENGINE=InnoDB {$this->charsetCollate};";
	}
}