#!/usr/bin/env python
# coding: utf-8

# In[ ]:


"""rbb_data_upate is a script that updates the data from a chosen year in the main table rbb_vragenlijst"""

# Import needed libraries
import os
import pandas as pd
import numpy as np
import sqlalchemy
import pymysql
import re
import html
from sqlalchemy import String, Float, Integer, DECIMAL, Text, types
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import MetaData, Table, text
print(f"Pandas version: {pd.__version__} (pandas version used: 2.1.4)")
print(f"Numpy version: {np.__version__} (numpy version used: 2.0.25)")
print(f"sqlalchemy version: {sqlalchemy.__version__} (sqlalchemy version used: 1.4.6)")
print(f"pymysql version: {pymysql.__version__} (pymysql version used: 1.4.6)")
# Containrization? 


#############################################################################################
# Ophalen van de database verbinding inlog gegevens en het maken van de connectie
#############################################################################################
from get_secrets import SecretsManager
# Maak een instantie van de SecretsManager-klasse
secrets_manager = SecretsManager()
# Haal een secret op
db_credentials = secrets_manager.get_secret("dev/venster/rrb_amazon_db")

def create_connection():
    """Maakt een databaseverbinding met credentials uit AWS Secrets Manager."""
    if not db_credentials:
        raise Exception("Kon databasecredentials niet ophalen.")

    username = db_credentials["username"]
    password = db_credentials["password"]
    host = db_credentials["host"]
    database = db_credentials["dbname"]

    # Gebruik de credentials in de databaseverbinding
    engine = sqlalchemy.create_engine(
        f"mysql+pymysql://{username}:{password}@{host}/{database}"
    )
    return engine


#############################################################################################
# Ophalen van de inummers/oude kolomnamen en de bijbehorende nieuwe kolomnamen
#############################################################################################
def get_source_target_dict():
    engine = create_connection()  # Gebruik je bestaande functie om de connectie te maken
    
    query = "SELECT INUM, COLUMN_NAME FROM inum_mappings_rbb"
    
    with engine.connect() as connection:
        df = pd.read_sql(query, connection)  # Haal de data op als DataFrame
    
    # Zet de DataFrame om naar een dictionary
    source_target_dict = dict(zip(df["INUM"], df["COLUMN_NAME"]))

    return source_target_dict

# Voorbeeldgebruik
source_target_dict = get_source_target_dict()


#############################################################################################
# Function to convert MySQL datatype strings to SQLAlchemy type
#############################################################################################
def convert_to_sqlalchemy_type(data_type):
    data_type = data_type.lower()  # Zorg ervoor dat alles lowercase is

    if "varchar" in data_type:
        # Haal de lengte op (bijv. varchar(40) -> 40)
        length = int(data_type.split("(")[-1].strip(")")) if "(" in data_type else 255
        return String(length)
    elif "text" in data_type:
        return Text()  # SQLAlchemy behandelt dit als TEXT
    elif "decimal" in data_type:
        # Haal precisie en schaal op (bijv. decimal(14,4) -> DECIMAL(14,4))
        precision, scale = map(int, data_type.split("(")[-1].strip(")").split(","))
        return DECIMAL(precision, scale)
    elif "int" in data_type:
        return Integer()
    elif "float" in data_type:
        return Float()
    else:
        raise ValueError(f"❌ ERROR: Unsupported data type: {data_type}")
    

#############################################################################################
# Ophalen van de datatypes van de kolomen die weg worden geschreven naar de bestemming tabel
#############################################################################################
def get_export_dt_dict():
    engine = create_connection()  # Gebruik je bestaande databaseverbinding
    
    query = "SELECT INUM, COLUMN_NAME, DATA_TYPE_RBB_VRAGENLIJST FROM inum_mappings_rbb WHERE actief = 1"
    
    with engine.connect() as connection:
        df = pd.read_sql(query, connection)  # Haal de data op als DataFrame

    df["converted_dt"] = df["DATA_TYPE_RBB_VRAGENLIJST"].apply(convert_to_sqlalchemy_type)
    
    # Zet de DataFrame om naar dictionaries
    export_dt_dict = dict(zip(df["INUM"], df["DATA_TYPE_RBB_VRAGENLIJST"]))  # Ruwe MySQL-datatypes
    export_dt_dict_converted = dict(zip(df["INUM"], df["converted_dt"]))  # SQLAlchemy-datatypes

    return export_dt_dict, export_dt_dict_converted

# Voorbeeldgebruik
export_dt_dict, export_dt_dict_converted = get_export_dt_dict()


#############################################################################################
# Verwijderd alle rijen van de bestemming tabel voor het jaar dat je wilt verversen
#############################################################################################

def delete_existing_rows(engine, destination_table, year):
    with engine.begin() as conn:  # Use `begin()` to ensure the transaction is committed
        # Count rows with year = {year} before deletion
        count_query = sqlalchemy.text(f"SELECT COUNT(*) FROM {destination_table} WHERE year = {year}")
        initial_count = conn.execute(count_query).scalar()
        
        if initial_count > 0:
            # Delete rows with year = {year}
            delete_query = sqlalchemy.text(f"DELETE FROM {destination_table} WHERE year = {year}")
            result = conn.execute(delete_query)
            
            # Commit is automatically handled with `begin()` context manager
            print(f"Deleted {result.rowcount} rows from {destination_table}.")
            
            # Count rows again after deletion to confirm
            final_count = conn.execute(count_query).scalar()
            print(f"Rows with year {year} before deletion: {initial_count}")
            print(f"Rows with year {year} after deletion: {final_count}")
        else:
            print(f"No rows with year {year} found.")


#############################################################################################
# Importing all specified columns from data_upd_{year} into a pandas dataframe. 
#############################################################################################

def extract_data(engine, source_table, year):
    query = f"SELECT * FROM {source_table}"
    df = pd.read_sql(query, engine)

    # Fix encoding issues early
    df = fix_encoding_errors(df)

    # Step 1: Remove the year prefix (e.g., "i2024.") from column names
    prefix = f"i{year}."
    df = df.rename(columns=lambda x: x.replace(prefix, "") if isinstance(x, str) else x)

    # Create a copy of the original column names (INUMs) for later use
    original_columns = df.columns.tolist()

    # Step 2: Map column names for reference (without renaming the actual columns)
    mapped_columns = {col: source_target_dict.get(col, col) for col in original_columns}
    
    # Store the mapping as an attribute of the DataFrame for easy access
    df.attrs['mapped_columns'] = mapped_columns

    print(f"✅ Extracted data from {source_table} with original INUM column names preserved.")
    return df


#############################################################################################
# Ophalen van een lijst met alle kolommen die gemapped moeten worden (het omzetten van een waarde zoals bijv. 'Ongeveer evenveel zelf als uitbesteed' naar 0.5)
# De waarde correspondeert met het getal achter de functie map_values1() die zich bevind in transform data functie
#############################################################################################
def get_mapped_columns(serie):
    engine = create_connection()  # Gebruik je bestaande databaseverbinding
    
    query = f"SELECT INUM FROM inum_mappings_rbb WHERE mapping = {serie} and actief = 1"
    
    with engine.connect() as connection:
        df = pd.read_sql(query, connection)  # Haal de data op als DataFrame

    return df["INUM"].tolist()  # Zet de kolomnamen om in een lijst

#############################################################################################
# Ophalen van een lijst met alle kolommen die niet meegneomen (meer) hoeven worden
#############################################################################################
def get_inactive_columns():
    engine = create_connection()  # Gebruik je bestaande databaseverbinding
    
    query = "SELECT INUM FROM inum_mappings_rbb WHERE actief = 0"
    
    with engine.connect() as connection:
        df = pd.read_sql(query, connection)  # Haal de data op als DataFrame

    return df["INUM"].tolist()  # Zet de kolomnamen om in een lijst


#############################################################################################
# Ophalen van een lijst met data type voor alle kolomen, we verdelen ze onder in decimaal en int kolom lijsten
#############################################################################################
def get_column_types():
    engine = create_connection()  # Gebruik je bestaande databaseverbinding
    
    query = "SELECT INUM, DATA_TYPE_RBB_VRAGENLIJST FROM inum_mappings_rbb where actief = 1 and (berekening IS NULL or berekening = '')"
    
    with engine.connect() as connection:
        df = pd.read_sql(query, connection)  # Haal de data op als DataFrame

    # Filter de kolommen per datatype
    decimal_columns = df[df["DATA_TYPE_RBB_VRAGENLIJST"].str.contains("decimal", case=False, na=False)]["INUM"].tolist()
    integer_columns = df[df["DATA_TYPE_RBB_VRAGENLIJST"].str.contains("int", case=False, na=False)]["INUM"].tolist()

    return decimal_columns, integer_columns

#############################################################################################
# Fix encoding errors like ambiëren → ambiëren and &gt; → >, &lt; → <, &amp; → &
#############################################################################################

def fix_encoding_errors(df):
    def has_mojibake_pattern(text):
        return bool(re.search(r'[䢞‰Ÿ©±]', text))

    for col in df.select_dtypes(include=['object']).columns:
        try:
            before = df[col].dropna().astype(str).head(5).tolist()

            def fix(val):
                if not isinstance(val, str):
                    return val

                # HTML-unescape (e.g., &lt; → <)
                val = html.unescape(val)

                # Try latin1 → utf8
                try:
                    fixed = val.encode('latin1').decode('utf8')
                    if has_mojibake_pattern(val):
                        return fixed
                except Exception:
                    pass

                # Try utf8 → latin1
                try:
                    fixed = val.encode('utf8').decode('latin1')
                    if has_mojibake_pattern(val):
                        return fixed
                except Exception:
                    pass

                return val  # fallback if no fix applied

            df[col] = df[col].apply(fix)

            after = df[col].dropna().astype(str).head(5).tolist()
            if before != after:
                print(f"🛠️ Encoding fix applied in column: {col}")

        except Exception as e:
            print(f"[encoding-fix] ⚠️ Could not fix encoding for column '{col}': {e}")
    return df


#############################################################################################
# Transforming the values suitable for export to RBB_vragenlijst
#############################################################################################

def transform_data(df, year):
    
    def map_values1(series):
        series = series.replace({
            '1': 'Trede 1',
            '2': 'Trede 2',
            '3': 'Trede 3',
            '4': 'Trede 4',
            '5': 'Trede 5',
            '': np.nan,
            'nan': np.nan,
            np.nan: np.nan
        })

        # Laat bestaande 'Trede X' intact
        series = series.apply(lambda x: x if isinstance(x, str) and x.lower().startswith('trede') else x)
        return series

    # Check of 'naam_organisatie' bestaat voordat we de transformatie uitvoeren
    if 'naam_organisatie' in df.columns:
        mask = df['naam_organisatie'].str.startswith(('Gemeente ', 'gemeente ', 'Gemeenten '), na=False)

        df.loc[mask, 'naam_organisatie'] = df.loc[mask, 'naam_organisatie']\
            .str.replace('Gemeente ', '', regex=False)\
            .str.replace('gemeente ', '', regex=False)\
            .str.replace('Gemeenten ', '', regex=False)

    # Controleer of 'aantal_inwoners' bestaat voordat we de transformatie uitvoeren
    if 'aantal_inwoners' in df.columns:
        df['aantal_inwoners'] = pd.to_numeric(df['aantal_inwoners'], errors='coerce').astype(pd.Int64Dtype())

    # Categorizing the values of multiple columns with floats using a function
    columnmap1 = get_mapped_columns(1)
    for column in columnmap1:
        if column in df.columns:  # Controleer of de kolom bestaat
            df[column] = map_values1(df[column])

    # Adds a column to the dataframe where all values are 'year'
    df['year'] = year

    # Ophalen van de lijsten
    decimal_columns, integer_columns = get_column_types()

    for column in decimal_columns:  
        if column in df.columns and df[column].dtype != 'float64':  
            df[column] = df[column].astype(str).str.replace(',', '.', regex=False)  
            df[column] = pd.to_numeric(df[column], errors='coerce').astype('float64')  

    for column in integer_columns:
        if column in df.columns and df[column].dtype != 'Int64':
            df[column] = pd.to_numeric(df[column], errors='coerce').astype('Int64')

    # Making sure that missing values in object type columns are 'None'
    object_columns = df.select_dtypes(include=['object']).columns
    for col in object_columns:
        df[col] = df[col].where(pd.notna(df[col]), None)

    # Ophalen van kolommen die we moeten droppen
    columns_inactive = get_inactive_columns()
    
    # Drop alleen kolommen als ze in de DataFrame staan
    df = df.drop(columns=[col for col in columns_inactive if col in df.columns])

    return df


#############################################################################################
# Het wegschrijven van de data, als er nieuwe kolomen zijn bij gekomen worden deze aan de bestemming tabel eerst toegevoegd.
# Kolomen die niet in de inum_mappings_rbb tabel staan worden niet meegenomen.
#############################################################################################
def load_data(df, table_name, export_dt_dict):
    engine = create_connection()  # Gebruik je bestaande databaseverbinding
    metadata = MetaData()
    
    with engine.connect() as connection:
        # Haal bestaande kolommen op uit de target tabel
        existing_columns = []
        if engine.dialect.has_table(connection, table_name):  # Controleer of de tabel bestaat
            table = Table(table_name, metadata, autoload_with=engine)
            existing_columns = table.columns.keys()
        
        # **Verwijder kolommen uit df die niet in export_dt_dict staan**
        columns_to_keep = set(export_dt_dict.keys()).union({'year'})  
        columns_to_drop = [col for col in df.columns if col not in columns_to_keep]
        
        if columns_to_drop:
            print(f"🗑️ Kolommen die niet in export_dt_dict staan, worden verwijderd uit df: {columns_to_drop}")
            df.drop(columns=columns_to_drop, inplace=True, errors='ignore')

        # **Vind kolommen die nog niet in de database staan**
        new_columns = [col for col in df.columns if col not in existing_columns]

        if new_columns:
            print(f"Nieuwe kolommen gevonden: {new_columns}. Deze worden toegevoegd aan de database.")

            # Lijst om ongeldige kolommen uit df te verwijderen
            columns_to_drop = []

            # Voer ALTER TABLE statements uit om nieuwe kolommen toe te voegen
            for col in new_columns:
                col_type = export_dt_dict[col]  # Haal het datatype op uit export_dt_dict

                if col in export_dt_dict:

                    col_type = str(export_dt_dict[col])  # Converteer naar string
                    alter_query = text(f"ALTER TABLE `{table_name}` ADD COLUMN `{col}` {col_type};")
                    
                    try:
                        connection.execute(alter_query)
                        print(f"Kolom toegevoegd: {col} ({col_type})")
                    except SQLAlchemyError as err:
                        print(f"Error bij toevoegen van kolom {col}: {err}")
        
        # Voeg de data toe aan de database
        try:
            # Adjust the dtype mapping for calculated columns
            dtype_mapping = get_dtype_mapping(df)
            # Load the data
            df.to_sql(name=table_name, con=engine, 
                    if_exists='append',  
                    index=False,  
                    dtype=dtype_mapping
                    )
            print(f"Loaded {len(df)} rows into {table_name}.")
            print("Data inserted successfully.")
        except SQLAlchemyError as err:
            print(f"Error inserting data: {err}")


#######################################################
#   Creer een lijst met datatype van de berekende kolomen behalve rbb_id en year. Standaard op decimal(18,4)
#######################################################
def get_dtype_mapping(df):
    # Haal alle kolomnamen op
    column_names = list(df.columns)

    # Standaard alle kolommen als DECIMAL(18,4)
    dtype_mapping = {col: types.DECIMAL(18, 4) for col in column_names}

    # Specifieke kolommen als INTEGER
    if 'RBB_id' in dtype_mapping:
        dtype_mapping['RBB_id'] = types.INTEGER
    if 'year' in dtype_mapping:
        dtype_mapping['year'] = types.INTEGER
    if 'u_id' in dtype_mapping:
        dtype_mapping['u_id'] = types.INTEGER

    return dtype_mapping


#############################################################################################
# Functie die leest welke organisatie minimaal 2 vragen hebben ingevuld. Zoja, zal deze functie de tabellen doet_mee en Organisaties aanvullen
#############################################################################################

def doet_mee(year: int,
             df: pd.DataFrame,
             *,
             min_percentage: float = 0.5,
             table: str = "RBB_doetmee_auto") -> None:
    """
    Bouwt de slice <year> in <table> opnieuw op.

    • ‘doet_mee’ = 1 zodra er ≥ min_percentage antwoorden in één rij staan
    • Haalt – indien beschikbaar – de naam uit vorig jaar zodat die stabiel blijft
    • Verwijdert eerst de bestaande rijen voor <year>, schrijft daarna de nieuwe set
    """

    print(f"\n--- doet_mee update for {year} ---")

    # ── 1. Filter op jaar ───────────────────────────────────────────────
    df_y = df[df["year"] == year].copy()
    if df_y.empty:
        print("Geen data voor dit jaar; niets te doen.")
        return

    # ── 2. Blanco's → NaN; bereken ingevulde percentage ─────────────────
    meta = {"u_id", "Organisatienaam", "year"}
    ans_cols = [c for c in df_y.columns if c not in meta]
    df_y[ans_cols] = df_y[ans_cols].replace(r'^\s*$', np.nan, regex=True)

    total_questions = len(ans_cols)
    if total_questions == 0:
        print("⚠️ Geen vragen gevonden om te beoordelen.")
        return

    filled_counts = (
        df_y.set_index("u_id")[ans_cols]
            .notna()
            .sum(axis=1)
            .reset_index(name="aantal_vragen")
    )
    filled_counts["percentage"] = filled_counts["aantal_vragen"] / total_questions

    # ── 3. Bepaal naamorganisatie ────────────────────────────────────────
    eng = create_connection()
    prev = pd.read_sql(
        f"SELECT u_id, naam_organisatie AS naam_prev "
        f"FROM {table} WHERE year = {year-1}", eng
    )

    now = (df_y.groupby("u_id")["Organisatienaam"]
           .first()
           .reset_index()
           .rename(columns={"Organisatienaam": "naam_now"}))

    c = (filled_counts
         .merge(prev, on="u_id", how="left")
         .merge(now, on="u_id", how="left"))

    c["naam_organisatie"] = c["naam_prev"].fillna(c["naam_now"])

    # ── 4. Vlag ‘doet_mee’ ───────────────────────────────────────────────
    c["doet_mee"] = (c["percentage"] >= min_percentage).astype(int)

    out = c[["u_id", "naam_organisatie"]].copy()
    out["year"] = year
    out["doet_mee"] = c["doet_mee"]

    # ── 5. Schrijf naar DB ───────────────────────────────────────────────
    delete_existing_rows(eng, table, year)
    with eng.begin() as conn:
        conn.execute(
            text(f"""
                INSERT INTO {table}
                    (u_id, naam_organisatie, year, doet_mee)
                VALUES
                    (:u_id, :naam_organisatie, :year, :doet_mee)
            """),
            out.to_dict("records")
        )

    print(f"✔︎ {len(out)} rijen opgeslagen in {table} voor {year}")


#############################################################################################
# Functie die alle andere functies in de juiste volgorde aanroept. Input is het jaar dat je wil verversen en de table waar je naar toe wilt schirjven
# Hij haalt data op uit data_upd_{year} dus de tabel van dat jaar moet wel bestaan (bijv. 2023 moet de tabel data_upd_2023 wel bestaan in de venster_voor_bedrijfsvoering database)
#############################################################################################
def update_year(year, destination_table):
    print(f"\n--- Start update process for year {year} ---")

    source_table = f'data_upd_{year}'
    engine = create_connection()

    try:
        print(f"Extracting data from {source_table}...")
        table = extract_data(engine, source_table, year)
        print(f"✅ Data extracted successfully from {source_table}. Rows: {len(table)}")
    except Exception as e:
        print(f"❌ ERROR: Failed to extract data from {source_table}. Reason: {e}")
        return
    
    try:
        print("Transforming data...")
        table_transformed = transform_data(table, year)
        print(f"✅ Data transformation completed successfully. Rows: {len(table_transformed)}")
    except Exception as e:
        print(f"❌ ERROR: Failed to transform data. Reason: {e}")
        return
    
    try:
        print(f"Deleting existing rows from {destination_table} for year {year}...")
        delete_existing_rows(engine, destination_table, year)
        print(f"✅ Old data deleted successfully from {destination_table}.")
    except SQLAlchemyError as e:
        print(f"❌ SQLAlchemy ERROR: Failed to delete data from {destination_table}. Reason: {e}")
        return
    except Exception as e:
        print(f"❌ ERROR: Unexpected issue during deletion from {destination_table}. Reason: {e}")
        return    

    try:
        print(f"Loading transformed data into {destination_table}...")
        load_data(table_transformed, destination_table, export_dt_dict)
        print(f"✅ Data successfully loaded into {destination_table}.")
    except SQLAlchemyError as e:
        print(f"❌ SQLAlchemy ERROR: Failed to load data into {destination_table}. Reason: {e}")
        return
    except Exception as e:
        print(f"❌ ERROR: Unexpected issue during data load. Reason: {e}")
        return
    
    try:
        print("Updating participation flags in RBB_doetmee_auto...")
        doet_mee(year, table_transformed)            # <-- THE CALL
        print("✅ Participation flags updated successfully.")
    except Exception as e:
        print(f"❌ ERROR: Failed to update participation flags. Reason: {e}")

    print(f"--- ✅ Update process for year {year} completed successfully ---\n")


if __name__ == '__main__':
    #
    # Als er in het platform data van 2023 wordt aangepast en data_upd_2023 wordt bijgewerkt kan je deze aanzetten om dat jaar ook bij te werken
    #
    # update_year(2023, 'RBB_vragenlijst')

    # Het huidige jaar dat we data ophalen (halen in 2025 data op voor 2024)
    update_year(2024, 'RBB_vragenlijst')