import pandas as pd
import pyodbc
import configparser
import hashlib

# Load configuration from config.ini file
config = configparser.ConfigParser()
config.read('config.ini')

# Extract Values
db = config['server_group1']
servers = db['servers'].split(',')
database = db['database']
uid = db['uid']
pwd = db['pwd']
port = int(db['port'])
SALT = config['security']['salt']


# Create function to load configuration

def load_config(section, path='config.ini'):
    config = configparser.ConfigParser()
    config.read(path)
    db = config[section]
    return {
        'servers': db['servers'].split(','),
        'database': db['database'],
        'uid': db['uid'],
        'pwd': db['pwd'],
        'port': int(db['port'])
    }

# Create function to connect to Database with manual failover

def connect_manual_failover(servers, database, uid, pwd, port):
    for server in servers:
        print(f"Trying server: {server}")
        try:
            connection_string = (
                f"DRIVER={{SQL Server}};"
                f"SERVER={server};"
                f"DATABASE={database};"
                f"UID={uid};"
                f"PWD={pwd};"
                f"PORT={port};"
            )
            con = pyodbc.connect(connection_string)
            print(f"Connected to {server}")
            return con
        except Exception as e:
            print(f"Failed on {server}: {e}")
    raise Exception("All servers failed.")

# Create function to run query on a server group

def run_query_on_group(section, sql, params=None):
    cfg = load_config(section)
    con = connect_manual_failover(**cfg)
    df = pd.read_sql(sql, con, params=params)
    con.close()
    return df

def pseudonymise_data(value,prefix):
    if pd.isna(value):
        return value
    
    value = str(value).strip().lower()
    raw = f"{SALT}:{value}".encode('utf-8')
    hashed = hashlib.sha256(raw).hexdigest()[:12]
    return f"{prefix}_{hashed}"


# Establish Connection for SQL Server

con = connect_manual_failover(servers, database, uid, pwd, port)

# SQL for xdailyissues

sql_xdi = f"""

        SELECT [HB]
      ,[issue_date]
      ,[location]
      ,[costcentre]
      ,[quantity]
      ,[issue_type]
      ,[drugfull]
      ,[drug_packsize]
      ,[quantity_container]
      ,[quantity_doseunits]
      ,[issue_time]
  FROM [WHPSMS].[dbo].[WSK_prd_DailyIssues]

WHERE HB = 'ctm'
  AND costcentre LIKE '%DERMA%'
  AND expenditure_category = 'drug'
  AND EXISTS (
      SELECT 1
      FROM (VALUES
          ('adalim'),
          ('bimeki'),
          ('brodal'),
          ('certol'),
          ('dupilum'),
          ('etanerc'),
          ('guselk'),
          ('inflixim'),
          ('ixekizu'),
		  ('lebrik'),
		  ('nemoli'),
		  ('omalizuma'),
		  ('risankizumab'),
		  ('ritux'),
		  ('secukinu'),
		  ('tildrak'),
		  ('tralokin'),
		  ('ustekinum'),
		  ('spesolimab')
      ) v(prefix)
      WHERE drugfull LIKE v.prefix + '%'
  )

    """

df_xdi = run_query_on_group('server_group2',sql_xdi)

# Create a homecare issue flag

df_xdi['homecare_issue'] = (
    df_xdi['location']
    .str.contains('homecare', case=False, na=False)
    .astype(int)
)

# Pseudonymise data

df_xdi['costcentre'] = df_xdi['costcentre'].apply(lambda x: pseudonymise_data(x,'cc'))
df_xdi['location'] = df_xdi['location'].apply(lambda x: pseudonymise_data(x,'loc'))
df_xdi['drugfull'] = df_xdi['drugfull'].apply(lambda x: pseudonymise_data(x,'drug'))

# write df to csv

df_xdi.to_csv('xdi_data.csv', index=False)