using System;
using System.Collections;
using System.Data;
using System.Threading;
using System.IO;
using System.Data.SqlClient;
using edu.jhu.ZTestS;
using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Servers;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;

namespace edu.jhu.Cas.WebTest{
	//throw exception if invalid, nothing if not
	public delegate void DSVerify(DataSet ds);
	public class QueryTest:ZTest{
		protected long wsid;
		protected string context,qry;
		private DSVerify dsverify;
		
		public QueryTest(ZTestLoader tl, long wsid_, string qry_, string context_, string title, string desc, DSVerify dsverify_,
            ZCleanUp cleanUp):base(tl,cleanUp){
			base.Title = title;
			base.Description = desc;
			qry = qry_;
			wsid = wsid_;
			context = context_;
			dsverify = dsverify_;
		}
		public override void Work(){
			CasJobs cjobs = new CasJobs();
            dsverify(cjobs.SubmitShortJob(wsid, qry, context, base.Title, 0));            
		}	
	}
    public abstract class QueryTests {
        public static QueryTest GetSimpCompound(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 0
select 1";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "simple compound",
                "A simple test of two selects in a single statement.\n"+q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[0].Rows[0][0] != 0 && (int)ds.Tables[1].Rows[0][0] != 1) throw new Exception("incorrect values");
                },null);
        }
        public static QueryTest GetDepCompound(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 7 as fake into #fake
go
select fake from #fake";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "dependent compound",
                "Compund query where the results of the 2nd are dependent on the first.\n" + q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[1].Rows[0][0] != 7) throw new Exception("incorrect values");
                },
                delegate() {
                   (new CasJobs()).SubmitShortJob(wsid, "drop table #fake", "mydb", "cleanup", 0);
                });
        }
        public static QueryTest GetIntoMyDB(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 0 as ra into mydb.fake";
            return new QueryTest(ztestLoader, wsid,q, def_context,
                "into mydb",
                "Simple select into mydb query\n"+q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[0].Rows[0][0] != 1) throw new ZTestException("Incorrect values");//checking rowcount here, not value
                },
                delegate() {
                    (new CasJobs()).SubmitShortJob(wsid, "drop table fake", "mydb", "cleanup", 0);
                }
                );
        }
        public static QueryTest GetIntoBMyDB(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 0 as ra into mydb.[f a k e]";
            return new QueryTest(ztestLoader, wsid,q, def_context,
                "into [mydb]",
                "Simple select into mydb query, into table in brackets\n"+q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[0].Rows[0][0] != 1) throw new ZTestException("Incorrect values");
                },
                delegate() {
                    (new CasJobs()).SubmitShortJob(wsid, "drop table [f a k e]", "mydb", "cleanup", 0);
                }
                );
        }
        public static QueryTest GetIntoTmp(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 3 as ra, 4 as dec into #tmp 
go
select ra,dec from #tmp";
            return new QueryTest(ztestLoader, wsid,q, def_context,
                "into #tmp",
                "Simple select into tmp table\n"+q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[1].Rows[0][0] != 3)throw new ZTestException("Incorrect values");
                }, null);
        }
        public static QueryTest GetCreateTab(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
CREATE TABLE fake
(
   job_id  smallint
      IDENTITY(1,1)
      PRIMARY KEY CLUSTERED,
   job_desc        varchar(50)     NOT NULL
      DEFAULT 'New Position - title not formalized yet',
   min_lvl tinyint NOT NULL
      CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL
      CHECK (max_lvl <= 250)
)";
            return new QueryTest(ztestLoader, wsid,q,"mydb",
                "create table",
                "Creates a table with some fancy options.\n"+q,
                delegate(DataSet ds) {
                    (new CasJobs()).SubmitShortJob(wsid, "drop table fake", "mydb", "cleanup", 0);
                },null);
        }
        public static QueryTest GetCreateFunc(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END";
            return new QueryTest(ztestLoader, wsid, q,"mydb",
                "create function",
                "Creates a function and verifies that it works.\n"+q,
                delegate(DataSet ds) {
                    (new CasJobs()).SubmitShortJob(wsid, "select dbo.isoweek(getdate())", "mydb", "verify", 0);
                },
                delegate() {
                      (new CasJobs()).SubmitShortJob(wsid, "drop function ISOweek", "mydb", "cleanup", 0);
                });
        }
        public static QueryTest GetCreateSP(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q= @"
CREATE PROCEDURE fake
AS
select 0
GO";
            return new QueryTest(ztestLoader, wsid, q,"mydb",
                "create procedure",
                "Creates a stored procedure and verifies that it works.\n"+q,
                delegate(DataSet ds) {
                    (new CasJobs()).SubmitShortJob(wsid, "exec fake", "mydb", "verify", 0);
                },
                delegate() {
                    (new CasJobs()).SubmitShortJob(wsid, "drop procedure fake", "mydb", "cleanup", 0);
                });
        }
        public static QueryTest GetMyDBFromOut(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 69 as tmp, 60 as tmp2 into mydb.faker
go
select tmp,tmp2 from mydb.faker";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "mydb from other",
                "Query a table in MyDB from another context.\n" + q,
                delegate(DataSet ds) {
                    if ((int)ds.Tables[1].Rows[0][0] != 69) throw new Exception("wrong values");
                },
                delegate() {
                    (new CasJobs()).SubmitShortJob(wsid, "drop table faker", "mydb", "cleanup", 0);
                });
        }
        public static QueryTest GetOtherFromMyDB(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select top 1 ra from " + def_context + ".phototag";
            return new QueryTest(ztestLoader, wsid, q, "mydb",
                "other from mydb",
                "Query a table outside of MyDB from MyDB.\n" + q,
                delegate(DataSet ds) {
                    if(ds.Tables[0].Rows.Count != 1)throw new Exception("wrong rowcount");
                },null);
        }
public static QueryTest GetSelectIntoMultiRows(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
 select top 10 objid, ra,dec into mydb.jugs from phototag";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "into mydb large",
                "Another simple select into MyDB, this time with multiple rows.  Verifies est rowcount is what expected, and then verifies that by running a count query.\n" + q,
                delegate(DataSet ds) {
                    if (ds.Tables[0].Rows.Count != 1) throw new Exception("wrong est rowcount");
                    ds = (new CasJobs()).SubmitShortJob(wsid, "select count(*) from jugs", "mydb", "select into multi verify", 0);
                    if ((int)ds.Tables[0].Rows[0][0] != 10) throw new Exception("wrong lit rowcount");
                }, delegate() {
                (new CasJobs()).SubmitShortJob(wsid, "drop table jugs", "mydb", "cleanup", 0);
                 });
        }
        public static QueryTest GetMultiSelectInto(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select top 5 objid, ra,dec into mydb.jugs1 from phototag
go
 select top 10 objid, ra,dec into mydb.jugs2 from phototag";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "multiple intos",
                "A pair of 'select into's in the same query.\n" + q,
                delegate(DataSet ds) {
                    if (ds.Tables[0].Rows[0][0].ToString().IndexOf("5") < 0) throw new Exception("wrong est rowcount");
                    if (ds.Tables[1].Rows[0][0].ToString().IndexOf("10") < 0) throw new Exception("wrong est rowcount");
                }, delegate() {
                (new CasJobs()).SubmitShortJob(wsid, "drop table jugs1\ngo\ndrop table jugs2", "mydb", "cleanup", 0);
            });
        }
        public static QueryTest GetExtraGo(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
select 0
go
 select 1
go
";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "extra go",
                "Couple queries with an extra go\n" + q,
                delegate(DataSet ds) {
                    if (ds.Tables[0].Rows[0][0].ToString().IndexOf("0") < 0) throw new Exception("wrong");
                    if (ds.Tables[1].Rows[0][0].ToString().IndexOf("1") < 0) throw new Exception("wrong");
                },null);
        }
        public static QueryTest DeclareSetInto(ZTestLoader ztestLoader, long wsid, string def_context) {
            string q = @"
declare @GOOD bigint         
set @GOOD = dbo.fFieldQuality('GOOD')
select top 3 
  f.fieldID
into mydb.rtest
from
  field F
where
  (F.quality = @GOOD or F.quality = 1)
";
            return new QueryTest(ztestLoader, wsid, q, def_context,
                "declare set into",
                "Couple queries with an extra go\n" + q,
                delegate(DataSet ds) {
                    if (ds.Tables[0].Rows[0][0].ToString().IndexOf("3") < 0) throw new Exception("wrong");
                }, delegate() {
                (new CasJobs()).SubmitShortJob(wsid, "drop table rtest", "mydb", "cleanup", 0);
            });
        }
    }
}