using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Text;
using System.Web;
using System.Net;

using ms.aq.cview.util;

namespace ms.aq.cview.query {
    /**
     * beware the monkey.
     * top_level query generation for queries destined for a snowflake schema
     */
    public class table_query_monkey {
        private Hashtable allhierarchies = new Hashtable(), allDimensions = new Hashtable();
        private table_cube_interface sci;
        private const string TOP = "TOP 500000";

        public string BuildFinalQuery(ref SelectionSets ss, string intoTable) {
            return null;
         //   return "drop table #tmp";
        }
        public table_query_monkey(table_cube_interface sci_) {
            sci = sci_;
            load_hierarchies();
        }
        private ArrayList GetSeries(ref SelectionSets ss) {
            ArrayList tmp = ss.GetCollectionsByPrefix(PrefixDecoder.P1_SERIES_PREFIX);
            ArrayList series = new ArrayList();
            foreach (object o in tmp) {
                StringCollection sc = (StringCollection)o;
                if (sc.Count != 1) continue;
                series.Add(sc[0]);
            }
            series.Sort();
            return series;
        }
        public string BuildDataQuery(ref SelectionSets ss, int pixWidth, int pixHeight,
            DBNumber xmin, DBNumber xmax, DBNumber xdif, DBNumber ymin, DBNumber ymax, DBNumber ydif, int block_size) {
            StringCollection range = ss.GetCollection(PrefixDecoder.P1_RANGE_PREFIX);
            if (range.Count == 0) throw new Exception("Cannot plot, no range selected.");
            ICollection ic = ss.GetPrefixes(PrefixDecoder.P1_SERIES_PREFIX);
            if (ic.Count == 0) throw new Exception("Cannot plot, no series added");
            StringBuilder sb = new StringBuilder();
            int cnt = 0;
            foreach (object o in ic) {
                string thisKey = (string)o;
                if (ss.GetCollection(thisKey) == null || ss.GetCollection(thisKey).Count == 0) continue;

                sb.AppendLine("SELECT ");
                string rangeAlias = Settings.XDIM_PREF + common.debracket(range[0]);
                sb.AppendLine("\t" +rangeAlias+",");
                
                
                string thisCol = ss.GetCollection(thisKey)[0];
                string thisColNB = common.debracket(thisCol);
                int thisSeriesNum = PrefixDecoder.SeriesNumFromPrefix(thisKey);
                string thisAlias = Settings.YDIM_PREF + thisColNB + "_" + thisSeriesNum;

                string thatKey, thatCol, thatColNB, thatAlias;
                int thatSeriesNum;
                foreach (object oo in ic) {
                    thatKey = (string)oo;
                    if (ss.GetCollection(thatKey) == null || ss.GetCollection(thatKey).Count == 0) continue;
                    thatCol = ss.GetCollection(thatKey)[0];
                    thatColNB = common.debracket(thatCol);
                    thatSeriesNum = PrefixDecoder.SeriesNumFromPrefix(thatKey);
                    thatAlias = Settings.YDIM_PREF+thatColNB+"_"+thatSeriesNum;
                    sb.Append("\t");
                    if (thisSeriesNum == thatSeriesNum)
                        sb.Append(thatAlias);
                    else
                        sb.Append("null as "+thatAlias);
                    sb.AppendLine(",");
                }
                sb.AppendLine("\tcount(*) as HITS FROM (");
                sb.AppendLine("SELECT");
                sb.AppendLine("\tCAST(" + floorIt(range[0], xmin, xdif, pixWidth, block_size) + " AS int) as " +rangeAlias +",");
                //sb.AppendLine("\tCAST("+ floorIt(thisColNB, ymin,ydif,pixWidth,block_size) + " AS int) as "+thisAlias);
                sb.AppendLine("\tCAST(((" + pixHeight + "/" + block_size + ")" + " - " +
                    floorIt(thisColNB, ymin, ydif, pixHeight, block_size) + ") AS int) as " + thisAlias);
                sb.AppendLine("FROM [" + sci.TableName + "]) a");
                sb.Append("GROUP BY ");
                sb.AppendLine("a." + rangeAlias + ", a." + thisAlias);
                sb.AppendLine("UNION");
            }
            sb.Remove(sb.Length - 7, 6);
            return sb.ToString();
        }
        private string floorIt(string columnName, DBNumber cmin, DBNumber cdif, int pix_span, int block_size) {
            return "floor(("+(pix_span/block_size)+"*("+columnName+" - "+cmin+"))/"+cdif+")";
        }
        private string aliasFromSelection(string selection) {
            string[] tmp1 = selection.Split(' ');
            return tmp1[tmp1.Length - 1];
        }
        public string BuildMinMax(ref SelectionSets ss) {
            StringBuilder rst = new StringBuilder();
            rst.AppendLine("SELECT");
            foreach (string s in ss.GetAllTokens()){
                rst.AppendLine("\tmax(" + s + ") as " + common.debracket(s) + "_MAX,");
                rst.AppendLine("\tmin(" + s+ ") as " + common.debracket(s) + "_MIN,");
            }
            rst.AppendLine("\tcount(*) as CNT");
            rst.AppendLine("FROM ["+sci.TableName+"]");
            return rst.ToString();
        }
        private void DumpSelect(StringBuilder sb, StringCollection sels,bool capIt) {
            sb.AppendLine("\nSELECT ");
            if (capIt) sb.AppendLine(TOP);
            else sb.AppendLine();
            for (int i = 0; i < sels.Count; i++) {
                sb.Append("\t" + sels[i]);
                if (i < (sels.Count - 1)) sb.Append(",");
                sb.AppendLine();
            }
        }
        private void DumpFrom(StringBuilder sb, ArrayList distinctTables) {
            sb.AppendLine("FROM");
            for (int i = 0; i < distinctTables.Count; i++) {
                FromTable ft = (FromTable)distinctTables[i];
                sb.Append("\t" + ft.Table + " " + ft.Alias);
                if (i < (distinctTables.Count - 1))
                    sb.Append(",");
                sb.AppendLine();
            }
        }
        private void DumpWhere(StringBuilder sb, ArrayList joins, StringCollection conditions) {
            if (joins.Count == 0 && conditions.Count == 0) return;
            sb.AppendLine("WHERE");
            for (int i = 0; i < joins.Count; i++) {
                sb.Append("\t(" + joins[i] + ")");
                if (i < (joins.Count - 1))
                    sb.Append(" AND");
                sb.AppendLine();
            }
            if (joins.Count > 0 && conditions.Count > 0)
                sb.AppendLine("\tAND");
            for (int i = 0; i < conditions.Count; i++) {
                sb.Append("\t(" + conditions[i] + ")");
                if (i < (conditions.Count - 1))
                    sb.Append("\n\tAND");
                sb.AppendLine();
            }
        }
        private void load_hierarchies() {
            foreach (VDimension vd in sci.GetDimensions()) {
                allDimensions.Add(vd.Name, vd);
            }
        }
    }
}
