using System;
using System.Threading;
using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Servers;
using NUnit.Framework;
using NUnit.Core;
using System.Configuration;

/**
 * test queries for cjobs
 * each test does the following:
 *	-runs in short queue
 *	-maybe verifies result
 *	-maybe drops table
 *	-runs in long queue
 *	-maybe verifies result
 *	-maybe drops table
 */

namespace edu.jhu.Cas.Test{
	[TestFixture]	
	public class QueryTests{
	
		public const string DEF_TARG = "DR1";
		
		[Test] 
		public void DeclareAndSelect(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
declare @BRIGHT bigint    set @BRIGHT=dbo.fPhotoFlags('BRIGHT')
declare @EDGE bigint      set @EDGE=dbo.fPhotoFlags('EDGE')
declare @SATURATED bigint set @SATURATED=dbo.fPhotoFlags('SATURATED')
declare @NODEBLEND bigint set @NODEBLEND=dbo.fPhotoFlags('NODEBLEND')
--declare @bad_flags bigint set @bad_flags=(@SATURATED|@BRIGHT|@EDGE|@NODEBLEND)

select top 11 run,rerun,camcol,field,obj,colc,rowc,parentID,nChild,ra,dec,
       extinction_r,psfMag_u,psfMag_g,psfMag_r,psfMag_i,psfMag_z,
       psfMagErr_u,psfMagErr_g,psfMagErr_r,psfMagErr_i,psfMagErr_z,
       rowv,colv,rowvErr,colvErr,rowc_u,colc_u,rowc_g,colc_g,rowc_r,
       colc_r,rowc_i,colc_i,rowc_z,colc_z,offsetRa_u,offsetDec_u,
       offsetRa_g,offsetDec_g,offsetRa_r,offsetDec_r,offsetRa_i,
       offsetDec_i,offsetRa_z,offsetDec_z
 into MyDB.testdr3
 from Star
 where nChild=0
--  and (flags & @bad_flags) = 0
  and abs(psfMag_u)<21 and abs(psfMag_g)<21
  and abs(psfMagErr_u)<=0.1 and abs(psfMagErr_r)<=0.1
  and abs(psfMagErr_g)<=0.05 and abs(psfMagErr_i)<=0.05
  and abs(psfMagErr_z)<=0.05
-- and sqrt((offsetRa_u-offsetRa_z)*(offsetRa_u-offsetRa_z)*cos(dec*0.01745)*cos(dec*0.01745)+(offsetDec_u-offsetDec_z)*(offsetDec_u-offsetDec_z))>=0.2
",
				"",DEF_TARG,"select count(*) from testdr3","11","drop table testdr3");
			tests.RunAllTests();
		}

		[Test]
		public void SelectIntoFromTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"select top 10 ra into mydb.test1 from photoobj",
				"select into from target",
				DEF_TARG,"select count(*) from test1","10","drop table test1");
			tests.RunAllTests();
		}

		[Test]
		public void SelectIntoFromMyDB(){
			QTests tests = new QTests();
			tests.AddTest(
				@"select top 12 ra into test1 from dr1.photoobj",
				"select into from mydb",
				"mydb","select count(*) from test1","12","drop table test1");
			tests.RunAllTests();
		}
		[Test]
		public void SelectIntoFromNeitherMyDBNorTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"select top 13 ra into mydb.test1 from dr1.photoobj",
				"select into from neither mydb nor target",
				"EDR","select count(*) from test1","13","drop table test1");
			tests.RunAllTests();
		}
		[Test]
		public void DotDotQuery(){
			QTests tests = new QTests();
			tests.AddTest(
				@"select top 12 ra into test1 from dr1..photoobj",
				"DotDotQuery",
				"mydb","select count(*) from test1","12","drop table test1");
			tests.RunAllTests();
		}
		[Test]
		public void CreateFunctionWithNoTableRefs(){
			QTests tests = new QTests();
			
			tests.AddTest(
				@"
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",
				"function with no table refs",
				"MYDB",null,null,"drop function isoweek");
			tests.RunAllTests();
		}

		[Test]
		public void SimpleNestedFrom(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
					select * into mydb.test1 from (select top 9 ra from photoobj) as abc",
				"simple nested from",
				DEF_TARG,"select count(*) from test1","9","drop table test1");
			tests.RunAllTests();
		}

		[Test]//no verify; this should just run
		public void CallTargetFunctionAtTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
					select dbo.fPhotoFlags('CHILD') as a into mydb.test1",
				"CallTargetFunctionAtTarget",
				DEF_TARG,null,null,"drop table test1");
			tests.RunAllTests();
		}

        
		[Test]//also just runs
		public void CallMyDBFunctionAtMyDB(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
					select dbo.dbcreationdate('master') as a into mydb.test1",
				"CallMyDBFunctionAtMyDB",
				"MYDB",null,null,"drop table test1");
			tests.RunAllTests();
		}

		[Test]
		[ExpectedException(typeof(CasJobsException))]
			/*
			 * this should fail with the can't-call-remote-function error
			 */
		public void CallMyDBFunctionAtTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
					select mydb.dbcreationdate('master') as a into mydb.test1",
				"",
				DEF_TARG,null,null,"drop table test1");
			tests.RunAllTests();
		}
	
		[Test]
		[ExpectedException(typeof(CasJobsException))]
		public void CallTargetFunctionAtMyDB(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
					select dr1.fPhotoFlags('CHILD') as a into mydb.test1",
				"",
				"MyDB",null,null,"drop table test1");
			tests.RunAllTests();
		}

		[Test]
		public void CreateProcedureWithTargetTableRef(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
CREATE PROCEDURE SP_GETID2
@ASDF BIGINT
AS
SELECT OBJID
FROM dr1.PHOTOOBJ 
WHERE OBJID = @ASDF",
				"",
				"MyDB",null,null,"drop procedure sp_getid2");
			tests.RunAllTests();
		}

		[Test]
		public void CallMyDBProcedureFromMyDB(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
exec sp_getid 582099903707873490
				",
				"",
				"MyDB",null,null,null);
			tests.RunAllTests();
		}

		[Test]
		public void CallMyDBProcedureFromTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
exec mydb.sp_getid 582099903707873490
				",
				"",
				DEF_TARG,null,null,null);
			tests.RunAllTests();
		}
		[Test]
		public void CallTargetProcedureFromMyDB(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
				execute dr1.spSkyServerColumns 'galaxy','dbo'
				",
				"",
				"MYDB",null,null,null);
			tests.RunAllTests();
		}
		[Test]
		public void CallTargetProcedureFromTarget(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
				execute spSkyServerColumns 'galaxy','dbo'
				",
				"",
				DEF_TARG,null,null,null);
			tests.RunAllTests();
		}

		[Test]
		[ExpectedException(typeof(CasJobsException))]
			//don't allow these
		public void ColumnsWithoutNames(){
			QTests tests = new QTests();
			tests.AddTest(
				@"
				SELECT  top 12 objID,
        (flags_u & 0x0000000010000000),
        (flags_g & 0x0000000010000000)
        
into mydb.asdf
FROM DR2.Star

				",
				"",
				DEF_TARG,"select count(*) from asdf","12","drop table asdf");
			tests.RunAllTests();
		}
	}
}
