<?php
/**
 * Database schema.
 *
 * SpaceBay stores community content in its own tables because feeds,
 * comments and reactions are high-volume relational data that would overload
 * wp_posts and wp_postmeta. Users stay in wp_users and settings in wp_options.
 *
 * @package SpaceBay
 */

namespace SpaceBay\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Creates and updates SpaceBay tables with dbDelta().
 */
final class Schema {

	/**
	 * Creates or updates every table.
	 *
	 * @return void
	 */
	public static function create() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();

		foreach ( self::definitions() as $short_name => $columns ) {
			$table = Tables::name( $short_name );
			dbDelta( "CREATE TABLE {$table} (\n{$columns}\n) {$charset};" );
		}
	}

	/**
	 * Drops every table. Used only by uninstall when the site owner opted in.
	 *
	 * @return void
	 */
	public static function drop() {
		global $wpdb;

		foreach ( Tables::all() as $table ) {
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Uninstall only, after the owner opted in.
		}
	}

	/**
	 * Column definitions per table, in dbDelta format.
	 *
	 * @return array<string, string>
	 */
	private static function definitions() {
		return array(
			'spaces'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
type varchar(20) NOT NULL DEFAULT 'space',
title varchar(191) NOT NULL DEFAULT '',
slug varchar(191) NOT NULL DEFAULT '',
description text NULL,
logo_id bigint(20) unsigned NOT NULL DEFAULT 0,
cover_id bigint(20) unsigned NOT NULL DEFAULT 0,
privacy varchar(20) NOT NULL DEFAULT 'public',
status varchar(20) NOT NULL DEFAULT 'published',
settings longtext NULL,
position int(11) NOT NULL DEFAULT 0,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY slug (slug),
KEY parent_id (parent_id),
KEY privacy_status (privacy,status)",

			'space_members'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
space_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
role varchar(20) NOT NULL DEFAULT 'member',
status varchar(20) NOT NULL DEFAULT 'active',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY space_user (space_id,user_id),
KEY user_status (user_id,status),
KEY status_space (status,space_id)",

			'posts'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
space_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
type varchar(20) NOT NULL DEFAULT 'text',
title varchar(255) NOT NULL DEFAULT '',
slug varchar(191) NOT NULL DEFAULT '',
message longtext NULL,
message_rendered longtext NULL,
status varchar(20) NOT NULL DEFAULT 'published',
is_pinned tinyint(1) NOT NULL DEFAULT 0,
is_featured tinyint(1) NOT NULL DEFAULT 0,
comments_count int(11) unsigned NOT NULL DEFAULT 0,
reactions_count int(11) unsigned NOT NULL DEFAULT 0,
meta longtext NULL,
scheduled_at datetime NULL DEFAULT NULL,
last_activity_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY space_status_created (space_id,status,created_at),
KEY status_created (status,created_at),
KEY status_activity (status,last_activity_at),
KEY user_id (user_id),
KEY slug (slug)",

			'comments'             => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT 0,
parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
message longtext NULL,
message_rendered longtext NULL,
status varchar(20) NOT NULL DEFAULT 'published',
is_pinned tinyint(1) NOT NULL DEFAULT 0,
reactions_count int(11) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY post_status (post_id,status),
KEY parent_id (parent_id),
KEY user_id (user_id),
KEY post_thread (post_id,parent_id,status)",

			'reactions'            => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
object_type varchar(20) NOT NULL DEFAULT 'post',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
type varchar(20) NOT NULL DEFAULT 'like',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY object_user_type (object_type,object_id,user_id,type),
KEY user_type (user_id,type)",

			'terms'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
taxonomy varchar(20) NOT NULL DEFAULT 'topic',
title varchar(191) NOT NULL DEFAULT '',
slug varchar(191) NOT NULL DEFAULT '',
description text NULL,
settings longtext NULL,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY taxonomy_slug (taxonomy,slug)",

			'term_relations'       => 'term_id bigint(20) unsigned NOT NULL DEFAULT 0,
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY  (term_id,object_id),
KEY object_id (object_id)',

			'media'                => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
object_type varchar(20) NOT NULL DEFAULT '',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
file_path varchar(255) NOT NULL DEFAULT '',
file_url varchar(255) NOT NULL DEFAULT '',
mime_type varchar(100) NOT NULL DEFAULT '',
file_size bigint(20) unsigned NOT NULL DEFAULT 0,
settings longtext NULL,
status varchar(20) NOT NULL DEFAULT 'pending',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY object (object_type,object_id),
KEY user_status (user_id,status)",

			'profiles'             => "user_id bigint(20) unsigned NOT NULL DEFAULT 0,
avatar_id bigint(20) unsigned NOT NULL DEFAULT 0,
cover_id bigint(20) unsigned NOT NULL DEFAULT 0,
headline varchar(191) NOT NULL DEFAULT '',
bio text NULL,
website varchar(255) NOT NULL DEFAULT '',
location varchar(191) NOT NULL DEFAULT '',
custom_fields longtext NULL,
points int(11) NOT NULL DEFAULT 0,
privacy varchar(20) NOT NULL DEFAULT 'members',
status varchar(20) NOT NULL DEFAULT 'active',
last_activity_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (user_id),
KEY status_activity (status,last_activity_at),
KEY points (points)",

			'notifications'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
action varchar(50) NOT NULL DEFAULT '',
object_type varchar(20) NOT NULL DEFAULT '',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
content text NULL,
url varchar(255) NOT NULL DEFAULT '',
is_read tinyint(1) NOT NULL DEFAULT 0,
email_pending tinyint(1) NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY user_read_created (user_id,is_read,created_at),
KEY object (object_type,object_id),
KEY email_pending (email_pending,id)",

			'reports'              => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
object_type varchar(20) NOT NULL DEFAULT '',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
reporter_id bigint(20) unsigned NOT NULL DEFAULT 0,
reason varchar(50) NOT NULL DEFAULT '',
note text NULL,
status varchar(20) NOT NULL DEFAULT 'open',
handled_by bigint(20) unsigned NOT NULL DEFAULT 0,
handled_at datetime NULL DEFAULT NULL,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY status_created (status,created_at),
KEY object (object_type,object_id)",

			'conversations'        => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
type varchar(20) NOT NULL DEFAULT 'direct',
title varchar(191) NOT NULL DEFAULT '',
direct_key varchar(64) NULL DEFAULT NULL,
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
last_message_id bigint(20) unsigned NOT NULL DEFAULT 0,
last_message_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY direct_key (direct_key),
KEY last_message_at (last_message_at)",

			'conversation_members' => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
conversation_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
role varchar(20) NOT NULL DEFAULT 'member',
status varchar(20) NOT NULL DEFAULT 'active',
last_read_id bigint(20) unsigned NOT NULL DEFAULT 0,
is_muted tinyint(1) NOT NULL DEFAULT 0,
joined_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY conversation_user (conversation_id,user_id),
KEY user_status (user_id,status)",

			'messages'             => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
conversation_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
type varchar(20) NOT NULL DEFAULT 'text',
message longtext NULL,
message_rendered longtext NULL,
meta longtext NULL,
status varchar(20) NOT NULL DEFAULT 'published',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY conversation_id (conversation_id,id),
KEY user_id (user_id)",

			'points'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
points int(11) NOT NULL DEFAULT 0,
reason varchar(20) NOT NULL DEFAULT '',
ref varchar(100) NOT NULL DEFAULT '',
object_type varchar(20) NOT NULL DEFAULT '',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
note varchar(191) NOT NULL DEFAULT '',
created_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY user_ref (user_id,ref),
KEY created_user (created_at,user_id),
KEY user_reason (user_id,reason),
KEY ref (ref),
KEY created_user_points (created_at,user_id,points)",

			'user_badges'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
badge varchar(40) NOT NULL DEFAULT '',
awarded_by bigint(20) unsigned NOT NULL DEFAULT 0,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY user_badge (user_id,badge),
KEY badge (badge)",

			'events'               => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
space_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
title varchar(191) NOT NULL DEFAULT '',
description longtext NULL,
description_rendered longtext NULL,
location_type varchar(20) NOT NULL DEFAULT 'online',
location varchar(255) NOT NULL DEFAULT '',
online_url varchar(255) NOT NULL DEFAULT '',
capacity int(11) unsigned NOT NULL DEFAULT 0,
status varchar(20) NOT NULL DEFAULT 'published',
going_count int(11) unsigned NOT NULL DEFAULT 0,
maybe_count int(11) unsigned NOT NULL DEFAULT 0,
reminder_sent tinyint(1) NOT NULL DEFAULT 0,
starts_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
ends_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY space_starts (space_id,starts_at),
KEY status_starts (status,starts_at),
KEY ends_at (ends_at),
KEY user_id (user_id)",

			'event_rsvps'          => "id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
event_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
status varchar(20) NOT NULL DEFAULT 'going',
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (id),
UNIQUE KEY event_user (event_id,user_id),
KEY user_status (user_id,status),
KEY event_status (event_id,status)",

			'meta'                 => "meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
object_type varchar(20) NOT NULL DEFAULT '',
object_id bigint(20) unsigned NOT NULL DEFAULT 0,
meta_key varchar(191) NOT NULL DEFAULT '',
meta_value longtext NULL,
PRIMARY KEY  (meta_id),
KEY object (object_type,object_id),
KEY meta_key (meta_key)",
		);
	}
}