prefix`. * @param string $collate `$wpdb->get_charset_collate()`. * @return array Statement per table key. */ public static function statements( string $prefix, string $collate ): array { $t = $prefix . 'interviewnea_'; return array( 'sessions' => "CREATE TABLE {$t}sessions ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, title varchar(190) NOT NULL, session_date date NOT NULL, start_time time NOT NULL, end_time time NOT NULL, meet_url text NOT NULL, checkin_before_minutes smallint(5) unsigned NOT NULL DEFAULT 15, status varchar(24) NOT NULL DEFAULT 'scheduled', current_candidate_id bigint(20) unsigned NOT NULL DEFAULT 0, pause_until datetime NULL, pause_reason varchar(120) NOT NULL DEFAULT '', created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), KEY session_date (session_date), KEY status (status) ) ENGINE=InnoDB {$collate};", 'candidates' => "CREATE TABLE {$t}candidates ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, session_id bigint(20) unsigned NOT NULL, full_name varchar(190) NOT NULL, name_normalized varchar(190) NOT NULL, email varchar(190) NOT NULL, email_key char(64) NOT NULL, phone varchar(30) NOT NULL, phone_normalized varchar(24) NOT NULL, queue_order bigint(20) unsigned NOT NULL DEFAULT 0, report_time time NULL, status varchar(24) NOT NULL DEFAULT 'invited', token_hash char(64) NOT NULL DEFAULT '', checkin_at datetime NULL, queued_at datetime NULL, last_seen_at datetime NULL, called_at datetime NULL, called_until datetime NULL, join_requested_at datetime NULL, joined_at datetime NULL, late_at datetime NULL, reminder_due_at datetime NULL, reminder_status varchar(20) NOT NULL DEFAULT 'pending', reminder_sent_at datetime NULL, reminder_attempted_at datetime NULL, join_reminder_status varchar(20) NOT NULL DEFAULT 'pending', join_reminder_sent_at datetime NULL, completed_at datetime NULL, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY session_email (session_id,email_key), KEY session_queue (session_id,status,queue_order), KEY token_hash (token_hash), KEY email_key (email_key) ) ENGINE=InnoDB {$collate};", 'events' => "CREATE TABLE {$t}events ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, session_id bigint(20) unsigned NOT NULL, candidate_id bigint(20) unsigned NOT NULL DEFAULT 0, event_type varchar(45) NOT NULL, description varchar(190) NOT NULL DEFAULT '', actor_id bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL, PRIMARY KEY (id), KEY session_created (session_id,id), KEY candidate_id (candidate_id) ) ENGINE=InnoDB {$collate};", 'emails' => "CREATE TABLE {$t}emails ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, session_id bigint(20) unsigned NOT NULL, candidate_id bigint(20) unsigned NOT NULL DEFAULT 0, recipient varchar(190) NOT NULL, subject text NOT NULL, body_html longtext NOT NULL, mail_type varchar(24) NOT NULL DEFAULT 'reminder', delivery_status varchar(24) NOT NULL DEFAULT 'failed', detail varchar(255) NOT NULL DEFAULT '', created_at datetime NOT NULL, PRIMARY KEY (id), KEY session_created (session_id,id), KEY candidate_id (candidate_id), KEY created_at (created_at) ) ENGINE=InnoDB {$collate};", ); } }