<?php
namespace AdFuz\Controllers;

use AdFuz\Helpers\Common;
use AdFuz\Helpers\TableName;
use AdFuz\Requests\Request;

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

class DatabaseToolsController {

	private static $instance = null;

	public static function get_instance(): self {
		if ( self::$instance === null ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	private static function getCapabilities(): array {
		return array( 'manage_options' );
	}


	// ── get_table_status ──────────────────────────────────────────────────
	public static function get_table_status(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		global $wpdb;
		$rows = array();

		foreach ( TableName::definitions( true ) as $def ) {
			$table  = $def['table'];
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;

			$row_count = null;
			$size_kb   = null;

			if ( $exists ) {
				$safe_table = '`' . esc_sql( $table ) . '`';
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$row_count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$safe_table}" );
				$size_row  = $wpdb->get_row(
					$wpdb->prepare(
						'SELECT ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024, 2) AS size_kb
                     FROM information_schema.TABLES
                     WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s',
						DB_NAME,
						$table
					)
				);
				$size_kb   = $size_row ? (float) $size_row->size_kb : 0;
			}

			$rows[] = array(
				'table'       => $table,
				'group'       => $def['group'],
				'description' => $def['description'],
				'exists'      => $exists,
				'row_count'   => $row_count,
				'size_kb'     => $size_kb,
			);
		}

		$options = $wpdb->get_results(
			"SELECT option_name, LENGTH(option_value) AS value_size
             FROM {$wpdb->options}
             WHERE option_name LIKE 'adfuz_%'
             ORDER BY option_name ASC"
		);

		wp_send_json_success(
			array(
				'tables'  => $rows,
				'options' => $options,
			)
		);
	}

	// ── truncate_table ────────────────────────────────────────────────────
	public static function truncate_table(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		$table = static::request()->input( 'table', 'post', '' );
		if ( ! self::is_valid_adfuz_table( $table ) ) {
			wp_send_json_error( array( 'message' => 'Invalid table name.' ) );
		}

		global $wpdb;
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=0' );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
		$result = $wpdb->query( "TRUNCATE TABLE `{$table}`" );
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=1' );

		$result === false
			? wp_send_json_error( array( 'message' => 'Failed: ' . $wpdb->last_error ) )
			: wp_send_json_success( array( 'message' => "All data deleted from `{$table}`." ) );
	}

	// ── drop_table ────────────────────────────────────────────────────────
	public static function drop_table(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		$table = static::request()->input( 'table', 'post', '' );
		if ( ! self::is_valid_adfuz_table( $table ) ) {
			wp_send_json_error( array( 'message' => 'Invalid table name.' ) );
		}

		global $wpdb;
		$safe_table = esc_sql( $table );
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=0' );
		$result = $wpdb->query( "DROP TABLE IF EXISTS `{$safe_table}`" );
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=1' );

		$result === false
			? wp_send_json_error( array( 'message' => 'Failed: ' . $wpdb->last_error ) )
			: wp_send_json_success( array( 'message' => "Table `{$safe_table}` dropped." ) );
	}

	// ── install_table ─────────────────────────────────────────────────────
	public static function install_table(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		$table = static::request()->input( 'table', 'post', '' );
		if ( ! self::is_valid_adfuz_table( $table ) ) {
			wp_send_json_error( array( 'message' => 'Invalid table name.' ) );
		}

		if ( ! function_exists( 'dbDelta' ) ) {
			// Required to access dbDelta() for table creation — standard WP pattern for schema.
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		$result = \AdFuz\Database\Install::get_instance()->install_single_table( $table );

		$result
			? wp_send_json_success( array( 'message' => "Table `{$table}` installed." ) )
			: wp_send_json_error( array( 'message' => "Could not install `{$table}`." ) );
	}

	public static function install_all_tables(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		\AdFuz\Database\Install::get_instance()->install_all_tables();

		wp_send_json_success( array( 'message' => 'All AdFuz tables installed successfully.' ) );
	}

	// ── drop_all_tables ───────────────────────────────────────────────────
	public static function drop_all_tables(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		global $wpdb;
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=0' );
		foreach ( array_reverse( TableName::definitions( true ) ) as $def ) {
			$wpdb->query( "DROP TABLE IF EXISTS `{$def['table']}`" );
		}
		$wpdb->query( 'SET FOREIGN_KEY_CHECKS=1' );

		wp_send_json_success( array( 'message' => 'All AdFuz tables dropped.' ) );
	}

	// ── delete_option ─────────────────────────────────────────────────────
	public static function delete_option(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		$name = static::request()->input( 'option_name', 'post', '' );
		if ( empty( $name ) || strpos( $name, 'adfuz' ) !== 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid option name.' ) );
		}

		delete_option( sanitize_key( $name ) )
			? wp_send_json_success( array( 'message' => "Option `{$name}` deleted." ) )
			: wp_send_json_error( array( 'message' => "Option `{$name}` not found." ) );
	}

	// ── delete_all_options ────────────────────────────────────────────────
	public static function delete_all_options(): void {
		Common::checkCapability( static::getCapabilities() );
		Common::verifyNonce();

		global $wpdb;
		$opts = $wpdb->get_col(
			"SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE 'adfuz_%'"
		);
		foreach ( $opts as $opt ) {
			delete_option( $opt );
		}

		wp_send_json_success( array( 'message' => count( $opts ) . ' option(s) deleted.' ) );
	}

	// ── Helpers ───────────────────────────────────────────────────────────
	private static function is_valid_adfuz_table( string $table ): bool {
		global $wpdb;
		$allowed = array_column( TableName::definitions( true ), 'table' );

		return in_array( $table, $allowed, true )
				&& str_starts_with( $table, $wpdb->prefix . 'adfuz_' );
	}

	private static function request() {
		return Request::get_instance();
	}
}