<?php
namespace AcademyProGroupPlus\Db\Migrations;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * @class CreateTeamMembersTable
 */
final class CreateTeamMembersTable extends Migration {

	/** @var string $table */
	protected string $table = 'team_members';

	/**
	 * @method up
	 * Run query on addon activation
	 * @return string
	 */
	public function up(): string {
		global $wpdb;
		return "
			CREATE TABLE IF NOT EXISTS {$this->table}
			(
				id          INT 	     AUTO_INCREMENT PRIMARY KEY,
				user_id     BIGINT(20)   NULL,
				group_id    INT          NOT NULL COMMENT 'Group ID', 
				team_id     INT          NOT NULL COMMENT 'Team ID', 
				member_id   BIGINT       UNSIGNED NOT NULL COMMENT 'Member ID',
				team_leader BOOL         NULL COMMENT 'Is Team Leader',
				updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				FOREIGN KEY (group_id) 
					REFERENCES {$this->prefix}groups(id)
						ON DELETE CASCADE
						ON UPDATE CASCADE,
				FOREIGN KEY (team_id) 
					REFERENCES {$this->prefix}teams(id)
						ON DELETE CASCADE
						ON UPDATE CASCADE,
				FOREIGN KEY (member_id) 
					REFERENCES {$wpdb->prefix}users(ID)
						ON DELETE CASCADE
						ON UPDATE CASCADE
			) {$this->charset_collate};
		";
	}

	/**
	 * @method down
	 * Run query on addon deactivation
	 * @return string
	 */
	public function down(): string {
		// nothing
		return '';
	}

}
