using System;
using System.Data;
using System.Text;
using edu.jhu.Cas.Util;

namespace edu.jhu.Cas.Servers {
    public class JobServers : SqlJob {
        public JobServers()
            : base() {
            init();
        }

        public const string NonQuickQueuesStatement = @"
select target,queue from servers s1
	where s1.name not in
	(
		select name 
		from servers s, (
			select target, min(queue) as queue
			from servers
			group by target
		) a
		where a.target = s.target and s.queue = a.queue
	)";
        public const string FindUserServersStatement = @"
select * from (
	select s.* from servers s, (
		select g.privs from groups g,(
			select distinct gid from groupmembers
			where webservicesid = "+WEB_SERVICES_ID+@" and
			status = 2
		) gm
		where gm.gid = g.gid and
		g.privs is not null
	) gg
	where (charindex(s.privs,gg.privs) > 0)
	union
	select * from servers
	where privs is null
) z order by z.rank desc
";
        public const string FindAdminServersStatement = @"select * from servers";
        public const string ClosestQueueStatement = @"
select s2.* from servers s2, (
	select min(d.queue) as queue from (
		select s.* from servers s, (
			select min (queue) as queue from servers where target like " + TARGET + @"
			and (queue > = " + ESTIMATE + @")
		) b 
		where b.queue = s.queue and s.target like " + TARGET + @"
		union
		select s1.* from servers s1, (
			select max(queue) as queue from servers where target like " + TARGET + @"
		) c
		where c.queue = s1.queue and s1.target like " + TARGET + @"
	) d
) e 
where s2.target like " + TARGET + @" and s2.queue = e.queue";

        public const string HighestQueueStatement =
@"select max (queue) from servers where target = " + TARGET;
        public const string MinTwoQueuesStatement = @"
select * from servers s1, (
	select min(s.queue) as q1, avg(ss.queue) as q2 from servers s,
	(
		select min(queue) as queue from servers where target = " + TARGET + @") ss
		where target = " + TARGET + @" and s.queue <> ss.queue
	) s2 
where (s1.queue = s2.q1 or s1.queue = s2.q2) and s1.target = " + TARGET + @"
order by s1.queue
";

        public void init() {
            Add(WEB_SERVICES_ID, new JobInfo(SqlDbType.BigInt, 0, 0));
            Add(ESTIMATE, new JobInfo(SqlDbType.Int, 0, 0));
            Add(TARGET, new JobInfo(SqlDbType.VarChar, 200, ""));
        }


        /** begin package keys **/
        public const string WEB_SERVICES_ID = "@WebServicesID";
        public const string TARGET = "@Target";
        public const string ESTIMATE = "@Estimate";
        /**end package keys*/
    }
}
