<?php
/**
 * BitForgeNet Guest Tickets - Installation, database schema and migrations.
 *
 * @package BitForgeNet_Guest_Tickets
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

// This file talks to the plugin's own tables. WordPress has no API for them --
// they are not posts and not meta -- so $wpdb is the only way in. The rows
// change on every booking and every scan, which makes an object cache wrong
// more often than it is right. Table names are built from $wpdb->prefix and
// never from a request, so interpolating one carries nothing to inject; every
// value goes through $wpdb->prepare(), and the few identifiers that cannot
// (ORDER BY columns) are matched against a whitelist first.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter


/**
 * Handles schema creation, upgrades, roles and protected storage.
 */
class BFNET_Install {

	/**
	 * Capabilities granted by the plugin.
	 *
	 * @var array
	 */
	public static $capabilities = array(
		'bfnet_manage_events',
		'bfnet_manage_orders',
		'bfnet_validate_payments',
		'bfnet_manage_tickets',
		'bfnet_scan_tickets',
		'bfnet_manage_templates',
		'bfnet_view_reports',
		'bfnet_manage_settings',
	);

	/**
	 * Run on plugin activation.
	 *
	 * @return void
	 */
	public static function activate() {
		self::create_tables();
		self::add_roles();
		self::create_storage();
		self::seed_defaults();

		update_option( 'bfnet_db_version', BFNET_DB_VERSION );
		update_option( 'bfnet_activated_at', current_time( 'mysql', true ) );

		if ( ! wp_next_scheduled( 'bfnet_cron_hourly' ) ) {
			wp_schedule_event( time() + 300, 'hourly', 'bfnet_cron_hourly' );
		}
		if ( ! wp_next_scheduled( 'bfnet_cron_daily' ) ) {
			wp_schedule_event( time() + 600, 'daily', 'bfnet_cron_daily' );
		}

		flush_rewrite_rules();
	}

	/**
	 * Run on plugin deactivation. Data is preserved.
	 *
	 * @return void
	 */
	public static function deactivate() {
		wp_clear_scheduled_hook( 'bfnet_cron_hourly' );
		wp_clear_scheduled_hook( 'bfnet_cron_daily' );
		flush_rewrite_rules();
	}

	/**
	 * Run pending migrations when the stored DB version is behind.
	 *
	 * @return void
	 */
	/**
	 * The columns a table actually has right now.
	 *
	 * Cached per request, because it is asked once per write.
	 *
	 * @param string $table Fully qualified table name.
	 * @return array
	 */
	public static function columns( $table ) {
		static $cache = array();

		if ( isset( $cache[ $table ] ) ) {
			return $cache[ $table ];
		}

		global $wpdb;

		$names = $wpdb->get_col( "SHOW COLUMNS FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL
		$cache[ $table ] = is_array( $names ) ? $names : array();

		return $cache[ $table ];
	}

	/**
	 * Drop anything the table cannot store.
	 *
	 * A single unknown column makes the whole INSERT or UPDATE fail, and WordPress
	 * reports that failure to nobody: the form comes back looking normal and not
	 * one field has been saved. That is a miserable way to lose an afternoon, so
	 * every write is filtered through here. A schema that has not caught up yet
	 * costs you the new fields, never the old ones.
	 *
	 * @param string $table Table name.
	 * @param array  $row   Column => value.
	 * @return array
	 */
	public static function writable( $table, array $row ) {
		$columns = self::columns( $table );

		if ( ! $columns ) {
			return $row;
		}

		$clean = array_intersect_key( $row, array_flip( $columns ) );

		// If something was dropped, the schema is behind. Say so once, in the log,
		// so the cause is findable rather than mysterious.
		if ( count( $clean ) !== count( $row ) && class_exists( 'BFNET_Log' ) ) {
			$missing = array_keys( array_diff_key( $row, $clean ) );
			BFNET_Log::audit(
				'system',
				0,
				'schema_behind',
				array( 'table' => $table, 'missing' => implode( ', ', $missing ) ),
				'warning'
			);
		}

		return $clean;
	}

	/**
	 * Force the schema back into shape, whatever the recorded version says.
	 *
	 * @return void
	 */
	public static function repair() {
		self::create_tables();
		self::create_storage();
		update_option( 'bfnet_db_version', BFNET_DB_VERSION );
	}

	public static function maybe_upgrade() {
		$stored = get_option( 'bfnet_db_version', '0' );

		if ( version_compare( $stored, BFNET_DB_VERSION, '>=' ) ) {
			// Recorded as current. Confirm it, cheaply, against one column added
			// by the most recent migration — a dbDelta interrupted half way
			// through leaves the version bumped and the table unchanged, and
			// every later save then fails without a word.
			$events = self::table( 'events' );
			if ( in_array( 'tables_mode', self::columns( $events ), true ) ) {
				return;
			}
		}

		self::create_tables();
		self::add_roles();
		self::create_storage();
		self::seed_defaults();

		update_option( 'bfnet_db_version', BFNET_DB_VERSION );

		if ( class_exists( 'BFNET_Log' ) ) {
			BFNET_Log::audit( 'system', 0, 'db_upgraded', array( 'from' => $stored, 'to' => BFNET_DB_VERSION ) );
		}
	}

	/**
	 * Fully qualified table name for a BitForgeNet Guest Tickets entity.
	 *
	 * @param string $name Short table name.
	 * @return string
	 */
	public static function table( $name ) {
		global $wpdb;
		return $wpdb->prefix . 'bfnet_' . $name;
	}

	/**
	 * Create or update all plugin tables via dbDelta.
	 *
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

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

		$charset = $wpdb->get_charset_collate();
		$p       = $wpdb->prefix . 'bfnet_';

		$sql = array();

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}organizers (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(190) NOT NULL DEFAULT '',
			slug varchar(190) NOT NULL DEFAULT '',
			tagline varchar(255) NOT NULL DEFAULT '',
			description longtext NULL,
			logo_id bigint(20) unsigned NOT NULL DEFAULT 0,
			email varchar(190) NOT NULL DEFAULT '',
			phone varchar(60) NOT NULL DEFAULT '',
			whatsapp varchar(60) NOT NULL DEFAULT '',
			website varchar(255) NOT NULL DEFAULT '',
			instagram varchar(255) NOT NULL DEFAULT '',
			facebook varchar(255) NOT NULL DEFAULT '',
			brand_primary varchar(20) NOT NULL DEFAULT '#C9A227',
			brand_secondary varchar(20) NOT NULL DEFAULT '#0B0B0B',
			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),
			KEY slug (slug),
			KEY status (status)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}venues (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(190) NOT NULL DEFAULT '',
			address varchar(255) NOT NULL DEFAULT '',
			city varchar(120) NOT NULL DEFAULT '',
			state varchar(80) NOT NULL DEFAULT '',
			postcode varchar(30) NOT NULL DEFAULT '',
			country varchar(120) NOT NULL DEFAULT '',
			google_maps_url text NULL,
			latitude decimal(10,7) NULL,
			longitude decimal(10,7) NULL,
			capacity int(11) NOT NULL DEFAULT 0,
			phone varchar(60) NOT NULL DEFAULT '',
			notes longtext NULL,
			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),
			KEY city (city),
			KEY status (status)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}events (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			uuid char(36) NOT NULL DEFAULT '',
			title varchar(190) NOT NULL DEFAULT '',
			slug varchar(190) NOT NULL DEFAULT '',
			subtitle varchar(255) NOT NULL DEFAULT '',
			series varchar(190) NOT NULL DEFAULT '',
			edition varchar(60) NOT NULL DEFAULT '',
			description longtext NULL,
			short_description text NULL,
			cover_id bigint(20) unsigned NOT NULL DEFAULT 0,
			gallery longtext NULL,
			organizer_id bigint(20) unsigned NOT NULL DEFAULT 0,
			venue_id bigint(20) unsigned NOT NULL DEFAULT 0,
			template_id bigint(20) unsigned NOT NULL DEFAULT 0,
			starts_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			ends_at datetime NULL,
			doors_open_at datetime NULL,
			timezone varchar(64) NOT NULL DEFAULT '',
			tables_mode varchar(20) NOT NULL DEFAULT 'manual',
			tables_from int(11) NOT NULL DEFAULT 1,
			tables_to int(11) NOT NULL DEFAULT 0,
			table_seats int(11) NOT NULL DEFAULT 0,
			table_zones longtext NULL,
			sponsors_image bigint(20) unsigned NOT NULL DEFAULT 0,
			menu_enabled tinyint(1) NOT NULL DEFAULT 0,
			menu_title varchar(190) NOT NULL DEFAULT '',
			menu_body longtext NULL,
			lineup longtext NULL,
			dress_code varchar(190) NOT NULL DEFAULT '',
			min_age smallint(5) NOT NULL DEFAULT 0,
			age_check_mode varchar(20) NOT NULL DEFAULT 'off',
			terms_page_id bigint(20) unsigned NOT NULL DEFAULT 0,
			regulation_page_id bigint(20) unsigned NOT NULL DEFAULT 0,
			capacity int(11) NOT NULL DEFAULT 0,
			price_cents bigint(20) NOT NULL DEFAULT 0,
			currency char(3) NOT NULL DEFAULT 'CAD',
			max_per_order smallint(5) NOT NULL DEFAULT 10,
			collect_guest_names tinyint(1) NOT NULL DEFAULT 1,
			ticket_prefix varchar(20) NOT NULL DEFAULT '',
			status varchar(30) NOT NULL DEFAULT 'draft',
			registration_opens_at datetime NULL,
			registration_closes_at datetime NULL,
			whatsapp_number varchar(60) NOT NULL DEFAULT '',
			payment_instructions longtext NULL,
			terms longtext NULL,
			settings longtext NULL,
			sold_count int(11) NOT NULL DEFAULT 0,
			checked_in_count 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 uuid (uuid),
			KEY slug (slug),
			KEY status (status),
			KEY starts_at (starts_at),
			KEY organizer_id (organizer_id),
			KEY venue_id (venue_id)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}discounts (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			code varchar(40) NOT NULL DEFAULT '',
			label varchar(190) NOT NULL DEFAULT '',
			kind varchar(20) NOT NULL DEFAULT 'percent',
			value_cents bigint(20) NOT NULL DEFAULT 0,
			window_mode varchar(20) NOT NULL DEFAULT 'always',
			starts_at datetime NULL,
			ends_at datetime NULL,
			window_hours int(11) NOT NULL DEFAULT 0,
			min_quantity smallint(5) NOT NULL DEFAULT 1,
			max_uses int(11) NOT NULL DEFAULT 0,
			used_count int(11) NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'active',
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY code (code)
		) {$charset};";

		$sql[] = "CREATE TABLE {$p}ticket_types (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			name varchar(190) NOT NULL DEFAULT '',
			description text NULL,
			price_cents bigint(20) NOT NULL DEFAULT 0,
			capacity int(11) NOT NULL DEFAULT 0,
			sold_count int(11) NOT NULL DEFAULT 0,
			min_per_order smallint(5) NOT NULL DEFAULT 1,
			max_per_order smallint(5) NOT NULL DEFAULT 10,
			sort_order smallint(5) NOT NULL DEFAULT 0,
			badge varchar(60) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'active',
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY status (status)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}orders (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			uuid char(36) NOT NULL DEFAULT '',
			reference varchar(40) NOT NULL DEFAULT '',
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			first_name varchar(120) NOT NULL DEFAULT '',
			last_name varchar(120) NOT NULL DEFAULT '',
			email varchar(190) NOT NULL DEFAULT '',
			phone varchar(60) NOT NULL DEFAULT '',
			whatsapp varchar(60) NOT NULL DEFAULT '',
			country varchar(120) NOT NULL DEFAULT '',
			city varchar(120) NOT NULL DEFAULT '',
			quantity smallint(5) NOT NULL DEFAULT 1,
			subtotal_cents bigint(20) NOT NULL DEFAULT 0,
			discount_cents bigint(20) NOT NULL DEFAULT 0,
			discount_code varchar(40) NOT NULL DEFAULT '',
			discount_label varchar(190) NOT NULL DEFAULT '',
			total_cents bigint(20) NOT NULL DEFAULT 0,
			currency char(3) NOT NULL DEFAULT 'CAD',
			payment_status varchar(30) NOT NULL DEFAULT 'pending',
			payment_method varchar(40) NOT NULL DEFAULT 'manual',
			payment_reference varchar(190) NOT NULL DEFAULT '',
			paid_at datetime NULL,
			paid_by bigint(20) unsigned NOT NULL DEFAULT 0,
			tickets_issued tinyint(1) NOT NULL DEFAULT 0,
			tickets_sent_at datetime NULL,
			customer_note longtext NULL,
			admin_note longtext NULL,
			consent_terms tinyint(1) NOT NULL DEFAULT 0,
			consent_marketing tinyint(1) NOT NULL DEFAULT 0,
			source varchar(60) NOT NULL DEFAULT 'website',
			ip_hash char(64) NOT NULL DEFAULT '',
			user_agent varchar(255) NOT NULL DEFAULT '',
			locale varchar(20) NOT NULL DEFAULT '',
			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 uuid (uuid),
			UNIQUE KEY reference (reference),
			KEY event_id (event_id),
			KEY email (email),
			KEY payment_status (payment_status),
			KEY created_at (created_at)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}attendees (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			ticket_type_id bigint(20) unsigned NOT NULL DEFAULT 0,
			position smallint(5) NOT NULL DEFAULT 1,
			first_name varchar(120) NOT NULL DEFAULT '',
			last_name varchar(120) NOT NULL DEFAULT '',
			email varchar(190) NOT NULL DEFAULT '',
			phone varchar(60) NOT NULL DEFAULT '',
			category varchar(40) NOT NULL DEFAULT 'standard',
			birth_date date NULL,
			table_number varchar(20) NOT NULL DEFAULT '',
			notes text NULL,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY event_id (event_id)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}tickets (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			uuid char(36) NOT NULL DEFAULT '',
			code varchar(40) NOT NULL DEFAULT '',
			serial varchar(40) NOT NULL DEFAULT '',
			token_hash char(64) NOT NULL DEFAULT '',
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			attendee_id bigint(20) unsigned NOT NULL DEFAULT 0,
			ticket_type_id bigint(20) unsigned NOT NULL DEFAULT 0,
			template_id bigint(20) unsigned NOT NULL DEFAULT 0,
			status varchar(30) NOT NULL DEFAULT 'issued',
			checked_in tinyint(1) NOT NULL DEFAULT 0,
			checked_in_at datetime NULL,
			checked_in_by bigint(20) unsigned NOT NULL DEFAULT 0,
			gate varchar(60) NOT NULL DEFAULT '',
			scan_count int(11) NOT NULL DEFAULT 0,
			last_scan_at datetime NULL,
			pdf_file varchar(255) NOT NULL DEFAULT '',
			pdf_generated_at datetime NULL,
			issued_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			voided_at datetime NULL,
			void_reason varchar(190) NOT NULL DEFAULT '',
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY uuid (uuid),
			UNIQUE KEY code (code),
			UNIQUE KEY token_hash (token_hash),
			KEY event_id (event_id),
			KEY order_id (order_id),
			KEY attendee_id (attendee_id),
			KEY status (status),
			KEY checked_in (checked_in)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}scans (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			ticket_id bigint(20) unsigned NOT NULL DEFAULT 0,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			result varchar(40) NOT NULL DEFAULT '',
			gate varchar(60) NOT NULL DEFAULT '',
			token_fragment varchar(20) NOT NULL DEFAULT '',
			ip_hash char(64) NOT NULL DEFAULT '',
			user_agent varchar(255) NOT NULL DEFAULT '',
			note varchar(255) NOT NULL DEFAULT '',
			scanned_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY ticket_id (ticket_id),
			KEY event_id (event_id),
			KEY result (result),
			KEY scanned_at (scanned_at)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}templates (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(190) NOT NULL DEFAULT '',
			description text NULL,
			background_file varchar(255) NOT NULL DEFAULT '',
			preview_file varchar(255) NOT NULL DEFAULT '',
			width_px int(11) NOT NULL DEFAULT 1181,
			height_px int(11) NOT NULL DEFAULT 2953,
			dpi smallint(5) NOT NULL DEFAULT 300,
			bleed_mm decimal(5,2) NOT NULL DEFAULT 0,
			fieldmap longtext NULL,
			palette longtext NULL,
			is_default tinyint(1) NOT NULL DEFAULT 0,
			is_generated tinyint(1) NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'active',
			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),
			KEY is_default (is_default),
			KEY status (status)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}emails (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			ticket_id bigint(20) unsigned NOT NULL DEFAULT 0,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			type varchar(60) NOT NULL DEFAULT '',
			recipient varchar(190) NOT NULL DEFAULT '',
			subject varchar(255) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'queued',
			attempts smallint(5) NOT NULL DEFAULT 0,
			error text NULL,
			sent_at datetime NULL,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY type (type),
			KEY status (status)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}archive_batches (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			uuid char(36) NOT NULL DEFAULT '',
			label varchar(190) NOT NULL DEFAULT '',
			reason varchar(255) NOT NULL DEFAULT '',
			scope varchar(40) NOT NULL DEFAULT 'all',
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			counts longtext NULL,
			created_by bigint(20) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY event_id (event_id),
			KEY created_at (created_at)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}archive_records (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			batch_id bigint(20) unsigned NOT NULL DEFAULT 0,
			entity varchar(30) NOT NULL DEFAULT '',
			entity_id bigint(20) unsigned NOT NULL DEFAULT 0,
			event_id bigint(20) unsigned NOT NULL DEFAULT 0,
			reference varchar(60) NOT NULL DEFAULT '',
			email varchar(190) NOT NULL DEFAULT '',
			payload longtext NULL,
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY batch_id (batch_id),
			KEY entity (entity),
			KEY event_id (event_id),
			KEY reference (reference),
			KEY email (email)
		) {$charset};";

		/* ------------------------------------------------------------- */
		$sql[] = "CREATE TABLE {$p}logs (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			object_type varchar(40) NOT NULL DEFAULT '',
			object_id bigint(20) unsigned NOT NULL DEFAULT 0,
			action varchar(60) NOT NULL DEFAULT '',
			context longtext NULL,
			level varchar(20) NOT NULL DEFAULT 'info',
			ip_hash char(64) NOT NULL DEFAULT '',
			created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY object_type (object_type),
			KEY action (action),
			KEY level (level),
			KEY created_at (created_at)
		) {$charset};";

		foreach ( $sql as $statement ) {
			dbDelta( $statement );
		}
	}

	/**
	 * Register plugin roles and grant capabilities.
	 *
	 * @return void
	 */
	public static function add_roles() {
		$admin = get_role( 'administrator' );
		if ( $admin ) {
			foreach ( self::$capabilities as $cap ) {
				$admin->add_cap( $cap );
			}
		}

		remove_role( 'bfnet_manager' );
		add_role(
			'bfnet_manager',
			'BitForgeNet Guest Tickets Manager',
			array_merge(
				array( 'read' => true, 'upload_files' => true ),
				array_fill_keys(
					array( 'bfnet_manage_events', 'bfnet_manage_orders', 'bfnet_validate_payments', 'bfnet_manage_tickets', 'bfnet_scan_tickets', 'bfnet_manage_templates', 'bfnet_view_reports' ),
					true
				)
			)
		);

		remove_role( 'bfnet_door_agent' );
		add_role(
			'bfnet_door_agent',
			'BitForgeNet Guest Tickets Door Agent',
			array(
				'read'             => true,
				'bfnet_scan_tickets' => true,
			)
		);
	}

	/**
	 * Remove plugin roles and capabilities (used by uninstall).
	 *
	 * @return void
	 */
	public static function remove_roles() {
		$admin = get_role( 'administrator' );
		if ( $admin ) {
			foreach ( self::$capabilities as $cap ) {
				$admin->remove_cap( $cap );
			}
		}
		remove_role( 'bfnet_manager' );
		remove_role( 'bfnet_door_agent' );
	}

	/**
	 * Base directory used for private plugin storage.
	 *
	 * @return array {
	 *     @type string $dir Absolute path.
	 *     @type string $url Public URL (blocked by .htaccess where supported).
	 * }
	 */
	public static function storage() {
		$uploads = wp_upload_dir();
		return array(
			'dir' => trailingslashit( $uploads['basedir'] ) . 'bitforgenet-guest-tickets',
			'url' => trailingslashit( $uploads['baseurl'] ) . 'bitforgenet-guest-tickets',
		);
	}

	/**
	 * Create protected upload folders for tickets and templates.
	 *
	 * @return void
	 */
	public static function create_storage() {
		$base = self::storage();
		$dirs = array(
			$base['dir'],
			$base['dir'] . '/tickets',
			$base['dir'] . '/templates',
			$base['dir'] . '/tmp',
			$base['dir'] . '/exports',
		);

		foreach ( $dirs as $dir ) {
			if ( ! file_exists( $dir ) ) {
				wp_mkdir_p( $dir );
			}
			$index = $dir . '/index.php';
			if ( ! file_exists( $index ) ) {
				file_put_contents( $index, "<?php\n// Silence is golden.\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions
			}
		}

		$htaccess = $base['dir'] . '/.htaccess';
		if ( ! file_exists( $htaccess ) ) {
			$rules = "# BitForgeNet Guest Tickets private storage\n"
				. "<IfModule mod_authz_core.c>\n\tRequire all denied\n</IfModule>\n"
				. "<IfModule !mod_authz_core.c>\n\tOrder deny,allow\n\tDeny from all\n</IfModule>\n";
			file_put_contents( $htaccess, $rules ); // phpcs:ignore WordPress.WP.AlternativeFunctions
		}

		$webconfig = $base['dir'] . '/web.config';
		if ( ! file_exists( $webconfig ) ) {
			$xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<configuration>\n<system.webServer>\n"
				. "<authorization><deny users=\"*\" /></authorization>\n</system.webServer>\n</configuration>\n";
			file_put_contents( $webconfig, $xml ); // phpcs:ignore WordPress.WP.AlternativeFunctions
		}
	}

	/**
	 * Seed default settings, the founding organizer and the built-in ticket template.
	 *
	 * @return void
	 */
	public static function seed_defaults() {
		global $wpdb;

		if ( false === get_option( 'bfnet_settings' ) ) {
			add_option( 'bfnet_settings', BFNET_Settings::defaults() );
		}

		// Founding organizer.
		$organizers = self::table( 'organizers' );
		$exists     = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$organizers}" ); // phpcs:ignore WordPress.DB.PreparedSQL
		if ( 0 === $exists ) {
			$wpdb->insert(
				$organizers,
				array(
					'name'            => get_bloginfo( 'name' ),
					'slug'            => sanitize_title( get_bloginfo( 'name' ) ),
					'tagline'         => '',
					'description'     => '',
					'email'           => get_option( 'admin_email' ),
					'brand_primary'   => BFNET_Settings::defaults()['brand_primary'],
					'brand_secondary' => BFNET_Settings::defaults()['brand_secondary'],
					'status'          => 'active',
					'created_at'      => current_time( 'mysql' ),
					'updated_at'      => current_time( 'mysql' ),
				)
			);
		}

		// Built-in ticket template.
		$templates = self::table( 'templates' );
		$has_tpl   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$templates}" ); // phpcs:ignore WordPress.DB.PreparedSQL
		if ( 0 === $has_tpl && class_exists( 'BFNET_Template' ) ) {
			BFNET_Template::install_builtin();
		}
	}

	/**
	 * Drop every plugin table. Only called from uninstall.php.
	 *
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;

		$tables = array( 'archive_records', 'archive_batches', 'logs', 'emails', 'scans', 'tickets', 'attendees', 'orders', 'discounts', 'ticket_types', 'templates', 'events', 'venues', 'organizers' );
		foreach ( $tables as $table ) {
			$name = self::table( $table );
			$wpdb->query( "DROP TABLE IF EXISTS {$name}" ); // phpcs:ignore WordPress.DB.PreparedSQL
		}
	}
}
