<Query Kind="Program">
  <Connection>
    <ID>580b3b8d-cc02-4a68-8fbd-d1ec374a25f2</ID>
    <Persist>true</Persist>
    <Server>D0001-sql-11.sdt.local</Server>
    <Database>jazz dev master Prod</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <NuGetReference>AWSSDK.DynamoDBv2</NuGetReference>
  <NuGetReference>AWSSDK.S3</NuGetReference>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Amazon.DynamoDBv2.DataModel</Namespace>
  <Namespace>Amazon.DynamoDBv2</Namespace>
  <Namespace>Amazon.DynamoDBv2.DocumentModel</Namespace>
  <Namespace>Amazon.DynamoDBv2.Model</Namespace>
</Query>

public static Dictionary<int, Tuple<DateTime, DateTime>> Versions = new Dictionary<int, System.Tuple<System.DateTime, System.DateTime>>();

void Main()
{
	Versions = (from v in CaseTypeFamilyVersions select v)
	.ToDictionary(v => v.CaseTypeFamilyVersionID, t => new Tuple<DateTime, DateTime>(t.StartDate, t.EndDate));
	var aprdrg = (from aprDto in CaseTypeFamilyMappingAPRDRGs
				  join ctfDto in CaseTypeFamilies on aprDto.CaseTypeFamilyID equals ctfDto.CaseTypeFamilyID
				  select new AllCaseTypeFamily(
					  ctfDto.CaseTypeFamilyID,
					  ctfDto.CaseTypeFamilyGUID,
					  ctfDto.Name,
					  ctfDto.CaseTypeFamilyGlobalID,
					  aprDto.APRDRGCode,
					  aprDto.CaseTypeFamilyVersionID)).ToList();
	var msdrg = (from icd10Dto in CaseTypeFamilyMappingICD10s
				 join ctfDto in CaseTypeFamilies on icd10Dto.CaseTypeFamilyID equals ctfDto.CaseTypeFamilyID
				 select new AllCaseTypeFamily(
					 ctfDto.CaseTypeFamilyID,
					 ctfDto.CaseTypeFamilyGUID,
					 ctfDto.Name,
					 ctfDto.CaseTypeFamilyGlobalID,
					 icd10Dto.SkipICD,
					 icd10Dto.ICD10Code,
					 icd10Dto.MSDRGCode,
					 icd10Dto.CaseTypeFamilyVersionID)).ToList();
	var cpt = (from cptDto in CaseTypeFamilyMappingCPTs
			   join ctfDto in CaseTypeFamilies on cptDto.CaseTypeFamilyID equals ctfDto.CaseTypeFamilyID
			   select new AllCaseTypeFamily(
				   ctfDto.CaseTypeFamilyID,
				   ctfDto.CaseTypeFamilyGUID,
				   ctfDto.Name,
				   ctfDto.CaseTypeFamilyGlobalID,
				   cptDto.CaseTypeFamilyVersionID,
				   cptDto.CPTCode)).ToList();
	//cpt.Dump(nameof(cpt));
	var allCaseTypeFamily = aprdrg.Union(msdrg).Union(cpt)
	.OrderBy(x => x.CaseTypeFamilyID)
	.ThenByDescending(x => x.CaseTypeFamilyVersionID);
	var globalCaseTypeFamily = allCaseTypeFamily
	.GroupBy(ctf => new { ctf.CaseTypeFamilyID, ctf.CaseTypeFamilyGUID, ctf.Name, ctf.CaseTypeFamilyGlobalID, ctf.CaseTypeFamilyVersionID },
	(g, d) => new GlobalCaseTypeFamily(
		g.CaseTypeFamilyID,
		g.CaseTypeFamilyGUID,
		g.Name,
		g.CaseTypeFamilyGlobalID,
		g.CaseTypeFamilyVersionID,
		d.First().StartDate,
		d.First().EndDate,
		d.Where(x => !string.IsNullOrEmpty(x.APRDRGCode)).Select(x => x.APRDRGCode),
		d.Where(x => !string.IsNullOrEmpty(x.CPTCode)).Select(x => x.CPTCode),
		d
		.Where(x => (string.IsNullOrEmpty(x.APRDRGCode) || string.IsNullOrEmpty(x.CPTCode)) && !x.SkipICD)
		.Select(x => new ICD10Code(x.ICD10Code, x.MSDRGCode)),
		d
		.Where(x => (string.IsNullOrEmpty(x.APRDRGCode) || string.IsNullOrEmpty(x.CPTCode)) && x.SkipICD)
		.Select(x => new ICD10Code(x.ICD10Code, x.MSDRGCode))
	));
	globalCaseTypeFamily.Dump();
/*
	try
	{
		var config = new AmazonDynamoDBConfig
		{
			ServiceURL = "http://localhost:8000"
		};
		var client = new AmazonDynamoDBClient(config);
		var context = new DynamoDBContext(client);
		DeleteTable(client, nameof(GlobalCaseTypeFamily));
		CreateTableGlobalCaseTypeFamily(client);
		var table = Amazon.DynamoDBv2.DocumentModel.Table.LoadTable(client, nameof(GlobalCaseTypeFamily));
		globalCaseTypeFamily.ToList()
		.ForEach(ctf =>
		{
			table.PutItem(Document.FromJson(JsonConvert.SerializeObject(ctf)));
		});
	}
	catch (Exception ex)
	{
		ex.Dump();
		throw;
	}
	*/
	//var result = JsonConvert.SerializeObject(globalCaseTypeFamily);
	//result.Dump(nameof(globalCaseTypeFamily));

	//	Util.WriteCsv(result, @"D:\CaseTypeFamily.csv");
	//System.IO.File.WriteAllText(@"D:\CaseTypeFamily.json", JsonConvert.SerializeObject(result, Newtonsoft.Json.Formatting.Indented));	
}

private static void CreateTableGlobalCaseTypeFamily(AmazonDynamoDBClient client)
{
	string tableName = nameof(GlobalCaseTypeFamily);
	var response = client.CreateTable(new CreateTableRequest
	{
		TableName = tableName,
		AttributeDefinitions = new List<AttributeDefinition>()
		{
			new AttributeDefinition
			{
				AttributeName = "PartitionKey",
				AttributeType = "S"
			},
			new AttributeDefinition
			{
				AttributeName = "CaseTypeFamilyID",
				AttributeType = "N"
			},
			new AttributeDefinition
			{
				AttributeName = "CaseTypeFamilyGUID",
				AttributeType = "S"
			},
			new AttributeDefinition
			{
				AttributeName = "Name",
				AttributeType = "S"
			},
			new AttributeDefinition
			{
				AttributeName = "CaseTypeFamilyGlobalID",
				AttributeType = "S"
			}
		},
		KeySchema = new List<KeySchemaElement>()
		{
			new KeySchemaElement()
			{
				AttributeName = "PartitionKey",
				KeyType = "HASH"
			},
			new KeySchemaElement
			{
				AttributeName = "CaseTypeFamilyID",
				KeyType = "RANGE"
			}
		}
	});
	WaitTillTableCreated(client, tableName, response);
}

#region AWS DynamoDB Helpers

private static void DeleteTable(AmazonDynamoDBClient client, string tableName)
{
	try
	{
		var deleteTableResponse = client.DeleteTable(new DeleteTableRequest()
		{
			TableName = tableName
		});
		WaitTillTableDeleted(client, tableName, deleteTableResponse);
	}
	catch (ResourceNotFoundException)
	{
		// There is no such table.
	}
}

private static void WaitTillTableCreated(AmazonDynamoDBClient client, string tableName,
					  CreateTableResponse response)
{
	var tableDescription = response.TableDescription;

	string status = tableDescription.TableStatus;

	Console.WriteLine(tableName + " - " + status);

	// Let us wait until table is created. Call DescribeTable.
	while (status != "ACTIVE")
	{
		System.Threading.Thread.Sleep(5000); // Wait 5 seconds.
		try
		{
			var res = client.DescribeTable(new DescribeTableRequest
			{
				TableName = tableName
			});
			Console.WriteLine("Table name: {0}, status: {1}", res.Table.TableName,
					  res.Table.TableStatus);
			status = res.Table.TableStatus;
		}
		// Try-catch to handle potential eventual-consistency issue.
		catch (ResourceNotFoundException)
		{ }
	}
}

private static void WaitTillTableDeleted(AmazonDynamoDBClient client, string tableName,
							 DeleteTableResponse response)
{
	var tableDescription = response.TableDescription;

	string status = tableDescription.TableStatus;

	Console.WriteLine(tableName + " - " + status);

	// Let us wait until table is created. Call DescribeTable
	try
	{
		while (status == "DELETING")
		{
			System.Threading.Thread.Sleep(5000); // wait 5 seconds

			var res = client.DescribeTable(new DescribeTableRequest
			{
				TableName = tableName
			});
			Console.WriteLine("Table name: {0}, status: {1}", res.Table.TableName,
					  res.Table.TableStatus);
			status = res.Table.TableStatus;
		}
	}
	catch (ResourceNotFoundException)
	{
		// Table deleted.
	}
}

#endregion

public class ICD10Code
{
	[DynamoDBProperty]
	public string ICD10_Code { get; set; }
	[DynamoDBProperty]
	public string MSDRGCode { get; set; }

	public ICD10Code()
	{
		ICD10_Code = string.Empty;
		MSDRGCode = string.Empty;
	}

	public ICD10Code(string icd10code, string msdrgcode)
	{
		ICD10_Code = icd10code;
		MSDRGCode = msdrgcode;
	}
}

[DynamoDBTable("GlobalCaseTypeFamily")]
public class GlobalCaseTypeFamily
{
	[DynamoDBHashKey]
	public string PartitionKey => $"{CaseTypeFamilyVersionID}|{CaseTypeFamilyID}";
	[DynamoDBProperty]
	public int CaseTypeFamilyID { get; set; }
	[DynamoDBProperty]
	public Guid CaseTypeFamilyGUID { get; set; }
	[DynamoDBProperty]
	public string Name { get; set; }
	[DynamoDBProperty]
	public string CaseTypeFamilyGlobalID { get; set; }
	[DynamoDBProperty]
	public int CaseTypeFamilyVersionID { get; set; }
	[DynamoDBProperty]
	public DateTime StartDate { get; set; }
	[DynamoDBProperty]
	public DateTime EndDate { get; set; }
	[DynamoDBProperty]
	public List<string> APRDRGCodes { get; set; }
	[DynamoDBProperty]
	public List<string> CPTCodes { get; set; }
	[DynamoDBProperty]
	public List<ICD10Code> ICD10Codes { get; set; }
	[DynamoDBProperty]
	public List<ICD10Code> SkipICD10Codes { get; set; }

	public GlobalCaseTypeFamily()
	{
		CaseTypeFamilyID = -1;
		CaseTypeFamilyGUID = Guid.Empty;
		Name = string.Empty;
		CaseTypeFamilyGlobalID = string.Empty;
		CaseTypeFamilyVersionID = -1;
		StartDate = DateTime.MinValue;
		EndDate = DateTime.MaxValue;
		APRDRGCodes = new List<string>();
		CPTCodes = new List<string>();
	}

	public GlobalCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, int caseTypeFamilyVersionID,
	DateTime startDate, DateTime endDate)
	{
		CaseTypeFamilyID = caseTypeFamilyID;
		CaseTypeFamilyGUID = caseTypeFamilyGuid;
		Name = name;
		CaseTypeFamilyGlobalID = caseTypeFamilyGlobalID;
		CaseTypeFamilyVersionID = caseTypeFamilyVersionID;
		StartDate = startDate;
		EndDate = endDate;
	}

	public GlobalCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, int caseTypeFamilyVersionID,
	DateTime startDate, DateTime endDate,
	IEnumerable<string> aprdrgcodes,
	IEnumerable<string> cptcodes,
	IEnumerable<ICD10Code> icd10codes,
	IEnumerable<ICD10Code> skipicd10codes)
	: this(caseTypeFamilyID, caseTypeFamilyGuid, name, caseTypeFamilyGlobalID, caseTypeFamilyVersionID, startDate, endDate)
	{
		APRDRGCodes = aprdrgcodes.ToList();
		CPTCodes = cptcodes.ToList();
		ICD10Codes = icd10codes.ToList();
		SkipICD10Codes = skipicd10codes.ToList();
	}
}

public class AllCaseTypeFamily
{
	public int CaseTypeFamilyID { get; set; }
	public Guid CaseTypeFamilyGUID { get; set; }
	public string Name { get; set; }
	public string CaseTypeFamilyGlobalID { get; set; }
	public string APRDRGCode { get; set; }
	public bool SkipICD { get; set; }
	public string ICD10Code { get; set; }
	public string MSDRGCode { get; set; }
	public string CPTCode { get; set; }
	public int CaseTypeFamilyVersionID { get; set; }
	public DateTime StartDate { get; set; }
	public DateTime EndDate { get; set; }

	public AllCaseTypeFamily()
	{
		CaseTypeFamilyID = -1;
		CaseTypeFamilyGUID = Guid.Empty;
		Name = string.Empty;
		CaseTypeFamilyGlobalID = string.Empty;
		APRDRGCode = string.Empty;
		SkipICD = false;
		ICD10Code = string.Empty;
		MSDRGCode = string.Empty;
		CPTCode = string.Empty;
		CaseTypeFamilyVersionID = -1;
		StartDate = DateTime.MinValue;
		EndDate = DateTime.MaxValue;
	}

	public AllCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID)
	: this()
	{
		CaseTypeFamilyID = caseTypeFamilyID;
		CaseTypeFamilyGUID = caseTypeFamilyGuid;
		Name = name;
		CaseTypeFamilyGlobalID = caseTypeFamilyGlobalID;
	}

	public AllCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, int caseTypeFamilyVersionID)
	: this(caseTypeFamilyID, caseTypeFamilyGuid, name, caseTypeFamilyGlobalID)
	{
		CaseTypeFamilyVersionID = caseTypeFamilyVersionID;
		if (Versions.ContainsKey(caseTypeFamilyVersionID))
		{
			StartDate = UserQuery.Versions[caseTypeFamilyVersionID].Item1;
			EndDate = UserQuery.Versions[caseTypeFamilyVersionID].Item2;
		}
	}

	public AllCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, string aprdrgCode, int caseTypeFamilyVersionID)
	: this(caseTypeFamilyID, caseTypeFamilyGuid, name, caseTypeFamilyGlobalID, caseTypeFamilyVersionID)
	{
		APRDRGCode = aprdrgCode;
	}

	public AllCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, bool skipICD, string icd10Code, string msdrgCode, int caseTypeFamilyVersionID)
	: this(caseTypeFamilyID, caseTypeFamilyGuid, name, caseTypeFamilyGlobalID, caseTypeFamilyVersionID)
	{
		SkipICD = skipICD;
		ICD10Code = icd10Code;
		MSDRGCode = msdrgCode;
	}

	public AllCaseTypeFamily(int caseTypeFamilyID, Guid caseTypeFamilyGuid, string name, string caseTypeFamilyGlobalID, int caseTypeFamilyVersionID, string cptCode)
	: this(caseTypeFamilyID, caseTypeFamilyGuid, name, caseTypeFamilyGlobalID, caseTypeFamilyVersionID)
	{
		CPTCode = cptCode;
	}

}