<?php

namespace RoosterLMS\Core;

defined( 'ABSPATH' ) || exit;

final class Schema {

	public const VERSION = 44;

	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'rooster_lms_' . $name;
	}

	public static function names(): array {
		return array_keys( self::definitions() );
	}

	public static function install(): array {
		global $wpdb;
		$charset = $wpdb->get_charset_collate();
		$sql     = array();
		foreach ( self::definitions() as $name => $body ) {
			$sql[] = 'CREATE TABLE ' . self::table( $name ) . " (\n" . $body . "\n) ENGINE=InnoDB " . $charset . ';';
		}
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		return dbDelta( $sql );
	}

	public static function missing_tables(): array {
		global $wpdb;
		$like     = $wpdb->esc_like( $wpdb->prefix . 'rooster_lms_' ) . '%';
		$existing = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$missing  = array();
		foreach ( self::names() as $name ) {
			if ( ! in_array( self::table( $name ), $existing, true ) ) {
				$missing[] = $name;
			}
		}
		return $missing;
	}

	public static function drop_all(): void {
		global $wpdb;
		foreach ( array_reverse( self::names() ) as $name ) {
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( $name ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		}
	}

	private static function definitions(): array {
		return array(

			'courses'              => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
home_post_id bigint(20) unsigned NOT NULL,
visibility tinyint(3) unsigned NOT NULL DEFAULT 1,
enrol_policy tinyint(3) unsigned NOT NULL DEFAULT 1,
completion_rule tinyint(3) unsigned NOT NULL DEFAULT 1,
completion_percent tinyint(3) unsigned NOT NULL DEFAULT 100,
sequential tinyint(3) unsigned NOT NULL DEFAULT 0,
access_days smallint(5) unsigned DEFAULT NULL,
validity_days smallint(5) unsigned DEFAULT NULL,
available_from datetime DEFAULT NULL,
available_until datetime DEFAULT NULL,
certificate_tpl_id bigint(20) unsigned DEFAULT NULL,
prereq_count smallint(5) unsigned NOT NULL DEFAULT 0,
edition_of bigint(20) unsigned DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
delivery tinyint(3) unsigned NOT NULL DEFAULT 1,
capacity int(10) unsigned NOT NULL DEFAULT 0,
venue_id bigint(20) unsigned DEFAULT NULL,
attendance_required tinyint(3) unsigned NOT NULL DEFAULT 0,
level_id bigint(20) unsigned DEFAULT NULL,
duration_label varchar(100) NOT NULL DEFAULT '',
video_duration_label varchar(100) NOT NULL DEFAULT '',
is_featured tinyint(3) unsigned NOT NULL DEFAULT 0,
badge_id bigint(20) unsigned DEFAULT NULL,
badge_from datetime DEFAULT NULL,
badge_until datetime DEFAULT NULL,
coming_soon_flags tinyint(3) unsigned NOT NULL DEFAULT 0,
qa_mode tinyint(3) unsigned NOT NULL DEFAULT 0,
reviews_mode tinyint(3) unsigned NOT NULL DEFAULT 0,
page_template varchar(64) NOT NULL DEFAULT '',
grade_scale_id bigint(20) unsigned DEFAULT NULL,
meeting_links tinyint(3) unsigned NOT NULL DEFAULT 0,
qa_private tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY home_post (home_post_id),
KEY catalogue (state,visibility),
KEY by_edition (edition_of),
KEY by_delivery (delivery,state),
KEY by_featured (state,is_featured),
KEY by_level (level_id,state),
KEY by_badge (badge_id)",

			'course_links'         => 'course_id bigint(20) unsigned NOT NULL,
linked_id bigint(20) unsigned NOT NULL,
kind tinyint(3) unsigned NOT NULL DEFAULT 2,
sort smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (course_id,linked_id),
KEY by_linked (linked_id)',

			'units'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
unit_type tinyint(3) unsigned NOT NULL,
home_post_id bigint(20) unsigned DEFAULT NULL,
duration_sec int(10) unsigned DEFAULT NULL,
scheduled_at datetime DEFAULT NULL,
pass_percent tinyint(3) unsigned DEFAULT NULL,
max_attempts smallint(5) unsigned DEFAULT NULL,
time_limit_sec int(10) unsigned DEFAULT NULL,
settings longtext DEFAULT NULL,
quiz_mode tinyint(3) unsigned NOT NULL DEFAULT 1,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
lesson_kind tinyint(3) unsigned NOT NULL DEFAULT 1,
duration_label varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
UNIQUE KEY home_post (home_post_id),
KEY by_kind (unit_type,lesson_kind,state)",

			'object_posts'         => "post_id bigint(20) unsigned NOT NULL,
object_type tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
PRIMARY KEY  (post_id),
UNIQUE KEY by_object_lang (object_type,object_id,lang)",

			'sections'             => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL,
title varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
KEY by_course (course_id,position)",

			'items'                => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
section_id bigint(20) unsigned DEFAULT NULL,
unit_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL,
is_required tinyint(3) unsigned NOT NULL DEFAULT 1,
is_preview tinyint(3) unsigned NOT NULL DEFAULT 0,
unlock_rule tinyint(3) unsigned NOT NULL DEFAULT 0,
unlock_days smallint(5) unsigned DEFAULT NULL,
unlock_at datetime DEFAULT NULL,
quiz_completion tinyint(3) unsigned NOT NULL DEFAULT 1,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY placement (course_id,unit_id),
KEY by_order (course_id,section_id,position),
KEY by_unit (unit_id)',

			'course_staff'         => 'course_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
role tinyint(3) unsigned NOT NULL DEFAULT 1,
position smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (course_id,user_id),
KEY by_user (user_id)',

			'course_prereqs'       => 'course_id bigint(20) unsigned NOT NULL,
requires_course_id bigint(20) unsigned NOT NULL,
rule tinyint(3) unsigned NOT NULL DEFAULT 1,
min_percent tinyint(3) unsigned NOT NULL DEFAULT 100,
created_at datetime NOT NULL,
PRIMARY KEY  (course_id,requires_course_id),
KEY by_required (requires_course_id)',

			'categories'           => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
taxonomy tinyint(3) unsigned NOT NULL,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
position int(10) unsigned NOT NULL DEFAULT 0,
image_id bigint(20) unsigned DEFAULT NULL,
icon varchar(64) NOT NULL DEFAULT '',
color char(7) NOT NULL DEFAULT '',
depth smallint(5) unsigned NOT NULL DEFAULT 0,
tree_rank int(10) unsigned NOT NULL DEFAULT 0,
direct_count int(10) unsigned NOT NULL DEFAULT 0,
subtree_count int(10) unsigned NOT NULL DEFAULT 0,
course_order tinyint(3) unsigned NOT NULL DEFAULT 1,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
page_template varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
KEY siblings (taxonomy,parent_id,position),
KEY by_rank (taxonomy,state,tree_rank),
KEY by_image (image_id)",

			'category_terms'       => "term_id bigint(20) unsigned NOT NULL,
category_id bigint(20) unsigned NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
PRIMARY KEY  (term_id),
UNIQUE KEY by_category_lang (category_id,lang)",

			'category_paths'       => 'ancestor_id bigint(20) unsigned NOT NULL,
descendant_id bigint(20) unsigned NOT NULL,
depth smallint(5) unsigned NOT NULL,
PRIMARY KEY  (ancestor_id,descendant_id),
KEY by_descendant (descendant_id,depth)',

			'category_objects'     => 'category_id bigint(20) unsigned NOT NULL,
object_type tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (category_id,object_type,object_id),
KEY by_object (object_type,object_id),
KEY by_position (category_id,object_type,position)',

			'orgs'                 => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
slug varchar(100) NOT NULL,
external_id varchar(64) DEFAULT NULL,
name varchar(190) NOT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
seat_limit int(10) unsigned DEFAULT NULL,
timezone varchar(64) NOT NULL DEFAULT '',
lang varchar(12) NOT NULL DEFAULT '',
access_tpl_id bigint(20) unsigned DEFAULT NULL,
logo_id bigint(20) unsigned DEFAULT NULL,
brand_color char(7) NOT NULL DEFAULT '',
settings longtext DEFAULT NULL,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
archived_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
UNIQUE KEY external_id (external_id),
KEY by_parent (parent_id,status)",

			'org_members'          => 'org_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
roles tinyint(3) unsigned NOT NULL DEFAULT 1,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
active_until date DEFAULT NULL,
location_id bigint(20) unsigned DEFAULT NULL,
job_role_id bigint(20) unsigned DEFAULT NULL,
joined_at datetime NOT NULL,
left_at datetime DEFAULT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
name_key varchar(190) NOT NULL DEFAULT \'\',
PRIMARY KEY  (org_id,user_id),
KEY by_user (user_id,left_at),
KEY by_org_filter (org_id,left_at,status,location_id,job_role_id),
KEY by_job_role (job_role_id,org_id,left_at),
KEY by_org_unit (org_id,left_at,location_id),
KEY by_org_roles (org_id,left_at,roles),
KEY by_org_name (org_id,left_at,name_key)',

			'member_field_values'  => 'org_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
field_id smallint(5) unsigned NOT NULL,
cur tinyint(3) unsigned NOT NULL DEFAULT 1,
value varchar(190) NOT NULL DEFAULT \'\',
vkey varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT \'\',
updated_at datetime NOT NULL,
updated_by bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (org_id,user_id,field_id),
KEY by_field (org_id,field_id,cur,vkey,user_id),
KEY by_user (user_id)',

			'member_placements'    => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
open_slot tinyint(3) unsigned DEFAULT 1,
joined_at datetime NOT NULL,
left_at datetime DEFAULT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
ended_by bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_open (org_id,user_id,unit_id,open_slot),
KEY by_unit (unit_id,open_slot,user_id),
KEY by_user (user_id,open_slot)',

			'org_locations'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
name varchar(190) NOT NULL,
address varchar(255) NOT NULL DEFAULT '',
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
KEY by_org (org_id,position)",

			'org_units'            => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
depth smallint(5) unsigned NOT NULL DEFAULT 0,
type_id smallint(5) unsigned NOT NULL DEFAULT 0,
root_slot tinyint(3) unsigned DEFAULT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
name varchar(190) NOT NULL DEFAULT '',
address varchar(255) NOT NULL DEFAULT '',
seat_limit int(10) unsigned DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_root (org_id,root_slot),
KEY by_org (org_id,state,depth,position),
KEY siblings (parent_id,state,position)",

			'org_unit_paths'       => 'ancestor_id bigint(20) unsigned NOT NULL,
descendant_id bigint(20) unsigned NOT NULL,
depth smallint(5) unsigned NOT NULL,
PRIMARY KEY  (ancestor_id,descendant_id),
KEY by_descendant (descendant_id,depth)',

			'org_unit_catalogue'   => 'unit_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
added_at datetime NOT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (unit_id,course_id),
KEY by_course (course_id,org_id)',

			'role_assignments'     => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
roles tinyint(3) unsigned NOT NULL DEFAULT 2,
open_slot tinyint(3) unsigned DEFAULT 1,
starts_on date DEFAULT NULL,
ends_on date DEFAULT NULL,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
revoked_by bigint(20) unsigned DEFAULT NULL,
revoked_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_open (org_id,user_id,unit_id,open_slot),
KEY by_user (user_id,open_slot),
KEY by_unit (unit_id,open_slot),
KEY by_org_open (org_id,open_slot,unit_id)',

			'org_unit_settings'    => "unit_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
ui_template varchar(64) NOT NULL DEFAULT '',
updated_at datetime NOT NULL,
PRIMARY KEY  (unit_id),
KEY by_org (org_id)",

			'structure_ops'        => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
op_key char(32) NOT NULL,
user_id bigint(20) unsigned NOT NULL,
created_at datetime NOT NULL,
result text NOT NULL,
receipt mediumtext DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY op (org_id,op_key),
KEY created (created_at)',

			'persons'              => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
user_id bigint(20) unsigned DEFAULT NULL,
org_id bigint(20) unsigned DEFAULT NULL,
given_name varchar(190) NOT NULL DEFAULT '',
family_name varchar(190) NOT NULL DEFAULT '',
honorific varchar(60) NOT NULL DEFAULT '',
headline varchar(255) NOT NULL DEFAULT '',
name_key varchar(190) NOT NULL DEFAULT '',
photo_id bigint(20) unsigned DEFAULT NULL,
links text DEFAULT NULL,
email varchar(190) DEFAULT NULL,
flags tinyint(3) unsigned NOT NULL DEFAULT 1,
state tinyint(3) unsigned NOT NULL DEFAULT 2,
consent_at datetime DEFAULT NULL,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
UNIQUE KEY user_id (user_id),
KEY by_state (state,name_key),
KEY by_org (org_id,state),
KEY by_photo (photo_id),
KEY by_email (email)",

			'person_groups'        => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(60) NOT NULL,
type_id bigint(20) unsigned NOT NULL DEFAULT 0,
owner_type tinyint(3) unsigned NOT NULL DEFAULT 0,
owner_id bigint(20) unsigned NOT NULL DEFAULT 0,
page_id bigint(20) unsigned DEFAULT NULL,
name varchar(190) NOT NULL,
html mediumtext DEFAULT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY owner_slug (owner_type,owner_id,slug),
KEY by_owner (owner_type,owner_id,state,position),
KEY by_type (type_id,state)',

			'person_group_types'   => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(60) NOT NULL,
name varchar(190) NOT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_state (state,position)',

			'person_roles'         => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(60) NOT NULL,
name varchar(190) NOT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_state (state,position)',

			'person_links'         => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
person_id bigint(20) unsigned NOT NULL,
target_type tinyint(3) unsigned NOT NULL,
target_id bigint(20) unsigned NOT NULL,
role_id bigint(20) unsigned NOT NULL DEFAULT 0,
label varchar(190) NOT NULL DEFAULT '',
position int(10) unsigned NOT NULL DEFAULT 0,
starts_on date DEFAULT NULL,
ends_on date DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_target (target_type,target_id,state,position),
KEY by_person (person_id,target_type),
KEY by_role (role_id)",

			'person_tags'          => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(60) NOT NULL,
name varchar(190) NOT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_state (state,position)',

			'person_tag_map'       => 'person_id bigint(20) unsigned NOT NULL,
tag_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (person_id,tag_id),
KEY by_tag (tag_id,person_id)',

			'person_files'         => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
person_id bigint(20) unsigned NOT NULL,
attachment_id bigint(20) unsigned DEFAULT NULL,
url varchar(2048) NOT NULL DEFAULT '',
title varchar(190) NOT NULL DEFAULT '',
position smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_person (person_id,position),
KEY by_attachment (attachment_id)",

			'job_roles'            => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
name varchar(190) NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug)',

			'org_job_roles'        => 'org_id bigint(20) unsigned NOT NULL,
job_role_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (org_id,job_role_id)',

			'audiences'            => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
name varchar(190) NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug)',

			'audience_job_roles'   => 'audience_id bigint(20) unsigned NOT NULL,
job_role_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (audience_id,job_role_id),
KEY by_role (job_role_id)',

			'course_audiences'     => 'course_id bigint(20) unsigned NOT NULL,
audience_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (course_id,audience_id),
KEY by_audience (audience_id)',

			'org_catalogue'        => 'org_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
added_at datetime NOT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (org_id,course_id),
KEY by_course (course_id)',

			'access_templates'     => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
source_id bigint(20) unsigned DEFAULT NULL,
name varchar(190) NOT NULL,
updated_at datetime NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
KEY by_org (org_id)',

			'access_rules'         => 'template_id bigint(20) unsigned NOT NULL,
job_role_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (template_id,job_role_id,course_id,item_id),
KEY by_course (template_id,course_id),
KEY by_item (item_id)',

			'grants'               => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
sources smallint(5) unsigned NOT NULL DEFAULT 0,
granted_at datetime NOT NULL,
granted_by bigint(20) unsigned NOT NULL DEFAULT 0,
revoked_at datetime DEFAULT NULL,
revoked_by bigint(20) unsigned DEFAULT NULL,
due_at datetime DEFAULT NULL,
expires_at datetime DEFAULT NULL,
cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY assignment (org_id,user_id,course_id),
KEY by_user (user_id,course_id,revoked_at),
KEY by_org_course (org_id,course_id,revoked_at),
KEY by_org_user (org_id,user_id,revoked_at),
KEY by_org_due (org_id,due_at),
KEY by_cohort (cohort_id,revoked_at)',

			'enrolments'           => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
status tinyint(3) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
enrolled_at datetime NOT NULL,
access_until datetime DEFAULT NULL,
ended_at datetime DEFAULT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY learner_course (user_id,course_id),
KEY by_course (course_id,status)',

			'grant_scope'          => 'grant_id bigint(20) unsigned NOT NULL,
kind tinyint(3) unsigned NOT NULL,
scope_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (grant_id,kind,scope_id)',

			'plans'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
home_post_id bigint(20) unsigned DEFAULT NULL,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
seat_limit int(10) unsigned DEFAULT NULL,
period tinyint(3) unsigned NOT NULL DEFAULT 2,
period_count smallint(5) unsigned NOT NULL DEFAULT 1,
display_price_minor bigint(20) unsigned DEFAULT NULL,
currency char(3) NOT NULL DEFAULT '',
access_template_id bigint(20) unsigned DEFAULT NULL,
cta tinyint(3) unsigned NOT NULL DEFAULT 3,
cta_url varchar(500) NOT NULL DEFAULT '',
flow_id bigint(20) unsigned DEFAULT NULL,
badge tinyint(3) unsigned NOT NULL DEFAULT 0,
sort int(10) unsigned NOT NULL DEFAULT 0,
settings longtext DEFAULT NULL,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY home_post (home_post_id),
KEY by_status (status,kind,sort)",

			'plan_courses'         => 'plan_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
category_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (plan_id,course_id,category_id),
KEY by_course (course_id),
KEY by_category (category_id)',

			'plan_assignments'     => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
plan_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
status tinyint(3) unsigned NOT NULL DEFAULT 2,
open_slot tinyint(3) unsigned DEFAULT 1,
seats int(10) unsigned DEFAULT NULL,
source tinyint(3) unsigned NOT NULL DEFAULT 1,
source_ref varchar(64) NOT NULL DEFAULT '',
starts_at datetime NOT NULL,
ends_at datetime DEFAULT NULL,
grace_ends_at datetime DEFAULT NULL,
reminded tinyint(3) unsigned NOT NULL DEFAULT 0,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_open (plan_id,org_id,user_id,open_slot),
KEY by_org (org_id,status),
KEY by_user (user_id,status),
KEY by_plan (plan_id,status),
KEY by_end (status,ends_at),
KEY by_source (source_ref)",

			'plan_suspensions'     => 'assignment_id bigint(20) unsigned NOT NULL,
grant_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
sources smallint(5) unsigned NOT NULL DEFAULT 0,
suspended_at datetime NOT NULL,
PRIMARY KEY  (assignment_id,grant_id),
KEY by_user (user_id),
KEY by_grant (grant_id)',

			'offers'               => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
product_id bigint(20) unsigned NOT NULL,
variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
target_type tinyint(3) unsigned NOT NULL,
target_id bigint(20) unsigned NOT NULL,
scope_ids longtext DEFAULT NULL,
sale_window tinyint(3) unsigned NOT NULL DEFAULT 0,
grace_min smallint(5) unsigned NOT NULL DEFAULT 0,
access_days smallint(5) unsigned DEFAULT NULL,
seats_per_qty int(10) unsigned NOT NULL DEFAULT 1,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_per_target (product_id,variation_id,target_type,target_id),
KEY by_target (target_type,target_id,state)',

			'sales'                => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
order_item_id bigint(20) unsigned NOT NULL,
order_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
offer_id bigint(20) unsigned NOT NULL,
qty int(10) unsigned NOT NULL DEFAULT 1,
grant_ids longtext DEFAULT NULL,
assignment_id bigint(20) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
flags tinyint(3) unsigned NOT NULL DEFAULT 0,
access_until datetime DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY order_item (order_item_id),
KEY by_order (order_id),
KEY by_user (user_id,state),
KEY by_offer (offer_id,state),
KEY by_expiry (state,access_until)',

			'progress'             => 'user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
status tinyint(3) unsigned NOT NULL DEFAULT 0,
result tinyint(3) unsigned NOT NULL DEFAULT 0,
percent tinyint(3) unsigned NOT NULL DEFAULT 0,
score decimal(5,2) DEFAULT NULL,
items_done smallint(5) unsigned NOT NULL DEFAULT 0,
items_total smallint(5) unsigned NOT NULL DEFAULT 0,
time_spent_sec int(10) unsigned NOT NULL DEFAULT 0,
started_at datetime DEFAULT NULL,
completed_at datetime DEFAULT NULL,
last_activity_at datetime DEFAULT NULL,
expires_at datetime DEFAULT NULL,
flags smallint(5) unsigned NOT NULL DEFAULT 0,
steer_org_id bigint(20) unsigned DEFAULT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (user_id,course_id,cycle),
KEY by_course_status (course_id,status,completed_at),
KEY by_expiry (expires_at)',

			'item_progress'        => 'user_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
course_id bigint(20) unsigned NOT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT 0,
score decimal(5,2) DEFAULT NULL,
attempts smallint(5) unsigned NOT NULL DEFAULT 0,
time_spent_sec int(10) unsigned NOT NULL DEFAULT 0,
started_at datetime DEFAULT NULL,
completed_at datetime DEFAULT NULL,
completed_by tinyint(3) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
flags tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (user_id,item_id,cycle),
KEY by_learner_course (user_id,course_id,cycle),
KEY by_item (item_id,status)',

			'questions'            => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
uuid char(36) NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL,
qtype tinyint(3) unsigned NOT NULL,
version_id bigint(20) unsigned DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
time_limit_sec smallint(5) unsigned DEFAULT NULL,
is_required tinyint(3) unsigned NOT NULL DEFAULT 0,
category_id bigint(20) unsigned DEFAULT NULL,
difficulty tinyint(3) unsigned NOT NULL DEFAULT 0,
expected_time_sec smallint(5) unsigned DEFAULT NULL,
skill_id bigint(20) unsigned DEFAULT NULL,
eff_skill_id bigint(20) unsigned NOT NULL DEFAULT 0,
family_id bigint(20) unsigned DEFAULT NULL,
calib_epoch smallint(5) unsigned NOT NULL DEFAULT 1,
global_uuid char(36) NOT NULL DEFAULT \'\',
origin_uuid char(36) DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY by_unit_uuid (unit_id,uuid),
UNIQUE KEY by_global_uuid (global_uuid),
KEY by_quiz (unit_id,position),
KEY by_category (category_id),
KEY by_eff_skill (eff_skill_id,state,unit_id),
KEY by_family (family_id)',

			'question_versions'    => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
question_id bigint(20) unsigned NOT NULL,
version_no smallint(5) unsigned NOT NULL,
points decimal(6,2) NOT NULL DEFAULT 1.00,
answer_key longtext NOT NULL,
key_hash char(40) NOT NULL DEFAULT '',
changes tinyint(3) unsigned NOT NULL DEFAULT 0,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
option_count tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY by_question (question_id,version_no)",

			'question_snapshots'   => "version_id bigint(20) unsigned NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
display longtext NOT NULL,
display_hash char(40) NOT NULL DEFAULT '',
exact tinyint(3) unsigned NOT NULL DEFAULT 1,
reworded smallint(5) unsigned NOT NULL DEFAULT 0,
frozen_at datetime DEFAULT NULL,
updated_at datetime NOT NULL,
word_count smallint(5) unsigned NOT NULL DEFAULT 0,
media tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (version_id,lang)",

			'question_options'     => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
question_id bigint(20) unsigned NOT NULL,
uuid char(36) NOT NULL,
position smallint(5) unsigned NOT NULL,
label varchar(500) NOT NULL DEFAULT '',
side tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY by_uuid (question_id,uuid)",

			'attempts'             => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
attempt_no smallint(5) unsigned NOT NULL,
token char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
status tinyint(3) unsigned NOT NULL DEFAULT 1,
started_at datetime NOT NULL,
submitted_at datetime DEFAULT NULL,
points decimal(8,2) DEFAULT NULL,
max_points decimal(8,2) DEFAULT NULL,
score_pct decimal(5,2) DEFAULT NULL,
result tinyint(3) unsigned NOT NULL DEFAULT 0,
duration_sec int(10) unsigned DEFAULT NULL,
deadline_at datetime DEFAULT NULL,
graded_at datetime DEFAULT NULL,
pass_percent tinyint(3) unsigned DEFAULT NULL,
mode tinyint(3) unsigned NOT NULL DEFAULT 1,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
delivery tinyint(3) unsigned NOT NULL DEFAULT 1,
disclosure tinyint(3) unsigned NOT NULL DEFAULT 2,
time_factor smallint(5) unsigned NOT NULL DEFAULT 100,
current_seq smallint(5) unsigned NOT NULL DEFAULT 0,
release_at datetime DEFAULT NULL,
released_at datetime DEFAULT NULL,
device tinyint(3) unsigned NOT NULL DEFAULT 0,
tz_offset smallint(6) NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY token (token),
UNIQUE KEY numbering (user_id,item_id,cycle,attempt_no),
KEY by_unit_time (unit_id,submitted_at),
KEY by_unit_id (unit_id,id),
KEY by_unit_status (unit_id,status,user_id),
KEY review (status,submitted_at),
KEY by_course_status (course_id,status,submitted_at),
KEY by_release (status,release_at),
KEY by_deadline (status,deadline_at),
KEY by_org_unit (org_id,unit_id,status,item_id,release_at,released_at),
KEY by_cohort_unit (cohort_id,unit_id,status,item_id,release_at,released_at)",

			'attempt_answers'      => 'attempt_id bigint(20) unsigned NOT NULL,
question_id bigint(20) unsigned NOT NULL,
version_id bigint(20) unsigned NOT NULL,
response longtext NOT NULL,
verdict tinyint(3) unsigned NOT NULL DEFAULT 0,
points decimal(6,2) DEFAULT NULL,
graded_by bigint(20) unsigned NOT NULL DEFAULT 0,
graded_at datetime DEFAULT NULL,
feedback text DEFAULT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
flags tinyint(3) unsigned NOT NULL DEFAULT 0,
shown_version_id bigint(20) unsigned NOT NULL DEFAULT 0,
seq smallint(5) unsigned NOT NULL DEFAULT 0,
limit_ms int(10) unsigned NOT NULL DEFAULT 0,
shown_at datetime(3) DEFAULT NULL,
deadline_at datetime(3) DEFAULT NULL,
answered_at datetime(3) DEFAULT NULL,
response_ms int(10) unsigned DEFAULT NULL,
hidden_ms int(10) unsigned DEFAULT NULL,
first_input_ms int(10) unsigned DEFAULT NULL,
last_change_ms int(10) unsigned DEFAULT NULL,
changes tinyint(3) unsigned NOT NULL DEFAULT 0,
confidence tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (attempt_id,question_id),
KEY by_question_state (question_id,version_id,flags),
KEY timing (question_id,verdict,response_ms)',

			'regrades'             => 'run_id bigint(20) unsigned NOT NULL,
attempt_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
before_points decimal(8,2) DEFAULT NULL,
before_max decimal(8,2) DEFAULT NULL,
before_pct decimal(5,2) DEFAULT NULL,
before_result tinyint(3) unsigned NOT NULL DEFAULT 0,
after_points decimal(8,2) DEFAULT NULL,
after_max decimal(8,2) DEFAULT NULL,
after_pct decimal(5,2) DEFAULT NULL,
after_result tinyint(3) unsigned NOT NULL DEFAULT 0,
applied_at datetime DEFAULT NULL,
kind tinyint(3) unsigned NOT NULL DEFAULT 0,
completes tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (run_id,attempt_id),
KEY by_attempt (attempt_id),
KEY by_unit (unit_id,run_id),
KEY by_user (user_id)',

			'certificates'         => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
code char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
template_id bigint(20) unsigned DEFAULT NULL,
issued_at datetime NOT NULL,
expires_at datetime DEFAULT NULL,
revoked_at datetime DEFAULT NULL,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
source_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY code (code),
UNIQUE KEY one_per_source (user_id,course_id,cycle,kind,source_id),
KEY by_course (course_id,issued_at),
KEY by_source (kind,source_id,user_id),
KEY by_kind (kind,issued_at)',

			'cert_templates'       => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
design longtext NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_kind (kind,id)',

			'cohorts'              => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
name varchar(190) NOT NULL,
starts_at datetime NOT NULL,
ends_at datetime NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
programme_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_org_course (org_id,course_id,starts_at),
KEY by_end (state,ends_at),
KEY by_programme (programme_id,state),
KEY by_unit (org_id,unit_id,state)',

			'offerings'            => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
org_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned NOT NULL,
cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
period_id bigint(20) unsigned NOT NULL DEFAULT 0,
stage_id bigint(20) unsigned NOT NULL DEFAULT 0,
name varchar(190) NOT NULL DEFAULT '',
starts_at datetime NOT NULL,
ends_at datetime NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
closed_at datetime DEFAULT NULL,
run_id bigint(20) unsigned NOT NULL DEFAULT 0,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_per (org_id,course_id,cohort_id,period_id),
KEY by_course (course_id,state,starts_at),
KEY by_cohort (cohort_id,state),
KEY by_org_unit (org_id,unit_id,state,starts_at),
KEY by_period (period_id,stage_id),
KEY by_end (state,ends_at),
KEY by_run (run_id)",

			'offering_enrolments'  => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
offering_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
joined_at datetime NOT NULL,
left_at datetime DEFAULT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
run_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_cycle (user_id,course_id,cycle),
UNIQUE KEY one_per_offering (offering_id,user_id),
KEY by_user (user_id,offering_id),
KEY by_run (run_id)',

			'cohort_members'       => 'cohort_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
joined_at datetime NOT NULL,
left_at datetime DEFAULT NULL,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (cohort_id,user_id),
KEY by_user (user_id,left_at)',

			'assessment_policies'  => 'item_id bigint(20) unsigned NOT NULL DEFAULT 0,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
policy longtext NOT NULL,
locks smallint(5) unsigned NOT NULL DEFAULT 0,
updated_by bigint(20) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (item_id,org_id,cohort_id),
KEY by_org (org_id,cohort_id)',

			'feedback_forms'       => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
unit_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
category_id bigint(20) unsigned NOT NULL DEFAULT 0,
scope tinyint(3) unsigned NOT NULL DEFAULT 3,
privacy tinyint(3) unsigned NOT NULL DEFAULT 1,
editable tinyint(3) unsigned NOT NULL DEFAULT 0,
position smallint(5) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_scope (state,org_id,course_id),
KEY by_unit (unit_id),
KEY by_category (category_id,state)',

			'feedback_responses'   => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
form_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned NOT NULL,
section_id bigint(20) unsigned NOT NULL DEFAULT 0,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
category_id bigint(20) unsigned NOT NULL DEFAULT 0,
attempt_id bigint(20) unsigned NOT NULL DEFAULT 0,
phase tinyint(3) unsigned NOT NULL DEFAULT 0,
lang varchar(12) NOT NULL DEFAULT '',
submitted_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_response (form_id,user_id,course_id,section_id,cycle,category_id,attempt_id,phase),
KEY by_learner (user_id,course_id,cycle),
KEY by_attempt (attempt_id,phase),
KEY by_category (category_id,form_id),
KEY by_course (course_id,section_id,form_id),
KEY by_org (org_id,course_id,form_id)",

			'feedback_answers'     => 'response_id bigint(20) unsigned NOT NULL,
question_id bigint(20) unsigned NOT NULL,
version_id bigint(20) unsigned NOT NULL,
response longtext NOT NULL,
rating tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY  (response_id,question_id),
KEY by_question (question_id,rating)',

			'announcements'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
scope_type tinyint(3) unsigned NOT NULL,
scope_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
author_id bigint(20) unsigned NOT NULL DEFAULT 0,
title varchar(190) NOT NULL,
body text NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
status tinyint(3) unsigned NOT NULL DEFAULT 1,
notify tinyint(3) unsigned NOT NULL DEFAULT 0,
published_at datetime NOT NULL,
updated_at datetime NOT NULL,
audience tinyint(3) unsigned NOT NULL DEFAULT 1,
target_course_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_scope (scope_type,scope_id,published_at),
KEY by_target (target_course_id,audience),
KEY by_author (author_id,published_at)",

			'live_sessions'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
unit_id bigint(20) unsigned DEFAULT NULL,
placement tinyint(3) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned DEFAULT NULL,
provider tinyint(3) unsigned NOT NULL,
provider_ref varchar(255) NOT NULL DEFAULT '',
eroom_post_id bigint(20) unsigned DEFAULT NULL,
timezone varchar(64) NOT NULL,
starts_local datetime DEFAULT NULL,
starts_at datetime DEFAULT NULL,
duration_min smallint(5) unsigned NOT NULL DEFAULT 60,
rrule varchar(255) NOT NULL DEFAULT '',
ends_at datetime DEFAULT NULL,
join_url text DEFAULT NULL,
host_url text DEFAULT NULL,
password varchar(64) NOT NULL DEFAULT '',
join_before_min smallint(5) unsigned NOT NULL DEFAULT 10,
orgs_only tinyint(3) unsigned NOT NULL DEFAULT 0,
options longtext DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
venue_id bigint(20) unsigned DEFAULT NULL,
delivery tinyint(3) unsigned NOT NULL DEFAULT 2,
capacity int(10) unsigned NOT NULL DEFAULT 0,
stream_url text DEFAULT NULL,
studio_url text DEFAULT NULL,
certificate_tpl_id bigint(20) unsigned DEFAULT NULL,
certificate_rule tinyint(3) unsigned NOT NULL DEFAULT 1,
certificate_min_pct tinyint(3) unsigned NOT NULL DEFAULT 100,
PRIMARY KEY  (id),
UNIQUE KEY eroom (eroom_post_id),
KEY by_unit (unit_id),
KEY by_course (course_id,state),
KEY by_start (state,starts_at),
KEY by_certificate (certificate_tpl_id)",

			'live_session_orgs'    => 'session_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (session_id,org_id),
KEY by_org (org_id)',

			'live_occurrences'     => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
session_id bigint(20) unsigned NOT NULL,
starts_at datetime NOT NULL,
ends_at datetime NOT NULL,
provider_ref varchar(255) NOT NULL DEFAULT '',
state tinyint(3) unsigned NOT NULL DEFAULT 1,
phase tinyint(3) unsigned NOT NULL DEFAULT 1,
phase_at datetime DEFAULT NULL,
phase_by bigint(20) unsigned NOT NULL DEFAULT 0,
recording_kind tinyint(3) unsigned NOT NULL DEFAULT 0,
recording_unit_id bigint(20) unsigned DEFAULT NULL,
recording_url text DEFAULT NULL,
recording_file varchar(255) NOT NULL DEFAULT '',
recording_mime varchar(100) NOT NULL DEFAULT '',
recording_size bigint(20) unsigned NOT NULL DEFAULT 0,
recording_at datetime DEFAULT NULL,
note text DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_per_start (session_id,starts_at),
KEY by_window (state,starts_at)",

			'attendance'           => 'occurrence_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
status tinyint(3) unsigned NOT NULL,
method tinyint(3) unsigned NOT NULL,
marked_by bigint(20) unsigned NOT NULL DEFAULT 0,
marked_at datetime NOT NULL,
PRIMARY KEY  (occurrence_id,user_id),
KEY by_user (user_id)',

			'material_qr'          => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
kind tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
code varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
active tinyint(3) unsigned NOT NULL DEFAULT 1,
scan_day date DEFAULT NULL,
scans int(10) unsigned NOT NULL DEFAULT 0,
changed_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_per_object (kind,object_id),
UNIQUE KEY code (code)',

			'venues'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(190) NOT NULL,
address varchar(255) NOT NULL DEFAULT '',
city varchar(100) NOT NULL DEFAULT '',
country char(2) NOT NULL DEFAULT '',
map_url varchar(500) NOT NULL DEFAULT '',
capacity int(10) unsigned NOT NULL DEFAULT 0,
timezone varchar(64) NOT NULL DEFAULT '',
org_location_id bigint(20) unsigned DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_state (state,city)",

			'unit_materials'       => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
unit_id bigint(20) unsigned NOT NULL,
attachment_id bigint(20) unsigned DEFAULT NULL,
url text DEFAULT NULL,
title varchar(190) NOT NULL DEFAULT '',
release_phase tinyint(3) unsigned NOT NULL DEFAULT 1,
sort smallint(5) unsigned NOT NULL DEFAULT 0,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
added_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_unit (unit_id,release_phase,sort),
KEY by_attachment (attachment_id)",

			'events'               => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
occurred_at datetime NOT NULL,
recorded_at datetime NOT NULL,
verb smallint(5) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
item_id bigint(20) unsigned NOT NULL DEFAULT 0,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
cycle smallint(5) unsigned NOT NULL DEFAULT 0,
actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
source tinyint(3) unsigned NOT NULL DEFAULT 0,
value decimal(10,2) DEFAULT NULL,
ref_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_learner (user_id,course_id,occurred_at),
KEY by_org_time (org_id,occurred_at,verb),
KEY by_course_verb (course_id,verb,occurred_at)',

			'course_stats'         => 'course_id bigint(20) unsigned NOT NULL,
learners int(10) unsigned NOT NULL DEFAULT 0,
completions int(10) unsigned NOT NULL DEFAULT 0,
in_progress int(10) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
verified_at datetime DEFAULT NULL,
rating_count int(10) unsigned NOT NULL DEFAULT 0,
rating_sum int(10) unsigned NOT NULL DEFAULT 0,
rating_avg decimal(3,2) DEFAULT NULL,
PRIMARY KEY  (course_id),
KEY by_rating (rating_avg)',

			'org_course_stats'     => 'org_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
assigned int(10) unsigned NOT NULL DEFAULT 0,
not_started int(10) unsigned NOT NULL DEFAULT 0,
in_progress int(10) unsigned NOT NULL DEFAULT 0,
completed int(10) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (org_id,course_id)',

			'unit_stats'           => 'org_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
shard tinyint(3) unsigned NOT NULL DEFAULT 0,
members int(10) unsigned NOT NULL DEFAULT 0,
assigned int(10) unsigned NOT NULL DEFAULT 0,
not_started int(10) unsigned NOT NULL DEFAULT 0,
in_progress int(10) unsigned NOT NULL DEFAULT 0,
completed int(10) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (org_id,unit_id,course_id,shard),
KEY by_course (org_id,course_id,unit_id)',

			'unit_stats_orgs'      => 'org_id bigint(20) unsigned NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 0,
filled_at datetime DEFAULT NULL,
verified_at datetime DEFAULT NULL,
mismatches int(10) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (org_id)',

			'people'               => "user_id bigint(20) unsigned NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
name_key varchar(190) NOT NULL DEFAULT '',
updated_at datetime NOT NULL,
time_factor smallint(5) unsigned NOT NULL DEFAULT 100,
notify_offers tinyint(3) unsigned NOT NULL DEFAULT 0,
notify_offers_at datetime DEFAULT NULL,
notify_offers_src tinyint(3) unsigned NOT NULL DEFAULT 0,
notify_service smallint(5) unsigned NOT NULL DEFAULT 0,
phone varchar(40) NOT NULL DEFAULT '',
privacy_accepted_at datetime DEFAULT NULL,
privacy_page_id bigint(20) unsigned DEFAULT NULL,
terms_accepted_at datetime DEFAULT NULL,
terms_page_id bigint(20) unsigned DEFAULT NULL,
email_verified char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
email_changed_at datetime DEFAULT NULL,
PRIMARY KEY  (user_id),
KEY name_key (name_key)",

			'registration_forms'   => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(190) NOT NULL DEFAULT '',
fields longtext NOT NULL,
confirm tinyint(3) unsigned NOT NULL DEFAULT 1,
consents longtext DEFAULT NULL,
redirect varchar(500) NOT NULL DEFAULT '',
is_default tinyint(3) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_default (is_default,state)",

			'registration_pending' => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
token_hash char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
email varchar(100) NOT NULL,
payload longtext NOT NULL,
form_id bigint(20) unsigned NOT NULL DEFAULT 0,
flow_id bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
expires_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY token_hash (token_hash),
KEY by_email (email),
KEY by_expiry (expires_at)',

			'invitations'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
occurrence_id bigint(20) unsigned NOT NULL DEFAULT 0,
template_id bigint(20) unsigned NOT NULL DEFAULT 0,
announcement_id bigint(20) unsigned NOT NULL DEFAULT 0,
version int(10) unsigned NOT NULL DEFAULT 1,
attachment_ids longtext DEFAULT NULL,
rsvp tinyint(3) unsigned NOT NULL DEFAULT 3,
rsvp_until datetime DEFAULT NULL,
yes_action tinyint(3) unsigned NOT NULL DEFAULT 0,
places int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
scheduled_at datetime DEFAULT NULL,
sent_at datetime DEFAULT NULL,
subject varchar(190) NOT NULL DEFAULT '',
body text DEFAULT NULL,
lang varchar(12) NOT NULL DEFAULT '',
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime DEFAULT NULL,
target_cohort_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_unit (unit_id,state),
KEY by_course (course_id,state),
KEY by_state (state,scheduled_at),
KEY by_target_cohort (target_cohort_id,state)",

			'invitees'             => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
invitation_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
email_hash char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
email varchar(100) NOT NULL DEFAULT '',
first_name varchar(100) NOT NULL DEFAULT '',
last_name varchar(100) NOT NULL DEFAULT '',
lang varchar(12) NOT NULL DEFAULT '',
source tinyint(3) unsigned NOT NULL DEFAULT 6,
token_hash char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
token_seed char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
token_expires_at datetime DEFAULT NULL,
sent_version int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 0,
sent_at datetime DEFAULT NULL,
answer tinyint(3) unsigned NOT NULL DEFAULT 0,
answered_at datetime DEFAULT NULL,
answer_src tinyint(3) unsigned NOT NULL DEFAULT 0,
answer_changes smallint(5) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
outcome tinyint(3) unsigned NOT NULL DEFAULT 0,
outcome_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_per_address (invitation_id,email_hash),
UNIQUE KEY token_hash (token_hash),
KEY by_answer (invitation_id,answer),
KEY by_email (email_hash,sent_at),
KEY by_user (user_id)",

			'guest_checkins'       => 'invitee_id bigint(20) unsigned NOT NULL,
occurrence_id bigint(20) unsigned NOT NULL,
checked_in_at datetime NOT NULL,
checked_in_by bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (invitee_id,occurrence_id),
KEY by_occurrence (occurrence_id,checked_in_at)',

			'translations'         => 'object_type tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
field varchar(32) NOT NULL,
lang varchar(12) NOT NULL,
value text NOT NULL,
PRIMARY KEY  (object_type,object_id,field,lang)',

			'notifications'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
recipient_id bigint(20) unsigned NOT NULL,
notif_key varchar(64) NOT NULL,
org_id bigint(20) unsigned NOT NULL DEFAULT 0,
open_slot tinyint(3) unsigned DEFAULT 1,
lang varchar(12) NOT NULL DEFAULT '',
payload longtext NOT NULL,
first_change_at datetime NOT NULL,
send_after datetime NOT NULL,
sent_at datetime DEFAULT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
tries tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY one_open (recipient_id,notif_key,org_id,open_slot),
KEY due (status,send_after)",

			'message_templates'    => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
msg_key varchar(64) NOT NULL,
side tinyint(3) unsigned NOT NULL DEFAULT 1,
channel tinyint(3) unsigned NOT NULL DEFAULT 1,
slot varchar(32) NOT NULL DEFAULT '',
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
enabled tinyint(3) unsigned NOT NULL DEFAULT 1,
custom_name varchar(190) NOT NULL DEFAULT '',
subject varchar(255) NOT NULL DEFAULT '',
body longtext NOT NULL,
body_format tinyint(3) unsigned NOT NULL DEFAULT 2,
trigger_key varchar(64) NOT NULL DEFAULT '',
timing tinyint(3) unsigned NOT NULL DEFAULT 0,
offset_min int(11) NOT NULL DEFAULT 0,
at_time char(5) NOT NULL DEFAULT '',
min_lead_min int(10) unsigned NOT NULL DEFAULT 0,
scope_type tinyint(3) unsigned NOT NULL DEFAULT 0,
scope_ids varchar(2000) NOT NULL DEFAULT '[]',
only_this tinyint(3) unsigned NOT NULL DEFAULT 0,
staff_roles tinyint(3) unsigned NOT NULL DEFAULT 0,
sort int(11) NOT NULL DEFAULT 0,
updated_by bigint(20) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_slot (msg_key,side,channel,scope_type,slot),
KEY by_timing (timing,enabled),
KEY by_parent (parent_id)",

			'message_log'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
template_id bigint(20) unsigned NOT NULL DEFAULT 0,
msg_key varchar(64) NOT NULL DEFAULT '',
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
email_hash char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
object_type tinyint(3) unsigned NOT NULL DEFAULT 0,
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
occurrence_start datetime DEFAULT NULL,
state tinyint(4) NOT NULL DEFAULT 0,
reason varchar(64) NOT NULL DEFAULT '',
lang varchar(12) NOT NULL DEFAULT '',
created_at datetime NOT NULL,
sent_at datetime DEFAULT NULL,
opened_at datetime DEFAULT NULL,
clicked_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY one_send (template_id,email_hash,object_type,object_id,occurrence_start),
KEY by_object (object_type,object_id),
KEY by_user (user_id),
KEY by_template (template_id,state)",

			'suppressions'         => 'email_hash char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
source tinyint(3) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
PRIMARY KEY  (email_hash)',

			'runs'                 => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
kind varchar(32) NOT NULL,
status tinyint(3) unsigned NOT NULL,
dry_run tinyint(3) unsigned NOT NULL DEFAULT 0,
cursor_json longtext DEFAULT NULL,
totals_json longtext DEFAULT NULL,
actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
started_at datetime DEFAULT NULL,
finished_at datetime DEFAULT NULL,
options_json longtext DEFAULT NULL,
control tinyint(3) unsigned NOT NULL DEFAULT 0,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
locked_until datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
KEY by_kind (kind,status),
KEY by_parent (parent_id)',

			'findings'             => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
run_id bigint(20) unsigned NOT NULL,
check_key varchar(32) NOT NULL,
object_type tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
detail longtext DEFAULT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY  (id),
KEY by_run (run_id,check_key)',

			'import_map'           => "source_lms varchar(20) NOT NULL,
source_type varchar(24) NOT NULL,
source_key varchar(64) NOT NULL,
object_type tinyint(3) unsigned NOT NULL,
object_id bigint(20) unsigned NOT NULL,
run_id bigint(20) unsigned NOT NULL,
checksum char(40) NOT NULL DEFAULT '',
mode tinyint(3) unsigned NOT NULL DEFAULT 1,
prev_json longtext DEFAULT NULL,
PRIMARY KEY  (source_lms,source_type,source_key),
KEY by_object (object_type,object_id),
KEY by_run_kind (run_id,object_type,object_id)",

			'import_archive'       => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
run_id bigint(20) unsigned NOT NULL,
source_lms varchar(20) NOT NULL,
source_table varchar(64) NOT NULL,
source_key varchar(64) NOT NULL,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
course_id bigint(20) unsigned NOT NULL DEFAULT 0,
reason tinyint(3) unsigned NOT NULL,
payload longtext NOT NULL,
archived_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY source (source_lms,source_table,source_key),
KEY by_user (user_id),
KEY by_run (run_id)',

			'ui_prefs'             => 'user_id bigint(20) unsigned NOT NULL,
pref_key varchar(32) NOT NULL,
value longtext NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (user_id,pref_key)',

			'locks'                => 'name varchar(64) NOT NULL,
PRIMARY KEY  (name)',

			'log'                  => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
logged_at datetime NOT NULL,
level tinyint(3) unsigned NOT NULL,
channel varchar(32) NOT NULL,
actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
message varchar(190) NOT NULL,
context longtext DEFAULT NULL,
PRIMARY KEY  (id),
KEY by_time (logged_at),
KEY by_channel (channel,logged_at)',

			'course_levels'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
label varchar(100) NOT NULL DEFAULT '',
position smallint(5) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
builtin tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_position (state,position)",

			'course_badges'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
label varchar(60) NOT NULL DEFAULT '',
color char(7) NOT NULL DEFAULT '',
icon varchar(64) NOT NULL DEFAULT '',
position smallint(5) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
builtin tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_position (state,position)",

			'course_points'        => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
kind tinyint(3) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
text varchar(500) NOT NULL,
PRIMARY KEY  (id),
KEY by_course (course_id,kind,position)',

			'course_texts'         => 'course_id bigint(20) unsigned NOT NULL,
kind tinyint(3) unsigned NOT NULL,
html longtext NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (course_id,kind)',

			'course_files'         => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
attachment_id bigint(20) unsigned DEFAULT NULL,
url text DEFAULT NULL,
title varchar(190) NOT NULL DEFAULT '',
access tinyint(3) unsigned NOT NULL DEFAULT 1,
sort smallint(5) unsigned NOT NULL DEFAULT 0,
added_by bigint(20) unsigned NOT NULL DEFAULT 0,
added_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_course (course_id,access,sort),
KEY by_attachment (attachment_id)",

			'faq_groups'           => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
slug varchar(190) NOT NULL,
name varchar(190) NOT NULL DEFAULT '',
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY siblings (parent_id,state,position)",

			'faqs'                 => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
group_id bigint(20) unsigned NOT NULL DEFAULT 0,
question varchar(500) NOT NULL,
answer longtext NOT NULL,
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_group (group_id,state,position)',

			'faq_links'            => 'target_type tinyint(3) unsigned NOT NULL,
target_id bigint(20) unsigned NOT NULL,
kind tinyint(3) unsigned NOT NULL,
ref_id bigint(20) unsigned NOT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (target_type,target_id,kind,ref_id),
KEY by_target (target_type,target_id,position),
KEY by_ref (kind,ref_id)',

			'media'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
owner_type tinyint(3) unsigned NOT NULL,
owner_id bigint(20) unsigned NOT NULL,
role tinyint(3) unsigned NOT NULL DEFAULT 1,
kind tinyint(3) unsigned NOT NULL,
source tinyint(3) unsigned NOT NULL,
attachment_id bigint(20) unsigned DEFAULT NULL,
url text DEFAULT NULL,
embed text DEFAULT NULL,
ref varchar(190) NOT NULL DEFAULT '',
poster_id bigint(20) unsigned DEFAULT NULL,
width smallint(5) unsigned DEFAULT NULL,
duration_sec int(10) unsigned DEFAULT NULL,
pages smallint(5) unsigned DEFAULT NULL,
required_percent tinyint(3) unsigned DEFAULT NULL,
flags tinyint(3) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY owner (owner_type,owner_id,role),
KEY by_attachment (attachment_id)",

			'media_tracks'         => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
media_id bigint(20) unsigned NOT NULL,
attachment_id bigint(20) unsigned NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
label varchar(60) NOT NULL DEFAULT '',
sort smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_media (media_id,sort)",

			'media_progress'       => 'user_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
media_id bigint(20) unsigned NOT NULL,
position_sec int(10) unsigned NOT NULL DEFAULT 0,
max_percent tinyint(3) unsigned NOT NULL DEFAULT 0,
seconds_played int(10) unsigned NOT NULL DEFAULT 0,
pages_seen smallint(5) unsigned NOT NULL DEFAULT 0,
pages_map varbinary(625) DEFAULT NULL,
beat_at datetime DEFAULT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (user_id,item_id,cycle),
KEY by_media (media_id)',

			'course_waitlist'      => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned DEFAULT NULL,
email varchar(190) DEFAULT NULL,
lang varchar(12) NOT NULL DEFAULT '',
token char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
confirmed_at datetime DEFAULT NULL,
created_at datetime NOT NULL,
notified_at datetime DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY by_user (course_id,user_id),
UNIQUE KEY by_email (course_id,email),
UNIQUE KEY token (token),
KEY to_notify (course_id,notified_at)",

			'discussions'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned DEFAULT NULL,
body text NOT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
is_answer tinyint(3) unsigned NOT NULL DEFAULT 0,
pinned tinyint(3) unsigned NOT NULL DEFAULT 0,
reply_count smallint(5) unsigned NOT NULL DEFAULT 0,
lang varchar(12) NOT NULL DEFAULT '',
last_activity_at datetime NOT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
author_label varchar(100) NOT NULL DEFAULT '',
is_private tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_unit (course_id,unit_id,parent_id,state,last_activity_at),
KEY by_parent (parent_id,created_at),
KEY by_user (user_id,created_at),
KEY by_course (course_id,state,last_activity_at)",

			'course_reviews'       => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
course_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned DEFAULT NULL,
rating tinyint(3) unsigned NOT NULL,
title varchar(190) NOT NULL DEFAULT '',
body text NOT NULL,
lang varchar(12) NOT NULL DEFAULT '',
state tinyint(3) unsigned NOT NULL DEFAULT 1,
source tinyint(3) unsigned NOT NULL DEFAULT 1,
feedback_response_id bigint(20) unsigned DEFAULT NULL,
reply text DEFAULT NULL,
replied_by bigint(20) unsigned DEFAULT NULL,
replied_at datetime DEFAULT NULL,
approved_by bigint(20) unsigned DEFAULT NULL,
approved_at datetime DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
author_label varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
UNIQUE KEY one_each (course_id,user_id),
KEY by_course (course_id,state,created_at),
KEY by_user (user_id)",

			'question_draws'       => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
quiz_unit_id bigint(20) unsigned NOT NULL,
bank_unit_id bigint(20) unsigned NOT NULL,
category_id bigint(20) unsigned DEFAULT NULL,
count smallint(5) unsigned NOT NULL DEFAULT 1,
position smallint(5) unsigned NOT NULL DEFAULT 0,
kind tinyint(3) unsigned NOT NULL DEFAULT 0,
difficulty_min tinyint(3) unsigned NOT NULL DEFAULT 0,
difficulty_max tinyint(3) unsigned NOT NULL DEFAULT 0,
skill_id bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY  (id),
KEY by_quiz (quiz_unit_id,position),
KEY by_bank (bank_unit_id)',

			'points_rules'         => 'event tinyint(3) unsigned NOT NULL,
points int(11) NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
updated_at datetime NOT NULL,
PRIMARY KEY  (event)',

			'points_ledger'        => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
event tinyint(3) unsigned NOT NULL,
object_type tinyint(3) unsigned NOT NULL DEFAULT 0,
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
points int(11) NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_user (user_id,created_at),
KEY by_award (user_id,event,object_type,object_id)',

			'points_totals'        => 'user_id bigint(20) unsigned NOT NULL,
total int(11) NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (user_id),
KEY by_total (total)',

			'wishlists'            => 'user_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY  (user_id,course_id),
KEY by_course (course_id)',

			'grade_scales'         => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(190) NOT NULL DEFAULT '',
state tinyint(3) unsigned NOT NULL DEFAULT 1,
is_default tinyint(3) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id)",

			'grade_bands'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
scale_id bigint(20) unsigned NOT NULL,
min_percent decimal(5,2) NOT NULL DEFAULT 0.00,
label varchar(60) NOT NULL DEFAULT '',
points decimal(6,2) DEFAULT NULL,
position smallint(5) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY by_scale (scale_id,min_percent)",

			'question_categories'  => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
slug varchar(190) NOT NULL,
name varchar(190) NOT NULL DEFAULT '',
position int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
skill_id bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY by_position (state,position)",

			'skills'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
uuid char(36) NOT NULL,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
slug varchar(190) NOT NULL,
name varchar(190) NOT NULL DEFAULT '',
code varchar(64) NOT NULL DEFAULT '',
framework varchar(64) NOT NULL DEFAULT '',
level_label varchar(64) NOT NULL DEFAULT '',
default_time_sec smallint(5) unsigned DEFAULT NULL,
mastery_theta decimal(5,2) DEFAULT NULL,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
position int(10) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY by_uuid (uuid),
UNIQUE KEY slug (slug),
KEY by_parent (parent_id,position)",

			'skill_paths'          => 'ancestor_id bigint(20) unsigned NOT NULL,
descendant_id bigint(20) unsigned NOT NULL,
depth smallint(5) unsigned NOT NULL,
PRIMARY KEY  (ancestor_id,descendant_id),
KEY by_descendant (descendant_id,depth)',

			'skill_prereqs'        => 'skill_id bigint(20) unsigned NOT NULL,
prereq_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (skill_id,prereq_id),
KEY by_prereq (prereq_id)',

			'submissions'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
course_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
try_no smallint(5) unsigned NOT NULL DEFAULT 1,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
body longtext DEFAULT NULL,
files text DEFAULT NULL,
staff_files text DEFAULT NULL,
feedback text DEFAULT NULL,
grader_id bigint(20) unsigned NOT NULL DEFAULT 0,
score decimal(5,2) DEFAULT NULL,
lang varchar(12) NOT NULL DEFAULT '',
started_at datetime NOT NULL,
deadline_at datetime DEFAULT NULL,
submitted_at datetime DEFAULT NULL,
graded_at datetime DEFAULT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
UNIQUE KEY numbering (user_id,item_id,cycle,try_no),
KEY queue (status,submitted_at),
KEY by_unit (unit_id,status),
KEY by_course (course_id,status,submitted_at)",

			'external_courses'     => "course_id bigint(20) unsigned NOT NULL,
provider tinyint(3) unsigned NOT NULL DEFAULT 9,
ref varchar(64) NOT NULL DEFAULT '',
url text NOT NULL,
affiliate_url text DEFAULT NULL,
button_label varchar(100) NOT NULL DEFAULT '',
price_minor bigint(20) DEFAULT NULL,
sale_price_minor bigint(20) DEFAULT NULL,
currency char(3) NOT NULL DEFAULT '',
rating_avg decimal(3,2) DEFAULT NULL,
rating_count int(10) unsigned DEFAULT NULL,
rating_dist varchar(100) NOT NULL DEFAULT '',
students int(10) unsigned DEFAULT NULL,
lectures smallint(5) unsigned DEFAULT NULL,
quizzes smallint(5) unsigned DEFAULT NULL,
content_sec int(10) unsigned DEFAULT NULL,
has_certificate tinyint(3) unsigned NOT NULL DEFAULT 0,
caption_langs varchar(255) NOT NULL DEFAULT '',
instructors_label varchar(255) NOT NULL DEFAULT '',
image_url text DEFAULT NULL,
outline longtext DEFAULT NULL,
source tinyint(3) unsigned NOT NULL DEFAULT 1,
account_id bigint(20) unsigned DEFAULT NULL,
feed_id bigint(20) unsigned DEFAULT NULL,
clicks int(10) unsigned NOT NULL DEFAULT 0,
state tinyint(3) unsigned NOT NULL DEFAULT 1,
synced_at datetime DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (course_id),
KEY by_ref (provider,ref),
KEY by_account (account_id),
KEY by_feed (feed_id,state)",

			'import_run_events'    => 'run_id bigint(20) unsigned NOT NULL,
event_id bigint(20) unsigned NOT NULL,
PRIMARY KEY  (run_id,event_id)',

			'quiz_reports'         => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
attempt_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
question_id bigint(20) unsigned NOT NULL DEFAULT 0,
version_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL,
reason tinyint(3) unsigned NOT NULL DEFAULT 9,
message text NOT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT 1,
reply text DEFAULT NULL,
replied_by bigint(20) unsigned NOT NULL DEFAULT 0,
replied_at datetime DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_status (status,created_at),
KEY by_attempt (attempt_id,user_id,question_id),
KEY by_user (user_id,created_at)',

			'answer_corrections'   => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
attempt_id bigint(20) unsigned NOT NULL,
question_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
report_id bigint(20) unsigned NOT NULL DEFAULT 0,
kind tinyint(3) unsigned NOT NULL DEFAULT 1,
reason varchar(500) NOT NULL DEFAULT '',
before_response longtext NOT NULL,
before_verdict tinyint(3) unsigned NOT NULL DEFAULT 0,
before_points decimal(6,2) DEFAULT NULL,
after_response longtext NOT NULL,
after_verdict tinyint(3) unsigned NOT NULL DEFAULT 0,
after_points decimal(6,2) DEFAULT NULL,
before_pct decimal(5,2) DEFAULT NULL,
after_pct decimal(5,2) DEFAULT NULL,
before_result tinyint(3) unsigned NOT NULL DEFAULT 0,
after_result tinyint(3) unsigned NOT NULL DEFAULT 0,
notified tinyint(3) unsigned NOT NULL DEFAULT 0,
corrected_by bigint(20) unsigned NOT NULL DEFAULT 0,
corrected_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_attempt (attempt_id,question_id),
KEY by_user (user_id),
KEY by_report (report_id)",

			'attempt_allowances'   => 'user_id bigint(20) unsigned NOT NULL,
item_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
extra smallint(5) unsigned NOT NULL DEFAULT 1,
base smallint(5) unsigned NOT NULL DEFAULT 0,
granted_by bigint(20) unsigned NOT NULL DEFAULT 0,
granted_at datetime NOT NULL,
PRIMARY KEY  (user_id,item_id,cycle)',

		) + self::h5p_definitions();
	}

	private static function h5p_definitions(): array {
		return array(

			'h5p_libraries'              => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(127) NOT NULL,
title varchar(255) NOT NULL,
major_version int(10) unsigned NOT NULL,
minor_version int(10) unsigned NOT NULL,
patch_version int(10) unsigned NOT NULL,
runnable tinyint(3) unsigned NOT NULL DEFAULT 0,
restricted tinyint(3) unsigned NOT NULL DEFAULT 0,
fullscreen tinyint(3) unsigned NOT NULL DEFAULT 0,
embed_types varchar(255) NOT NULL DEFAULT '',
preloaded_js text DEFAULT NULL,
preloaded_css text DEFAULT NULL,
drop_library_css text DEFAULT NULL,
semantics longtext NOT NULL,
tutorial_url varchar(1023) NOT NULL DEFAULT '',
has_icon tinyint(3) unsigned NOT NULL DEFAULT 0,
metadata_settings text DEFAULT NULL,
add_to text DEFAULT NULL,
source tinyint(3) unsigned NOT NULL DEFAULT 1,
installed_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY name_version (name,major_version,minor_version,patch_version),
KEY runnable (runnable)",

			'h5p_libraries_libraries'    => 'library_id bigint(20) unsigned NOT NULL,
required_library_id bigint(20) unsigned NOT NULL,
dependency_type varchar(31) NOT NULL,
PRIMARY KEY  (library_id,required_library_id)',

			'h5p_libraries_languages'    => 'library_id bigint(20) unsigned NOT NULL,
language_code varchar(31) NOT NULL,
translation longtext NOT NULL,
PRIMARY KEY  (library_id,language_code)',

			'h5p_libraries_cachedassets' => 'library_id bigint(20) unsigned NOT NULL,
hash varchar(64) NOT NULL,
PRIMARY KEY  (library_id,hash)',

			'h5p_libraries_hub_cache'    => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
machine_name varchar(127) NOT NULL,
major_version int(10) unsigned NOT NULL,
minor_version int(10) unsigned NOT NULL,
patch_version int(10) unsigned NOT NULL,
h5p_major_version int(10) unsigned DEFAULT NULL,
h5p_minor_version int(10) unsigned DEFAULT NULL,
title varchar(255) NOT NULL,
summary text NOT NULL,
description text NOT NULL,
icon varchar(511) NOT NULL DEFAULT '',
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
is_recommended tinyint(3) unsigned NOT NULL DEFAULT 0,
popularity int(10) unsigned NOT NULL DEFAULT 0,
screenshots text DEFAULT NULL,
license text DEFAULT NULL,
example varchar(511) NOT NULL DEFAULT '',
tutorial varchar(511) DEFAULT NULL,
keywords text DEFAULT NULL,
categories text DEFAULT NULL,
owner varchar(511) DEFAULT NULL,
wcag tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (id),
KEY name_version (machine_name,major_version,minor_version,patch_version)",

			'h5p_contents'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
library_id bigint(20) unsigned NOT NULL,
parameters longtext NOT NULL,
filtered longtext NOT NULL,
slug varchar(127) NOT NULL DEFAULT '',
embed_type varchar(127) NOT NULL DEFAULT '',
disable int(10) unsigned NOT NULL DEFAULT 0,
content_type varchar(127) DEFAULT NULL,
authors longtext DEFAULT NULL,
source varchar(2083) DEFAULT NULL,
year_from int(10) unsigned DEFAULT NULL,
year_to int(10) unsigned DEFAULT NULL,
license varchar(32) DEFAULT NULL,
license_version varchar(10) DEFAULT NULL,
license_extras longtext DEFAULT NULL,
author_comments longtext DEFAULT NULL,
changes longtext DEFAULT NULL,
default_language varchar(32) DEFAULT NULL,
a11y_title varchar(255) DEFAULT NULL,
shared tinyint(3) unsigned NOT NULL DEFAULT 0,
language varchar(16) NOT NULL DEFAULT '',
params_hash varchar(64) NOT NULL DEFAULT '',
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
updated_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
archived_at datetime DEFAULT NULL,
imported_from varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
KEY by_library (library_id),
KEY by_updated (archived_at,updated_at),
KEY by_hash (params_hash)",

			'h5p_contents_libraries'     => 'content_id bigint(20) unsigned NOT NULL,
library_id bigint(20) unsigned NOT NULL,
dependency_type varchar(31) NOT NULL,
weight smallint(5) unsigned NOT NULL DEFAULT 0,
drop_css tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (content_id,library_id,dependency_type)',

			'h5p_user_data'              => 'content_id bigint(20) unsigned NOT NULL,
user_id bigint(20) unsigned NOT NULL,
sub_content_id bigint(20) unsigned NOT NULL DEFAULT 0,
data_type varchar(127) NOT NULL,
unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
data longtext NOT NULL,
preload tinyint(3) unsigned NOT NULL DEFAULT 0,
invalidate tinyint(3) unsigned NOT NULL DEFAULT 0,
updated_at datetime NOT NULL,
PRIMARY KEY  (content_id,user_id,sub_content_id,data_type,unit_id,cycle),
KEY by_user (user_id,content_id,cycle)',

			'h5p_results'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
unit_id bigint(20) unsigned NOT NULL,
cycle smallint(5) unsigned NOT NULL DEFAULT 1,
content_id bigint(20) unsigned NOT NULL,
params_hash varchar(64) NOT NULL DEFAULT '',
library_name varchar(127) NOT NULL DEFAULT '',
library_version varchar(31) NOT NULL DEFAULT '',
attempt_no smallint(5) unsigned NOT NULL DEFAULT 1,
score decimal(10,2) DEFAULT NULL,
max_score decimal(10,2) DEFAULT NULL,
scaled decimal(6,5) DEFAULT NULL,
completed tinyint(3) unsigned NOT NULL DEFAULT 0,
success tinyint(3) unsigned DEFAULT NULL,
duration_sec int(10) unsigned NOT NULL DEFAULT 0,
started_at datetime DEFAULT NULL,
finished_at datetime NOT NULL,
statement_id varchar(64) NOT NULL,
payload text DEFAULT NULL,
PRIMARY KEY  (id),
UNIQUE KEY by_statement (statement_id),
KEY by_unit (unit_id,user_id,cycle),
KEY by_content (content_id)",

			'h5p_tmpfiles'               => 'id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
path varchar(255) NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY  (id),
KEY by_created (created_at)',

		);
	}

	public const H5P_TABLES = array(
		'h5p_libraries',
		'h5p_libraries_libraries',
		'h5p_libraries_languages',
		'h5p_libraries_cachedassets',
		'h5p_libraries_hub_cache',
		'h5p_contents',
		'h5p_contents_libraries',
		'h5p_user_data',
		'h5p_results',
		'h5p_tmpfiles',
	);

	public static function utf8_columns(): void {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- migration.
		$columns  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s AND CHARACTER_SET_NAME = 'ascii' ORDER BY TABLE_NAME, ORDINAL_POSITION",
				$wpdb->esc_like( $wpdb->prefix . 'rooster_lms_' ) . '%'
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$by_table = array();
		foreach ( (array) $columns as $column ) {
			$by_table[ (string) $column['TABLE_NAME'] ][] = $column;
		}
		foreach ( $by_table as $table => $table_columns ) {
			Collation::modify_binary( $table, $table_columns );
		}
	}
}