from sklearn import datasets

digits = datasets.load_digits()
X = digits.data
n_classes = len(digits.target_names)

from sklearn.ensemble import RandomForestClassifier

clf = RandomForestClassifier(n_estimators=512, max_depth=7, min_samples_leaf=30, random_state = 1960)
clf.fit(digits.data, digits.target)

#clf.__dict__

"""
Explanation: Scikit-Learn Model Deployment Use Case
In this case, we will build a very large ensemble model (here, Random Foreast with 512 trees) on a digits dataset  (not very original !!!) and generate a SQL code for deployment using the web service. 
We then execute the SQL code on a local database (postgresql) and compare the SQL execution result with scikit-learn predict/predict_proba/.predict_log_proba result. 
Both results are stored in pandas dataframes. 
Build a scikit-learn model
End of explanation
"""




def test_ws_sql_gen(pickle_data):
    WS_URL="http://localhost:1888/model"
    b64_data = base64.b64encode(pickle_data).decode('utf-8')
    data={"Name":"model1", "PickleData":b64_data , "SQLDialect":"postgresql"}
    r = requests.post(WS_URL, json=data)
    #print(r.__dict__)
    content = r.json()
    # print(content)
    lSQL = content["model"]["SQLGenrationResult"][0]["SQL"]
    return lSQL;



pickle_data = pickle.dumps(clf)
lSQL = test_ws_sql_gen(pickle_data)
N = len(lSQL)
P = 4000
print(lSQL[0:P] + "..." + lSQL[N//2:(N//2 + P)] + "..." + lSQL[-P:])

"""
Explanation: Generate SQL Code from the Model
End of explanation
"""


# save the dataset in a database table

engine = sa.create_engine('postgresql://db:db@localhost/db?port=5432' , echo=False)
conn = engine.connect()

lTable = pd.DataFrame(digits.data);
lTable.columns = ['Feature_' + str(c) for c in range(digits.data.shape[1])]
lTable['KEY'] = range(lTable.shape[0])
lTable.to_sql("INPUT_DATA" , conn,   if_exists='replace', index=False)


sql_output = pd.read_sql(lSQL , conn);
sql_output = sql_output.sort_values(by='KEY').reset_index(drop=True)
conn.close()

sql_output.sample(12, random_state=1960)

sql_output.Decision.value_counts()

"""
Explanation: Execute the SQL Code
End of explanation
"""


skl_outputs = pd.DataFrame()
skl_output_key = pd.DataFrame(list(range(X.shape[0])), columns=['KEY']);
skl_output_score = pd.DataFrame(columns=['Score_' + str(c) for c in range(n_classes)]);
skl_output_proba = pd.DataFrame(clf.predict_proba(X), columns=['Proba_' + str(c) for c in range(n_classes)])
skl_output_log_proba = pd.DataFrame(clf.predict_log_proba(X), columns=['LogProba_' + str(c) for c in range(n_classes)])
skl_output_decision = pd.DataFrame(clf.predict(X), columns=['Decision'])
skl_output = pd.concat([skl_output_key, skl_output_score, skl_output_proba, skl_output_log_proba, skl_output_decision] , axis=1)
skl_output.sample(12, random_state=1960)


"""
Explanation: Scikit-learn Prediction
End of explanation
"""


sql_skl_join = skl_output.join(sql_output , how='left', on='KEY', lsuffix='_skl', rsuffix='_sql')

sql_skl_join.sample(12, random_state=1960)

condition = (sql_skl_join.Decision_sql != sql_skl_join.Decision_skl)
sql_skl_join[condition]


"""
Explanation: Comparing the SQL and Scikit-learn Predictions
End of explanation
"""


