using MarketAlly.AIPlugin;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace MarketAlly.AIPlugin.Analysis.Plugins
{
	/// <summary>
	/// Plugin that reads and analyzes SQLite database schemas
	/// </summary>
	[AIPlugin("SQLiteSchemaReader", "Reads and analyzes SQLite database schemas with detailed table, index, and relationship information")]
	public class SQLiteSchemaReaderPlugin : IAIPlugin
	{
		private readonly ILogger<SQLiteSchemaReaderPlugin>? _logger;

		/// <summary>
		/// Creates a new instance of SQLiteSchemaReaderPlugin
		/// </summary>
		/// <param name="logger">Optional logger for recording operations</param>
		public SQLiteSchemaReaderPlugin(ILogger<SQLiteSchemaReaderPlugin>? logger = null)
		{
			_logger = logger;
		}

		[AIParameter("Full path to the SQLite database file", required: true)]
		public string DatabasePath { get; set; } = string.Empty;

		[AIParameter("Include table row counts in analysis", required: false)]
		public bool IncludeRowCounts { get; set; } = true;

		[AIParameter("Include detailed index information", required: false)]
		public bool IncludeIndexes { get; set; } = true;

		[AIParameter("Include foreign key relationships", required: false)]
		public bool IncludeForeignKeys { get; set; } = true;

		[AIParameter("Include database metadata and statistics", required: false)]
		public bool IncludeMetadata { get; set; } = true;

		[AIParameter("Output format: structured, readable, json", required: false)]
		public string OutputFormat { get; set; } = "structured";

		[AIParameter("Maximum number of sample rows to include per table", required: false)]
		public int MaxSampleRows { get; set; } = 0;

		public IReadOnlyDictionary<string, Type> SupportedParameters => new Dictionary<string, Type>
		{
			["databasePath"] = typeof(string),
			["includeRowCounts"] = typeof(bool),
			["includeIndexes"] = typeof(bool),
			["includeForeignKeys"] = typeof(bool),
			["includeMetadata"] = typeof(bool),
			["outputFormat"] = typeof(string),
			["maxSampleRows"] = typeof(int)
		};

		public async Task<AIPluginResult> ExecuteAsync(IReadOnlyDictionary<string, object> parameters)
		{
			try
			{
				_logger?.LogInformation("SQLiteSchemaReader plugin executing for database {DatabasePath}", parameters["databasePath"]);

				// Extract parameters
				string databasePath = parameters["databasePath"].ToString() ?? string.Empty;

				bool includeRowCounts = parameters.TryGetValue("includeRowCounts", out var rowCountsValue)
					? Convert.ToBoolean(rowCountsValue)
					: true;

				bool includeIndexes = parameters.TryGetValue("includeIndexes", out var indexesValue)
					? Convert.ToBoolean(indexesValue)
					: true;

				bool includeForeignKeys = parameters.TryGetValue("includeForeignKeys", out var fkValue)
					? Convert.ToBoolean(fkValue)
					: true;

				bool includeMetadata = parameters.TryGetValue("includeMetadata", out var metadataValue)
					? Convert.ToBoolean(metadataValue)
					: true;

				string outputFormat = parameters.TryGetValue("outputFormat", out var formatValue)
					? formatValue?.ToString()?.ToLower() ?? "structured"
					: "structured";

				int maxSampleRows = parameters.TryGetValue("maxSampleRows", out var sampleValue)
					? Convert.ToInt32(sampleValue)
					: 0;

				// Validate database file exists
				if (!File.Exists(databasePath))
				{
					return new AIPluginResult(
						new FileNotFoundException($"Database file not found: {databasePath}"),
						"Database file not found"
					);
				}

				// Read the schema
				var schemaData = await ReadSchemaAsync(databasePath, includeRowCounts, includeIndexes,
					includeForeignKeys, includeMetadata, maxSampleRows);

				// Format output based on requested format
				object result = outputFormat switch
				{
					"json" => schemaData,
					"readable" => await GenerateReadableSchemaAsync(schemaData),
					_ => schemaData // structured (default)
				};

				_logger?.LogInformation("Successfully analyzed SQLite database schema for {DatabasePath}, found {TableCount} tables",
					databasePath, schemaData.Tables?.Count ?? 0);

				return new AIPluginResult(
					result,
					$"Successfully analyzed SQLite database schema: {Path.GetFileName(databasePath)}"
				);
			}
			catch (Exception ex)
			{
				_logger?.LogError(ex, "Failed to read SQLite schema from {DatabasePath}", parameters["databasePath"]);
				return new AIPluginResult(ex, "Failed to read SQLite database schema");
			}
		}

		private async Task<DatabaseSchema> ReadSchemaAsync(string databasePath, bool includeRowCounts,
			bool includeIndexes, bool includeForeignKeys, bool includeMetadata, int maxSampleRows)
		{
			var connectionString = $"Data Source={databasePath}";
			var schema = new DatabaseSchema
			{
				DatabasePath = databasePath,
				DatabaseName = Path.GetFileNameWithoutExtension(databasePath),
				Tables = new List<TableSchema>()
			};

			using var connection = new SqliteConnection(connectionString);
			await connection.OpenAsync();

			// Get all tables
			var tableNames = await GetTablesAsync(connection);
			schema.TableCount = tableNames.Count;

			foreach (var tableName in tableNames)
			{
				var table = new TableSchema
				{
					Name = tableName,
					Columns = await GetTableSchemaAsync(connection, tableName)
				};

				if (includeIndexes)
				{
					table.Indexes = await GetTableIndexesAsync(connection, tableName);
				}

				if (includeForeignKeys)
				{
					table.ForeignKeys = await GetTableForeignKeysAsync(connection, tableName);
				}

				if (includeRowCounts)
				{
					table.RowCount = await GetTableRowCountAsync(connection, tableName);
				}

				if (maxSampleRows > 0)
				{
					table.SampleData = await GetSampleDataAsync(connection, tableName, maxSampleRows);
				}

				schema.Tables.Add(table);
			}

			if (includeMetadata)
			{
				schema.Metadata = await GetDatabaseMetadataAsync(connection, databasePath);
			}

			return schema;
		}

		private async Task<List<string>> GetTablesAsync(SqliteConnection connection)
		{
			var tables = new List<string>();

			using var command = connection.CreateCommand();
			command.CommandText = @"
                SELECT name 
                FROM sqlite_master 
                WHERE type='table' 
                AND name NOT LIKE 'sqlite_%'
                ORDER BY name";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				tables.Add(reader.GetString(0));
			}

			return tables;
		}

		private async Task<List<ColumnSchema>> GetTableSchemaAsync(SqliteConnection connection, string tableName)
		{
			var columns = new List<ColumnSchema>();

			using var command = connection.CreateCommand();
			command.CommandText = $"PRAGMA table_info([{tableName}])";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				var column = new ColumnSchema
				{
					Position = reader.GetInt32(0),
					Name = reader.GetString(1),
					DataType = reader.GetString(2),
					NotNull = reader.GetBoolean(3),
					DefaultValue = reader.IsDBNull(4) ? null : reader.GetValue(4)?.ToString(),
					IsPrimaryKey = reader.GetBoolean(5)
				};

				columns.Add(column);
			}

			return columns;
		}

		private async Task<List<IndexSchema>> GetTableIndexesAsync(SqliteConnection connection, string tableName)
		{
			var indexes = new List<IndexSchema>();

			using var command = connection.CreateCommand();
			command.CommandText = $"PRAGMA index_list([{tableName}])";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				var index = new IndexSchema
				{
					Sequence = reader.GetInt32(0),
					Name = reader.GetString(1),
					IsUnique = reader.GetBoolean(2),
					Origin = reader.GetString(3),
					IsPartial = reader.GetBoolean(4),
					Columns = await GetIndexColumnsAsync(connection, reader.GetString(1))
				};

				indexes.Add(index);
			}

			return indexes;
		}

		private async Task<List<string>> GetIndexColumnsAsync(SqliteConnection connection, string indexName)
		{
			var columns = new List<string>();

			using var command = connection.CreateCommand();
			command.CommandText = $"PRAGMA index_info([{indexName}])";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				columns.Add(reader.GetString(2));
			}

			return columns;
		}

		private async Task<List<ForeignKeySchema>> GetTableForeignKeysAsync(SqliteConnection connection, string tableName)
		{
			var foreignKeys = new List<ForeignKeySchema>();

			using var command = connection.CreateCommand();
			command.CommandText = $"PRAGMA foreign_key_list([{tableName}])";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				var fk = new ForeignKeySchema
				{
					Id = reader.GetInt32(0),
					Sequence = reader.GetInt32(1),
					ReferencedTable = reader.GetString(2),
					FromColumn = reader.GetString(3),
					ToColumn = reader.GetString(4),
					OnUpdate = reader.GetString(5),
					OnDelete = reader.GetString(6),
					Match = reader.GetString(7)
				};

				foreignKeys.Add(fk);
			}

			return foreignKeys;
		}

		private async Task<long> GetTableRowCountAsync(SqliteConnection connection, string tableName)
		{
			using var command = connection.CreateCommand();
			command.CommandText = $"SELECT COUNT(*) FROM [{tableName}]";

			var result = await command.ExecuteScalarAsync();
			return Convert.ToInt64(result);
		}

		private async Task<List<Dictionary<string, object>>> GetSampleDataAsync(SqliteConnection connection, string tableName, int maxRows)
		{
			var sampleData = new List<Dictionary<string, object>>();

			using var command = connection.CreateCommand();
			command.CommandText = $"SELECT * FROM [{tableName}] LIMIT {maxRows}";

			using var reader = await command.ExecuteReaderAsync();
			while (await reader.ReadAsync())
			{
				var row = new Dictionary<string, object>();
				for (int i = 0; i < reader.FieldCount; i++)
				{
					row[reader.GetName(i)] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
				}
				sampleData.Add(row);
			}

			return sampleData;
		}

		private async Task<DatabaseMetadata> GetDatabaseMetadataAsync(SqliteConnection connection, string databasePath)
		{
			var metadata = new DatabaseMetadata();

			// Get SQLite version
			using var versionCommand = connection.CreateCommand();
			versionCommand.CommandText = "SELECT sqlite_version()";
			metadata.SqliteVersion = await versionCommand.ExecuteScalarAsync() as string ?? "Unknown";

			// Get database size and page info
			using var sizeCommand = connection.CreateCommand();
			sizeCommand.CommandText = "PRAGMA page_count; PRAGMA page_size;";

			using var reader = await sizeCommand.ExecuteReaderAsync();
			if (await reader.ReadAsync())
			{
				metadata.PageCount = reader.GetInt64(0);
			}
			if (await reader.NextResultAsync() && await reader.ReadAsync())
			{
				metadata.PageSize = reader.GetInt64(0);
			}

			metadata.DatabaseSize = metadata.PageCount * metadata.PageSize;
			metadata.FormattedSize = FormatBytes(metadata.DatabaseSize);

			// Get file info
			var fileInfo = new FileInfo(databasePath);
			metadata.FileSize = fileInfo.Length;
			metadata.CreatedDate = fileInfo.CreationTime;
			metadata.ModifiedDate = fileInfo.LastWriteTime;

			// Get encoding
			using var encodingCommand = connection.CreateCommand();
			encodingCommand.CommandText = "PRAGMA encoding";
			metadata.Encoding = await encodingCommand.ExecuteScalarAsync() as string ?? "Unknown";

			// Get journal mode
			using var journalCommand = connection.CreateCommand();
			journalCommand.CommandText = "PRAGMA journal_mode";
			metadata.JournalMode = await journalCommand.ExecuteScalarAsync() as string ?? "Unknown";

			return metadata;
		}

		private Task<string> GenerateReadableSchemaAsync(DatabaseSchema schema)
		{
			var output = new StringBuilder();

			output.AppendLine("=== SQLite Database Schema ===");
			output.AppendLine($"Database: {schema.DatabaseName}");
			output.AppendLine($"Path: {schema.DatabasePath}");
			output.AppendLine($"Tables: {schema.TableCount}");
			output.AppendLine();

			foreach (var table in schema.Tables)
			{
				output.AppendLine($"TABLE: {table.Name}");
				output.AppendLine(new string('-', 50));

				foreach (var column in table.Columns)
				{
					var columnInfo = new StringBuilder();
					columnInfo.Append($"  {column.Name,-25} {column.DataType,-15}");

					if (column.IsPrimaryKey) columnInfo.Append(" PRIMARY KEY");
					if (column.NotNull && !column.IsPrimaryKey) columnInfo.Append(" NOT NULL");
					if (!string.IsNullOrEmpty(column.DefaultValue)) columnInfo.Append($" DEFAULT {column.DefaultValue}");

					output.AppendLine(columnInfo.ToString());
				}

				if (table.Indexes?.Count > 0)
				{
					output.AppendLine();
					output.AppendLine("  INDEXES:");
					foreach (var index in table.Indexes)
					{
						var indexInfo = new StringBuilder();
						indexInfo.Append($"    {index.Name}");
						if (index.IsUnique) indexInfo.Append(" (UNIQUE)");
						indexInfo.Append($" ON ({string.Join(", ", index.Columns)})");
						output.AppendLine(indexInfo.ToString());
					}
				}

				if (table.ForeignKeys?.Count > 0)
				{
					output.AppendLine();
					output.AppendLine("  FOREIGN KEYS:");
					foreach (var fk in table.ForeignKeys)
					{
						var fkInfo = $"    {fk.FromColumn} -> {fk.ReferencedTable}.{fk.ToColumn}";
						if (fk.OnUpdate != "NO ACTION") fkInfo += $" ON UPDATE {fk.OnUpdate}";
						if (fk.OnDelete != "NO ACTION") fkInfo += $" ON DELETE {fk.OnDelete}";
						output.AppendLine(fkInfo);
					}
				}

				if (table.RowCount.HasValue)
				{
					output.AppendLine();
					output.AppendLine($"  ROW COUNT: {table.RowCount.Value:N0}");
				}

				output.AppendLine();
			}

			if (schema.Metadata != null)
			{
				output.AppendLine("=== Database Information ===");
				output.AppendLine($"SQLite Version: {schema.Metadata.SqliteVersion}");
				output.AppendLine($"Database Size: {schema.Metadata.FormattedSize}");
				output.AppendLine($"Encoding: {schema.Metadata.Encoding}");
				output.AppendLine($"Journal Mode: {schema.Metadata.JournalMode}");
				output.AppendLine($"Created: {schema.Metadata.CreatedDate}");
				output.AppendLine($"Modified: {schema.Metadata.ModifiedDate}");
			}

			return Task.FromResult(output.ToString());
		}

		private static string FormatBytes(long bytes)
		{
			string[] sizes = { "B", "KB", "MB", "GB", "TB" };
			double len = bytes;
			int order = 0;

			while (len >= 1024 && order < sizes.Length - 1)
			{
				order++;
				len = len / 1024;
			}

			return $"{len:0.##} {sizes[order]}";
		}
	}

	// Supporting data structures
	public class DatabaseSchema
	{
		public string DatabasePath { get; set; } = string.Empty;
		public string DatabaseName { get; set; } = string.Empty;
		public int TableCount { get; set; }
		public List<TableSchema> Tables { get; set; } = new();
		public DatabaseMetadata Metadata { get; set; } = new();
	}

	public class TableSchema
	{
		public string Name { get; set; } = string.Empty;
		public List<ColumnSchema> Columns { get; set; } = new();
		public List<IndexSchema> Indexes { get; set; } = new();
		public List<ForeignKeySchema> ForeignKeys { get; set; } = new();
		public long? RowCount { get; set; }
		public List<Dictionary<string, object>> SampleData { get; set; } = new();
	}

	public class ColumnSchema
	{
		public int Position { get; set; }
		public string Name { get; set; } = string.Empty;
		public string DataType { get; set; } = string.Empty;
		public bool NotNull { get; set; }
		public string? DefaultValue { get; set; }
		public bool IsPrimaryKey { get; set; }
	}

	public class IndexSchema
	{
		public int Sequence { get; set; }
		public string Name { get; set; } = string.Empty;
		public bool IsUnique { get; set; }
		public string Origin { get; set; } = string.Empty;
		public bool IsPartial { get; set; }
		public List<string> Columns { get; set; } = new();
	}

	public class ForeignKeySchema
	{
		public int Id { get; set; }
		public int Sequence { get; set; }
		public string ReferencedTable { get; set; } = string.Empty;
		public string FromColumn { get; set; } = string.Empty;
		public string ToColumn { get; set; } = string.Empty;
		public string OnUpdate { get; set; } = string.Empty;
		public string OnDelete { get; set; } = string.Empty;
		public string Match { get; set; } = string.Empty;
	}

	public class DatabaseMetadata
	{
		public string SqliteVersion { get; set; } = string.Empty;
		public long PageCount { get; set; }
		public long PageSize { get; set; }
		public long DatabaseSize { get; set; }
		public string FormattedSize { get; set; } = string.Empty;
		public long FileSize { get; set; }
		public DateTime CreatedDate { get; set; }
		public DateTime ModifiedDate { get; set; }
		public string Encoding { get; set; } = string.Empty;
		public string JournalMode { get; set; } = string.Empty;
	}
}