/*
Anonymize API - A product of Monkey Consultancy B.V.
https://www.linkedin.com/company/monkey-consultancy/

Anonymization is the technique of making personal data unidentifiable by encrypting or removing identifying information.
Once a person can no longer be re-identified, the data is no longer considered personal and is not subject to GDPR regulations.

Data that has had personal identifying information removed can still have value, 
as characteristics such as general location, age range, generation, or gender can be useful for analysis.

Our Anonymize API can help you do just that. By setting a minimum and maximum range/distance, 
you can protect your customers' anonymity while still maintaining geolocation data that's useful for analytics. 
And, our solution is fully GDPR-compliant.

Would you like to see a demonstration? If so, please reach out to me: clint.huijbers@monkeyconsultancy.nl

More info? Read our article 'Prevent huge fines and damage to your reputation, anonymize your customer data!' here:
https://www.monkeyconsultancy.nl/2023/01/31/prevent-huge-fines-and-damage-to-your-reputation-anonymize-your-customer-data/
*/


/*
sp_invoke_external_rest_endpoint is a new feature (currently in preview), which is only available for Azure SQL Databases

Microsoft - about sp_invoke_external_rest_endpoint: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql
YouTube - about sp_invoke_external_rest_endpoint: https://www.youtube.com/watch?v=R6l-2XJyu2c

Examples: https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints
Script based on: https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-api-management.ipynb
*/


/*
--*** Initial setup ***:

-- make sure a database master key exists
if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin
	create master key encryption by password = 'LONg_Pa$$_w0rd!'
end

-- create database scoped credential
if exists(select * from sys.database_scoped_credentials where [name] = 'https://api.anonymize.nl/') begin
	drop database scoped credential [https://api.anonymize.nl/];
end

create database scoped credential [https://api.anonymize.nl/]
with identity = 'HTTPEndpointHeaders', secret = '{}';
go
*/

DROP TABLE IF EXISTS [dbo].[Customer];

--Populate a sample table:
CREATE TABLE [dbo].[Customer](
	[Id] [int] NULL,
	[FirstName] [nvarchar](250) NULL,
	[LastName] [nvarchar](250) NULL,
	[Gender] [nvarchar](250) NULL,
	[Street] [nvarchar](250) NULL,
	[HouseNumber] [nvarchar](250) NULL,
	[PostalCode] [nvarchar](250) NULL,
	[City] [nvarchar](250) NULL,
	[CountryCode] [nvarchar](250) NULL,
	[CountryName] [nvarchar](250) NULL,
	[Latitude] [numeric](12, 6) NULL,
	[Longitude] [numeric](12, 6) NULL,
	[EmailAddress] [nvarchar](250) NULL,
	[TelephoneNumber] [nvarchar](250) NULL,
	[BirthDate] [date] NULL
) ON [PRIMARY];

INSERT INTO [dbo].[Customer] ([Id],[Gender],[PostalCode],[CountryCode],[CountryName],[BirthDate])
SELECT
	1 AS [Id]
	,'Male' AS [Gender]
	,'1055 DR' AS [PostalCode]
	,'NL' AS [CountryCode]
	,'Netherlands' AS [CountryName]
	,CAST('1985-05-12' AS DATE) AS [BirthDate] --Convert it to a birthyear to make it more anonymous
UNION ALL
SELECT
	2 AS [Id]
	,'Female' AS [Gender]
	,'CA 90017' AS [PostalCode]
	,'US' AS [CountryCode]
	,'United States' AS [CountryName]
	,CAST('1992-02-23' AS DATE) AS [BirthDate] --Convert it to a birthyear to make it more anonymous
UNION ALL
SELECT
	3 AS [Id]
	,'Male' AS [Gender]
	,' 34040' AS [PostalCode]
	,'TR' AS [CountryCode]
	,'Turkey' AS [CountryName] --Using the previous name of the country, new official name is the Republic of Türkiye - https://en.wikipedia.org/wiki/Turkey
	,CAST('1989-11-01' AS DATE) AS [BirthDate] --Convert it to a birthyear to make it more anonymous


DECLARE
	@Id INT
	,@Gender NVARCHAR(250)
	,@PostalCode NVARCHAR(250)
	,@CountryCode NVARCHAR(250)
	,@CountryName NVARCHAR(250)
	,@BirthYear INT
	,@RangeMinDist INT = 50 -- Minimum distance from geolocation in meters
	,@RangeMaxDist INT = 250 -- Maximum distance from geolocation in meters


DECLARE CustomerCursor CURSOR FOR
	SELECT [Id],[Gender],[PostalCode],[CountryCode]
		,CAST(DATEPART(YEAR,[BirthDate]) AS INT) AS [BirthYear] --Convert into [BirthYear]
	FROM [dbo].[Customer]

OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor INTO @Id,@Gender,@PostalCode,@CountryCode,@BirthYear
WHILE (@@fetch_status = 0)
BEGIN

	DECLARE 
		@Body NVARCHAR(MAX)
		,@ret INT
		,@response NVARCHAR(MAX)
	
	--Set Body:
	SET @Body = 
	CONCAT('
	{
		"CountryCode": "',@CountryCode,'",
		"Location": "',@PostalCode,' ',@CountryName,'",
		"BirthYear": "',@BirthYear,'",
		"Gender": "',@Gender,'",
		"RangeMinDist": ',@RangeMinDist,',
		"RangeMaxDist": ',@RangeMaxDist,'
	}')
	
	--Execute POST request:
	EXEC @ret = sp_invoke_external_rest_endpoint 
		@url = N'https://api.anonymize.nl/?apikey=XXXXXXXXXXX',
		@method = 'POST', 
		@credential = [https://api.anonymize.nl/],
		@headers = N'{}',
		@payload = @Body,
		@response = @response OUTPUT

	--API Response:
	--SELECT 
	--	@ret AS ReturnCode,     
	--	JSON_QUERY(@response, '$.response') AS Response,
	--	JSON_QUERY(@response, '$.result') AS Result;

	--Update the customer data and replace it with synthetic data to be GDPR proof:
	UPDATE T --Target
	SET T.[FirstName] = S.[FirstName]
		,T.[LastName] = S.[LastName]
		,T.[Gender] = S.[Gender]
		,T.[Street] = S.[Street]
		,T.[HouseNumber] = S.[HouseNumber]
		,T.[PostalCode] = S.[PostalCode]
		,T.[City] = S.[City]
		,T.[CountryCode] = S.[CountryCode]
		,T.[CountryName] = S.[CountryName]
		,T.[Latitude] = S.[Latitude]
		,T.[Longitude] = S.[Longitude]
		,T.[EmailAddress] = S.[EmailAddress]
		,T.[TelephoneNumber] = S.[TelephoneNumber]
	FROM [dbo].[Customer] AS T --Target
		INNER JOIN
		(
			SELECT
				@Id AS [Id]
				,[FirstName]
				,[LastName]
				,[Gender]
				,[Street]
				,[HouseNumber]
				,[PostalCode]
				,[City]
				,[CountryCode]
				,[CountryName]
				,[Latitude]
				,[Longitude]
				,[EmailAddress]
				,[TelephoneNumber]
			FROM OPENJSON(@response, '$.result')
			WITH 
			(
				[FirstName] NVARCHAR(2000) '$.FirstName'
				,[LastName] NVARCHAR(2000) '$.LastName'
				,[Gender] NVARCHAR(2000) '$.Gender'
				,[Street] NVARCHAR(2000) '$.Street'
				,[HouseNumber] NVARCHAR(2000) '$.HouseNumber'
				,[PostalCode] NVARCHAR(2000) '$.PostalCode'
				,[City] NVARCHAR(2000) '$.City'
				,[CountryCode] NVARCHAR(2000) '$.CountryCode'
				,[CountryName] NVARCHAR(2000) '$.CountryName'
				,[Latitude] NVARCHAR(2000) '$.Latitude'
				,[Longitude] NVARCHAR(2000) '$.Longitude'
				,[EmailAddress] NVARCHAR(2000) '$.EmailAddress'
				,[TelephoneNumber] NVARCHAR(2000) '$.TelephoneNumber'
			)		
		) AS S --Source
			ON T.[Id] = S.[Id]


	FETCH NEXT FROM CustomerCursor INTO @Id,@Gender,@PostalCode,@CountryCode,@BirthYear
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

SELECT *
FROM [dbo].[Customer]