USE [LegitimateNATIONAL]
GO

/*association mode*/
INSERT INTO Md.AssociationModes(AssociationModeID, Description, OlasID, IsCancelled)
SELECT pkiModeId, sModeDesc, pkiModeId, bActive
FROM NDoTNTR_2023.[dbo].[tluMode] 
WHERE pkiModeId NOT IN (SELECT OlasID FROM Md.AssociationModes)
GO

/*place types*/
INSERT INTO Md.PlaceTypes(PlaceTypeID, Description, OlasID, IsCancelled)
SELECT pkiPlaceTypeId, sPlaceTypeDesc, pkiPlaceTypeId, bActive
FROM NDoTNTR_2023.[dbo].[tluPlaceType]
WHERE pkiPlaceTypeId NOT IN (SELECT OlasID FROM Md.PlaceTypes)
GO

/*Associations Types*/
-- SELECT * FROM [Md].[AssociationTypes]
INSERT INTO [Md].[AssociationTypes](AssociationTypeID,[Description],OlasID,LtpID,IsCancelled)
SELECT pkiAssocTypeId,sAssocTypeDesc,pkiAssocTypeId, NULL,bActive
FROM NDoTNTR_2023.[dbo].[tluAssocType]
WHERE pkiAssocTypeId NOT IN (SELECT OlasID FROM Md.AssociationTypes)
GO

/*Associations Modes*/
INSERT INTO [Md].[AssociationModes](AssociationModeID,[Description],OlasID,LtpsID,IsCancelled)
SELECT pkiModeId,sModeDesc,pkiModeId,NULL,bActive
FROM NDoTNTR_2023.[dbo].[tluMode]
WHERE pkiModeId NOT IN (SELECT OlasID FROM Md.AssociationModes)
GO

/*VehicleMakes*/
INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode, Latitude, Longitude, OlasID, LtpsID, NeoID, IsCancelled)
SELECT DISTINCT ISNULL(sPlaceNameDesc,'Place - ' + sPlaceCode), tblRouteNode.fkiPlaceTypeId, sPlaceCode, tblPlaceName.Latitude, tblPlaceName.Longitude, 
	tblRouteNode.fkiPlaceId, NULL, NULL,tblPlaceName.bActive
FROM OLASJun2023.dbo.tblRouteNode INNER JOIN OLASJun2023.dbo.tblPlaceName 
ON tblRouteNode.fkiPlaceId = tblPlaceName.pkiPlaceNameID
WHERE tblRouteNode.fkiPlaceId NOT IN (SELECT ISNULL(OlasID,-1) FROM Verified.Places)
GO

INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode, Latitude, Longitude, IntersectionOlasID, LtpsID, NeoID, IsCancelled)
SELECT DISTINCT 'Intersection - ' + sPlaceCode, tblRouteNode.fkiPlaceTypeId, sPlaceCode, tblIntersection.Latitude, tblIntersection.Longitude, 
	tblRouteNode.fkiPlaceId, NULL, NULL,tblIntersection.bActive
FROM OLASJun2023.dbo.tblRouteNode INNER JOIN OLASJun2023.dbo.tblIntersection 
ON tblRouteNode.fkiPlaceId = tblIntersection.pkiIntersectionId
WHERE tblRouteNode.fkiPlaceId NOT IN (SELECT ISNULL(IntersectionOlasID,-1) FROM Verified.Places)
GO

INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode, Latitude, Longitude, RoadlinkOlasID, LtpsID, NeoID, IsCancelled)
SELECT DISTINCT ISNULL(tblRoadLink.sRoadLinkName,'Road link - ' + sPlaceCode), tblRouteNode.fkiPlaceTypeId, sPlaceCode, NULL, NULL, 
	tblRouteNode.fkiPlaceId, NULL, NULL,tblRoadLink.bActive
FROM OLASJun2023.dbo.tblRouteNode INNER JOIN OLASJun2023.dbo.tblRoadLink 
ON tblRouteNode.fkiPlaceId = tblRoadLink.pkiRoadLinkID
WHERE tblRouteNode.fkiPlaceId NOT IN (SELECT ISNULL(RoadlinkOlasID,-1) FROM Verified.Places)
GO

INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode, Latitude, Longitude, TownOlasID, LtpsID, NeoID, IsCancelled)
SELECT DISTINCT ISNULL(tblTown.sTownDesc,'Town - ' + sPlaceCode),tblRouteNode.fkiPlaceTypeId, sPlaceCode, tblTown.Latitude, tblTown.Longitude, 
	tblRouteNode.fkiPlaceId, NULL, NULL,tblTown.bActive
FROM OLASJun2023.dbo.tblRouteNode INNER JOIN OLASJun2023.dbo.tblTown 
ON tblRouteNode.fkiPlaceId = tblTown.pkiTownID
WHERE tblRouteNode.fkiPlaceId NOT IN (SELECT ISNULL(TownOlasID,-1) FROM Verified.Places)
GO

INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode, Latitude, Longitude, RankOlasID, LtpsID, NeoID,IsCancelled)
SELECT DISTINCT ISNULL(tblAuthorisedRanks.sAuthorisedRankDesc,'Rank - ' + sPlaceCode)c,tblRouteNode.fkiPlaceTypeId, sPlaceCode, tblAuthorisedRanks.Latitude, tblAuthorisedRanks.Longitude, 
	tblRouteNode.fkiPlaceId, NULL, NULL,tblAuthorisedRanks.bActive
FROM OLASJun2023.dbo.tblRouteNode INNER JOIN OLASJun2023.dbo.tblAuthorisedRanks 
ON tblRouteNode.fkiPlaceId = tblAuthorisedRanks.pkiRankID
WHERE tblRouteNode.fkiPlaceId NOT IN (SELECT ISNULL(RankOlasID,-1) FROM Verified.Places)
GO

INSERT INTO Verified.Places([Description], PlaceTypeID, PlaceCode)
SELECT 'UNKNOWN',2,'UNKNOWN'
GO