import base64
import datetime
import hmac
import json
import time
from hashlib import sha256

import requests
from google.cloud import bigquery

client = bigquery.Client()

feishu_webhook = "https://open.feishu.cn/open-apis/bot/v2/hook/402619ca-3aad-4d9f-86a4-8e1bea90bb76"
BOT_SECRET = "Rk7vQ3pmlG1uIrfQmWWvab"

billing_table = 'pingcap-gardener.dbaas_billing.gcp_billing_export_v1_010870_592F9D_20D797'
project_id = 'dbaas-prod'

def get_day_cost(yesterday, today):
  thisMonth = datetime.datetime.now().strftime('%Y%m')
  # tommorrow = datetime.datetime.today()-datetime.timedelta(days=1)
  yesterday = yesterday.strftime('%Y-%m-%d')
  today = today.strftime('%Y-%m-%d')

  # 查询当月费用
  query = (
    "SELECT "
    " project.name, "
    " TO_JSON_STRING(project.labels) as project_labels, "
    " sum(cost) as total_cost, "
    " SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) as total_credits "
    " FROM  {}  "
    " WHERE invoice.month = '{}' and project.id='{}' "
    " GROUP BY 1, 2 "
    " ORDER BY 1; ".format(billing_table, thisMonth, project_id)
  )
  query_job = client.query(
    query,
  )

  data = query_job.result()
  print("query done")
  rows = list(data)
  print("This query will process {} {}.".format(rows[0][2], rows[0][3]))

  total_fee = rows[0][2] + rows[0][3]

  # 查询当日费用
  query = (
    "SELECT "
    " project.name, "
    " TO_JSON_STRING(project.labels) as project_labels, "
    " sum(cost) as total_cost, "
    " SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) as total_credits "
    " FROM  {}  "
    " WHERE project.id='{}' and usage_start_time >='{}' and usage_end_time < '{}' "
    " GROUP BY 1, 2 "
    " ORDER BY 1; "
  ).format(billing_table, project_id, yesterday, today)

  query_job = client.query(
    query
  )

  data = query_job.result()
  print("query done")
  rows = list(data)
  print("This query will process {} {}.".format(rows[0][2], rows[0][3]))
  today_fee = rows[0][2]
  today_credits = rows[0][3]

  # 查询loadbalancer费用
  query = (
    "SELECT "
    "    SUM(cost) AS cost "
    "FROM "
    "    {} "
    "WHERE "
    "    project.id = '{}' "
    "    AND usage_start_time>= '{}' "
    "    AND usage_end_time <= '{}' "
    "    AND sku.description LIKE 'Network Load Balancing%' "
  ).format(billing_table, project_id, yesterday, today)

  lb_query_job = client.query(
    query
  )

  loadbalance_fee_data = lb_query_job.result()
  print("query done")
  rows = list(loadbalance_fee_data)
  # print("This query will process {} {}.".format(rows[0][2], rows[0][3]))
  # network_fee = rows[0][0] + rows[0][3]
  network_fee = rows[0][0]

  # 查询存储费用
  query = (
    "SELECT "
    "    SUM(cost) AS cost "
    "FROM "
    "    {} "
    "WHERE "
    "    project.id = '{}' "
    "    AND usage_start_time>= '{}' "
    "    AND usage_end_time <= '{}' "
    "    AND service.description='Cloud Storage' "
  ).format(billing_table, project_id, yesterday, today)

  storage_query_job = client.query(
    query
  )

  storage_fee_data = storage_query_job.result()
  print("query done")
  rows = list(storage_fee_data)
  print("This query will process {}.".format(rows[0][0]))
  storage_fee = rows[0][0]

  # 查询tidb层费用
  query = (
    "SELECT "
    "SUM(cost) AS cost "
    "FROM ( "
    "    SELECT "
    "cost, "
    "( "
    "    SELECT "
    "    value "
    "    FROM "
    "    UNNEST(labels) "
    "WHERE "
    "key='tenant' "
    "LIMIT "
    "1) AS tenantId "
    "FROM "
    "{} "
    "WHERE "
    "project.id= '{}' "
    "AND usage_start_time>= '{}' "
    "AND usage_end_time <= '{}' "
    "AND service.description='Compute Engine') "
    "WHERE "
    "tenantId IS NOT NULL; "
  ).format(billing_table, project_id, yesterday, today)

  tidb_query_job = client.query(
    query
  )

  tidb_fee_data = tidb_query_job.result()
  print("query done")
  rows = list(tidb_fee_data)
  tidb_fee = rows[0][0]
  # 获取tidb的gcp折扣

  # 查询管理面费用
  query = (
    "SELECT "
    "SUM(cost) AS cost "
    "FROM ( "
    "    SELECT "
    "cost, "
    "( "
    "    SELECT "
    "    value "
    "    FROM "
    "    UNNEST(labels) "
    "WHERE "
    "key='tenant' "
    "LIMIT "
    "1) AS tenantId "
    "FROM "
    "{} "
    "WHERE "
    "project.id= '{}' "
    "AND usage_start_time>= '{}' "
    "AND usage_end_time <= '{}' "
    "AND service.description='Compute Engine') "
    "WHERE "
    "tenantId IS NULL; "
  ).format(billing_table, project_id, yesterday, today)

  management_query_job = client.query(
    query
  )
  management_data = management_query_job.result()
  print("management fee query done")
  rows = list(management_data)
  management_fee = rows[0][0]

  # 查询其他gcp费用
  query = (
    "SELECT "
    "   SUM(cost) AS cost "
    "FROM "
    "    {} "
    "WHERE "
    "    project.id = '{}' "
    "    AND usage_start_time>= '{}' "
    "    AND usage_end_time <= '{}' "
    "    AND service.description !='Cloud Storage' "
    "    AND service.description !='Compute Engine' "
    "    AND sku.description not LIKE 'Network Load Balancing%' "
  ).format(billing_table, project_id, yesterday, today)

  others_query_job = client.query(
    query
  )
  others_data = others_query_job.result()
  print("others fee query done")
  rows = list(others_data)
  others_fee = rows[0][0]

  return (total_fee, today_fee, network_fee, storage_fee, tidb_fee, management_fee,
          others_fee, today_credits)


def get_fields_contents(today_fee, yesterday_fee):
  post_infos = [
    {
      "is_short": False,
      "text": {
        "tag": "lark_md",
        "content": ""
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**当月累计总消耗**:\n" + str(round(today_fee[0], 3))
      }
    },
    {
      "is_short": False,
      "text": {
        "tag": "lark_md",
        "content": ""
      }
    },
    {
      "is_short": False,
      "text": {
        "tag": "lark_md",
        "content": "**今日账单**"
      }
    },
    {
      "is_short": False,
      "text": {
        "tag": "lark_md",
        "content": ""
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**总开支**:\n" + str(round(today_fee[1], 3)) + '(%s)' % str(
          round(yesterday_fee[1] - today_fee[1], 3))
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**TiDB 集群**:\n" + str(round(today_fee[4], 3)) + '(%s)' % str(
          round(yesterday_fee[4] - today_fee[4], 3))
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**Control Plane**:\n" + str(round(today_fee[5], 3)) + '(%s)' % str(
          round(yesterday_fee[5] - today_fee[5], 3))
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**网络和存储**:\n" + str(round(today_fee[2] + today_fee[3], 3)) + '(%s)' % str(
          round(yesterday_fee[3] - today_fee[3] + yesterday_fee[2] - today_fee[2], 3))
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**其他资源**:\n" + str(round(today_fee[6], 3))
      }
    },
    {
      "is_short": True,
      "text": {
        "tag": "lark_md",
        "content": "**折扣**:\n" + str(round(today_fee[7], 3))
      }
    },
    {
      "is_short": False,
      "text": {
        "tag": "lark_md",
        "content": ""
      }
    }
  ]
  return post_infos


def get_sign_timestamp():
  timestamp = str(int(time.time()))
  secret = BOT_SECRET

  key = f'{timestamp}\n{secret}'
  key_enc = key.encode('utf-8')
  msg = ""
  msg_enc = msg.encode('utf-8')
  hmac_code = hmac.new(key_enc, msg_enc, digestmod=sha256).digest()
  sign = base64.b64encode(hmac_code).decode('utf-8')
  return {'timestamp': timestamp, 'sign': sign}


def post_bot_webhook():
  url = feishu_webhook
  sign_timestamp = get_sign_timestamp()
  payload_message = {
    "timestamp": sign_timestamp['timestamp'],
    "sign": sign_timestamp['sign'],
    "msg_type": "interactive",
    "card": {
      "i18n_elements": {
        "en_us": [
          {
            "tag": "div",
            "text": {
              "tag": "lark_md",
              "content": "**Daily Cost Analysis - GCP TiDBCloud Prod** " + datetime.datetime.today().strftime(
                "%Y-%m-%d")
            },
            "fields": get_fields_contents(today_fee, yesterday_fee)
          },
        ]
      }
    }
  }
  headers = {
    'Content-Type': 'application/json'
  }

  response = requests.request(
    "POST", url, headers=headers, data=json.dumps(payload_message))
  print(response.text)


today_fee = get_day_cost(
  datetime.datetime.today() - datetime.timedelta(days=1),
  datetime.datetime.today()
)

yesterday_fee = get_day_cost(
  datetime.datetime.today() - datetime.timedelta(days=2),
  datetime.datetime.today() - datetime.timedelta(days=1)
)

post_bot_webhook()