# -*- coding: utf-8 -*-
"""
Created on Thu Apr  7 13:10:44 2022

@author: MarkvanKruistum
"""

import configparser
import pandas as pd
import sqlalchemy as sql
import logging
import sys

logger = logging.getLogger(__name__)


def init_logging(logger):
    '''
    Initialize logging formatter and handlers.
    Set logging levels.

    Parameters
    ----------
    logger : logging.Logger
        Logger object to use.

    Returns
    -------
    None.
    '''
    formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]')
    handler = logging.StreamHandler(sys.stdout)
    handler.setLevel(logging.DEBUG)
    handler.setFormatter(formatter)
    logger.addHandler(handler)
    logger.setLevel(logging.DEBUG)

def create_db_connection(config_path, programme_name):
    '''
    Read database details from config file and create a database engine object.

    Parameters
    ----------
    config_path : str
        Path to the config file containing the database credentials.
    programme_name : str
        Name of the programme. Possible values are 'gemeenten', 'waterschappen', 'rbb'. This determines the database.

    Returns
    -------
    engine : sqlachemy.engine.Engine
        SQLAlchemy Engine object with which connections to the database can be made.
    '''
    
    logger.debug("Getting database details from config file...")
    config = configparser.ConfigParser()
    try:
        config.read(config_path)
        server = config[programme_name]['server']
        database = config[programme_name]['database']
        user = config[programme_name]['user']
        password = config[programme_name]['password']
    except Exception:
        logger.exception("Reading config file failed")
        return

    try:
        engine = sql.create_engine("mysql+pymysql://{user}:{password}@{server}/{database}".format(user=user, password=password, server=server, database=database))
    except Exception:
        logger.exception("Creating database connection from connection string failed")
        return
    
    logger.debug("Database connection created succesfully")
    return engine

def read_excel_file(path, sheets):
    df_list = []
    for sheet in sheets:
        df = pd.read_excel(path, sheet_name = sheet)
        df_list.append(df)
    df = pd.concat(df_list)
    return df

def read_from_db(table_name, engine):
    logger.debug("Reading table from database...")
    
    try:
        df = pd.read_sql(table_name, engine)
    except Exception:
        logger.exception("Reading table from the database failed")
        return
    
    logger.debug("Dataframe read from database succesfully")
    return df

def remove_icode(df, year):
    df.columns = df.columns.str.replace('i{}.'.format(year), '', regex=True)
    return df

def write_to_db(engine, table_name, df, append=True):
    '''
    Write a specified DataFrame to a specified table in the database. 
    Can contain an append parameter indicating if rows should be appended or replaced.

    Parameters
    ----------
    engine : sqlachemy.engine.Engine
        SQLAlchemy Engine object with which connections to the database can be made.
    table_name : str
        Table name that the rows in the DataFrame are to be written to.
    df : DataFrame
        DataFrame containing rows to write to the database.
    append : boolean, optional
        Specifies if rows should be appended or replaced. The default is True (append).

    Returns
    -------
    None.
    '''
    
    mode = 'append' if append else 'replace'
    
    logger.debug("Writing dataframe to database...")
    
    if df is None or df.empty:
        logger.error("Empty or no dataframe given!")
        return
    
    try:
        df.to_sql(table_name, engine, index=False, if_exists=mode)
    except Exception:
        logger.exception("Writing rows to the database failed")
        return
    
    logger.debug("Dataframe written to database succesfully")

def main():
    print("Run one of the runners to read and write data!")
    
if __name__ == "__main__":
    main()