<?php

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
defined( 'ABSPATH' ) || exit;

/**
 * Migration: Create commerce events table.
 */
class BoostBot_Migration_Create_Commerce_Events_Table {

	/**
	 * Run the migration.
	 */
	public static function run() {
		$instance = new self();
		$instance->up();
	}

	/**
	 * Create the commerce telemetry table.
	 */
	public function up() {
		global $wpdb;

		$table_name       = $wpdb->prefix . 'boostbot_commerce_events';
		$charset_collate  = $wpdb->get_charset_collate();
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_type varchar(64) NOT NULL,
			occurred_at datetime NOT NULL,
			conversation_id varchar(191) DEFAULT NULL,
			visitor_id varchar(191) DEFAULT NULL,
			bot_id bigint(20) unsigned DEFAULT NULL,
			language varchar(10) NOT NULL DEFAULT 'he',
			currency_code varchar(10) DEFAULT NULL,
			widget_type varchar(50) DEFAULT NULL,
			widget_id bigint(20) unsigned DEFAULT NULL,
			source_page varchar(500) DEFAULT NULL,
			product_id bigint(20) unsigned DEFAULT NULL,
			product_name varchar(255) DEFAULT NULL,
			variation_id bigint(20) unsigned DEFAULT NULL,
			quantity int DEFAULT NULL,
			cart_item_key varchar(191) DEFAULT NULL,
			cart_item_count int DEFAULT NULL,
			cart_subtotal decimal(12,2) DEFAULT NULL,
			cart_total decimal(12,2) DEFAULT NULL,
			discount_total decimal(12,2) DEFAULT NULL,
			coupon_code varchar(100) DEFAULT NULL,
			bundle_id bigint(20) unsigned DEFAULT NULL,
			bundle_size int DEFAULT NULL,
			bundle_value decimal(12,2) DEFAULT NULL,
			order_value decimal(12,2) DEFAULT NULL,
			metadata longtext DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY event_type (event_type),
			KEY occurred_at (occurred_at),
			KEY language (language),
			KEY bot_id (bot_id),
			KEY widget_id (widget_id),
			KEY product_id (product_id),
			KEY coupon_code (coupon_code),
			KEY conversation_id (conversation_id),
			KEY visitor_id (visitor_id)
		) {$charset_collate};";

		dbDelta( $sql );
	}
}
