<Query Kind="Program">
  <Connection>
    <ID>db640701-a1c5-4177-9f03-5152d093ffa7</ID>
    <Persist>true</Persist>
    <Server>D0011-sql-01.sdt.local</Server>
    <Database>jazz tst StrataSphere 20191104.1</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <NuGetReference>RestSharp</NuGetReference>
  <Namespace>RestSharp</Namespace>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>System.Data.Common</Namespace>
</Query>

private string BaseUrl = "https://localhost:44325/api/v1/"; //"https://api-dev.sdt.local/starterset-api/api/v1/"; //"https://starterset-api.dev.k8s.sdt.local/api/v1/";
private List<DefinitionTranslationDto> TheGrade = new List<UserQuery.DefinitionTranslationDto>();

#region *** Enumerations ***

public enum QueryRegions
{
	Production,
	Training,
	Oak,
	Dev
}

#endregion

#region *** Options ***

public static QueryRegions queryRegion = QueryRegions.Dev;
public static string crossDBQueryPath = @"D:\CrossDBQueries"; // This is the path to find the queries
public static bool dumpOptions = false; // This flag will dumpt this set of options
public static bool dumpSmcSql = false; // This flag will dump the smc sql to the result
public static bool dumpDBList = false; // This flag will dump the databases to the result
public static bool dumpQueries = false; // This flag will dump the queries to the result
public static bool dumpDBExclusions = false; // This flag will dump the db and server exclusions
public static bool dumpDBTargets = false; // This flag will dump the db and server targets
public static bool dumpAppVersions = false; // This flag will dump the app versions used in the queryRegion
public static bool dumpAppVersionTargets = false; // This flag will dump the app versions targets
public static bool excludeExceptions = true;
public static bool queryCompress = false;
public static bool flatten = true; // This will flatten all of the results into a single list
public static bool showStopWatch = false;
public static bool noQueries = false; // This will stop the CrossDBQuery without running the queries

#endregion

public static Parms parms = new Parms
{
	name = "FY2020"
};

#region *** Database Selection ***

public static string[] appVersions = { // This is a list of appVersions to filter for in the database list
	//"2019.42"
};

public static string[] serverExclusions = { // This is a list of servers to exclude from the database list
	@"paonsql19.sdt.local\instance1",
	@"paonsql17.sdt.local\instance1",
	@"p0555-sql-01.sdt.local\instance1",
	"P0000-sql-01.sdt.local",
	"P9900-SQL-03.sdt.local",
	"P9900-sql-05.sdt.local"
};

public static string[] dbExclusions = { // This is a list of database physical names to exclude from the database list
};
public static string[] targetOrgPins = { // This is a list of org pins to target in the database list
};
public static string[] targetServers = { // This is a list of servers to target in the database list
};
public static string[] targetDBs = { // This is a list of database physicial names to target in the database list
//"jazz tst Aultman 20200417.1"
};

#endregion *** Options ***

#region *** SMC Settings and queries ***

public static string smcSql => @"SELECT di.ServerName, di.DatabaseGUID, di.DatabaseName, di.PhysicalName, di.OrgPin, di.AppVersion, di.Description
FROM [dbo].[viewDatabaseInfo] di
WHERE 1=1"
+ (queryRegion == QueryRegions.Dev
? @"
  AND di.PhysicalName like 'jazz%'"
: $@"
  AND di.PhysicalName like 'jazz {(queryRegion != QueryRegions.Production ? "tst" : "prd")}%'
  AND di.ServerName like 'p%'");
public static string smcServer { get; set; }
public static string smcDB { get; set; }

#endregion

#region *** Program variables and settings ***

public static IOrderedEnumerable<Database> databases => GetDatabases();

#endregion

public void OptionsDump()
{
	var options = new
	{
		queryRegion,
		crossDBQueryPath,
		dumpOptions,
		dumpSmcSql,
		dumpDBList,
		dumpQueries,
		dumpDBExclusions,
		dumpDBTargets,
		dumpAppVersions,
		dumpAppVersionTargets,
		excludeExceptions,
		queryCompress,
		flatten,
		showStopWatch,
		noQueries
	};
	options.Dump(nameof(options));
}

public class Parms
{
	public string name { get; set; }
}

void Main()
{
	RestClient client = new RestClient();
	var definitions = GetList<DefinitionDto>(client, "Definitions");
	var definitionTranslations = GetList<DefinitionTranslationDto>(client, "DefinitionTranslations");

	var work = definitions
	.GroupBy(d => new
	{
		d.HierarchyGuid,
		d.DimensionGuid,
		d.AttributeGuid,
		d.HierarchyId,
		d.DimensionId,
		d.AttributeId
	}, (g, d) => new WorkDto
	{
		DefinitionGuids = d.Select(x => x.Id).ToList(),
		HierarchyGuid = g.HierarchyGuid,
		DimensionGuid = g.DimensionGuid,
		AttributeGuid = g.AttributeGuid,
		HierarchyId = g.HierarchyId,
		DimensionId = g.DimensionId,
		AttributeId = g.AttributeId,
		Name = g.AttributeId == "a177cc88-e329-44e0-9063-2d9b59af51b2"
		? "Department"
		: g.AttributeId == "f23654b3-149f-490a-a366-fca35f57d66a"
		? "EntityID"
		: g.AttributeId == "MRDEPTENTITY"
		? "Entity"
		: string.Empty
	})
	.Distinct()
	.OrderBy(d => d.DimensionId)
	.ThenBy(d => d.AttributeId)
	.ToList();

	DoTheDumping();
	
	databases
	//.Where(d => d.DatabaseName == "Aultman")
	.OrderBy(d => d.DatabaseName)
	.ThenBy(d => d.OrgPin)
	.ThenBy(d => d.PhysicalName)
	.ToList()
	.ForEach(db =>
	{
		FindMissing(work, db);
	});
	var theWork = TheGrade
	.Distinct().ToList();
	//theWork.Dump(nameof(theWork));
	theWork.RemoveAll(tg => tg.AttributeGUID == Guid.Empty || tg.AttributeId == tg.AttributeGUID.ToString());
	JsonConvert.SerializeObject(theWork, Newtonsoft.Json.Formatting.Indented).Dump("json");
}

public void FindMissing(List<WorkDto> work, Database database)
{
	var db = new TypedDataContext(database.ConnectionString);
	try
	{
		var dimensions = work.Select(w => w.DimensionGuid).Distinct();
		var Score =
		(from d in db.ScoreDimensions
		 join dg in db.ScoreDimensionGroups on d.DimensionGUID equals dg.DimensionGUID
		 join a in db.ScoreAttributes on dg.DimensionGroupGUID equals a.DimensionGroupGUID
		 where dimensions.Contains(d.DimensionGUID)
		 select new ScoreInfo(database.DatabaseName, database.DatabaseGuid, d.FriendlyName, d.DefaultHierarchyGUID, d.DimensionGUID, a.FriendlyName, a.AttributeGUID))
		 .ToList();
		work.ToList()
		.ForEach(w =>
		{
			var scores = Score.Where(sw => sw.DimensionGUID == w.DimensionGuid).ToList();
			if (!scores.Any())
				return;
			var s = scores.FirstOrDefault(sw => sw.AttributeGUID == w.AttributeGuid);
			if (s != null)
			{
				w.score = s;
				return;
			}
			s = scores.FirstOrDefault(sw => sw.AttributeName == w.Name);
			if (s == null)
			{
				w.score = scores.FirstOrDefault();
			}
			w.ByName = true;
			w.score = s;
		});
		var missing = work.Where(w => w.score == null || w.ByName).ToList();
		missing.ForEach(m =>
		{
			if (m.score == null)
				m.score = new ScoreInfo(database.DatabaseName, database.DatabaseGuid, "", Guid.Empty, Guid.Empty, "", Guid.Empty);
			m.score.DefinitionGuids = new List<System.Guid>();
			m.score.DefinitionGuids.AddRange(m.DefinitionGuids ?? new List<System.Guid>());
		});
		var grades = missing.SelectMany(m => m.score.DefinitionGuids
		.Select(dg => new DefinitionTranslationDto(dg, m.score.DatabaseName, m.score.DatabaseGuid, m.AttributeId, m.score.AttributeName, m.score.AttributeGUID)));

		TheGrade.AddRange(grades);
		//missing.Dump(database.DatabaseName);
	}
	catch (Exception e)
	{

	}

}

[JsonObject(MemberSerialization.OptIn)]
public class DefinitionTranslationDto
{
	[JsonProperty]
	public Guid DefinitionId { get; set; }
	public string DatabaseName { get; set; }
	[JsonProperty]
	public Guid DatabaseGuid { get; set; }
	public string AttributeId { get; set; }
	public string AttributeName {get;set;}
	[JsonProperty]
	public Guid AttributeGUID { get; set; }

	public DefinitionTranslationDto(Guid definitionId, string databaseName, Guid databaseGuid, string attributeId, string attributeName, Guid attributeGuid)
	{
		DefinitionId = definitionId;
		DatabaseName = databaseName;
		DatabaseGuid = databaseGuid;
		AttributeId = attributeId;
		AttributeName = attributeName;
		AttributeGUID = attributeGuid;
	}
}

public class ScoreInfo
{
	public string DatabaseName { get; set; }
	public Guid DatabaseGuid { get; set; }
	public string DimensionName { get; set; }
	public Guid HierarchyGUID { get; set; }
	public Guid DimensionGUID { get; set; }
	public string AttributeName { get; set; }
	public Guid AttributeGUID { get; set; }
	public List<Guid> DefinitionGuids { get; set; } = new List<Guid>();

	public ScoreInfo(string databaseName, Guid databaseGuid, string dimensionName, Guid hierarchyGUID, Guid dimensionGUID, string attributeName, Guid attributeGUID)
	{
		DatabaseName = databaseName;
		DatabaseGuid = databaseGuid;
		DimensionName = dimensionName;
		HierarchyGUID = hierarchyGUID;
		DimensionGUID = dimensionGUID;
		AttributeName = attributeName;
		AttributeGUID = attributeGUID;
	}

}

public class WorkDto
{
	public Guid HierarchyGuid { get; set; }
	public Guid DimensionGuid { get; set; }
	public Guid AttributeGuid { get; set; }
	public string HierarchyId { get; set; }
	public string DimensionId { get; set; }
	public string AttributeId { get; set; }
	public string Name { get; set; }
	public List<Guid> DefinitionGuids { get; set; }
	public bool ByName { get; set; } = false;
	public ScoreInfo score { get; set; }
}

public void DumpScore()
{
	var Score =
	(from d in ScoreDimensions
	 join dg in ScoreDimensionGroups on d.DimensionGUID equals dg.DimensionGUID
	 join a in ScoreAttributes on dg.DimensionGroupGUID equals a.DimensionGroupGUID
	 select new { DimensionName = d.FriendlyName, d.DefaultHierarchyGUID, d.DimensionGUID, AttributeName = a.FriendlyName, a.AttributeGUID });

	Score
	.GroupBy(sdm => new { sdm.DimensionName, sdm.DefaultHierarchyGUID, sdm.DimensionGUID }, (g, d) => new
	{
		Dimension = new
		{
			g.DimensionName,
			g.DimensionGUID,
			g.DefaultHierarchyGUID,
			Attributes = d.Count()
		},
		Attributes = d.Select(sdm => new
		{
			sdm.AttributeName,
			sdm.AttributeGUID
		})
		.OrderBy(sdm => sdm.AttributeName)
		.ToList()
	})
	.OrderBy(ss => ss.Dimension.DimensionName)
	.Dump();
}

// Define other methods and classes here

#region *** Other classes ***

IEnumerable<T> GetList<T>(RestClient client, string something)
{
	return Get<List<T>>(client, something);
}

T Get<T>(RestClient client, string something)
{
	client.BaseUrl = new Uri(BaseUrl + something);
	RestRequest request = new RestRequest();
	request.Method = Method.GET;
	return JsonConvert.DeserializeObject<T>(client.Execute(request).Content,
	new JsonSerializerSettings
	{
		Error = delegate (object sender, Newtonsoft.Json.Serialization.ErrorEventArgs args)
		{
			args.ErrorContext.Error.Message.Dump();
			args.ErrorContext.Handled = true;
		}
	});
}

public class DefinitionDto
{
	public Guid Id { get; set; }
	public string Name { get; set; }
	public string Description { get; set; }
	public Guid HierarchyGuid { get; set; }
	public Guid DimensionGuid { get; set; }
	public Guid AttributeGuid { get; set; }
	public string HierarchyId { get; set; }
	public string DimensionId { get; set; }
	public string AttributeId { get; set; }
	public DateTime DateCreated { get; set; }
	public DateTime DateLastModified { get; set; }

	public DefinitionDto()
	{
	}
}

public class Database
{
	public string Server { get; set; }
	public Guid DatabaseGuid { get; set; }
	public string DatabaseName { get; set; }
	public string PhysicalName { get; set; }
	public string OrgPin { get; set; }
	public string AppVersion { get; set; }
	public string Description { get; set; }
	public Dictionary<string, string> Fields { get; set; }
	public string ConnectionString { get; set; }

	public Database()
	{

	}

	public Database(DataRow dr)
	{
		Server = dr.Field<string>("ServerName");
		DatabaseGuid = dr.Field<Guid>("DatabaseGuid");
		DatabaseName = dr.Field<string>("DatabaseName");
		PhysicalName = dr.Field<string>("PhysicalName");
		OrgPin = dr.Field<string>("OrgPin");
		AppVersion = dr.Field<string>("AppVersion");
		Description = dr.Field<string>("Description");
		Fields = new Dictionary<string, string>();
		foreach (DataColumn column in dr.Table.Columns)
		{
			var index = dr.Table.Columns.IndexOf(column);
			Fields.Add(column.ColumnName, dr[index].ToString());
		}
		ConnectionString = BuildConnectionString(Server, PhysicalName);
	}

	object ToDump() => (object)new
	{
		//Server,
		//DatabaseGuid,
		DatabaseName,
		//PhysicalName,
		//OrgPin,
		//AppVersion,
		//Description,
		ConnectionString
	};
}

#endregion

#region *** Other methods here ***

public void DoTheDumping()
{
	if (dumpOptions)
	{
		OptionsDump();
	}
	if (dumpDBExclusions)
	{
		dbExclusions.Dump(nameof(dbExclusions));
		serverExclusions.Dump(nameof(serverExclusions));
	}
	if (dumpDBTargets)
	{
		targetOrgPins.Dump(nameof(targetOrgPins));
		targetDBs.Dump(nameof(targetDBs));
		targetServers.Dump(nameof(targetServers));
	}
	if (dumpAppVersionTargets)
	{
		appVersions.Dump(nameof(appVersions));
	}
	if (dumpDBList)
	{
		databases.OrderBy(d => d.Server).ThenBy(d => d.PhysicalName).Dump($"{queryRegion} {nameof(databases)}");
	}
	if (dumpAppVersions)
	{
		databases
		.GroupBy(db => db.AppVersion, (grp, data) => data.Select(x => x.AppVersion).First())
		.OrderByDescending(db => db)
		.Dump($"{queryRegion} {nameof(appVersions)}");
	}
}

public static IOrderedEnumerable<Database> GetDatabases()
{
	switch (queryRegion)
	{
		case QueryRegions.Production:
			smcServer = "ioak-sql-smc-01";
			smcDB = "smc prod";
			break;
		case QueryRegions.Training:
			smcServer = "ioak-sql-smc-01";
			smcDB = "smc training";
			break;
		case QueryRegions.Oak:
			smcServer = "ioak-sql-smc-01.sdt.local";
			smcDB = "smc qa";
			break;
		default:
			smcServer = "DDEN-SQL-SMC-01.sdt.local";
			smcDB = "smc dev";
			break;
	}
	if (dumpSmcSql) smcSql.Dump(nameof(smcSql));
	List<Database> databases = new List<UserQuery.Database>();
	var connStr = BuildConnectionString(smcServer, smcDB);
	using (var conn = new SqlConnection(connStr))
	{
		var command = new SqlCommand(smcSql, conn);
		command.CommandType = CommandType.Text;
		var database = new DataSet();
		conn.Open();
		using (DbDataReader reader = command.ExecuteReader())
		{
			while (true)
			{
				var table = new DataTable();
				database.Tables.Add(table);
				table.Load(reader);
				if (reader.IsClosed || !reader.NextResult()) break;
			}
		}
		conn.Close();
		databases = database.Tables[0].AsEnumerable().Select(dr => new Database(dr)).ToList();
		databases = databases.Where(dr => !dbExclusions.Contains(dr.PhysicalName) && !serverExclusions.Contains(dr.Server)).ToList();
		if (targetOrgPins.Any())
			databases = databases.Where(dr => targetOrgPins.Contains(dr.OrgPin)).ToList();
		if (targetServers.Any())
			databases = databases.Where(dr => targetServers.Contains(dr.Server)).ToList();
		if (targetDBs.Any())
			databases = databases.Where(dr => targetDBs.Contains(dr.PhysicalName)).ToList();
		if (appVersions.Any())
			databases = databases.Where(dr => appVersions.Contains(dr.AppVersion)).ToList();
	}
	return databases
	.OrderBy(d => d.Server).ThenBy(d => d.PhysicalName);
}

public static string BuildConnectionString(string server, string database, string username = null, string password = null)
{
	return username == null
	? $"data source={server};initial catalog='{database}';persist security info=True;Integrated Security=SSPI;"
	: $"data source={server};initial catalog='{database}';User ID={username};pwd={password}";
}

#endregion