<?php

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

/**
 * Database integrity checker for Alex Reservations.
 *
 * Compares every table defined in install.php against the live database
 * and reports: existence, missing / extra columns, and charset (utf8mb3 vs utf8mb4).
 *
 * Columns are collected from BOTH sql_create() AND sql_update_columns_releases()
 * (ALTER TABLE ADD / DROP COLUMN) so that migration-added columns are expected.
 *
 * Accessible from WP-Admin  ->  Reservations  ->  DB Check
 *
 * @since 2.3.4
 */
class ALEXR_Database_Check {

	public function __construct() {
		add_action( 'admin_menu', [ $this, 'register_menu' ], 20 );
	}

	/* ------------------------------------------------------------------ */
	/*  MENU                                                               */
	/* ------------------------------------------------------------------ */

	public function register_menu() {
		add_submenu_page(
			'admin-alex-reservations',
			__( 'DB Check', 'alexr' ),
			__( 'DB Check', 'alexr' ),
			'manage_options',
			'alexr-database-check',
			[ $this, 'render_page' ]
		);
	}

	/* ------------------------------------------------------------------ */
	/*  TABLES LIST  (same as install.php)                                 */
	/* ------------------------------------------------------------------ */

	private function get_expected_tables(): array {
		return [
			'restaurants', 'restaurant_meta', 'restaurant_setting',
			'users', 'user_meta',
			'customers', 'customer_meta', 'ctaggroups', 'ctags', 'customer_ctag',
			'bookings', 'booking_meta', 'booking_notifications', 'booking_reviews',
			'btaggroups', 'btags', 'booking_btag',
			'restaurant_user',
			'settings', 'notifications', 'payments',
			'floors', 'areas', 'tables',
			'combinations', 'combination_table',
			'booking_table',
			'roles', 'actions', 'daily_notifications', 'tokens',
			'bookings_recurring',
			'ttaggroups', 'ttags', 'table_ttag', 'layouts',
			'automation_logs', 'booking_tracking',
			'whatsapp_flows',
			'whatsapp_conversations',
			'whatsapp_messages',
			'whatsapp_webhooks',
			'api_keys', 'api_logs',
			'api_keys_bot', 'api_logs_bot',
            'url_shorteners', 'missed_call_logs',
            'spaces',
            'vouchers', 'voucher_redemptions'
		];
	}

	/* ------------------------------------------------------------------ */
	/*  EXPECTED COLUMNS                                                   */
	/*  Parsed from sql_create()  +  sql_update_columns_releases()         */
	/* ------------------------------------------------------------------ */

	/**
	 * Return ALL expected columns for a table: base (CREATE TABLE) plus
	 * any columns ADDed by migrations, minus any DROPped by migrations.
	 */
	private function get_expected_columns( string $table_key ): array {
		$db_instance = WP_ALEXR()->{$table_key} ?? null;

		if ( ! $db_instance || ! method_exists( $db_instance, 'sql_create' ) ) {
			return [];
		}

		// 1. Base columns from sql_create()
		$sql     = $db_instance->sql_create();
		$columns = ! empty( $sql ) ? $this->parse_columns_from_create_sql( $sql ) : [];

		// 2. Read the source of sql_update_columns_releases() and apply ADD / DROP
		if ( method_exists( $db_instance, 'sql_update_columns_releases' ) ) {
			$source = $this->get_method_source( $db_instance, 'sql_update_columns_releases' );
			if ( $source !== '' ) {
				$columns = $this->apply_migration_columns( $columns, $source );
			}
		}

		return array_values( array_unique( $columns ) );
	}

	/**
	 * Parse column names from a CREATE TABLE statement.
	 * Ignores PRIMARY KEY, KEY, CONSTRAINT, UNIQUE KEY, INDEX lines.
	 */
	private function parse_columns_from_create_sql( string $sql ): array {
		$columns = [];

		// Get everything between the first ( and the last )
		if ( ! preg_match( '/CREATE TABLE[^(]+\((.+)\)\s*$/s', $sql, $m ) ) {
			return $columns;
		}

		$body  = $m[1];
		$lines = preg_split( '/\n/', $body );

		foreach ( $lines as $line ) {
			$line = trim( $line, " \t\r\n," );
			if ( $line === '' ) continue;

			// Skip non-column definitions
			$upper = strtoupper( $line );
			if (
				str_starts_with( $upper, 'PRIMARY KEY' ) ||
				str_starts_with( $upper, 'KEY ' ) ||
				str_starts_with( $upper, 'UNIQUE KEY' ) ||
				str_starts_with( $upper, 'CONSTRAINT' ) ||
				str_starts_with( $upper, 'INDEX' ) ||
				str_starts_with( $upper, 'FOREIGN KEY' )
			) {
				continue;
			}

			// First token is the column name (may be back-ticked)
			if ( preg_match( '/^`?(\w+)`?\s+/', $line, $cm ) ) {
				$columns[] = strtolower( $cm[1] );
			}
		}

		return $columns;
	}

	/**
	 * Use Reflection to read the PHP source code of a method.
	 */
	private function get_method_source( object $instance, string $method ): string {
		try {
			$ref   = new ReflectionMethod( $instance, $method );
			$file  = $ref->getFileName();
			$start = $ref->getStartLine();
			$end   = $ref->getEndLine();

			if ( ! $file || ! is_readable( $file ) ) {
				return '';
			}

			$lines = file( $file );
			if ( $lines === false ) {
				return '';
			}

			// getStartLine / getEndLine are 1-based
			return implode( '', array_slice( $lines, $start - 1, $end - $start + 1 ) );
		} catch ( \ReflectionException $e ) {
			return '';
		}
	}

	/**
	 * Parse ALTER TABLE ADD / DROP COLUMN from the raw PHP source of
	 * sql_update_columns_releases() and apply them to the column list.
	 *
	 * Recognised patterns:
	 *   ALTER TABLE ... ADD [COLUMN] `col_name` ...
	 *   ALTER TABLE ... DROP COLUMN `col_name`
	 */
	private function apply_migration_columns( array $columns, string $source ): array {

		// ADD [COLUMN] `column_name` or ADD column_name
		if ( preg_match_all(
			'/ALTER\s+TABLE\s+.+?\s+ADD\s+(?:COLUMN\s+)?`?(\w+)`?\s+/i',
			$source,
			$matches
		) ) {
			foreach ( $matches[1] as $col ) {
				$col = strtolower( $col );
				// Skip non-column keywords that might match
				if ( in_array( $col, [ 'index', 'key', 'unique', 'primary', 'constraint', 'foreign' ], true ) ) {
					continue;
				}
				if ( ! in_array( $col, $columns, true ) ) {
					$columns[] = $col;
				}
			}
		}

		// DROP COLUMN `column_name`
		if ( preg_match_all(
			'/ALTER\s+TABLE\s+.+?\s+DROP\s+COLUMN\s+`?(\w+)`?/i',
			$source,
			$matches
		) ) {
			foreach ( $matches[1] as $col ) {
				$col     = strtolower( $col );
				$columns = array_filter( $columns, fn( $c ) => $c !== $col );
			}
		}

		return $columns;
	}

	/* ------------------------------------------------------------------ */
	/*  LIVE DATABASE INSPECTION                                           */
	/* ------------------------------------------------------------------ */

	private function get_full_table_name( string $table_key ): string {
		$db_instance = WP_ALEXR()->{$table_key} ?? null;
		if ( $db_instance ) {
			return $db_instance->table_name;
		}

		// Fallback
		global $wpdb;
		return $wpdb->prefix . EVAVEL_DB_NAMESPACE . '_' . $table_key;
	}

	private function table_exists_in_db( string $full_name ): bool {
		global $wpdb;
		return $wpdb->get_var(
			$wpdb->prepare( "SHOW TABLES LIKE %s", $full_name )
		) === $full_name;
	}

	/**
	 * Return associative array of column_name => column_type for a live table.
	 */
	private function get_live_columns( string $full_name ): array {
		global $wpdb;
		$rows    = $wpdb->get_results( "SHOW COLUMNS FROM `{$full_name}`" );
		$columns = [];

		foreach ( $rows as $row ) {
			$columns[ strtolower( $row->Field ) ] = $row->Type;
		}

		return $columns;
	}

	/**
	 * Return charset & collation of a table.
	 */
	private function get_table_charset_info( string $full_name ): array {
		global $wpdb;

		$row = $wpdb->get_row(
			"SELECT TABLE_COLLATION, ENGINE
			 FROM information_schema.TABLES
			 WHERE TABLE_SCHEMA = DATABASE()
			   AND TABLE_NAME = '{$full_name}'"
		);

		if ( ! $row ) {
			return [ 'charset' => '?', 'collation' => '?', 'engine' => '?' ];
		}

		$collation = $row->TABLE_COLLATION ?? '?';

		if ( str_starts_with( $collation, 'utf8mb4' ) ) {
			$charset = 'utf8mb4';
		} elseif ( str_starts_with( $collation, 'utf8' ) ) {
			$charset = 'utf8 (utf8mb3)';
		} elseif ( str_starts_with( $collation, 'latin' ) ) {
			$charset = 'latin1';
		} else {
			$charset = strtok( $collation, '_' );
		}

		return [
			'charset'   => $charset,
			'collation' => $collation,
			'engine'    => $row->ENGINE ?? '?',
		];
	}

	/* ------------------------------------------------------------------ */
	/*  BUILD THE FULL REPORT                                              */
	/* ------------------------------------------------------------------ */

	private function build_report(): array {
		$tables = $this->get_expected_tables();
		$report = [];

		foreach ( $tables as $table_key ) {
			$full_name = $this->get_full_table_name( $table_key );
			$exists    = $this->table_exists_in_db( $full_name );

			$entry = [
				'key'              => $table_key,
				'full_name'        => $full_name,
				'exists'           => $exists,
				'charset'          => '—',
				'collation'        => '—',
				'engine'           => '—',
				'missing_columns'  => [],
				'extra_columns'    => [],
				'expected_columns' => [],
				'live_columns'     => [],
				'status'           => 'missing',
			];

			if ( $exists ) {
				$charset_info         = $this->get_table_charset_info( $full_name );
				$entry['charset']     = $charset_info['charset'];
				$entry['collation']   = $charset_info['collation'];
				$entry['engine']      = $charset_info['engine'];

				$expected = $this->get_expected_columns( $table_key );
				$live     = $this->get_live_columns( $full_name );

				$live_names = array_keys( $live );

				$entry['expected_columns'] = $expected;
				$entry['live_columns']     = $live;
				$entry['missing_columns']  = array_values( array_diff( $expected, $live_names ) );
				$entry['extra_columns']    = array_values( array_diff( $live_names, $expected ) );

				if ( empty( $entry['missing_columns'] ) ) {
					$entry['status'] = 'ok';
				} else {
					$entry['status'] = 'columns_missing';
				}
			}

			$report[] = $entry;
		}

		return $report;
	}

	/* ------------------------------------------------------------------ */
	/*  RENDER                                                             */
	/* ------------------------------------------------------------------ */

	public function render_page() {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_die( __( 'You do not have permission to access this page.', 'alexr' ) );
		}

		$report = $this->build_report();

		$total   = count( $report );
		$ok      = count( array_filter( $report, fn( $r ) => $r['status'] === 'ok' ) );
		$missing = count( array_filter( $report, fn( $r ) => $r['status'] === 'missing' ) );
		$issues  = $total - $ok - $missing;

		?>
		<div class="wrap">
			<h1>Alex Reservations — Database Integrity Check</h1>

			<p style="font-size:14px; margin:15px 0 20px;">
				Tables: <strong><?php echo $total; ?></strong> &nbsp;|&nbsp;
				<span style="color:#46b450;">OK: <strong><?php echo $ok; ?></strong></span> &nbsp;|&nbsp;
				<?php if ( $missing ): ?>
					<span style="color:#dc3232;">Missing tables: <strong><?php echo $missing; ?></strong></span> &nbsp;|&nbsp;
				<?php endif; ?>
				<?php if ( $issues ): ?>
					<span style="color:#f56e28;">Column issues: <strong><?php echo $issues; ?></strong></span>
				<?php endif; ?>
				<?php if ( !$missing && !$issues ): ?>
					<span style="color:#46b450;">All tables are correct</span>
				<?php endif; ?>
			</p>

			<table class="widefat striped fixed" style="max-width:1200px;">
				<thead>
					<tr>
						<th style="width:30px;">#</th>
						<th style="width:280px;">Table</th>
						<th style="width:80px;">Exists</th>
						<th style="width:120px;">Charset</th>
						<th style="width:100px;">Engine</th>
						<th style="width:120px;">Columns</th>
						<th>Issues</th>
					</tr>
				</thead>
				<tbody>
				<?php $i = 0; foreach ( $report as $entry ): $i++; ?>
					<tr>
						<td><?php echo $i; ?></td>
						<td>
							<strong><?php echo esc_html( $entry['key'] ); ?></strong><br>
							<small style="color:#888;"><?php echo esc_html( $entry['full_name'] ); ?></small>
						</td>
						<td>
							<?php if ( $entry['exists'] ): ?>
								<span style="color:#46b450;">&#10003; Yes</span>
							<?php else: ?>
								<span style="color:#dc3232;">&#10007; No</span>
							<?php endif; ?>
						</td>
						<td>
							<?php if ( $entry['exists'] ): ?>
								<?php
								$charset_color = '#333';
								if ( str_contains( $entry['charset'], 'utf8mb4' ) ) {
									$charset_color = '#0073aa';
								} elseif ( str_contains( $entry['charset'], 'utf8' ) ) {
									$charset_color = '#826eb4';
								}
								?>
								<span style="color:<?php echo $charset_color; ?>; font-weight:600;">
									<?php echo esc_html( $entry['charset'] ); ?>
								</span>
								<br>
								<small style="color:#888;"><?php echo esc_html( $entry['collation'] ); ?></small>
							<?php else: ?>
								—
							<?php endif; ?>
						</td>
						<td><?php echo esc_html( $entry['engine'] ); ?></td>
						<td>
							<?php if ( $entry['exists'] ): ?>
								<?php
								$expected_count = count( $entry['expected_columns'] );
								$live_count     = count( $entry['live_columns'] );
								?>
								<span title="Expected / Live"><?php echo $expected_count; ?> / <?php echo $live_count; ?></span>
							<?php else: ?>
								—
							<?php endif; ?>
						</td>
						<td>
							<?php if ( ! $entry['exists'] ): ?>
								<span style="color:#dc3232; font-weight:600;">Table does not exist</span>
							<?php elseif ( ! empty( $entry['missing_columns'] ) ): ?>
								<span style="color:#f56e28;">Missing columns:</span>
								<code><?php echo esc_html( implode( ', ', $entry['missing_columns'] ) ); ?></code>
							<?php else: ?>
								<span style="color:#46b450;">&#10003;</span>
							<?php endif; ?>

							<?php if ( ! empty( $entry['extra_columns'] ) ): ?>
								<br>
								<span style="color:#888;">Extra columns:</span>
								<small><?php echo esc_html( implode( ', ', $entry['extra_columns'] ) ); ?></small>
							<?php endif; ?>
						</td>
					</tr>
				<?php endforeach; ?>
				</tbody>
			</table>

			<p style="margin-top:20px; color:#888; font-size:12px;">
				Generated at <?php echo current_time( 'Y-m-d H:i:s' ); ?> &mdash;
				Plugin v<?php echo ALEXR_VERSION; ?> (Release <?php echo ALEXR_RELEASE; ?>)
			</p>
		</div>
		<?php
	}
}

new ALEXR_Database_Check();