# AUTOGENERATED! DO NOT EDIT! File to edit: nbs/30_traceable_edit_in_flask.ipynb (unless otherwise specified).

__all__ = ['get_static', 'edit_js', 'DefaultTemp', 'Editable']

# Cell
import pandas as pd
from datetime import datetime
import json
from sqlalchemy import create_engine as ce
from sqlalchemy import text
from jinja2 import Template

# Cell
from pathlib import Path
def get_static():
    import forgebox
    return Path(forgebox.__path__[0])/"static"

# Cell
def edit_js():
    with open(get_static()/"edit.js","r") as f:
        return f"<script>{f.read()}</script>"


class DefaultTemp(Template):
    """
    Jinjia template with some default render config
    """
    def render(self,dt):
        dt.update(dict(type=type,now = datetime.now()))
        return super().render(dt)

# Cell
from flask import request
from flask import g
from datetime import datetime

class Editable:
    def __init__(self,name,app,table_name,con,id_col,
                 log_con,log_table="editable_log",columns = None):
        """
        name: route name for url path,
            also it will be the task title appearning on the frontend
        app:flask app
        table_name: table to edit
        con:sqlachemy connnection, created by : con = sqlalchemy.create_engine
        id_col: a column with unique value
        log_con:sqlachemy connnection, for storaging change log
        """
        self.name = name
        self.app = app
        self.table_name = table_name
        self.con = con
        self.log_con = log_con
        self.columns = ",".join(columns) if columns!=None else "*"
        self.id_col = id_col

        self.t_workspace = self.load_temp(get_static()/"workspace.html")
        self.t_table = self.load_temp(get_static()/"table.html")
        self.assign()

    def assign(self):
        self.app.route(f"/{self.name}")(self.workspace)
        self.app.route(f"/{self.name}/df_api")(self.read_df)
        self.app.route(f"/{self.name}/save_api",
                       methods=["POST"])(self.save_data)

    def workspace(self):
        return self.t_workspace.render(dict(title=self.name,
                                                pk=self.id_col,
                                                edit_js = edit_js()))

    def save_data(self):
        data = json.loads(request.data)
        # update change and save log
        changes = data["changes"]
        log_df = pd.DataFrame(list(self.single_row(change) for change in changes))

        log_df["idx"] = log_df.idx.apply(str)
        log_df["original"] = log_df.original.apply(str)
        log_df["changed"] = log_df.changed.apply(str)
        log_df.to_sql(f"editable_log",con = self.log_con,index=False, if_exists="append")

        print(log_df)
        # return updated table
        query = data["query"]
        page = query["page"]
        where = query["where"]
        return self.data_table(page,where)

    def settype(self,k):
        if k[:3] == "int": return int
        elif "float" in k: return float
        elif k=="str":return str
        elif k=="list":return list
        elif k=="dict":return dict
        else: return eval(k)

    def single_row(self,row):
        row["ip"]= request.remote_addr
        row["table_name"] = self.table_name
        row["ts"] = datetime.now()
        if row["original"]==row["changed"]:
            row['sql'] = ""
            return row
        else:
            col = row["col"]
            val = row["changed"]
            val = f"'{val}'" if 'str' in row["valtype"] else val
            idx = row["idx"]
            idx = f"'{idx}'" if type(idx)==str else idx
            set_clause = f"SET {col}={val}"
            sql = f"""UPDATE {self.table_name}
            {set_clause} WHERE {self.id_col}={idx}
            """
            row['sql'] = sql
            self.con.execute(sql)
            return row

    def read_df(self):
        page = request.args.get('page')
        where = request.args.get('where')
        return self.data_table(page,where)

    def data_table(self,page,where):
        where_clause = "" if where.strip() == "" else f"WHERE {where} "
        sql = f"""SELECT {self.columns} FROM {self.table_name} {where_clause}
        ORDER BY {self.id_col} ASC LIMIT {page},20
        """
        print(sql)
        df = pd.read_sql(sql,self.con)
        df = df.set_index(self.id_col)
        return self.t_table.render(dict(df = df))

    def load_temp(self,path):
        with open(path, "r") as f:
            return DefaultTemp(f.read())
# AUTOGENERATED! DO NOT EDIT! File to edit: nbs/30_traceable_edit_in_flask.ipynb (unless otherwise specified).

__all__ = ['get_static', 'edit_js', 'DefaultTemp', 'Editable']

# Cell
import pandas as pd
from datetime import datetime
import json
from sqlalchemy import create_engine as ce
from sqlalchemy import text
from jinja2 import Template

# Cell
from pathlib import Path
def get_static():
    import forgebox
    return Path(forgebox.__path__[0])/"static"

# Cell
def edit_js():
    with open(get_static()/"edit.js","r") as f:
        return f"<script>{f.read()}</script>"


class DefaultTemp(Template):
    """
    Jinjia template with some default render config
    """
    def render(self,dt):
        dt.update(dict(type=type,now = datetime.now()))
        return super().render(dt)

# Cell
from flask import request
from flask import g
from datetime import datetime

class Editable:
    def __init__(self,name,app,table_name,con,id_col,
                 log_con,log_table="editable_log",columns = None):
        """
        name: route name for url path,
            also it will be the task title appearning on the frontend
        app:flask app
        table_name: table to edit
        con:sqlachemy connnection, created by : con = sqlalchemy.create_engine
        id_col: a column with unique value
        log_con:sqlachemy connnection, for storaging change log
        """
        self.name = name
        self.app = app
        self.table_name = table_name
        self.con = con
        self.log_con = log_con
        self.columns = ",".join(columns) if columns!=None else "*"
        self.id_col = id_col

        self.t_workspace = self.load_temp(get_static()/"workspace.html")
        self.t_table = self.load_temp(get_static()/"table.html")
        self.assign()

    def assign(self):
        self.app.route(f"/{self.name}")(self.workspace)
        self.app.route(f"/{self.name}/df_api")(self.read_df)
        self.app.route(f"/{self.name}/save_api",
                       methods=["POST"])(self.save_data)

    def workspace(self):
        return self.t_workspace.render(dict(title=self.name,
                                                pk=self.id_col,
                                                edit_js = edit_js()))

    def save_data(self):
        data = json.loads(request.data)
        # update change and save log
        changes = data["changes"]
        log_df = pd.DataFrame(list(self.single_row(change) for change in changes))

        log_df["idx"] = log_df.idx.apply(str)
        log_df["original"] = log_df.original.apply(str)
        log_df["changed"] = log_df.changed.apply(str)
        log_df.to_sql(f"editable_log",con = self.log_con,index=False, if_exists="append")

        print(log_df)
        # return updated table
        query = data["query"]
        page = query["page"]
        where = query["where"]
        return self.data_table(page,where)

    def settype(self,k):
        if k[:3] == "int": return int
        elif "float" in k: return float
        elif k=="str":return str
        elif k=="list":return list
        elif k=="dict":return dict
        else: return eval(k)

    def single_row(self,row):
        row["ip"]= request.remote_addr
        row["table_name"] = self.table_name
        row["ts"] = datetime.now()
        if row["original"]==row["changed"]:
            row['sql'] = ""
            return row
        else:
            col = row["col"]
            val = row["changed"]
            val = f"'{val}'" if 'str' in row["valtype"] else val
            idx = row["idx"]
            idx = f"'{idx}'" if type(idx)==str else idx
            set_clause = f"SET {col}={val}"
            sql = f"""UPDATE {self.table_name}
            {set_clause} WHERE {self.id_col}={idx}
            """
            row['sql'] = sql
            self.con.execute(sql)
            return row

    def read_df(self):
        page = request.args.get('page')
        where = request.args.get('where')
        return self.data_table(page,where)

    def data_table(self,page,where):
        where_clause = "" if where.strip() == "" else f"WHERE {where} "
        sql = f"""SELECT {self.columns} FROM {self.table_name} {where_clause}
        ORDER BY {self.id_col} ASC LIMIT {page},20
        """
        print(sql)
        df = pd.read_sql(sql,self.con)
        df = df.set_index(self.id_col)
        return self.t_table.render(dict(df = df))

    def load_temp(self,path):
        with open(path, "r") as f:
            return DefaultTemp(f.read())
# AUTOGENERATED! DO NOT EDIT! File to edit: nbs/30_traceable_edit_in_flask.ipynb (unless otherwise specified). # Cell # Cell # Cell Jinjia template with some default render config # Cell name: route name for url path,             also it will be the task title appearning on the frontend         app:flask app         table_name: table to edit         con:sqlachemy connnection, created by : con = sqlalchemy.create_engine         id_col: a column with unique value         log_con:sqlachemy connnection, for storaging change log # update change and save log # return updated table UPDATE {self.table_name}             {set_clause} WHERE {self.id_col}={idx} SELECT {self.columns} FROM {self.table_name} {where_clause}         ORDER BY {self.id_col} ASC LIMIT {page},20