using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
namespace edu.jhu.Cas.Util {
    /* for running a series of queries */
    public class DRStatement : QueryObject {
        private bool sResults;
        private string ip;
        private bool DoQuick;
        public DRStatement(Job job_, Server server, Server virtualHost, bool storeResults, string _ip) {
            ip = _ip;
            job = job_;
            cString = server.ConnectionString;
            TimeOut = virtualHost.Queue * 60 * 1000;
            VirtualHost = virtualHost;
            queryThread = new Thread(new ThreadStart(this.ThreadRun));
            sResults = storeResults;
            mainThread = Thread.CurrentThread;
            DoQuick = IsQuick();
            if (sResults) dataSet = new DataSet();
        }
        public override void Run() {
            base.Run();
            queryThread.Start();
            /*
             * A query thread does not complete until it has finished cleaning up all of its connections, commands, readers, etc.
             * So a query mail have failed much earlier than its thread terminates.
             * check for this.
             */
            while (Error == null && !IsComplete)
                queryThread.Join(1500);
        }
        private void addReader2DataSet(SqlCommand command, DataSet dataSet, ReaderRapper reader) {
            do {
                if (DoQuick) {
                    DataTable dt = SqlFactory.RunSQL(reader, ID.QUERY_MEM_CAP);
                    dataSet.Tables.Add(dt);
                } else {
                    DataTable schemaTable = reader.GetSchemaTable();
                    DataTable dataTable = new DataTable();
                    if (schemaTable != null) {
                        for (int i = 0; i < schemaTable.Rows.Count; i++) {
                            DataRow dataRow = schemaTable.Rows[i];
                            string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
                            DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                            dataTable.Columns.Add(column);
                        }
                        dataSet.Tables.Add(dataTable);
                        for (; ; ) {
                            if (!reader.Read()) break;
                            DataRow dataRow = dataTable.NewRow();
                            for (int i = 0; i < reader.FieldCount; i++)
                                dataRow[i] = reader.GetValue(i);
                            dataTable.Rows.Add(dataRow);
                        }
                    }
                }
            } while (reader.NextResult());
        }
        protected ReaderRapper srcReader;
        //returns true if the datatype takes a size parameter
        static bool dataTypeNeedsSize(string dataTypeName) {
            switch (dataTypeName) {
                case "binary":
                case "varbinary":
                case "varchar":
                case "nvarchar":
                case "char":
                case "nchar":
                case "float":
                case "double":
                case "real":
                case "img":
                case "decimal": return true;
                default: return false;
            }
        }
        static bool dataTypeNeedsBits(string dataTypeName) {
            switch (dataTypeName) {
                case "varchar":
                case "nvarchar":
                case "char":
                case "nchar":
                case "binary":
                case "varbinary":
                case "img":
                    return false;
                default: return true;
            }
        }
        const string DBMAX = "MAX";
        static string size2DBSize(string dataTypeName, int val) {
            switch (dataTypeName) {
                case "nvarchar":
                case "varchar":
                case "char":
                case "nchar":
                case "binary":
                case "varbinary":
                case "img":
                    return val <= 8000 ? val.ToString() : DBMAX;
                default: return val.ToString();
            }
        }

        private void CreateMydbTable(string tableName, ReaderRapper srcReader, SqlCommand mydbCom) {
            string qry = "CREATE TABLE " + tableName + " ( ";
            DataTable schema = srcReader.GetSchemaTable();
            int count = schema.Columns.Count;
            string colname = null;
            for (int x = 0; x < srcReader.FieldCount; x++) {
                colname = srcReader.GetName(x);
                if (colname == null || colname.Length == 0)
                    throw new CasJobsException("Cannot determine name of column number " + x + " in the select clause of your query.  Please give this column an explicit name and try again.");
                qry += "[" + colname + "] ";
                qry += Swizle(srcReader.GetDataTypeName(x));
                string datatype = srcReader.GetDataTypeName(x).ToLower();
                if (dataTypeNeedsSize(datatype)) {
                    int csizeInt = int.Parse(schema.Rows[x]["ColumnSize"].ToString());
                    //reals are float(32).  They are actually float(24) but this gives precision problems.
                    if (srcReader.GetDataTypeName(x).ToLower().StartsWith("real"))
                        qry += "(32)";
                    else if(dataTypeNeedsBits(datatype))//some types want size in bits
                        qry += "(" + csizeInt*4 + ")";
                    else//others want it in bytes.  these types usually have a cap on the value, usually 8000 or MAX
                        qry += "(" + size2DBSize(datatype,csizeInt) + ")";
                }
                if (x < (srcReader.FieldCount - 1)) qry += ",";
            }
            qry += ")";
            //throw new Exception(qry);
            mydbCom.CommandText = qry;
            mydbCom.ExecuteNonQuery();
        }
        const int chunkSize = 1000;
        long rowCnt;
        public void bumpRowCnt(object sender, SqlRowsCopiedEventArgs e) {
            rowCnt = e.RowsCopied;
        }
        public long LoadDataIntoTable(string tableName, ReaderRapper srcReader, SqlCommand toCom) {
            SqlBulkCopy sbc = new SqlBulkCopy(toCom.Connection);
            try {
                sbc.BulkCopyTimeout = 0;
                sbc.BatchSize = chunkSize;
                SqlRowsCopiedEventHandler srceh = new SqlRowsCopiedEventHandler(bumpRowCnt);               
                sbc.SqlRowsCopied += srceh;
                sbc.DestinationTableName = tableName;
                /* firing after every row seems like a waste, but not aware of another way to get accurate rowcnt. */
                sbc.NotifyAfter = 1;
                sbc.WriteToServer(srcReader);
            } finally {
                sbc.Close();
            }
            return rowCnt;
        }
        public void MaybeDoWebLogStart(SqlCommand com) {
            if (FilterPower.DoWebLog) {
                com.CommandText = FilterPower.GetLogStart(job.WebServicesID, job.ModifiedQuery, ip);
                com.ExecuteNonQuery();
            }
        }
        public void MaybeDoWebLogEnd(SqlCommand com) {
            if (com == null) return;
            if (FilterPower.DoWebLog) {
                com.CommandText = FilterPower.GetCloser(job.WebServicesID, Error != null ? Error.ToString() : null);
                com.ExecuteNonQuery();
            }
        }
        /*
         * a dual connection query (requires select into)
         * command should already have CommandText set.
         */
        public void DoSelectInto(SqlCommand command, SqlCommand mydbCommand, ReaderRapper srcReader,
            string selectIntoTable) {
            CreateMydbTable(selectIntoTable, srcReader, mydbCommand);
            Rows += LoadDataIntoTable(selectIntoTable, srcReader, mydbCommand);
        }
        void FailQuery(Exception e, SqlCommand command) {
            if (Error != null) return;//been here before
            Error = e;//don't override an earlier error
            if (base.TimeoutTimer.Enabled)//stop the timer now, not after internal objects have finished cleaning up.
                base.TimeoutTimer.Stop();
            IsComplete = true;
            //base.Cancel(null, null);
            MaybeDoWebLogEnd(command);
        }
        /*
         * stores results of base query in dataSet, where appropriate.
         * if results should be saved, but there are now results, returns rows affected
         */
        void MaybeSaveResults(SqlCommand com, ReaderRapper srcReader, string intoTable) {
            if (!sResults) return;//this job is not supposed to return anything (it must be long)
            DataTable dt = srcReader.GetSchemaTable();
            if (dt == null || intoTable != null) {//no data for this query, do rows affected instead.
                srcReader.Close();
                com.CommandText = "select @@ROWCOUNT as [Rows Affected]";
                using (ReaderRapper srcReader2 = new ReaderRapper(com)) {//
                    addReader2DataSet(com, dataSet, srcReader2);
                    try {
                        Rows = long.Parse(dataSet.Tables[dataSet.Tables.Count - 1].Rows[0][0].ToString());
                    } catch {}
                }
            } else {
                addReader2DataSet(com, dataSet, srcReader);//add the original data
                com.CommandText = "select @@ROWCOUNT";
                try {
                    Rows = (int)com.ExecuteScalar();
                } catch { }
            }
        }
        /*
         * One big nasty query execution stack.
         * Exceptions are always handled within a 'using' block, in order for avoid stalling jobs during cleanup.
         */
        public virtual void ThreadRun() {
            try {
                using (SqlConnection sqc = new SqlConnection(VirtualHost.ConnectionString))//to outside db
                    try {
                        sqc.Open();
                        //command to outside db.
                        using (SqlCommand command = new SqlCommand("", sqc))
                            try {
                                string[] queries = job.ModifiedQuery.Split(FilterPower.QUERY_SEPERATOR);
                                int cmdTimeout = (TimeOut / 1000) + (5 * 60);//convert to seconds, then add a few minutes to give cancel some time
                                command.CommandTimeout = cmdTimeout;
                                MaybeDoWebLogStart(command);
                                for (int i = 0; i < queries.Length; i++) {
                                    string q = queries[i];
                                    command.CommandText = q;
                                    string selectIntoTable = FilterPower.GetSelectIntoTable(q);
                                    using (ReaderRapper srcReader = new ReaderRapper(command)) { //execute the base query
                                        try {
                                            if (selectIntoTable != null && srcReader.FieldCount > 0)//then will need a connection to mydb as well
                                                using (SqlConnection mydbSqlCon = new SqlConnection(cString))//con to inner db
                                                    try {
                                                        mydbSqlCon.Open();
                                                        using (SqlCommand mydbCommand = new SqlCommand("", mydbSqlCon))//command to inner db
                                                            try {
                                                                DoSelectInto(command, mydbCommand, srcReader, selectIntoTable);
                                                            } catch (Exception ex) {
                                                                //failures from mydbCommand; usually related to loading process, like mydb full, etc.
                                                                FailQuery(ex, command); return;
                                                            }
                                                    } catch (Exception ex) {//failures from mydb sqlconnection
                                                        FailQuery(ex, command); return;
                                                    }
                                            MaybeSaveResults(command, srcReader, selectIntoTable);//store the results in a dataset if necessary
                                        } catch (Exception ex) {//failures from srcReader (which reads data from the remote catalog) 
                                            FailQuery(ex, command); return;
                                        }
                                    }
                                }
                                //wow, actually made it all the way.  done now, but no need to mark query as completed (this is done elsewhere)
                                //just weblog it if required.
                                MaybeDoWebLogEnd(command);
                            } catch (Exception ex) {//failures from command.  These are generally early query execution problems, ie bad object name.
                                FailQuery(ex, command); return;
                            }
                    } catch (Exception ex) {//sqc failers. Initial connection to db failed, etc.
                        FailQuery(ex, null); return;
                    }
            } catch (Exception ex) {//one extra catch block.  don't see it likely to ever get here, but never know
                FailQuery(ex, null); return;
            }
            IsComplete = true;
            if (base.TimeoutTimer.Enabled)//made it through all the try blocks.  if the query didn't fail, it is done, so stop the timer.
                base.TimeoutTimer.Stop();
        }
    }
}



