<?php
/**
 * Database schema.
 *
 * @package Aslansheed\OTP
 */

namespace Aslansheed\OTP\Database;

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

/**
 * Table names and CREATE statements for dbDelta().
 */
final class Schema {

	/**
	 * Returns a full table name.
	 *
	 * @param string $short One of identities, otp_codes, attempts, audit_log, message_log.
	 * @return string
	 */
	public static function table( $short ) {
		global $wpdb;

		return $wpdb->prefix . 'aslansheed_' . $short;
	}

	/**
	 * Returns every short table name.
	 *
	 * @return string[]
	 */
	public static function tables() {
		return array( 'identities', 'otp_codes', 'attempts', 'audit_log', 'message_log' );
	}

	/**
	 * Creates or upgrades all tables.
	 *
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

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

		$charset = $wpdb->get_charset_collate();
		$tables  = array(
			self::table( 'identities' )  => 'CREATE TABLE %s (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				type varchar(20) NOT NULL,
				identifier varchar(191) NOT NULL,
				data varchar(191) DEFAULT NULL,
				verified_at datetime DEFAULT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY type_identifier (type,identifier),
				KEY user_id (user_id)
			) %s;',
			self::table( 'otp_codes' )   => 'CREATE TABLE %s (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				identifier varchar(191) NOT NULL,
				purpose varchar(30) NOT NULL,
				channel varchar(30) NOT NULL,
				code_hash char(64) NOT NULL,
				attempts smallint(5) unsigned NOT NULL DEFAULT 0,
				expires_at datetime NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY identifier_purpose (identifier,purpose),
				KEY expires_at (expires_at)
			) %s;',
			self::table( 'attempts' )    => 'CREATE TABLE %s (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				action varchar(30) NOT NULL,
				identifier varchar(191) NOT NULL,
				ip varchar(45) NOT NULL,
				success tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY identifier_lookup (action,identifier,created_at),
				KEY ip_lookup (action,ip,created_at)
			) %s;',
			self::table( 'audit_log' )   => 'CREATE TABLE %s (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				event varchar(60) NOT NULL,
				user_id bigint(20) unsigned NOT NULL DEFAULT 0,
				identifier varchar(191) NOT NULL,
				ip varchar(45) NOT NULL,
				user_agent varchar(255) NOT NULL,
				context longtext DEFAULT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY event_lookup (event,created_at),
				KEY user_id (user_id),
				KEY created_at (created_at)
			) %s;',
			self::table( 'message_log' ) => 'CREATE TABLE %s (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				channel varchar(30) NOT NULL,
				provider varchar(40) NOT NULL,
				recipient varchar(64) NOT NULL,
				purpose varchar(30) NOT NULL,
				status varchar(20) NOT NULL,
				response_code varchar(40) NOT NULL,
				response_message text DEFAULT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY channel_status (channel,status),
				KEY created_at (created_at)
			) %s;',
		);

		foreach ( $tables as $name => $sql ) {
			dbDelta( sprintf( $sql, $name, $charset ) );
		}
	}

	/**
	 * Drops every plugin table. Only called by the opt-in uninstall cleanup.
	 *
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;

		foreach ( self::tables() as $short ) {
			$table = self::table( $short );
			$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from the trusted prefix.
		}
	}
}