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

import tripleblind as tb


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

# Credentials for an Postgres demo database
host = "35.188.156.2"
port = 5432
database = "postgres"
username = "tb_readonly_user"
password = "iCantFryFishOnFridays33cp"
# 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 "iCantFryFishOnFridays33cp"
# 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 patient's Sex in a single character field
# which must be mapped to the standard "Male" or "Female" in the query.  Patient
# address information is also stored in a separate table which must be joined.
# Field names are also aliased to match the standard view.
q1 = """
SELECT
    p.id AS PatientID,
    p.dob AS PatientDOB,
    a.city AS City,
    a.state AS State,
    a.zip AS PostalCode,
    CASE p.sex
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END AS Sex
FROM patients p
JOIN addresses a ON p.addressid = a.addressid
"""

source3_patient = tb.asset.DatabaseDataset.create(
    connection=f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}",
    name="DEMO - Hospital Data Federation, Patient DB 3",
    desc="""Standard view of the Patient database for Harmony Research Hospital,
      backed by a Postgres database. This is part of the Hospital Data
      Federation demo.""",
    is_discoverable=True,
    allow_overwrite=True,
    query=q1,
)

# Create the standard Billed Visit view
#
# Note the visit type must be mapped as well as defining aliases for the
# fields to match the standard view.
q2 = """
SELECT
    e.patientid AS PatientID,
    e.admitdate AS AdmitDate,
    e.dischargedate AS DischargeDate,
    CASE e.visittype
        WHEN 'inp' THEN 'Inpatient'
        WHEN 'outp' THEN 'Outpatient'
    END AS VisitType,
    e.icdcmcode AS BillingCode,
    e.icdpcscode AS ProcedureCode,
    e.cost AS NetCharges
FROM encounters e
"""

source3_patient = tb.asset.DatabaseDataset.create(
    connection=f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}",
    name="DEMO - Hospital Data Federation, Billed Visit DB 3",
    desc="""Standard view of the Billed Visit database for Harmony Research
      Hospital, backed by a Postgres database. This is part of the Hospital Data
      Federation demo.""",
    is_discoverable=True,
    allow_overwrite=True,
    query=q2,
)
