<?php
/**
 * Magic link rows (Switchboard-compatible schema).
 *
 * @package MagicLogin
 */

namespace MagicLogin;

/**
 * Stores one-time login rows in {$wpdb->prefix}wpps_magic_links.
 */
class LinkRepository {

	private string $table = '';

	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'wpps_magic_links';
	}

	public function getTableName(): string {
		return $this->table;
	}

	public function install(): void {
		global $wpdb;
		$charset = $wpdb->get_charset_collate();
		$sql     = "CREATE TABLE {$this->table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL,
			token_hash varchar(64) NOT NULL,
			login_code varchar(64) DEFAULT NULL,
			expires_at datetime NOT NULL,
			created_at datetime NOT NULL,
			used_at datetime DEFAULT NULL,
			ip_address varchar(45) DEFAULT NULL,
			user_agent varchar(255) DEFAULT NULL,
			PRIMARY KEY (id),
			KEY user_id (user_id),
			KEY token_hash (token_hash),
			KEY login_code (login_code),
			KEY expires_at (expires_at)
		) $charset;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		$this->maybeAddLoginCodeColumn();
	}

	private function maybeAddLoginCodeColumn(): void {
		global $wpdb;
		$row = $wpdb->get_results( $wpdb->prepare(
			'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s',
			DB_NAME,
			$this->table,
			'login_code'
		) );
		if ( empty( $row ) ) {
			$wpdb->query( "ALTER TABLE {$this->table} ADD login_code varchar(64) DEFAULT NULL AFTER token_hash" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$this->table} ADD KEY login_code (login_code)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	public function invalidateUnusedForUser( int $user_id ): void {
		global $wpdb;
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$this->table} SET used_at = %s WHERE user_id = %d AND used_at IS NULL",
				gmdate( 'Y-m-d H:i:s' ),
				$user_id
			)
		);
	}

	/**
	 * @return int|false insert id
	 */
	public function insertRow(
		int $user_id,
		string $token_hash,
		?string $login_code_hash,
		int $expiry_ts,
		string $ip,
		string $user_agent
	) {
		global $wpdb;
		$expires = gmdate( 'Y-m-d H:i:s', $expiry_ts );
		$now     = gmdate( 'Y-m-d H:i:s' );
		$ok      = $wpdb->insert(
			$this->table,
			[
				'user_id'     => $user_id,
				'token_hash'  => $token_hash,
				'login_code'  => $login_code_hash,
				'expires_at'  => $expires,
				'created_at'  => $now,
				'ip_address'  => $ip,
				'user_agent'  => $user_agent,
			],
			[ '%d', '%s', '%s', '%s', '%s', '%s', '%s' ]
		);
		return $ok ? (int) $wpdb->insert_id : false;
	}

	/**
	 * @return object|null
	 */
	public function findByTokenAndUser( string $token_hash, int $user_id ) {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE token_hash = %s AND user_id = %d AND used_at IS NULL AND expires_at > %s",
				$token_hash,
				$user_id,
				gmdate( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * @return object|null
	 */
	public function findByCodeAndUser( string $code_hash, int $user_id ) {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE login_code = %s AND user_id = %d AND used_at IS NULL AND expires_at > %s",
				$code_hash,
				$user_id,
				gmdate( 'Y-m-d H:i:s' )
			)
		);
	}

	public function markUsed( int $row_id ): bool {
		global $wpdb;
		$now     = gmdate( 'Y-m-d H:i:s' );
		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$this->table} SET used_at = %s WHERE id = %d AND used_at IS NULL AND expires_at > %s",
				$now,
				$row_id,
				$now
			)
		);

		return is_int( $updated ) && $updated === 1;
	}

	public function cleanupExpired(): void {
		global $wpdb;
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$this->table} WHERE expires_at < %s OR (used_at IS NOT NULL AND used_at < %s)",
				gmdate( 'Y-m-d H:i:s', time() - WEEK_IN_SECONDS ),
				gmdate( 'Y-m-d H:i:s', time() - WEEK_IN_SECONDS )
			)
		);
	}

	public function countPendingCodesForUser( int $user_id ): int {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE user_id = %d AND login_code IS NOT NULL AND used_at IS NULL AND expires_at > %s",
				$user_id,
				gmdate( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * @return array{total:int,used:int,active:int,unique_users:int}
	 */
	public function getStats(): array {
		global $wpdb;
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table}" );
		$used  = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table} WHERE used_at IS NOT NULL" );
		$active = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE used_at IS NULL AND expires_at > %s",
				gmdate( 'Y-m-d H:i:s' )
			)
		);
		$unique = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$this->table} WHERE used_at IS NOT NULL" );

		return [
			'total'        => $total,
			'used'         => $used,
			'active'       => $active,
			'unique_users' => $unique,
		];
	}

	/**
	 * @return array{all:int,used:int,pending:int,expired:int}
	 */
	public function getActivityStatusCounts( string $search = '' ): array {
		global $wpdb;
		[ $search_sql, $search_args ] = $this->buildActivitySearchClause( $search );
		$now                           = gmdate( 'Y-m-d H:i:s' );
		$from                          = "FROM {$this->table} m INNER JOIN {$wpdb->users} u ON m.user_id = u.ID WHERE 1=1{$search_sql}";

		$queries = [
			'all'     => "SELECT COUNT(*) {$from}",
			'used'    => "SELECT COUNT(*) {$from} AND m.used_at IS NOT NULL",
			'pending' => "SELECT COUNT(*) {$from} AND m.used_at IS NULL AND m.expires_at > %s",
			'expired' => "SELECT COUNT(*) {$from} AND m.used_at IS NULL AND m.expires_at <= %s",
		];

		$out = [];
		foreach ( $queries as $key => $sql ) {
			$args = $search_args;
			if ( $key === 'pending' || $key === 'expired' ) {
				$args[] = $now;
			}
			if ( $args !== [] ) {
				$sql = $wpdb->prepare( $sql, $args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			}
			$out[ $key ] = (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return $out;
	}

	/**
	 * @param array{status?:string,search?:string,orderby?:string,order?:string,per_page?:int,offset?:int} $args
	 */
	public function countActivityItems( array $args ): int {
		global $wpdb;
		[ $where_sql, $where_args ] = $this->buildActivityQueryClauses( $args );
		$sql                         = "SELECT COUNT(*) FROM {$this->table} m INNER JOIN {$wpdb->users} u ON m.user_id = u.ID WHERE 1=1{$where_sql}";
		if ( $where_args !== [] ) {
			$sql = $wpdb->prepare( $sql, $where_args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
		return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * @param array{status?:string,search?:string,orderby?:string,order?:string,per_page?:int,offset?:int} $args
	 * @return array<int, object>
	 */
	public function getActivityItems( array $args ): array {
		global $wpdb;
		[ $where_sql, $where_args ] = $this->buildActivityQueryClauses( $args );
		$orderby                     = $this->mapActivityOrderBy( (string) ( $args['orderby'] ?? 'created' ) );
		$order                       = ( isset( $args['order'] ) && strtoupper( (string) $args['order'] ) === 'ASC' ) ? 'ASC' : 'DESC';
		$per_page                    = max( 1, min( 100, absint( $args['per_page'] ?? 20 ) ) );
		$offset                      = max( 0, absint( $args['offset'] ?? 0 ) );

		$sql = "SELECT m.*, u.display_name, u.user_email
			FROM {$this->table} m
			INNER JOIN {$wpdb->users} u ON m.user_id = u.ID
			WHERE 1=1{$where_sql}
			ORDER BY {$orderby} {$order}
			LIMIT %d OFFSET %d";

		$query_args = array_merge( $where_args, [ $per_page, $offset ] );
		$rows       = $wpdb->get_results( $wpdb->prepare( $sql, $query_args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * @return array{0:string,1:string} Label and status class.
	 */
	public function resolveActivityStatus( object $row ): array {
		$used_at    = (string) ( $row->used_at ?? '' );
		$expires_at = (string) ( $row->expires_at ?? '' );
		if ( $used_at !== '' ) {
			return [ __( 'Used', 'wp-powersuite' ), 'used' ];
		}
		if ( $expires_at !== '' && strtotime( $expires_at . ' UTC' ) < time() ) {
			return [ __( 'Expired', 'wp-powersuite' ), 'expired' ];
		}
		return [ __( 'Pending', 'wp-powersuite' ), 'pending' ];
	}

	/**
	 * @param array<int> $ids
	 */
	public function deleteRowsByIds( array $ids ): int {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );
		if ( $ids === [] ) {
			return 0;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$sql          = "DELETE FROM {$this->table} WHERE id IN ({$placeholders})";
		$deleted      = $wpdb->query( $wpdb->prepare( $sql, $ids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return is_int( $deleted ) ? $deleted : 0;
	}

	/**
	 * Mark pending rows as used (revoke).
	 *
	 * @param array<int> $ids
	 */
	public function revokeRowsByIds( array $ids ): int {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );
		if ( $ids === [] ) {
			return 0;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$now          = gmdate( 'Y-m-d H:i:s' );
		$sql          = "UPDATE {$this->table} SET used_at = %s WHERE used_at IS NULL AND id IN ({$placeholders})";
		$args         = array_merge( [ $now ], $ids );
		$updated      = $wpdb->query( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return is_int( $updated ) ? $updated : 0;
	}

	/**
	 * @param array{status?:string,search?:string} $args
	 * @return array{0:string,1:array<int, mixed>}
	 */
	private function buildActivityQueryClauses( array $args ): array {
		$status = sanitize_key( (string) ( $args['status'] ?? 'all' ) );
		$search = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		[ $search_sql, $search_args ] = $this->buildActivitySearchClause( $search );
		[ $status_sql, $status_args ] = $this->buildActivityStatusClause( $status );
		return [ $search_sql . $status_sql, array_merge( $search_args, $status_args ) ];
	}

	/**
	 * @return array{0:string,1:array<int, mixed>}
	 */
	private function buildActivitySearchClause( string $search ): array {
		global $wpdb;
		if ( $search === '' ) {
			return [ '', [] ];
		}
		$like = '%' . $wpdb->esc_like( $search ) . '%';
		return [
			' AND (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR m.ip_address LIKE %s)',
			[ $like, $like, $like, $like ],
		];
	}

	/**
	 * @return array{0:string,1:array<int, mixed>}
	 */
	private function buildActivityStatusClause( string $status ): array {
		$now = gmdate( 'Y-m-d H:i:s' );
		switch ( $status ) {
			case 'used':
				return [ ' AND m.used_at IS NOT NULL', [] ];
			case 'pending':
				return [ ' AND m.used_at IS NULL AND m.expires_at > %s', [ $now ] ];
			case 'expired':
				return [ ' AND m.used_at IS NULL AND m.expires_at <= %s', [ $now ] ];
			default:
				return [ '', [] ];
		}
	}

	private function mapActivityOrderBy( string $orderby ): string {
		$map = [
			'user'    => 'u.display_name',
			'email'   => 'u.user_email',
			'created' => 'm.created_at',
			'used'    => 'm.used_at',
			'expires' => 'm.expires_at',
			'ip'      => 'm.ip_address',
			'status'  => '(CASE WHEN m.used_at IS NOT NULL THEN 1 WHEN m.expires_at <= UTC_TIMESTAMP() THEN 3 ELSE 2 END)',
		];
		return $map[ $orderby ] ?? 'm.created_at';
	}

	/**
	 * @return array<int, object>
	 */
	public function getRecentActivity( int $limit = 10 ): array {
		global $wpdb;
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT m.*, u.display_name, u.user_email
				FROM {$this->table} m
				INNER JOIN {$wpdb->users} u ON m.user_id = u.ID
				ORDER BY m.created_at DESC
				LIMIT %d",
				$limit
			)
		);
		return is_array( $rows ) ? $rows : [];
	}

	public function getUserLoginCount( int $user_id ): int {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE user_id = %d AND used_at IS NOT NULL",
				$user_id
			)
		);
	}

	public function getUserLastUsed( int $user_id ): ?string {
		global $wpdb;
		$t = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT used_at FROM {$this->table} WHERE user_id = %d AND used_at IS NOT NULL ORDER BY used_at DESC LIMIT 1",
				$user_id
			)
		);
		return is_string( $t ) && $t !== '' ? $t : null;
	}
}
