using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DbTools.Model {
    internal class Database {
        public string ConnectionString { get; set; }
        public string SqlScript { get; set; }
        public TableCollection Tables { get; set; } = new TableCollection();
        public IDbConnection DbConnection { get; private set; }

        public Database() { }

        public Database(string connectionString) {
            ConnectionString = connectionString;
        }

        public Database(IDbConnection dbConnection, bool importImmediately = false) {
            DbConnection = dbConnection;
            ConnectionString = dbConnection.ConnectionString;

            // Give the user the option to import later to avoid unnecessary work
            if (importImmediately) {
                importFromSqlite();
            }
        }

        /// <summary>
        /// Loads the specified SQL script and initializes an in-memory SQLite database using the script.
        /// </summary>
        /// <remarks>This method creates a temporary SQLite database file, sets up a connection string,
        /// and executes the provided SQL script to initialize the database. The connection string for the database is
        /// stored in the <see cref="ConnectionString"/> property.</remarks>
        /// <param name="sql">The SQL script to be executed for creating and populating the database.</param>
        /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
        public void LoadSql(string sql) {
            if (!sql.Contains("-- Generated with DbTools")) {
                throw new ArgumentException("The provided SQL script does not appear to be generated by DbTools.");
            }

            ParseTablesFromSql(sql);
            SqlScript = ToSql();
        }

        /// <summary>
        /// Determines whether a table with the specified name exists in the collection.
        /// </summary>
        /// <param name="tableName">The name of the table to search for. The comparison is case-insensitive.</param>
        /// <returns><see langword="true"/> if a table with the specified name exists; otherwise, <see langword="false"/>.</returns>
        public bool ContainsTable(string tableName) {
            return Tables.Contains(tableName);
        }

        /// <summary>
        /// Gets the <see cref="SqlTable"/> with the specified table name, or <c>null</c> if no matching table is found.
        /// </summary>
        /// <param name="tableName">The name of the table to retrieve. The comparison is case-insensitive.</param>
        /// <returns></returns>
        public Table this[string tableName] {
            get {
                return Tables[tableName];
            }
        }

        /// <summary>
        /// Parses the provided SQL script to extract table definitions, along with their associated indexes and
        /// triggers.
        /// </summary>
        /// <remarks>This method processes the SQL script line by line to identify and parse table, index,
        /// and trigger definitions. It supports SQL scripts that include "CREATE TABLE", "CREATE INDEX", and "CREATE
        /// TRIGGER" statements. The method yields each parsed table as it is processed, allowing for efficient
        /// streaming of results. NOTE: This method requires the SQL script to be in the expected format, which can
        /// be generated using this project.</remarks>
        /// <param name="sql">The SQL script containing table, index, and trigger definitions. The script must be in a valid SQL format.</param>
        /// <returns>An enumerable collection of <see cref="Table"/> objects, each representing a table parsed from the SQL
        /// script. The collection includes the table's structure, indexes, and triggers as defined in the script.</returns>
        public IEnumerable<Table> ParseTablesFromSql(string sql) {
            Table table = null;
            StringBuilder sb = new StringBuilder();

            Dictionary<string, List<string>> indexes = new Dictionary<string, List<string>>();
            Dictionary<string, List<string>> triggers = new Dictionary<string, List<string>>();

            bool inTable = false;
            foreach (string line in Regex.Split(sql, "\\r\\n")) {
                if (string.IsNullOrEmpty(line) || line.StartsWith("--")) {
                    continue;
                }

                string trimmedLine = Regex.Replace(line.Trim(), @"\s+", " ");

                if (trimmedLine.ToUpper().StartsWith("CREATE TABLE ")) {
                    // Start a new table
                    var match = Regex.Match(trimmedLine, "CREATE TABLE( IF NOT EXISTS)? (\\w*) .*\\(");
                    if (match.Success) {
                        string tableName = match.Groups[2].Value.Trim();
                        table = new Table() {
                            TableName = tableName
                        };
                        sb = new StringBuilder();
                        sb.AppendLine(trimmedLine);
                        inTable = true;
                    }
                    continue;
                }

                // We assume indexes are always single line
                if (trimmedLine.ToUpper().StartsWith("CREATE INDEX ")) {
                    var matches = Regex.Match(trimmedLine, "CREATE INDEX( IF NOT EXISTS)? (\\w*) ON (\\w*).*\\);");
                    if (matches.Success) {
                        string tableName = matches.Groups[3].Value.Trim();
                        string indexName = matches.Groups[2].Value.Trim();

                        if (indexes.ContainsKey(tableName)) {
                            indexes[tableName].Add(indexName + ";" + trimmedLine);
                        } else {
                            indexes[tableName] = new List<string> { indexName + ";" + trimmedLine };
                        }
                        continue;
                    }
                }

                // We assume triggers are always single line
                if (trimmedLine.ToUpper().StartsWith("CREATE TRIGGER ")) {
                    var matches = Regex.Match(trimmedLine, "CREATE TRIGGER( IF NOT EXISTS)? (\\w*) .* ON (\\w*).*END;");
                    if (matches.Success) {
                        string tableName = matches.Groups[3].Value.Trim();
                        string triggerName = matches.Groups[2].Value.Trim();

                        if (triggers.ContainsKey(tableName)) {
                            triggers[tableName].Add(triggerName + ";" + trimmedLine);
                        } else {
                            triggers[tableName] = new List<string> { triggerName + ";" + trimmedLine };
                        }
                    }
                    continue;
                }

                // Inside a table definition, accumulate lines
                if (!inTable) {
                    if (trimmedLine == ");") {
                        // End of table definition
                        sb.AppendLine(trimmedLine);
                        table.ParseSql(sb.ToString());
                        Tables.Add(table);
                        inTable = false;
                        yield return table;
                    } else {
                        sb.AppendLine("\t" + trimmedLine);
                    }
                }
            }

            // Append indexes and triggers to their respective tables
            appendIndexes(indexes);
            appendTriggers(triggers);
        }


        /// <summary>
        /// Builds and returns an SQL statement based on the currently loaded database connection.
        /// </summary>
        /// <param name="includeIfNotExist">A boolean value indicating whether the generated SQL statement should include a conditional check  to ensure
        /// the existence of the target object before performing the operation.  <see langword="true"/> to include the
        /// conditional check; otherwise, <see langword="false"/>.</param>
        /// <returns>A string containing the generated SQL statement.</returns>
        public string BuildSql(bool includeIfNotExist = false) {
            importFromSqlite();
            return ToSql(includeIfNotExist);
        }

        /// <summary>
        /// Generates a SQL script for the current database schema.
        /// </summary>
        /// <remarks>The generated script includes metadata such as the generation timestamp and is
        /// appended to the existing <see cref="SqlScript"/> value. The method processes all tables in the schema and
        /// generates their corresponding SQL definitions.</remarks>
        /// <param name="includeIfNotExist">A value indicating whether to include conditional checks (e.g., "IF NOT EXISTS") in the generated SQL
        /// script.</param>
        /// <returns>A string containing the generated SQL script, including all tables in the current schema.</returns>
        public string ToSql(bool includeIfNotExist = false) {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("--");
            sb.AppendLine("-- Generated with DbTools on " + DateTime.Now.ToString("f"));
            sb.AppendLine("--");

            foreach (var table in Tables.GetTables()) {
                sb.AppendLine(table.FullSql());
            }

            SqlScript += sb.ToString();
            return SqlScript;
        }

        /// <summary>
        /// Imports the schema and metadata from the connected SQLite database into the application's internal
        /// structures.
        /// </summary>
        /// <remarks>This method reads the SQLite database schema, including tables, indexes, and
        /// triggers, and populates the  application's internal collections with the corresponding metadata. It
        /// processes the `sqlite_master` table  to extract the necessary information and organizes it into tables,
        /// indexes, and triggers. <para> The method clears any existing table metadata before importing new data. It
        /// also formats the SQL definitions  for tables to improve readability. </para> <para> This method assumes that
        /// the database connection is already open and accessible. Ensure that the connection  is valid before calling
        /// this method. </para></remarks>
        /// <returns><see langword="true"/> if the import operation completes successfully.</returns>
        private bool importFromSqlite() {
            DbConnection.Open();
            Tables.Clear();

            Dictionary<string, List<string>> indexes = new Dictionary<string, List<string>>();
            Dictionary<string, List<string>> triggers = new Dictionary<string, List<string>>();
            using (var cmd = DbConnection.CreateCommand()) {
                cmd.CommandText = "select * from sqlite_master";
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        if (reader["tbl_name"]?.ToString() == "sqlite_sequence" || string.IsNullOrEmpty(reader["sql"]?.ToString())) { continue; }
                        string recordType = reader["type"]?.ToString();

                        if (recordType == "table") {
                            string sql = reader["sql"]?.ToString();
                            string tableName = reader["tbl_name"]?.ToString();
                            Match m = Regex.Match(sql, "CREATE TABLE \\S+ \\((.*)\\)", RegexOptions.Singleline);
                            if (!m.Success) {
                                Trace.TraceWarning("Unable to match regex on table " + tableName);
                                continue;
                            }

                            string tableSql = "";
                            int startIndex = m.Groups[1].Index;
                            int length = m.Groups[1].Length;
                            string columns = Regex.Replace(m.Groups[1].Value, "\\s{2,}", " ");
                            columns = Regex.Replace(columns.Replace(", ", ",").Replace(",\n", ","), ",(?!\\d+\\))", ",\r\n\t");
                            tableSql += sql.Substring(0, startIndex) + "\r\n\t" +
                            columns.Trim() + "\r\n" +
                            sql.Substring(startIndex + length) + ";";

                            Table table = new Table() {
                                TableName = reader["tbl_name"]?.ToString(),
                                CreateTableSql = tableSql,
                            };
                            Tables.Add(table);
                        } else if (recordType == "index") {
                            string tableName = reader["tbl_name"]?.ToString();
                            string indexName = reader["name"]?.ToString();
                            string indexSql = reader["sql"]?.ToString();

                            if (indexes.ContainsKey(tableName)) {
                                indexes[tableName].Add(indexName + ";" + indexSql);
                            } else {
                                indexes[tableName] = new List<string> { indexName + ";" + indexSql };
                            }
                        } else if (recordType == "trigger") {
                            string tableName = reader["tbl_name"]?.ToString();
                            string triggerName = reader["name"]?.ToString();
                            string triggerSql = reader["sql"]?.ToString();

                            if (triggers.ContainsKey(tableName)) {
                                triggers[tableName].Add(triggerName + ";" + triggerSql);
                            } else {
                                triggers[tableName] = new List<string> { triggerName + ";" + triggerSql };
                            }
                        }
                    }

                }
            }

            appendIndexes(indexes);
            appendTriggers(triggers);

            return true;
        }

        /// <summary>
        /// Appends index definitions to the corresponding tables in the database schema.
        /// </summary>
        /// <remarks>This method updates the <c>Indexes</c> collection of each table in the <c>Tables</c>
        /// dictionary with the provided index definitions. If a table does not exist in the <c>Tables</c> dictionary,
        /// it is skipped. Only valid index definitions (those containing both a name and a SQL definition) are
        /// added.</remarks>
        /// <param name="indexes">A dictionary where the key is the name of a table, and the value is a list of index definitions. Each index
        /// definition is a semicolon-separated string, where the first part is the index name and the second part is
        /// the index SQL definition.</param>
        private void appendIndexes(Dictionary<string, List<string>> indexes) {
            foreach (string index in indexes.Keys) {
                Table table = Tables[index];
                if (table != null) {
                    foreach (string indexSql in indexes[index]) {
                        var parts = indexSql.Split(new char[] { ';' }, 2);
                        if (parts.Length == 2) {
                            table.Indexes[parts[0]] = parts[1];
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Appends trigger definitions to the corresponding tables in the database schema.
        /// </summary>
        /// <remarks>This method updates the <see cref="Table.Triggers"/> collection for each table
        /// specified in the  <paramref name="triggers"/> dictionary. If a table does not exist in the <see
        /// cref="Tables"/>  collection, it is skipped. Only trigger definitions with a valid format (name and SQL body
        /// separated  by a semicolon) are added.</remarks>
        /// <param name="triggers">A dictionary where each key represents the name of a table, and the associated value is a list of  trigger
        /// definitions for that table. Each trigger definition is a string containing the trigger name  and its SQL
        /// body, separated by a semicolon.</param>
        private void appendTriggers(Dictionary<string, List<string>> triggers) {
            foreach (string trigger in triggers.Keys) {
                Table table = Tables[trigger];
                if (table != null) {
                    foreach (string triggerSql in triggers[trigger]) {
                        var parts = triggerSql.Split(new char[] { ';' }, 2);
                        if (parts.Length == 2) {
                            table.Triggers[parts[0]] = parts[1];
                        }
                    }
                }
            }
        }
    }
}