import json
from superset.app import create_app

app = create_app()
with app.app_context():
    from superset import db
    from superset.models.slice import Slice
    from superset.models.dashboard import Dashboard
    from superset.connectors.sqla.models import SqlaTable

    tbl = (
        db.session.query(SqlaTable)
        .filter_by(table_name="historical_sales", schema="historical")
        .first()
    )
    ds_id = tbl.id

    rev = {"expressionType": "SIMPLE", "column": {"column_name": "amount"},
           "aggregate": "SUM", "label": "Revenue", "hasCustomLabel": True}
    cnt = {"expressionType": "SIMPLE", "column": {"column_name": "order_id"},
           "aggregate": "COUNT", "label": "Orders", "hasCustomLabel": True}

    def upsert_slice(name, viz_type, params):
        sl = db.session.query(Slice).filter_by(slice_name=name).first()
        if not sl:
            sl = Slice(slice_name=name, datasource_type="table", datasource_id=ds_id,
                       viz_type=viz_type, params=json.dumps(params))
            db.session.add(sl)
        else:
            sl.viz_type = viz_type
            sl.params = json.dumps(params)
            sl.datasource_id = ds_id
        db.session.commit()
        return sl

    s_year = upsert_slice(
        "Historical Revenue by Year", "table",
        {"viz_type": "table", "datasource": f"{ds_id}__table",
         "query_mode": "aggregate", "groupby": ["order_year"],
         "metrics": [rev, cnt], "row_limit": 100,
         "order_desc": True, "adhoc_filters": []},
    )
    s_region = upsert_slice(
        "Historical Revenue by Region", "pie",
        {"viz_type": "pie", "datasource": f"{ds_id}__table",
         "groupby": ["region"], "metric": rev, "row_limit": 100,
         "adhoc_filters": []},
    )
    s_cat = upsert_slice(
        "Historical Revenue by Category", "pie",
        {"viz_type": "pie", "datasource": f"{ds_id}__table",
         "groupby": ["product_category"], "metric": rev, "row_limit": 100,
         "adhoc_filters": []},
    )

    title = "Historical Sales (HDFS archive)"
    dash = db.session.query(Dashboard).filter_by(slug="historical-sales").first()
    if not dash:
        dash = Dashboard(dashboard_title=title, slug="historical-sales", published=True)
        db.session.add(dash)
    dash.slices = [s_year, s_region, s_cat]

    def chart_node(cid, slice_obj, w, h):
        return {"type": "CHART", "id": cid, "children": [],
                "parents": ["ROOT_ID", "GRID_ID", "ROW-1"],
                "meta": {"chartId": slice_obj.id, "width": w, "height": h,
                         "sliceName": slice_obj.slice_name}}

    pos = {
        "DASHBOARD_VERSION_KEY": "v2",
        "ROOT_ID": {"type": "ROOT", "id": "ROOT_ID", "children": ["GRID_ID"]},
        "GRID_ID": {"type": "GRID", "id": "GRID_ID", "children": ["ROW-1"], "parents": ["ROOT_ID"]},
        "HEADER_ID": {"type": "HEADER", "id": "HEADER_ID", "meta": {"text": title}},
        "ROW-1": {"type": "ROW", "id": "ROW-1", "children": ["CHART-A", "CHART-B", "CHART-C"],
                  "parents": ["ROOT_ID", "GRID_ID"], "meta": {"background": "BACKGROUND_TRANSPARENT"}},
        "CHART-A": chart_node("CHART-A", s_year, 4, 50),
        "CHART-B": chart_node("CHART-B", s_region, 4, 50),
        "CHART-C": chart_node("CHART-C", s_cat, 4, 50),
    }
    dash.position_json = json.dumps(pos)
    db.session.commit()
    print("DASHBOARD_ID", dash.id, "slug", dash.slug, "slices", [s.id for s in dash.slices])