-- OPEN SYMMETRIC KEY MasterKey DECRYPTION BY CERTIFICATE MasterCertificate;

SELECT 
	EDS.ExternalDataLocation,
	EDS.ExternalDataID,
	EDS.ExternalUserName,
	-- CONVERT(varchar(1000), DecryptByKey(ExternalPassword)) AS ExternalPassword,
	ExternalPassword AS ExternalPassword,
	EDS.DataSourceTypeID,
	DST.Description AS DataSourceType,
	EDS.ExternalProvider,
	COALESCE(L.DataContractDataDate,COALESCE(EDS.FirstDataDate,'2000-01-01 00:00')) AS LastFetchTime,
	EDS.Description,
	COALESCE(T.TableNames,'ExternalData') AS TableNames,
	COALESCE(EDS.ImportFolder,'') AS LocalFolder,
	EDS.ExternalDataSourceID
FROM dbo.ExternalDataSource EDS
JOIN dbo.DataSourceType DST ON EDS.DataSourceTypeID = DST.DataSourceTypeID
CROSS JOIN LATERAL
(
	SELECT
		DI.DataContractDataDate
	FROM dbo.DataImportDataContract DI
	WHERE DI.DataSourceTypeID = EDS.DataSourceTypeID
		AND DI.DataImportStatus = 'Success'
	ORDER BY DI.DataContractDataDate DESC
	LIMIT 1
) L
CROSS JOIN LATERAL
(
	SELECT STRING_AGG(TableName,',') TableNames FROM dbo.DataSourceTypeTable WHERE DataSourceTypeID = DST.DataSourceTypeID
) T
WHERE EDS.ExternalDataSourceID = @ExternalDataSourceID
-- WHERE EDS.ExternalDataSourceID = 27
	AND EDS.Enabled::int = 1
	AND EDS.ExternalProvider = 'AWS-S3-CSV';

-- CLOSE SYMMETRIC KEY MasterKey;