using System;
using System.Data;
using System.Text;
using edu.jhu.Cas.Util;
namespace edu.jhu.Cas.Users
{
	public class JobGroups : SqlJob
	{
		public const string CreateGroupStatement1 = "select gid from groups where name = "+GROUP_NAME;
        public const string CreateGroupStatement2 = @"
		insert into groups (name,gid,owner,description) values (" +
        GROUP_NAME + "," + GID + "," + OWNER + "," + DESCRIPTION + ")";
        public const string InviteUserStatement = "insert into groupmembers(gid,webservicesid,status) " +
			"values ("+GID+","+WEB_SERVICES_ID+",1)";
        public const string RemoveUserStatement = @"
delete from groupmembers 
where webservicesid = " + WEB_SERVICES_ID + " and gid = " + GID + @"
delete from grouptables
where webservicesid = " + WEB_SERVICES_ID + " and gid = " + GID;
        public const string AcceptInviteStatement = "update groupmembers set status = 2 where gid = " + GID +
			" and webservicesid = "+WEB_SERVICES_ID+" and status = 1";
        public const string DeclineInviteStatement = "delete from groupmembers where gid = " + GID +
			" and webservicesid = "+WEB_SERVICES_ID;
        public const string PublishTableStatement = "insert into grouptables(webservicesid,tableName,gid) values (" +
			WEB_SERVICES_ID+","+TABLE_NAME+","+GID+")";
        public const string DePublishTableStatement = "delete from groupTables where webservicesid = " +
			WEB_SERVICES_ID+" and tablename = "+TABLE_NAME+" and gid = "+GID;
       
        public const string IsOwnerStatement = "select name from groups where gid = " + GID + " and owner = " + WEB_SERVICES_ID;
        public const string IsMemberStatement = "select webservicesid from groupmembers where gid = " + GID + " and webservicesid = " + WEB_SERVICES_ID;
        public const string DestroyGroupsGroupsStatement = "delete from groups where gid = " + GID;
        public const string DestroyGroupsMembersStatement = "delete from groupmembers where gid = " + GID;
        public const string DestroyGroupsTablesStatement = "delete from grouptables where gid = " + GID;
        public const string GetPublishedTableDescriptionStatement = "select description from grouptables " +
			" where webservicesid = "+WEB_SERVICES_ID+
			" and tableName = "+TABLE_NAME;

        public const string GetPublishedTableDescriptionStatementID = "select description from grouptables g," +
			" users u where userid = "+USERID+" and tableName = "+TABLE_NAME+
			" and u.webservicesid = g.webservicesid";
        public const string SetPublishedTableDescriptionStatement = "update grouptables " +
			" set description = "+DESCRIPTION+
			" where webservicesid = "+WEB_SERVICES_ID+
			" and tableName = "+TABLE_NAME;
        public const string GetGroupDescriptionStatement = "select description from groups where gid=" + GID;
        public const string SetGroupDescriptionStatement = "update groups set description = " + DESCRIPTION +
			" where gid = "+GID;
        public const string ListGroupMembersStatement = "select u.userid,u.Name,u.email" +
				" from groupmembers members, groups groups,users u"+
				" where groups.gid = members.gid and groups.gid = "+GID+
				" and u.webservicesid = members.webservicesid and members.status=2";
        public const string GetGroupAdminStatement = "select u.UserID from groups g, users u " +
			" where g.gid = "+GID+" and u.webservicesid = g.owner";
        public static string CanReadTableStatement = @"
--should return 1 if canread, 0 otherwise
select count(*) from (
select count(gm.webservicesid) as cnt from 
grouptables gt,groupmembers gm
where gt.tablename = " + TABLE_NAME + @"
and gt.webservicesid = " + WEB_SERVICES_ID + @" --lenderid
and gt.gid = gm.gid --groups this table is published to
and gm.webservicesid = " + BORROWER_WEB_SERVICES_ID + @" --borrowerid
union
select count(*) as cnt from grouptables gt
where gid = " + ID.PUBLIC_GID + @" and tableName = " + TABLE_NAME + ") a";

        public const string TablePublishedToStatement = @"
select g.* 
from grouptables gt, groups g
where g.gid = gt.gid and
tablename = " + TABLE_NAME + @" and 
webservicesid = " + WEB_SERVICES_ID;
        public static string ListGroupTablesStatement = @"
select gt.tablename,u.mydbname,u.userid,u.webservicesid,g.name as groupname
from grouptables gt, groupmembers gm,users u,groups g
where gm.webservicesid = "+WEB_SERVICES_ID+@" --borrowerid
and gm.status = 2 --accepted invite
and gm.gid = gt.gid --visible tables for all groups
and g.gid = gt.gid
and gt.webservicesid <> "+WEB_SERVICES_ID+ @" --not this user's tables
and gt.webservicesid = u.webservicesid --get lender user info
union
select gt.tablename, u.mydbname, u.userid, u.webservicesid, g.name as groupname
from grouptables gt, users u,groups g
where 
g.gid = "+ID.PUBLIC_GID+@"
and g.gid = gt.gid
and gt.webservicesid <> "+WEB_SERVICES_ID+@"  --not this user's tables
and gt.webservicesid = u.webservicesid --get lender user info
";
		
		public JobGroups():base()
		{
			init();
		}
		public void init()
		{
			Add(USERID, new JobInfo(SqlDbType.VarChar, 32, ""));
			Add(WEB_SERVICES_ID, new JobInfo(SqlDbType.BigInt, 0, 0));
            Add(BORROWER_WEB_SERVICES_ID, new JobInfo(SqlDbType.BigInt, 0, 0));
			Add(GROUP_NAME, new JobInfo(SqlDbType.VarChar, 200,""));
			Add(DESCRIPTION, new JobInfo(SqlDbType.VarChar, 200,""));
			Add(GID, new JobInfo(SqlDbType.BigInt, 0,0));
			Add(OWNER, new JobInfo(SqlDbType.BigInt, 0,0));
			Add(STATUS, new JobInfo(SqlDbType.Int, 0,0));
			Add(TABLE_NAME, new JobInfo(SqlDbType.VarChar, 80,0));
		}
		public const string USERID = "@UserId";
        public const string WEB_SERVICES_ID = "@WebServicesID";
        public const string BORROWER_WEB_SERVICES_ID = "@WebServicesID2";
        public const string GROUP_NAME = "@name";
        public const string DESCRIPTION = "@Description";
        public const string GID = "@gid";
        public const string OWNER = "@owner";
        public const string STATUS = "@status";
        public const string TABLE_NAME = "@tablename";
	}
}
