url = "jdbc:sqlserver://ukcdmdb1.database.windows.net;databaseName=dyfedpowyscommunitymessaging"
driver: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
user = "dyfedpowyscommunitymessagingread"
password = "dsN3vrZVRxBJnsnk"
country = "GB"

sql = """
SELECT
  'U' op,
  CAST(p.SerID AS VARCHAR) + '.' + CAST (a.SerID AS VARCHAR) id,
  ISNULL(p.Firstname, '') + ' ' + ISNULL(p.Lastname, '') name,
  CASE
    WHEN  BusinessType > 0 THEN 'company'
    ELSE 'private'
  END category,
  'sms' [contact-1-type],
  CASE WHEN MobilePhoneYN = 1 AND MobileNumber IS NOT NULL AND MobileNumberPrefix IS NOT NULL
    THEN SUBSTRING(MobileNumberPrefix, 2, LEN(MobileNumberPrefix)) + REPLACE(SUBSTRING(MobileNumber, PATINDEX('%[^0]%', MobileNumber + '.'), LEN(MobileNumber)), ' ', '')
  ELSE ''
  END [contact-1-address],
  'voice' [contact-2-type],
  CASE WHEN HomePhoneYN = 1 AND HomePhone IS NOT NULL AND HomePhonePrefix IS NOT NULL
    THEN SUBSTRING(HomePhonePrefix, 2, LEN(HomePhonePrefix)) + REPLACE(SUBSTRING(HomePhone, PATINDEX('%[^0]%', HomePhone + '.'), LEN(HomePhone)), ' ', '')
  ELSE ''
  END [contact-2-address],
  'email' [contact-3-type],
  CASE WHEN EmailAddressYN = 1 AND EmailAddress IS NOT NULL
    THEN p.EmailAddress
  ELSE ''
  END [contact-3-address],
  'smartphone' [contact-4-type],
  CASE WHEN AppYN = 1 AND DeviceID IS NOT NULL AND p.DeviceType IS NOT NULL
    THEN p.DeviceType + ':' + p.DeviceID
  ELSE ''
  END [contact-4-address],
  AgeRange [birthyear-agerange],
  Ethnicity [option-ethnicity],
  Nationality [option-nationality],
  Disability [option-disability],
  NWatchYN [option-nhw_role],
  Gender [option-gender],
  Religion [option-religion],
  Source [option-source],
  BusinessType [option-business_type],
  CASE Language
  WHEN 'C' THEN 'cv'
  ELSE 'en'
  END [meta-language],
  Interests [option-interests],
  Address_StreetName [meta-streetname],
  Address_HouseNumber [meta-housenumber],
  Address_HouseName [meta-housename],
  CASE
    WHEN  BusinessType > 0 THEN 'company'
    ELSE 'private'
  END [meta-usertype],

  Latitude [adr-1-lat],
  Longitude [adr-1-lon],
  Address_HouseNumber + ' ' + Address_StreetName [adr-1-address],
  Address_Zipcode [adr-1-zip],
  Address_Place [adr-1-city]
FROM
  PersonAddress a JOIN Person p ON a.PersonSerID=p.SerID
WHERE a.UpdatedOn > DATEADD(year, """${year}"""-1900, DATEADD(month, """${month}"""-1, DATEADD(day, """${day}"""-1, 0)))
  OR p.UpdatedOn > DATEADD(year, """${year}"""-1900, DATEADD(month, """${month}"""-1, DATEADD(day, """${day}"""-1, 0)))
  OR p.CreatedOn > DATEADD(year, """${year}"""-1900, DATEADD(month, """${month}"""-1, DATEADD(day, """${day}"""-1, 0)))
  OR p.UpdatedOn > DATEADD(year, """${year}"""-1900, DATEADD(month, """${month}"""-1, DATEADD(day, """${day}"""-1, 0)))
UNION ALL
SELECT
  'D'                                                         op,
  CAST(PersonSerId AS VARCHAR) + '.' + CAST(SerId AS VARCHAR) + '-0' id,
  ''                                                        name,
  'private'                                                 category,
  'sms'                                                     [contact-1-type],
  ''                                                        [contact-1-address],
  'voice'                                                   [contact-2-type],
  ''                                                        [contact-2-address],
  'email'                                                   [contact-3-type],
  ''                                                        [contact-3-address],
  'smartphone'                                              [contact-4-type],
  ''                                                        [contact-4-address],
  NULL [birthyear-agerange],
  NULL [option-ethnicity],
  NULL [option-nationality],
  NULL [option-disability],
  NULL [option-nhw_role],
  NULL [option-gender],
  NULL [option-religion],
  NULL [option-source],
  NULL [option-business_type],
  NULL [meta-language],
  NULL [option-interests],
  NULL [meta-streetname],
  NULL [meta-housenumber],
  NULL [meta-housename],
  NULL [meta-usertype],
  NULL [adr-1-lat],
  NULL [adr-1-lon],
  NULL [adr-1-address],
  NULL [adr-1-zip],
  NULL [adr-1-city]
FROM DeletedPersonAddresses
WHERE DeletedOn > DATEADD(year, """${year}"""-1900, DATEADD(month, """${month}"""-1, DATEADD(day, """${day}"""-1, 0)))
"""
schemaSpec = """
{
  "fields" : [ {
    "@type" : "group",
    "field" : "type",
    "name" : "User Type",
    "groupEntries" : [ {
      "name" : "Individual",
      "fields" : [ {
        "@type" : "fixed",
        "field" : "metaData.usertype",
        "value" : "private",
        "name" : ""
      }, {
        "@type" : "select",
        "field" : "metaData.agerange",
        "name" : "Age Range",
        "options" : [ {
          "name" : "0 - 18",
          "value" : "6810"
        }, {
          "name" : "19 - 34",
          "value" : "7037"
        }, {
          "name" : "35 - 54",
          "value" : "7264"
        }, {
          "name" : "55 - 74",
          "value" : "7491"
        }, {
          "name" : "75+",
          "value" : "7718"
        } ]
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.gender",
        "name" : "Gender",
        "multiselct" : false,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'GENDER' and EnabledYN = 1"
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.ethnicity",
        "name" : "Ethnicity",
        "multiselct" : true,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'ETHNICITY' and EnabledYN = 1"
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.nationality",
        "name" : "Nationality",
        "multiselct" : true,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'NATIONALITY' and EnabledYN = 1"
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.religion",
        "name" : "Religion",
        "multiselct" : false,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'RELIGION' and EnabledYN = 1"
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.disability",
        "name" : "Disablity",
        "multiselct" : true,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'DISABILITY' and EnabledYN = 1"
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.nhw_role",
        "name" : "NHW role",
        "multiselct" : true,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'NWYN' and EnabledYN = 1"
      } ]
    }, {
      "name" : "Business",
      "fields" : [ {
        "@type" : "fixed",
        "field" : "metaData.usertype",
        "value" : "company",
        "name" : ""
      }, {
        "@type" : "sqlOptions",
        "field" : "metaData.business_type",
        "name" : "Business Type",
        "multiselct" : false,
        "any" : "Any",
        "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'BUSINESSTYPE' and EnabledYN = 1"
      } ]
    }, {
      "name" : "Any",
      "fields" : [ ]
    } ]
  }, {
    "@type" : "sqlOptions",
    "field" : "metaData.interests",
    "name" : "Interests",
    "multiselct" : true,
    "any" : "None",
    "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'INTERESTS' and EnabledYN = 1"
  }, {
    "@type" : "sqlOptions",
    "field" : "metaData.source",
    "name" : "How did you hear about us",
    "multiselct" : true,
    "any" : "None",
    "sqlSelect" : "select LOV_ID, DisplayValue from LOV where SegmentKey = 'SOURCE' and EnabledYN = 1"
  } ]
}"""