<?php
/** Shared staff subject access without premium employee execution. */
namespace Abientot\Repositories;
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}
final class StaffPrivacyRepository {
	public function export_rows( string $email, int $page = 1 ): array {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( ! is_email( $email ) ) {
			return array();
		}
		$user = get_user_by( 'email', $email );
		$uid  = $user ? (int) $user->ID : 0;
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT s.* FROM {$wpdb->prefix}abientot_staff s WHERE LOWER(s.email)=%s OR (%d > 0 AND EXISTS (SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=%d AND m.meta_key=%s AND m.meta_value=CAST(s.id AS CHAR))) ORDER BY s.id LIMIT 100 OFFSET %d", $email, $uid, $uid, $wpdb->prefix . 'abientot_staff_id', ( max( 1, $page ) - 1 ) * 100 ), ARRAY_A );
		$this->check();
		return $rows;
	}
	/** One schedule table per WordPress exporter; no first-page staff-ID truncation. */
	public function export_subject_schedule( string $kind, string $email, int $page = 1 ): array {
		global $wpdb;
		if ( ! in_array( $kind, array( 'availability_rules', 'availability_exceptions' ), true ) || $page < 1 || $page > 1000000 ) {
			throw new \InvalidArgumentException( 'Staff export scope invalid.' );
		}
		$email = strtolower( trim( $email ) );
		if ( ! is_email( $email ) ) {
			return array();
		}
		$user = get_user_by( 'email', $email );
		$uid  = $user ? (int) $user->ID : 0;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT r.* FROM %i r INNER JOIN %i s ON s.id=r.staff_id WHERE LOWER(s.email)=%s OR (%d > 0 AND EXISTS (SELECT 1 FROM %i m WHERE m.user_id=%d AND m.meta_key=%s AND m.meta_value=CAST(s.id AS CHAR))) ORDER BY r.id LIMIT 100 OFFSET %d', $wpdb->prefix . 'abientot_' . $kind, $wpdb->prefix . 'abientot_staff', $email, $uid, $wpdb->usermeta, $uid, $wpdb->prefix . 'abientot_staff_id', ( $page - 1 ) * 100 ), ARRAY_A );
		$this->check();
		return $rows;
	}
	/** Only call after independent administrator verification of this exact staff ID. */
	public function export_id( int $id ): array {
		global $wpdb;
		$rows = $id > 0 ? $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}abientot_staff WHERE id=%d", $id ), ARRAY_A ) : array();
		$this->check();
		return $rows;
	}
	/** Bounded schedules for an independently verified staff subject. */
	public function export_schedules( int $id, int $page = 1 ): array {
		global $wpdb;
		if ( $id <= 0 || $page <= 0 || $page > 1000000 ) {
			throw new \InvalidArgumentException( 'Staff export scope invalid.' );
		}
		$data = array();
		foreach ( array( 'availability_rules', 'availability_exceptions' ) as $kind ) {
			$data[ $kind ] = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE staff_id=%d ORDER BY id LIMIT 100 OFFSET %d', $wpdb->prefix . 'abientot_' . $kind, $id, ( $page - 1 ) * 100 ), ARRAY_A );
			$this->check();
		}
		return $data;
	}
	public function erase_id( int $id ): array {
		if ( $id <= 0 ) {
			throw new \InvalidArgumentException( 'Staff ID required.' );
		}
		return $this->erase_scope( '', $id );
	}
	/** Keep staff IDs and operational records; business reviews retained schedules/history. */
	public function erase( string $email ): array {
		return $this->erase_scope( $email );
	}
	private function erase_scope( string $email, int $verified_id = 0 ): array {
		global $wpdb;
		$owns    = ! BookingLock::is_held();
		$started = false;
		$users   = array();
		if ( ! BookingLock::acquire() ) {
			throw new \RuntimeException( 'Privacy lock unavailable.' );
		}
		try {
			foreach ( array( $wpdb->prefix . 'abientot_staff', $wpdb->prefix . 'abientot_availability_exceptions', $wpdb->usermeta ) as $table ) {
				$engine = $wpdb->get_var( $wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', $table ) );
				$this->check();
				if ( strtoupper( (string) $engine ) !== 'INNODB' ) {
					throw new \RuntimeException( 'Staff privacy transaction unavailable.' );
				}
			}
			\Abientot\Support\TransactionGuard::prepare_write();
			$this->write( $wpdb->query( 'START TRANSACTION' ) );
			$started = true;
			$rows    = $verified_id > 0 ? $this->export_id( $verified_id ) : $this->export_rows( $email );
			foreach ( $rows as $row ) {
				$id = (int) $row['id'];
				$this->write(
					$wpdb->update(
						$wpdb->prefix . 'abientot_staff',
						array(
							'display_name' => 'Staff #' . $id,
							'email'        => null,
							'phone'        => null,
							'bio'          => null,
							'photo_id'     => null,
							'updated_at'   => gmdate( 'Y-m-d H:i:s' )
						),
						array( 'id' => $id )
					)
				);
				$this->write(
					$wpdb->update(
						$wpdb->prefix . 'abientot_availability_exceptions',
						array(
							'reason'     => null,
							'updated_at' => gmdate( 'Y-m-d H:i:s' )
						),
						array( 'staff_id' => $id )
					)
				);
				$key    = $wpdb->prefix . 'abientot_staff_id';
				$mapped = $wpdb->get_col( $wpdb->prepare( "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key=%s AND meta_value=%s", $key, (string) $id ) );
				$this->check();
				$users = array_merge( $users, $mapped );
				$this->write( $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->usermeta} WHERE meta_key=%s AND meta_value=%s", $key, (string) $id ) ) );
			}
			$this->write( $wpdb->query( 'COMMIT' ) );
			$started = false;
			foreach ( array_unique( $users ) as $uid ) {
				wp_cache_delete( (int) $uid, 'user_meta' );
			}
			return array(
				'removed'  => (bool) $rows,
				'retained' => (bool) $rows,
				'done'     => count( $rows ) < 100,
				'messages' => $rows ? array( __( 'Staff IDs, schedules and booking history are retained for business review.', 'abientot' ), __( 'Shared WordPress media may contain the staff photo. Review it manually before deleting media used elsewhere.', 'abientot' ) ) : array()
			);
		} catch ( \Throwable $e ) {
			if ( $started ) {
				$wpdb->query( 'ROLLBACK' );
			}
			throw $e;
		} finally {
			if ( $owns ) {
				BookingLock::release();
			}
		}
	}
	private function check(): void {
		global $wpdb;
		if ( $wpdb->last_error ) {
			throw new \RuntimeException( 'Staff privacy read failed.' );
		}
	}
	private function write( $result ): void {
		if ( $result === false ) {
			throw new \RuntimeException( 'Staff privacy write failed.' );
		}
	}
}