"""
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/
"""

import pyodbc
import pandas as pd
import json
import requests
from datetime import datetime
from requests.structures import CaseInsensitiveDict

"""
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 Turkiye - https://en.wikipedia.org/wiki/Turkey
	,CAST('1989-11-01' AS DATE) AS [BirthDate] --Convert it to a birthyear to make it more anonymous

"""

# Connect to the database
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=Dummy;Trusted_Connection=yes')
cursor = conn.cursor()

# Fetch customer records from the database
query = "SELECT * FROM [dbo].[Customer]"
df = pd.read_sql(query, conn)

#print(df)

print("")
print("----------------------------------------------")
print("")

# Update values from API
for index, row in df.iterrows():

    BirthYear = row["BirthDate"].year
    BirthYear = str(BirthYear) #without the '-01-01' suffix

    headers = CaseInsensitiveDict()
    headers["Content-Type"] = "application/json"

    url = 'https://api.anonymize.nl/?apikey=XXXXXXXXXXX'
    data = {
		"CountryCode": row["CountryCode"],
		"Location": row["PostalCode"]+" "+row["CountryName"],
		"BirthYear": BirthYear,
		"Gender": row["Gender"],
		"RangeMinDist": 100,
		"RangeMaxDist": 250
    }

    print("- POST Request body -")
    print("")
    print(data)
    print("")
    print("")
    response = requests.post(url, headers=headers, data=json.dumps(data))
    #print(response.status_code)
    #print(response.reason)
    #print("")

    SQLQuery = "UPDATE [dbo].[Customer] SET "+"FirstName = '"+str(response.json()['FirstName'])+"', "+"LastName = '"+str(response.json()['LastName'])+"', "+"Gender = '"+str(response.json()['Gender'])+"', "+"Street = '"+str(response.json()['Street'])+"', "+"HouseNumber = '"+str(response.json()['HouseNumber'])+"', "+"PostalCode = '"+str(response.json()['PostalCode'])+"', "+"City = '"+str(response.json()['City'])+"', "+"CountryCode = '"+str(response.json()['CountryCode'])+"', "+"CountryName = '"+str(response.json()['CountryName'])+"', "+"Latitude = '"+str(response.json()['Latitude'])+"', "+"Longitude = '"+str(response.json()['Longitude'])+"', "+"EmailAddress = '"+str(response.json()['EmailAddress'])+"', "+"TelephoneNumber = '"+str(response.json()['TelephoneNumber'])+"'"+" WHERE Id = '"+str(row['Id'])+"'"

    print("- Update statement -")
    print("")
    print(SQLQuery)
    print("")
    print("")

    cursor.execute(SQLQuery)
    conn.commit()

    print("----------------------------------------------")
    print("")
    print("")

# Close the database connection
conn.close()