#!/usr/bin/python
# -*- coding: utf-8 -*-
# 必要なライブラリ
import pandas as pd
pd.set_option('display.max_columns', 99999)
pd.set_option('display.max_rows', 500)
import pandas as pd
from pandas import Series, DataFrame
import datetime
from datetime import timedelta
import worldmesh
from geojson import Polygon, Feature, FeatureCollection, dump

import cgi
import sqlite3
import sys
import json


# Webページ情報取得
data = sys.stdin.read()
params = json.loads(data)


selected_year = ['']
selected_month = ['']
selected_date = ['']
selected_hour = ['']
selected_dow = ['']
selected_holiday = ['']
selected_meshcode = ['']
selected_attr1 = ['']
selected_attr2 = ['']
selected_attr3 = ['']
selected_attr4 = ['']
selected_attr5 = ['']

if len(params['year']) != 0:
    selected_year =  [int(i) for i in params['year']]
if len(params['month']) != 0:
    selected_month =  [int(i) for i in params['month']]
if len(params['date']) != 0:
    selected_date =  [int(i) for i in params['date']]
if len(params['mesh']) != 0:
    selected_meshcode = params['mesh']
if len(params['time']) != 0:
    selected_hour =  [int(i) for i in params['time']]
if len(params['yobi']) != 0:
    selected_dow =  params['yobi']
if len(params['holyday']) != 0:
    selected_holiday = params['holyday']

if len(params['attr1']) != 0:
    selected_attr1 = params['attr1']
if len(params['attr2']) != 0:
    selected_attr2 = params['attr2']
if len(params['attr3']) != 0:
    selected_attr3 = params['attr3']
if len(params['attr4']) != 0:
    selected_attr4 = params['attr4']
if len(params['attr5']) != 0:
    selected_attr5 = params['attr5']

if params['attr'] is not None :
    if len(params['attr']) != 0:
        selected_attr = params['attr']
else :
    selected_attr = ""

df = pd.read_csv('testdata_1.csv', dtype = 'object')
# 属性カラムを判断し左からattr1, attr2, .. と命名 ※ 必須項目以外について、存在しない場合の処理はお任せします。
attr_columns = df.columns.drop(['meshcode', 'mesh_size', 'year', 'month', 'date', 'hour', 'holiday', 'value'])
attrs = [] # attr1, attr2, ...→この後の処理に使用 
attrs_name = [] #  csv内のもとの名前→HTML内のフィルターの題名や、属性分析の属性選択リストに使用
for i, column in enumerate(attr_columns):
    df.rename(columns={column: f'attr{i+1}'}, inplace=True)
    attrs.append(f'attr{i+1}')
    attrs_name.append(column)
# 型指定（attr情報はobjectのまま）
df['meshcode'] = df['meshcode'].astype(str)
df['mesh_size'] = df['mesh_size'].astype(int)
df['year'] = df['year'].astype(int)
df['month'] = df['month'].astype(int)
df['date'] = df['date'].astype(int)
df['hour'] = df['hour'].astype(int)
df['value'] = df['value'].astype(float)
# 時間を持たせる
df['yyyymmdd'] = pd.to_datetime(df['year'].astype(str)+'-'+df['month'].astype(str)+'-'+df['date'].astype(str))
# 曜日を持たせる
df['dow'] = df['yyyymmdd'].dt.strftime("%a")
df_all = df.copy()

mesh_ext = False
spec = ""

if [*df.groupby("mesh_size").groups][0] == 100 :
    mesh_ext = True

if (mesh_ext == True) and (len([*df.groupby("meshcode").groups][0]) == 11):
    spec="ex100m_13"

# フィルターを反映 ※ここでは選択された項目は例です。実装ではフィルター欄で選択された項目をリストとして入れてください。
df = df_all.copy()
# selected_year = [2019]
# selected_month = [7]
# selected_date = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]
# selected_hour = [10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21]
# selected_dow = ['Mon', 'Tue', 'Wed', 'Sat']
# selected_holiday = ['平日', '休日']
# selected_meshcode = ['53394633334', '53394664133']
# selected_meshcode = ['すべて']
# selected_attr1 = ['男性', '女性']
# selected_attr2 = ['30', '40', '50', '60']
# selected_attr3 = ['すべて']
# selected_attr4 = ['すべて']
# selected_attr5 = ['すべて']

if selected_meshcode != ['すべて']:
    df = df[df['meshcode'].isin(selected_meshcode)]
if 'selected_year' in locals():
    if selected_year != ['すべて']:
        df = df[df['year'].isin(selected_year)]
if 'selected_month' in locals():
    if selected_month != ['すべて']:
        df = df[df['month'].isin(selected_month)]
if 'selected_date' in locals():
    if selected_date != ['すべて']:
        df = df[df['date'].isin(selected_date)]
if 'selected_hour' in locals():
    if selected_hour != ['すべて']:
        df = df[df['hour'].isin(selected_hour)]
if 'selected_dow' in locals():
    if selected_dow != ['すべて']:
        df = df[df['dow'].isin(selected_dow)]
if 'selected_holiday' in locals():
    if selected_holiday != ['すべて']:
        df = df[df['holiday'].isin(selected_holiday)]
if 'selected_attr1' in locals():
    if selected_attr1 != ['すべて']:
        df = df[df['attr1'].isin(selected_attr1)]
if 'selected_attr2' in locals():
    if selected_attr2 != ['すべて']:
        df = df[df['attr2'].isin(selected_attr2)]
if 'selected_attr3' in locals():
    if selected_attr3 != ['すべて']:
        df = df[df['attr3'].isin(selected_attr3)]
if 'selected_attr4' in locals():
    if selected_attr4 != ['すべて']:
        df = df[df['attr4'].isin(selected_attr4)]
if 'selected_attr5' in locals():
    if selected_attr5 != ['すべて']:
        df = df[df['attr5'].isin(selected_attr5)]
df_selected = df.copy().reset_index(drop=True)

if len(df_selected) == 0:
    result = {}
    print("Content-type: application/json")
    print("\n\n")
    print(json.dumps(result))

    print('\n')
    exit()

# フィルター後の日数を持っておく
nod = df_selected['yyyymmdd'].nunique()
nod_week = df_selected[df_selected['holiday']=='平日']['yyyymmdd'].nunique()
nod_holi = df_selected[df_selected['holiday']=='休日']['yyyymmdd'].nunique()

### マップ用のメッシュ別平均推計人数(人/日) df_a
df = df_selected.copy()
df = df.groupby(['meshcode'], as_index=False)['value'].sum()
df['cnt'] = df['value'] / nod
df_a = df[['meshcode', 'cnt']]
### 年月日別合計 df_b
df = df_selected.copy()
df_b = df.groupby(['yyyymmdd'], as_index=False)['value'].sum().rename(columns={'value': 'cnt'})
### 時間帯別平均 df_c
df = df_selected.copy()
df = df.groupby(['hour'], as_index=False)['value'].sum()
df['cnt'] = df['value'] / nod
df_c = df[['hour', 'cnt']]

####### 曜日別分析 #######

# 年月日別合計 df_d
df = df_selected.copy()
df = df.groupby(['yyyymmdd', 'holiday'], as_index=False)['value'].sum()
df.loc[df['holiday']=='平日', 'cnt'] = df['value'] 
df.loc[df['holiday']=='休日', 'cnt'] = df['value'] 
df_d = df[['yyyymmdd', 'holiday', 'cnt']]


# 曜日別平均 df_e
df = df_d.copy()
df_e = df.groupby(['holiday'], as_index=False)['cnt'].mean()
# 時間帯別平均 df_f
df = df_selected.copy()
df = df.groupby(['holiday', 'hour'], as_index=False)['value'].sum()
df.loc[df['holiday']=='平日', 'cnt'] = df['value'] / nod_week
df.loc[df['holiday']=='休日', 'cnt'] = df['value'] / nod_holi
df_f = df[['holiday', 'hour', 'cnt']]

####### P1右側 属性別分析 #######
# ※ 「集計対象」で選択された属性が「attr1」の場合の例です。
for i, column in enumerate(attr_columns):
    df_selected.rename(columns={f'attr{i+1}' : column }, inplace=True)

if selected_attr != "" :
    # 年月日別合計 df_g
    df = df_selected.copy()
    df_g = df.groupby(['yyyymmdd', selected_attr], as_index=False)['value'].sum().rename(columns={'value': 'cnt'})


    # 属性別平均 df_h
    df = df_g.copy()
    df_h = df.groupby([selected_attr], as_index=False)['cnt'].mean()
    # 時間帯別平均 df_i
    df = df_selected.copy()
    df = df.groupby([selected_attr, 'hour'], as_index=False)['value'].sum()
    df['cnt'] = df['value'] / nod
    df_i = df[[selected_attr, 'hour', 'cnt']]


# Webページ処理用データ加工

data_list = []
for item in df_a['meshcode']:
    result = worldmesh.meshcode_to_latlong_grid('20' + item,mesh_ext)
    data_list.append([item,result["long0"],result["lat0"],result["long1"],result["lat1"]])

df_a_g = pd.DataFrame(data_list, columns=["meshcode", "long0", "lat0", "long1", "lat1"])
df_a_m = pd.merge(df_a_g, df_a)

geojson_data = df_a_m.to_dict(orient='records')
# Prepare Geojson FeatureCollection
ft_all = []
for i, p in enumerate(geojson_data):
    lat0, lon0, lat1, lon1, = p['lat0'], p['long0'],  p['lat1'], p['long1']
    ft = Feature(geometry = Polygon([[(lon0, lat0),(lon0, lat1),(lon1, lat1),(lon1, lat0),(lon0, lat0)]]),
                 properties = {'meshcode': p['meshcode'], 'cnt': p['cnt']},id= p['meshcode'])
    ft_all.append(ft)
ft_colct = FeatureCollection(ft_all)

with open('outfile.geojson', 'w') as f:
    dump(ft_colct, f, indent=2)



data_list = []
df_meshcode_g = [*df_all.groupby("meshcode").groups]
for item in df_meshcode_g:
    result = worldmesh.meshcode_to_latlong_grid('20' + item,mesh_ext)
    data_list.append([item,result["long0"],result["lat0"],result["long1"],result["lat1"]])

df_geo = pd.DataFrame(data_list, columns=["meshcode", "long0", "lat0", "long1", "lat1"])


geojson_data = df_geo.to_dict(orient='records')
# Prepare Geojson FeatureCollection
ft_all = []
for i, p in enumerate(geojson_data):
    lat0, lon0, lat1, lon1, = p['lat0'], p['long0'],  p['lat1'], p['long1']
    ft = Feature(geometry = Polygon([[(lon0, lat0),(lon0, lat1),(lon1, lat1),(lon1, lat0),(lon0, lat0)]]),
                 properties = {'meshcode': p['meshcode']},id= p['meshcode'])
    ft_all.append(ft)
ft_colct = FeatureCollection(ft_all)

with open('outfile1-2.geojson', 'w') as f:
    dump(ft_colct, f, indent=2)


df_d_m = df_d.pivot_table(values=['cnt'], index=['yyyymmdd'], columns=['holiday'], aggfunc='sum',fill_value=0)
df_d_m.columns = [cols[1] for cols in df_d_m.columns]
df_d_m = df_d_m.reset_index()

if selected_attr != "" :
    df_g.rename(columns={selected_attr : 'attr1'}, inplace=True)
    df_h.rename(columns={selected_attr : 'attr1'}, inplace=True)
    df_i.rename(columns={selected_attr : 'attr1'}, inplace=True)

    df_g_m = df_g.pivot_table(values=['cnt'], index=['yyyymmdd'], columns=['attr1'], aggfunc='sum',fill_value=0,margins=True)
    df_g_m = df_g_m.iloc[:-1]
    df_g_m.columns = [cols[1] for cols in df_g_m.columns]
    df_g_m = df_g_m.reset_index()

if not '平日' in df_d_m.columns:
    df_d_m['平日'] = 0
if not '休日' in df_d_m.columns:
    df_d_m['休日'] = 0


result = {}
df_b['yyyymmdd'] = df_b['yyyymmdd'].astype(str)
df_d_m['yyyymmdd'] = df_d_m['yyyymmdd'].astype(str)

if selected_attr != "" :
    df_g_m['yyyymmdd'] = df_g_m['yyyymmdd'].astype(str)

result['df_a'] = df_a.to_dict(orient='records')
result['df_b'] = df_b.to_dict(orient='records')
result['df_c'] = df_c.to_dict(orient='records')
result['df_d'] = df_d_m.to_dict(orient='records')
result['df_e'] = df_e.to_dict(orient='records')
result['df_f'] = df_f.to_dict(orient='records')
if selected_attr != "" :
    result['df_g'] = df_g_m.to_dict(orient='records')
    result['df_h'] = df_h.sort_values('cnt', ascending=False).to_dict(orient='records')
    result['df_i'] = df_i.to_dict(orient='records')
else :
    result['df_g'] = []
    result['df_h'] = []
    result['df_i'] = []

result['pm'] = params
df_selected['yyyymmdd'] = df_selected['yyyymmdd'].astype(str)
result['df_selected'] = df_selected.to_dict(orient='records')

print("Content-type: application/json")
print("\n\n")
print(json.dumps(result))

print('\n')