using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;

namespace MultiUser.Web
{
    /// <summary>
    /// Summary description for SmartDataSet.
    /// </summary>
    public class SmartDataSet : DataSet
    {
        //---------------------------------------------------------------------
        #region ** fields

        OleDbConnection _conn = new OleDbConnection();
        Dictionary<DataTable, OleDbDataAdapter> _adapters = new Dictionary<DataTable, OleDbDataAdapter>();

        #endregion

        //---------------------------------------------------------------------
        #region ** ctor

        public SmartDataSet(IContainer container)
        {
            container.Add(this);
        }
        public SmartDataSet(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public SmartDataSet() { }

        #endregion

        //---------------------------------------------------------------------
        #region ** object model

        /// <summary>
        /// Gets or sets the connection string used by this <see cref="SmartDataSet"/>.
        /// </summary>
        [DefaultValue("")]
        public string ConnectionString
        {
            get { return _conn.ConnectionString; }
            set { _conn.ConnectionString = value; }
        }
        /// <summary>
        /// Gets a reference to the <see cref="OleDbConnection"/> used by this 
        /// <see cref="SmartDataSet"/>.
        /// </summary>
        public OleDbConnection Connection
        {
            get { return _conn; }
        }
        /// <summary>
        /// Relations read from schema
        /// </summary>
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        new public DataRelationCollection Relations
        {
            get { return base.Relations; }
        }
        /// <summary>
        /// Reset the <see cref="SmartDataSet"/>.
        /// </summary>
        public override void Reset()
        {
            _adapters.Clear();
            _conn.Close();
            base.Reset();
        }
        /// <summary>
        /// Fill all tables, relations, and constraints.
        /// </summary>
        public void Fill()
        {
            Fill(null);
        }
        /// <summary>
        /// Fill selected tables.
        /// </summary>
        /// <param name="tables">Array containing the names of the tables to load.</param>
        public void Fill(params string[] tables)
        {
            // clear dataset
            Reset();

            // no conn string? no work
            if (string.IsNullOrEmpty(ConnectionString))
            {
                return;
            }

            // always close the connection
            using (_conn)
            {
                // open connection
                _conn.Open();

                // get table list if not provided
                if (tables == null)
                {
                    tables = GetTableList();
                }

                // populate DataSet with all tables
                foreach (string table in tables)
                {
                    string sql = string.Format("select * from [{0}]", table.Trim());
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, _conn);
                    Tables.Add(table);
                    da.Fill(this, table);
                }

                // read relations
                ReadRelations();

                // read constraints (unique IDs, autonumber etc)
                ReadConstraints();
            }
        }
        /// <summary>
        /// Saves all changes back into the database.
        /// </summary>
        public void Update()
        {
            // no changes? no work
            if (!HasChanges())
            {
                return;
            }

            // always close the connection
            using (_conn)
            {
                // open connection
                _conn.Open();

                // save each table
                bool ok = false;
                int concurrencyErrors = 0;
                for (int errors = 0; !ok; errors++)
                {
                    // assume it's all gonna work
                    ok = true;

                    // update all tables
                    foreach (DataTable dt in Tables)
                    {
                        DataTable dtChanges = dt.GetChanges();
                        if (dtChanges != null)
                        {
                            // get data adapter
                            OleDbDataAdapter da = GetDataAdapter(dt);

                            // try updating
                            try
                            {
                                da.Update(dtChanges);
                            }
                            catch (DBConcurrencyException cx)
                            {
                                // review: this happens occasionally and I don't know why.
                                // but the db does seem to update correctly, so this must be bogus...
                                concurrencyErrors++;
                                Debug.WriteLine(string.Format("concurrency exception caught: table {0}:\r\n{1}", dtChanges.TableName, cx.Message));
                            }
                            catch (Exception x)
                            {
                                // if the error is "Operation must use an updateable query.", then
                                // the problem must be related to permissions, maybe the database file
                                // is in read-only mode?
                                if (x.Message.IndexOf("updateable query", StringComparison.OrdinalIgnoreCase) > -1)
                                {
                                    throw new InvalidProgramException("Update failed, check permissions on the database (read-only file?).");
                                }

                                // it failed, possibly because of constraint violations.
                                // so finish this batch, then try again a few times until all tables 
                                // update successfully (or until we get too many errors).
                                Debug.WriteLine(string.Format("exception caught: table {0}:\r\n{1}", dtChanges.TableName, x.Message));
                                ok = false;
                                if (errors > 2 * Tables.Count)
                                {
                                    throw x;
                                }
                            }
                            Debug.WriteLine(string.Format("errors? {0}", dt.HasErrors));
                        }
                    }
                }

                // accept the changes (avoids most concurrency errors)
                AcceptChanges();
            }
        }
        /// <summary>
        /// Delete a row, optionally deleting all its child rows.
        /// </summary>
        /// <param name="row">Row to delete.</param>
        /// <param name="cascading">Whether to delete child rows.</param>
        public void DeleteRow(DataRow row, bool cascading)
        {
            // delete children
            if (cascading)
            {
                foreach (DataRelation relation in Relations)
                {
                    if (relation.ParentTable == row.Table)
                    {
                        Debug.WriteLine(string.Format("Relation {0} -->> {1}", relation.ParentTable.TableName, relation.ChildTable.TableName));
                        DataRow[] children = row.GetChildRows(relation);
                        foreach (DataRow childRow in children)
                        {
                            DeleteRow(childRow, cascading);
                        }
                    }
                }
            }

            // delete row
            row.Delete();
        }
        #endregion

        //---------------------------------------------------------------------
        #region ** private

        private string[] GetTableList()
        {
            var tables = new List<string>();
            DataTable dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    // check the table type (no views/links)
                    string type = dr["TABLE_TYPE"].ToString().ToUpper();
                    if (type == "TABLE")
                    {
                        string table = dr["TABLE_NAME"].ToString();
                        tables.Add(table);
                    }
                }
                catch (Exception x)
                {
                    Debug.WriteLine(string.Format("Error reading table {0}: {1}", dr["TABLE_NAME"], x.Message));
                }
            }

            // done
            return tables.ToArray();
        }
        private void ReadConstraints()
        {
            // primary key constraints
            var dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
            var uniqueTables = new Dictionary<string, string>();
            foreach (DataRow dr in dt.Rows)
            {
                // get primary key info
                string tableName = dr["TABLE_NAME"].ToString();
                string columnName = dr["COLUMN_NAME"].ToString();

                // make sure this table is in our DataSet
                if (!Tables.Contains(tableName))
                {
                    continue;
                }

                // make sure it's unique
                if (uniqueTables.ContainsKey(tableName))
                {
                    uniqueTables.Remove(tableName);
                    continue;
                }

                // save and move on
                uniqueTables[tableName] = columnName;
            }

            // built unique list, now set up primary key columns
            foreach (string tableName in uniqueTables.Keys)
            {
                // set up column
                string columnName = (string)uniqueTables[tableName];
                DataTable table = Tables[tableName];
                DataColumn pk = table.Columns[columnName];
                pk.Unique = true;
                pk.AllowDBNull = false;

                // set auto increment
                if (pk.DataType != typeof(string) && pk.DataType != typeof(System.Guid))
                {
                    pk.AutoIncrement = true;
                    pk.ReadOnly = true;
                    if (table.Rows.Count > 0)
                    {
                        string expression = string.Format("max({0})", pk.ColumnName);
                        object max = table.Compute(expression, string.Empty);
                        long maxID = long.Parse(max.ToString());
                        //DataRow lastRow = table.Rows[table.Rows.Count-1];
                        //long maxID = long.Parse(lastRow[pk].ToString());
                        pk.AutoIncrementSeed = maxID + 1;
                    }
                }

                // set primary key on parent table
                Tables[tableName].PrimaryKey = new DataColumn[] { pk };
            }

            // set default values for columns that have them,
            // and also for the ones that don't have defaults but are not nullable
            dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
            foreach (DataRow dr in dt.Rows)
            {
                bool isNullable = (bool)dr["IS_NULLABLE"];
                bool hasDefault = (bool)dr["COLUMN_HASDEFAULT"];
                object defVal = hasDefault ? dr["COLUMN_DEFAULT"] : DBNull.Value;

                if (!isNullable || defVal != DBNull.Value)
                {
                    string tableName = dr["TABLE_NAME"].ToString();
                    DataTable table = Tables[tableName];
                    if (table != null)
                    {
                        string columnName = dr["COLUMN_NAME"].ToString();
                        DataColumn column = table.Columns[columnName];
                        if (column != null && !column.AutoIncrement && column.DataType != typeof(Guid))
                        {
                            if (defVal != DBNull.Value)
                            {
                                // there is a default value, so apply it
                                try
                                {
                                    column.DefaultValue = Convert.ChangeType(defVal, column.DataType);
                                }
                                catch
                                {
                                    Debug.WriteLine(string.Format("could not apply default value for [{0}].[{1}]", tableName, columnName));
                                }
                            }
                            else if (!isNullable)
                            {
                                // there's no default, but the column is not nullable, so use a reasonable value
                                if (column.DataType == typeof(bool))
                                {
                                    column.DefaultValue = false;
                                }
                                if (column.DataType == typeof(int))
                                {
                                    column.DefaultValue = 0;
                                }
                                else if (column.DataType == typeof(string))
                                {
                                    column.DefaultValue = string.Empty;
                                }
                            }
                        }
                    }
                }
            }
        }
        private void ReadRelations()
        {
            DataTable dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
            foreach (DataRow dr in dt.Rows)
            {
                // get primary/foreign table and column names
                string pkTableName = string.Format("{0}", dr["PK_TABLE_NAME"]);
                string fkTableName = string.Format("{0}", dr["FK_TABLE_NAME"]);
                string pkColumnName = string.Format("{0}", dr["PK_COLUMN_NAME"]);
                string fkColumnName = string.Format("{0}", dr["FK_COLUMN_NAME"]);

                // make sure both tables are in our DataSet
                if (!Tables.Contains(pkTableName) || !Tables.Contains(fkTableName))
                    continue;

                // at design time, make sure tables are different
                // this should avoid endless loops in the MS type editor
                if (DesignMode && pkTableName == fkTableName)
                    continue;

                // get unique relation name
                string relationName = pkTableName + '_' + fkTableName;
                if (Relations.Contains(relationName))
                    relationName += Relations.Count.ToString();

                // add to collection
                DataColumn pkColumn = Tables[pkTableName].Columns[pkColumnName];
                DataColumn fkColumn = Tables[fkTableName].Columns[fkColumnName];
                Relations.Add(relationName, pkColumn, fkColumn, true);
            }
        }
        private OleDbDataAdapter GetDataAdapter(DataTable dt)
        {
            OleDbDataAdapter da;
            if (!_adapters.TryGetValue(dt, out da))
            {
                // create adapter as usual
                string text = string.Format("select * from [{0}]", dt.TableName);
                da = new OleDbDataAdapter(text, _conn);

                // build commands automatically
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                //old version...
                //
                //This does work for InsertAndDeleteAndUpdate when the database is Access.
                //This does not work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express.
                //
                //cb.QuotePrefix = cb.QuoteSuffix = "`"; // << important!!!

                //new version...
                //
                //This does work for InsertAndDeleteAndUpdate when the database is Access.
                //This does work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express.
                //
                cb.QuotePrefix = "[";
                cb.QuoteSuffix = "]";

                // fix automatic commands (ugh!)
                da = new OleDbDataAdapter(text, _conn);
                if (dt.PrimaryKey.Length == 1)
                {
                    DataColumn pk = dt.PrimaryKey[0];
                    da.UpdateCommand = FixCommand(cb.GetUpdateCommand(), pk);
                    da.InsertCommand = FixCommand(cb.GetInsertCommand(), pk);
                    da.DeleteCommand = FixCommand(cb.GetDeleteCommand(), pk);
                }
                else
                {
                    string err = string.Format("Update requires a single primary key, table '{0}' has {1} keys.", dt.TableName, dt.PrimaryKey.Length);
                    throw new InvalidOperationException(err);
                }

                // save it for next update
                _adapters[dt] = da;
            }

            // return fixed adapter
            return da;
        }
        private OleDbCommand FixCommand(OleDbCommand cmd, DataColumn pk)
        {
            // trim everything after the id in the WHERE clause
            // to avoid stupid concurrency violation

            //old... this does work with Access but not with SqlServer...
            //
            //string whereID = string.Format("WHERE ((`{0}` = ?)", pk.ColumnName);

            //new when using sql server, use square brackets...
            //
            string whereID = string.Format("WHERE (([{0}] = ?)", pk.ColumnName);

            int pos = cmd.CommandText.IndexOf(whereID, StringComparison.OrdinalIgnoreCase);
            if (pos > -1)
            {
                whereID = whereID.Replace("((", "(");
                string extraString = cmd.CommandText.Substring(pos + whereID.Length + 1);
                cmd.CommandText = cmd.CommandText.Substring(0, pos) + whereID;
                //new... remove extra parameters as well
                //
                int myCountOfQuestionMarks = this.CountStringOccurrences(extraString, "?");
                while (myCountOfQuestionMarks > 0)
                {
                    cmd.Parameters.RemoveAt(cmd.Parameters.Count - 1);
                    myCountOfQuestionMarks--;
                }
            }

            // copied from VS-generated commands (review: is this needed??)
            cmd.UpdatedRowSource = UpdateRowSource.Both;

            foreach (OleDbParameter p in cmd.Parameters)
            {
                p.Precision = 0;
            }

            // return fixed command
            return cmd;
        }

        private int CountStringOccurrences(string text, string pattern)
        {
            // Loop through all instances of the string 'text'.
            int count = 0;
            int i = 0;

            while ((i = text.IndexOf(pattern, i)) != -1)
            {
                i += pattern.Length;
                count++;
            }

            return count;
        }

        #endregion
    }
}
