# 1/ Create virtual environment: python -m venv venv
# 2/ Activate virtual environment: .\venv\Scripts\activate
# 3/ Install requirements: pip install -r requirements.txt
# 4/ Change input paths

import polars as PI_POLARS
import os as PI_OS
import pandas as PI_PANDAS

# Step 1/ Create variables
ZV_NU_THRESHOLD = 10000
ZV_ST_SOURCES_FOLDER =r'C:\300FMASTERCLASS\03_PYTHON_MC202602\01_PYTHON_MEETING_01\01_SOURCES'
ZV_ST_SOURCE_FILE_EKKO='20240510_152615_1_EKKO.csv'
ZV_ST_SOURCE_FILE_EKPO='20251025_180621_1_EKPO.csv'
ZV_ST_RESULTS_FOLDER=r'C:\300FMASTERCLASS\03_PYTHON_MC202602\01_PYTHON_MEETING_01\03_RESULTS'


# Step 2/ Functions
# 2.1/ Import
def FC_IMPORT_TEXT(
        ZVFCI_ST_SOURCE_FILE, 
        ZVFCI_ST_FOLDER, 
        ZVFCI_ST_DELIMITER
    ):     
    ZV_ST_FILE_PATH = PI_OS.path.join(ZVFCI_ST_FOLDER, ZVFCI_ST_SOURCE_FILE)

    ZV_DF_HEADER = PI_POLARS.read_csv(
        ZV_ST_FILE_PATH,
        separator=ZVFCI_ST_DELIMITER,
        encoding='utf8',
        has_header=True,
        n_rows=1,
        infer_schema_length=0,
        quote_char=None
    )
    ZV_LI_COLS = ZV_DF_HEADER.columns

    ZV_DI_SCHEMA_OVERRIDES = {ZV_COL: PI_POLARS.Utf8 for ZV_COL in ZV_LI_COLS}

    ZV_DF = PI_POLARS.read_csv(
        ZV_ST_FILE_PATH,
        separator=ZVFCI_ST_DELIMITER,
        encoding='utf8',
        ignore_errors=True,
        has_header=True,
        schema_overrides=ZV_DI_SCHEMA_OVERRIDES,
        infer_schema_length=0,
        null_values=[""],
        quote_char=None        

    ).fill_null('')

    return ZV_DF


# 2.2/ Export
def FC_EXPORT_EXCEL(ZVFCI_DF_INPUT, ZVFCI_ST_RESULTS_FOLDER, ZVFCI_ST_RESULTS_FILE):

    PI_OS.makedirs(ZVFCI_ST_RESULTS_FOLDER, exist_ok=True)

    ZV_ST_FILE_PATH = PI_OS.path.join(ZVFCI_ST_RESULTS_FOLDER, ZVFCI_ST_RESULTS_FILE)

    ZV_DF_PANDAS = ZVFCI_DF_INPUT.to_pandas()
    
    ZV_DF_PANDAS.to_excel(
        ZV_ST_FILE_PATH,
        index=False,
        engine='openpyxl'
    )

    print(f"✅ Excel file successfully exported to:\n{ZV_ST_FILE_PATH}")

# Step 3/ Import the files
ZV_DF_EKKO = FC_IMPORT_TEXT(ZV_ST_SOURCE_FILE_EKKO,ZV_ST_SOURCES_FOLDER,'\t')
ZV_DF_EKPO = FC_IMPORT_TEXT(ZV_ST_SOURCE_FILE_EKPO,ZV_ST_SOURCES_FOLDER,'|')

# Step 4/ EKKO
# 4.1/ Select / rename
ZV_DF_EKKO = (
    ZV_DF_EKKO
    .select(
        [
            'EBELN',
            'BEDAT',
            'ERNAM',
            'LIFNR'
        ]
    )
    .rename(
        {
            'EBELN':'EKKO_EBELN',
            'BEDAT':'EKKO_BEDAT',
            'ERNAM':'EKKO_ERNAM',
            'LIFNR':'EKKO_LIFNR',                                    
        }
    )
)

# Step 5/ EKPO
# 5.1/ Select / rename
ZV_DF_EKPO= (
    ZV_DF_EKPO
    .select(
        [
            'EBELN',
            'EBELP',
            'NETPR',
            'NETWR',
            'MATNR',
            'BPUMZ',
            'BPUMN'
        ]
    )
    .with_columns(
        [
            PI_POLARS.col('NETPR')
            .cast(PI_POLARS.Float64)
            .alias('NETPR'),

            PI_POLARS.col('NETWR')
            .cast(PI_POLARS.Float64)
            .alias('NETWR'),            

            PI_POLARS.col('BPUMZ')
            .cast(PI_POLARS.Float64)
            .alias('BPUMZ'),

            PI_POLARS.col('BPUMN')
            .cast(PI_POLARS.Float64)
            .alias('BPUMN')            
        ]
    )
    .rename(
        {
            'EBELN':'EKPO_EBELN',
            'EBELP':'EKPO_EBELP',
            'NETPR':'EKPO_NETPR',
            'NETWR':'EKPO_NETWR',
            'MATNR':'EKPO_MATNR',
            'BPUMZ':'EKPO_BPUMZ',
            'BPUMN':'EKPO_BPUMN',                                                               
        }
    )
)


# Step 6/ Join
ZV_DF_EKPO_EKKO = (
    ZV_DF_EKPO
    .join(
        ZV_DF_EKKO,
        left_on='EKPO_EBELN',
        right_on='EKKO_EBELN',
        how='inner',
        suffix='_2'
    )
)

# Step 7/ Add columns
ZV_DF_EKPO_EKKO = (
    ZV_DF_EKPO_EKKO
    .with_columns(
        [
            PI_POLARS.when(
                PI_POLARS.col('EKPO_BPUMZ') == 0
            )
            .then(
                PI_POLARS.lit(1)
            )
            .otherwise(
                PI_POLARS.col('EKPO_BPUMZ')
            )
            .alias('ZF_EKPO_BPUMZ'),

            PI_POLARS.when(
                PI_POLARS.col('EKPO_BPUMN') == 0
            )
            .then(
                PI_POLARS.lit(1)
            )
            .otherwise(
                PI_POLARS.col('EKPO_BPUMN')
            )            
            .alias('ZF_EKPO_BPUMN'),
        ]

    )
    .with_columns(
        (
            PI_POLARS.col('EKPO_NETPR') *
            (
                PI_POLARS.col('ZF_EKPO_BPUMN')/
                PI_POLARS.col('ZF_EKPO_BPUMZ')
            )
        )
        .alias('ZF_EKPO_NETPR_BPUMNBPUMZ')
    )
)

# Step 8/ Group_by
ZV_DF_TT_EKPO_EKKO_GROUP_BY = (
    ZV_DF_EKPO_EKKO
    .filter(
        (
            PI_POLARS.col('EKPO_MATNR') !=''
        ) & 
        (
            PI_POLARS.col('ZF_EKPO_NETPR_BPUMNBPUMZ') !=0
        )
    )
    .group_by(
        'EKPO_MATNR'
    )
    .agg(
        [
            PI_POLARS.col('ZF_EKPO_NETPR_BPUMNBPUMZ')
            .mean()
            .alias('ZF_EKPO_NETPR_BPUMNBPUMZ_MEAN')
        ]
    )
)

# Step 9/ Join
ZV_DF_EKPO_EKKO = (
    ZV_DF_EKPO_EKKO
    .join(
        ZV_DF_TT_EKPO_EKKO_GROUP_BY,
        on='EKPO_MATNR',
        how='inner',
        suffix='_2'
    )
)

# Step 10/ Add fields/ filter
ZV_DF_EKPO_EKKO_HIGHNETPR = (
    ZV_DF_EKPO_EKKO
    .with_columns(
        (
            (
                (
                    PI_POLARS.col('EKPO_NETPR') - 
                    PI_POLARS.col('ZF_EKPO_NETPR_BPUMNBPUMZ_MEAN')
                ) /
                PI_POLARS.col('ZF_EKPO_NETPR_BPUMNBPUMZ_MEAN')
            ) * 100
        )
        .alias('ZF_EKPO_NETPR_DIFF_PER')
    )
    .filter(
        (
            PI_POLARS.col('ZF_EKPO_NETPR_DIFF_PER') > 50
        ) & 
        (
            PI_POLARS.col('EKPO_NETWR')>ZV_NU_THRESHOLD
        )        
    )
)


# 11/ Export the result

ZV_ST_RESULTS_FILE = 'ZV_DF_EKPO_EKKO_HIGHNETPR.xlsx'
FC_EXPORT_EXCEL(ZV_DF_EKPO_EKKO_HIGHNETPR, ZV_ST_RESULTS_FOLDER, ZV_ST_RESULTS_FILE)