<?php
/**
 * Database Management Class for AskAny Plugin
 *
 * Handles database operations, cleanup, optimization, and analytics
 * for the AskAny chat system.
 *
 * @package Askany
 * @since 1.0.0
 */

namespace Askany;

// Exit if accessed directly
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class DatabaseManager {

	/**
	 * Database instance
	 *
	 * @var Database
	 */
	private $database;

	/**
	 * Constructor
	 *
	 * @param Database $database Database instance
	 */
	public function __construct( Database $database ) {
		$this->database = $database;
	}

	/**
	 * Get database table sizes in MB
	 *
	 * @return array Array of table names and their sizes
	 */
	public function get_table_sizes() {
		global $wpdb;

		$tables = array(
			'chat_sessions' => $this->database->get_sessions_table(),
			'chat_messages' => $this->database->get_messages_table(),
			'qa_pairs' => $this->database->get_qa_pairs_table(),
			'external_sources' => $this->database->get_external_sources_table(),
			'content_index' => $this->database->get_content_index_table(),
			'pdf_index' => $this->database->get_pdf_index_table(),
			'chat_history' => $this->database->get_chat_history_table(),
			'user_agents' => $this->database->get_user_agents_table(),
			'typing_indicators' => $this->database->get_typing_indicators_table(),
			'home_faqs' => $this->database->get_home_faqs_table(),
			'home_links' => $this->database->get_home_links_table(),
			'api_stats' => $this->database->get_api_stats_table(),
			'feature_requests' => $this->database->get_feature_requests_table(),
			'bug_reports' => $this->database->get_bug_reports_table(),
			'embeddings' => $this->database->get_embeddings_table(),
		);

		$sizes = array();
		$total_size = 0;

		foreach ( $tables as $key => $table_name ) {
			$size_query = $wpdb->prepare(
				"SELECT 
					ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
					table_rows as row_count
				FROM information_schema.TABLES 
				WHERE table_schema = %s 
				AND table_name = %s",
				DB_NAME,
				$table_name
			);

			$result = $wpdb->get_row( $size_query );

			$size_mb = $result ? (float) $result->size_mb : 0;
			$row_count = $result ? (int) $result->row_count : 0;

			$sizes[ $key ] = array(
				'table_name' => $table_name,
				'size_mb' => $size_mb,
				'row_count' => $row_count,
				'formatted_size' => $this->format_bytes( $size_mb * 1024 * 1024 ),
			);

			$total_size += $size_mb;
		}

		$sizes['total'] = array(
			'size_mb' => $total_size,
			'formatted_size' => $this->format_bytes( $total_size * 1024 * 1024 ),
		);

		return $sizes;
	}

	/**
	 * Get record counts for each table
	 *
	 * @return array Array of table record counts
	 */
	public function get_record_counts() {
		global $wpdb;

		$tables = array(
			'chat_sessions' => $this->database->get_sessions_table(),
			'chat_messages' => $this->database->get_messages_table(),
			'qa_pairs' => $this->database->get_qa_pairs_table(),
			'external_sources' => $this->database->get_external_sources_table(),
			'content_index' => $this->database->get_content_index_table(),
			'pdf_index' => $this->database->get_pdf_index_table(),
			'chat_history' => $this->database->get_chat_history_table(),
			'user_agents' => $this->database->get_user_agents_table(),
			'typing_indicators' => $this->database->get_typing_indicators_table(),
			'home_faqs' => $this->database->get_home_faqs_table(),
			'home_links' => $this->database->get_home_links_table(),
			'api_stats' => $this->database->get_api_stats_table(),
			'feature_requests' => $this->database->get_feature_requests_table(),
			'bug_reports' => $this->database->get_bug_reports_table(),
			'embeddings' => $this->database->get_embeddings_table(),
		);

		$counts = array();

		foreach ( $tables as $key => $table_name ) {
			// Use the same method as get_table_sizes() for consistency
			$result = $wpdb->get_row( $wpdb->prepare(
				"SELECT table_rows as row_count
				FROM information_schema.TABLES 
				WHERE table_schema = %s 
				AND table_name = %s",
				DB_NAME,
				$table_name
			) );

			$counts[ $key ] = $result ? (int) $result->row_count : 0;
		}

		return $counts;
	}

	/**
	 * Delete data by date range
	 *
	 * @param string $start_date Start date (Y-m-d format)
	 * @param string $end_date End date (Y-m-d format)
	 * @param array $options Deletion options
	 * @return array Result with success status and details
	 */
	public function delete_data_by_date_range( $start_date, $end_date, $options = array() ) {
		global $wpdb;

		// Validate dates
		if ( ! $this->validate_date( $start_date ) || ! $this->validate_date( $end_date ) ) {
			return array(
				'success' => false,
				'message' => __( 'Invalid date format. Use Y-m-d format.', 'askany' ),
			);
		}

		// Convert to datetime for database query
		$start_datetime = $start_date . ' 00:00:00';
		$end_datetime = $end_date . ' 23:59:59';

		// Tables with date fields that can be filtered by date range
		$date_tables = array(
			'sessions' => $this->database->get_sessions_table(),
			'messages' => $this->database->get_messages_table(),
			'qa_pairs' => $this->database->get_qa_pairs_table(),
			'chat_history' => $this->database->get_chat_history_table(),
			'user_agents' => $this->database->get_user_agents_table(),
			'typing_indicators' => $this->database->get_typing_indicators_table(),
			'home_faqs' => $this->database->get_home_faqs_table(),
			'api_stats' => $this->database->get_api_stats_table(),
			'feature_requests' => $this->database->get_feature_requests_table(),
			'bug_reports' => $this->database->get_bug_reports_table(),
		);

		$sessions_table = $date_tables['sessions'];
		$messages_table = $date_tables['messages'];

		// Start transaction
		$wpdb->query( 'START TRANSACTION' );

		try {
			$deleted_counts = array();
			$total_deleted = 0;

			// First, handle sessions and messages (with foreign key relationship)
			$session_ids = $wpdb->get_col( $wpdb->prepare(
				"SELECT session_id FROM {$sessions_table} 
				WHERE created_at BETWEEN %s AND %s",
				$start_datetime,
				$end_datetime
			) );

			$session_count = count( $session_ids );
			$message_count = 0;

			if ( ! empty( $session_ids ) ) {
				$session_ids_placeholder = implode( ',', array_fill( 0, count( $session_ids ), '%s' ) );
				$message_count = $wpdb->get_var( $wpdb->prepare(
					"SELECT COUNT(*) FROM {$messages_table} WHERE session_id IN ({$session_ids_placeholder})",
					...$session_ids
				) );

				// Delete messages first (foreign key constraint)
				$deleted_messages = $wpdb->query( $wpdb->prepare(
					"DELETE FROM {$messages_table} WHERE session_id IN ({$session_ids_placeholder})",
					...$session_ids
				) );

				if ( $deleted_messages === false ) {
					throw new \Exception( __( 'Failed to delete messages.', 'askany' ) );
				}
			}

			// Delete sessions
			$deleted_sessions = $wpdb->query( $wpdb->prepare(
				"DELETE FROM {$sessions_table} 
				WHERE created_at BETWEEN %s AND %s",
				$start_datetime,
				$end_datetime
			) );

			if ( $deleted_sessions === false ) {
				throw new \Exception( __( 'Failed to delete sessions.', 'askany' ) );
			}

			$deleted_counts['sessions'] = $session_count;
			$deleted_counts['messages'] = $message_count;
			$total_deleted += $session_count + $message_count;

			// Delete from other date-based tables
			foreach ( $date_tables as $key => $table_name ) {
				if ( $key === 'sessions' || $key === 'messages' ) {
					continue; // Already handled above
				}

				$count_before = $wpdb->get_var( $wpdb->prepare(
					"SELECT COUNT(*) FROM {$table_name} WHERE created_at BETWEEN %s AND %s",
					$start_datetime,
					$end_datetime
				) );

				if ( $count_before > 0 ) {
					$deleted = $wpdb->query( $wpdb->prepare(
						"DELETE FROM {$table_name} WHERE created_at BETWEEN %s AND %s",
						$start_datetime,
						$end_datetime
					) );

					if ( $deleted === false ) {
						throw new \Exception( sprintf( __( 'Failed to delete from %s table.', 'askany' ), $key ) );
					}

					$deleted_counts[$key] = $count_before;
					$total_deleted += $count_before;
				} else {
					$deleted_counts[$key] = 0;
				}
			}

			// Commit transaction
			$wpdb->query( 'COMMIT' );

			// Log the operation
			$this->log_database_operation( 'delete_by_date_range', array(
				'start_date' => $start_date,
				'end_date' => $end_date,
				'deleted_counts' => $deleted_counts,
				'total_deleted' => $total_deleted,
			) );

			// Create summary message
			$summary_parts = array();
			foreach ( $deleted_counts as $table => $count ) {
				if ( $count > 0 ) {
					$summary_parts[] = sprintf( '%d %s', $count, $table );
				}
			}

			$summary = empty( $summary_parts ) ? 
				__( 'No data found in the specified date range.', 'askany' ) :
				sprintf( 
					__( 'Successfully deleted %s from %s to %s.', 'askany' ),
					implode( ', ', $summary_parts ),
					$start_date,
					$end_date
				);

			return array(
				'success' => true,
				'message' => $summary,
				'deleted_counts' => $deleted_counts,
				'total_deleted' => $total_deleted,
			);

		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			return array(
				'success' => false,
				'message' => $e->getMessage(),
			);
		}
	}

	/**
	 * Delete data older than specified days
	 *
	 * @param int $days Number of days
	 * @return array Result with success status and details
	 */
	public function delete_old_data( $days ) {
		$cutoff_date = date( 'Y-m-d', strtotime( "-{$days} days" ) );
		$start_date = '2020-01-01'; // Far back date to include all old data
		
		return $this->delete_data_by_date_range( $start_date, $cutoff_date );
	}

	/**
	 * Truncate all chat data
	 *
	 * @return array Result with success status and details
	 */
	public function truncate_all_data() {
		global $wpdb;

		// Get all table names
		$tables = array(
			'sessions' => $this->database->get_sessions_table(),
			'messages' => $this->database->get_messages_table(),
			'qa_pairs' => $this->database->get_qa_pairs_table(),
			'external_sources' => $this->database->get_external_sources_table(),
			'content_index' => $this->database->get_content_index_table(),
			'pdf_index' => $this->database->get_pdf_index_table(),
			'chat_history' => $this->database->get_chat_history_table(),
			'user_agents' => $this->database->get_user_agents_table(),
			'typing_indicators' => $this->database->get_typing_indicators_table(),
			'home_faqs' => $this->database->get_home_faqs_table(),
			'home_links' => $this->database->get_home_links_table(),
			'api_stats' => $this->database->get_api_stats_table(),
			'feature_requests' => $this->database->get_feature_requests_table(),
			'bug_reports' => $this->database->get_bug_reports_table(),
			'embeddings' => $this->database->get_embeddings_table(),
		);

		$sessions_table = $tables['sessions'];
		$messages_table = $tables['messages'];

		// Get counts before truncation for all tables
		$table_counts = array();
		$total_records = 0;

		foreach ( $tables as $key => $table_name ) {
			$count_info = $wpdb->get_row( $wpdb->prepare(
				"SELECT table_rows as row_count
				FROM information_schema.TABLES 
				WHERE table_schema = %s 
				AND table_name = %s",
				DB_NAME,
				$table_name
			) );
			
			$count = $count_info ? $count_info->row_count : 0;
			$table_counts[$key] = $count;
			$total_records += $count;
		}

		// Clear all data from tables
		try {
			// Disable foreign key checks temporarily
			$wpdb->query( 'SET FOREIGN_KEY_CHECKS = 0' );
			
			$truncated_tables = array();
			$failed_tables = array();

			// Truncate all tables
			foreach ( $tables as $key => $table_name ) {
				// Refresh table statistics first
				$wpdb->query( "ANALYZE TABLE `{$table_name}`" );
				
				// Check if we can see the data with direct queries
				$test_count = $wpdb->get_var( "SELECT COUNT(*) FROM `{$table_name}`" );
				
				// Use appropriate method based on data visibility
				if ( $test_count == 0 && $table_counts[$key] > 0 ) {
					// Data exists but not visible to COUNT queries - use TRUNCATE
					$result = $wpdb->query( "TRUNCATE TABLE `{$table_name}`" );
				} else {
					// Data is visible - use DELETE
					$result = $wpdb->query( "DELETE FROM `{$table_name}`" );
				}
				
				if ( $result !== false ) {
					$truncated_tables[] = $key;
					// Force table statistics refresh after operation
					$wpdb->query( "ANALYZE TABLE `{$table_name}`" );
				} else {
					$failed_tables[] = $key;
				}
			}
			
			// Re-enable foreign key checks
			$wpdb->query( 'SET FOREIGN_KEY_CHECKS = 1' );

			if ( ! empty( $failed_tables ) ) {
				throw new \Exception( sprintf(
					__( 'Failed to truncate some tables: %s. Database error: %s', 'askany' ),
					implode( ', ', $failed_tables ),
					$wpdb->last_error
				) );
			}

			// Log the operation
			$this->log_database_operation( 'truncate_all', array(
				'tables_truncated' => $truncated_tables,
				'total_records_deleted' => $total_records,
				'table_counts' => $table_counts,
			) );

			return array(
				'success' => true,
				'message' => sprintf(
					__( 'Successfully truncated all AskAny data. Removed %d total records from %d tables: %s.', 'askany' ),
					$total_records,
					count( $truncated_tables ),
					implode( ', ', $truncated_tables )
				),
				'tables_truncated' => $truncated_tables,
				'total_records_deleted' => $total_records,
				'table_counts' => $table_counts,
			);

		} catch ( \Exception $e ) {
			$wpdb->query( 'SET FOREIGN_KEY_CHECKS = 1' );
			return array(
				'success' => false,
				'message' => $e->getMessage(),
			);
		}
	}

	/**
	 * Optimize database tables
	 *
	 * @return array Result with success status and details
	 */
	public function optimize_tables() {
		global $wpdb;

		$tables = array(
			$this->database->get_sessions_table(),
			$this->database->get_messages_table(),
			$this->database->get_qa_pairs_table(),
			$this->database->get_external_sources_table(),
			$this->database->get_content_index_table(),
			$this->database->get_pdf_index_table(),
			$this->database->get_chat_history_table(),
			$this->database->get_user_agents_table(),
			$this->database->get_typing_indicators_table(),
			$this->database->get_home_faqs_table(),
			$this->database->get_home_links_table(),
			$this->database->get_api_stats_table(),
			$this->database->get_feature_requests_table(),
			$this->database->get_bug_reports_table(),
			$this->database->get_embeddings_table(),
		);

		$results = array();
		$success_count = 0;
		$space_reclaimed = 0;

		foreach ( $tables as $table ) {
			// Get size before optimization
			$size_before = $this->get_single_table_size( $table );
			
			// Step 1: Analyze table (updates statistics)
			$wpdb->query( "ANALYZE TABLE {$table}" );
			
			// Step 2: Optimize table (reclaims space)
			$result = $wpdb->query( "OPTIMIZE TABLE {$table}" );
			
			// Get size after optimization
			$size_after = $this->get_single_table_size( $table );
			
			$reclaimed = max( 0, $size_before - $size_after );
			$space_reclaimed += $reclaimed;
			
			$results[ $table ] = array(
				'success' => $result !== false,
				'size_before' => $this->format_bytes( $size_before ),
				'size_after' => $this->format_bytes( $size_after ),
				'reclaimed' => $this->format_bytes( $reclaimed ),
			);
			
			if ( $result !== false ) {
				$success_count++;
			}
		}

		$this->log_database_operation( 'optimize_tables', array(
			'tables_optimized' => $success_count,
			'total_tables' => count( $tables ),
			'space_reclaimed' => $this->format_bytes( $space_reclaimed ),
		) );

		return array(
			'success' => $success_count === count( $tables ),
			'message' => sprintf(
				__( 'Optimized %d out of %d tables. Reclaimed %s of space.', 'askany' ),
				$success_count,
				count( $tables ),
				$this->format_bytes( $space_reclaimed )
			),
			'details' => $results,
			'space_reclaimed' => $space_reclaimed,
			'space_reclaimed_formatted' => $this->format_bytes( $space_reclaimed ),
		);
	}

	/**
	 * Get single table size in bytes
	 *
	 * @param string $table_name Table name
	 * @return float Size in bytes
	 */
	private function get_single_table_size( $table_name ) {
		global $wpdb;
		
		$result = $wpdb->get_var( $wpdb->prepare(
			"SELECT (data_length + index_length) 
			FROM information_schema.TABLES 
			WHERE table_schema = %s 
			AND table_name = %s",
			DB_NAME,
			$table_name
		) );
		
		return $result ? (float) $result : 0;
	}

	/**
	 * Get database growth analytics
	 *
	 * @param int $days Number of days to analyze
	 * @return array Growth data
	 */
	public function get_growth_analytics( $days = 30 ) {
		global $wpdb;

		$sessions_table = $this->database->get_sessions_table();
		$messages_table = $this->database->get_messages_table();

		$start_date = date( 'Y-m-d', strtotime( "-{$days} days" ) );

		// Get daily session counts
		$session_growth = $wpdb->get_results( $wpdb->prepare(
			"SELECT 
				DATE(created_at) as date,
				COUNT(*) as count
			FROM {$sessions_table}
			WHERE created_at >= %s
			GROUP BY DATE(created_at)
			ORDER BY date ASC",
			$start_date
		) );

		// Get daily message counts
		$message_growth = $wpdb->get_results( $wpdb->prepare(
			"SELECT 
				DATE(created_at) as date,
				COUNT(*) as count
			FROM {$messages_table}
			WHERE created_at >= %s
			GROUP BY DATE(created_at)
			ORDER BY date ASC",
			$start_date
		) );

		return array(
			'sessions' => $session_growth,
			'messages' => $message_growth,
			'period_days' => $days,
		);
	}

	/**
	 * Format bytes to human readable format
	 *
	 * @param int $bytes Number of bytes
	 * @return string Formatted size
	 */
	private function format_bytes( $bytes ) {
		$units = array( 'B', 'KB', 'MB', 'GB', 'TB' );
		
		for ( $i = 0; $bytes > 1024 && $i < count( $units ) - 1; $i++ ) {
			$bytes /= 1024;
		}
		
		return round( $bytes, 2 ) . ' ' . $units[ $i ];
	}

	/**
	 * Validate date format
	 *
	 * @param string $date Date string
	 * @return bool True if valid
	 */
	private function validate_date( $date ) {
		$d = \DateTime::createFromFormat( 'Y-m-d', $date );
		return $d && $d->format( 'Y-m-d' ) === $date;
	}

	/**
	 * Log database operation
	 *
	 * @param string $operation Operation type
	 * @param array $details Operation details
	 */
	private function log_database_operation( $operation, $details = array() ) {
		$log_entry = array(
			'timestamp' => current_time( 'mysql' ),
			'user_id' => get_current_user_id(),
			'operation' => $operation,
			'details' => $details,
		);

		// Store in WordPress options or custom log table
		$logs = get_option( 'askany_database_operation_logs', array() );
		$logs[] = $log_entry;

		// Keep only last 100 log entries
		if ( count( $logs ) > 100 ) {
			$logs = array_slice( $logs, -100 );
		}

		update_option( 'askany_database_operation_logs', $logs );
	}
}