#!/usr/bin/env python
# Copyright (c) TripleBlind Holdings, Inc. Confidential and Proprietary. All rights reserved.

import tripleblind as tb


tb.initialize(tb.config.example_user1["token"])

# Credentials for an Microsoft Azure demo database
host = "tripleblindazuredemo.database.windows.net"
port = 1433
database = "tripleblindazuredemo"
username = "tb_ro_user"
password = "D3m0U53r=Pa$$w0rd"
# NOTE: You should not hard-code a password for several reasons:
# - It can be viewed by other users on your team with Asset Retrieve permissions
# - The password cannot be changed later
#
# You should reference a secret stored on your Access Point using the CLI tool
#   tbadmin secrets set my_password "D3m0U53r=Pa$$w0rd"
# with a reference to the secret name, like this:
#  password = "{{my_password}}"


# Create the standard Patient view (see the README.txt for details)
#
# Note that this hospital stores the date of birth in separate columns which
# are concatenated into a single DATE field in the query.  Additionally the
# patient address is stored in a different table which must be joined.  Finally,
# many field names must be aliased to match the standard view.
q1 = """
SELECT
    p.PatientPK AS PatientID,
    CAST(CONCAT(p.BirthYear, '-', p.BirthMonth, '-', p.BirthDay) AS DATE) AS PatientDOB,
    a.City AS City,
    a.State AS State,
    a.ZIP AS PostalCode,
    p.Gender AS Sex
FROM fedPatient p
JOIN fedAddress a ON p.AddressFK = a.AddressPK
"""

source1_patient = tb.asset.MSSQLDatabase.create(
    host=host,
    port=port,
    database=database,
    username=username,
    password=password,
    name="DEMO - Hospital Data Federation, Patient DB 1",
    desc="""Standard view of the Patient database for Blue Sky Memorial
      Hospital, backed by an Azure database. This is part of the Hospital Data
      Federation demo.""",
    is_discoverable=True,
    allow_overwrite=True,
    query=q1,
)

# Create the standard Billed Visit view
#
# Again, note the conversion of date fields to a single DATE field in the query.
q2 = """
SELECT
    bv.PatientFK AS PatientID,
    CAST(CONCAT(bv.AdmitYear, '-', bv.AdmitMonth, '-', bv.AdmitDay) AS DATE) AS AdmitDate,
    CAST(CONCAT(bv.DichargeYear, '-', bv.DischargeMonth, '-', bv.DischargeDay) AS DATE) AS DischargeDate,
    bv.VisitType AS VisitType,
    b.DiagnosisCode AS BillingCode,
    b.ProcedureCode AS ProcedureCode,
    b.TotalCost AS NetCharges
FROM fedBilledVisit bv
JOIN fedBilling b ON bv.BillingFK = b.BillingPK
"""

source1_billedvisit = tb.asset.MSSQLDatabase.create(
    host=host,
    port=port,
    database=database,
    username=username,
    password=password,
    name="DEMO - Hospital Data Federation, Billed Visit DB 1",
    desc="""Standard view of the Billed Visit database for Blue Sky Memorial
      Hospital, backed by an Azure database. This is part of the Hospital Data
      Federation demo.""",
    is_discoverable=True,
    allow_overwrite=True,
    query=q2,
)
