# sample) cron.yaml
cron:
- description: "cost alert for GCP"
  url: /tasks/GCPーcost-alert
  schedule: 5,10,15,20,25,30 of month 10:00

WITH
  -- 今月の各プロジェクトのトータルコストを取得
  C AS (
    SELECT
      project.name, SUM(cost) AS totalcost
    FROM
      `#{BILLING_DATASET}.#{BILLING_TABLE}`
    WHERE
      _PARTITIONTIME >= TIMESTAMP(?) AND _PARTITIONTIME <= TIMESTAMP(?) AND invoice.month = ? AND cost != 0
    GROUP BY
      project.name ),
  -- 前月の各プロジェクトのトータルコストを取得
  P AS (
    SELECT
      project.name, SUM(cost) AS totalcost
    FROM
      `#{BILLING_DATASET}.#{BILLING_TABLE}`
    WHERE
      _PARTITIONTIME >= TIMESTAMP(?) AND _PARTITIONTIME <= TIMESTAMP(?) AND invoice.month = ? AND cost != 0
    GROUP BY
      project.name ),
  -- C & Pから前月比における、差額と変動率を求める
  DIFF AS (
    SELECT
      C.name, C.totalcost AS current_cost, P.totalcost AS past_cost, C.totalcost - P.totalcost AS diff_cost, FLOOR((C.totalcost / P.totalcost) *100) AS increase_rate
    FROM
      C INNER JOIN P ON C.name = P.name )
  -- DIFF から閾値を条件に高額を判定
  SELECT
    name, round(current_cost,1) AS current_cost, round(past_cost,1) AS past_cost, round(diff_cost,1) AS diff_cost, increase_rate
  FROM
    DIFF
  WHERE
    (current_cost >= ? OR ( current_cost > ? AND increase_rate > ? ))

WITH
  -- 今月分の内訳
  A AS (
    SELECT
      service.description AS des,
      ROUND(SUM(cost),1) AS cost
    FROM
      `#{BILLING_DATASET}.#{BILLING_TABLE}`
    WHERE
      _PARTITIONTIME >= TIMESTAMP(?) AND _PARTITIONTIME <= TIMESTAMP(?) AND invoice.month = ? AND project.name = ?
    GROUP BY
      des ),
  -- 前月分の内訳
  B AS (
    SELECT
      service.description AS des,
      ROUND(SUM(cost),1) AS cost_prev
    FROM
      `#{BILLING_DATASET}.#{BILLING_TABLE}`
    WHERE
      _PARTITIONTIME >= TIMESTAMP(?) AND _PARTITIONTIME <= TIMESTAMP(?) AND invoice.month = ? AND project.name = ?
    GROUP BY
      des )
  -- A, B結果を一つのテーブルに纏める
  SELECT
    IFNULL(A.des, B.des) AS product, IFNULL(B.cost_prev, 0.0) AS cost_prev , IFNULL(A.cost, 0.0) AS cost
  FROM
    A FULL JOIN B ON A.des = B.des
  ORDER BY
    A.cost DESC

bigquery = Google::Cloud::Bigquery.new(
  project: BIGQUERY_PROJECT_ID,
  keyfile: CREDENTIAL_FILE,
  timeout: 600
)
# past_start, cur_start     = 2020-01-01, 2020-02-01
# past_end, cur_end         = 2020-01-10, 2020-02-10
# past_invoice, cur_invoice = 202001, 202002
# THR_COST_TOTAL_MONTH      = 10000 ($)
# THR_COST_DIFF_LOWEST      = 8000  ($)
# THR_COST_RATE_LOWEST      = 150   (%)
begin
  job = bigquery.query_job COST_SQL, params: ["#{cur_start}", "#{cur_end}", "#{cur_invoice}", "#{past_start}", "#{past_end}", "#{past_invoice}", THR_COST_TOTAL_MONTH, perDiff, THR_COST_RATE_LOWEST] \
                                , standard_sql: true, large_results: true, table: tmp_table
rescue Google::Cloud::InvalidArgumentError, Google::Apis::ServerError, Google::Apis::ClientError, Google::Apis::AuthorizationError => ex
  logger.error(ex.message)
  exit rtn_not_ok
end

job.wait_until_done!
rows = job.query_results
if rows.all.count == 0
  logger.info("Query returned zero record.")
  exit rtn_ok
end
# main loop
rows.each do |row|
  # プロジェクトごと費用の内訳を取得
  # メール送信先の取得
  # メール本文作成
  # メール送信
end

# main loop
rows.each do |row|
# ...
  # past_start, cur_start     = 2020-01-01, 2020-02-01
  # past_end, cur_end         = 2020-01-10, 2020-02-10
  # past_invoice, cur_invoice = 202001, 202002
  # row[:name]                = project name
  begin
    records = bigquery.query COST_DETAIL_SQL, params: ["#{cur_start}", "#{cur_end}", "#{cur_invoice}", "#{row[:name]}", "#{past_start}", "#{past_end}", "#{past_invoice}", "#{row[:name]}"], standard_sql: true
  rescue Google::Cloud::InvalidArgumentError, Google::Apis::ServerError, Google::Apis::ClientError, Google::Apis::AuthorizationError => ex
    logger.error(ex.message)
    records = nil
  end
# ...
end

# main loop
rows.each do |row|
# ...
  addr = []
  logger.info("[#{row[:name]}] Get the e-mail address")
  dt = bigquery.dataset row[:name]
  acc = dt.access

  for acc_hash in acc.to_a.select{|i| i[:role] == "READER"}
    acc_hash.each do |key, value|
      next if "#{key}" != "group_by_email"
      addr.push("#{value}")
    end
  end
# ...
end

# function: メール本文を作成
def func_createMAIL(pj, rate, dateOrig, dateCur, origCost, curCost, breakdown, rateMaxCost, maxAmount)
  realMax = THR_COST_TOTAL_MONTH
  realMax = maxAmount/100 if maxAmount != 0
  contents = <<EOS
<html>
<h2><%= pj %> 担当各位</h2> 

<%= pj %> の費用について、監視閾値を越えておりますため、<br/>
以下の観点からご確認の上返信をお願いいたします。<br/>
<ul>
<li>費用増加の原因</li>
<li>費用/売上比</li>
</ul>
....
<h3>費用の詳細</h3>
<table border="1" width="400" cellspacing="0" cellpadding="5" bordercolor="#333333">
<tr>
<th bgcolor="#808080" width="200"><font color="#FFFFFF">Product</font></th>
<th bgcolor="#808080" width="100"><font color="#FFFFFF"><%= dateOrig %>($)</font></th>
<th bgcolor="#808080" width="100"><font color="#FFFFFF"><%= dateCur %>($)</font></th>
</tr>
<% if  !breakdown.nil? and breakdown.count != 0
     breakdown.each do |data| %>

<tr>
<td bgcolor="#FFFFFF" valign="top"><%= data[:product] %></td>
<td bgcolor="#FFFFFF" valign="top" align="right"><%= data[:cost_prev] %></td>
<td bgcolor="#FFFFFF" valign="top" align="right"><%= data[:cost] %></td>
</tr>
<%   end %>
<% else %>
....
EOS
  erb = ERB.new(contents)
  return erb.result(binding)
end

# main loop
rows.each do |row|
# ...
  # row[:name]                  = project name
  # row[:increase_rate]         = 前月比増加率(%)
  # past_start_md, cur_start_md = 01/01, 02/01
  # past_end_md, cur_end_md     = 01/10, 02/10
  # row[:past_cost]             = 01/01~01/10 費用($)
  # row[:current_cost]          = 02/01~02/10 費用($)
  # records                     = {"product", "product 前月のコスト", "product 今月のコスト"}
  # perDiff                     = "前月比の増加率が 150% 以上である、且つ今月のコストが80万円を超過" に対し、80万円を日割り ($)
  # maxAmount                   = 10k($)
  body = func_createMAIL(row[:name], row[:increase_rate], "#{past_start_md} ~ #{past_end_md}", "#{cur_start_md} ~ #{cur_end_md}", row[:past_cost], row[:current_cost], records, perDiff, maxAmount)
# ...
end

# function: メール転送
def func_sendMail(mg, to_addr, subject, body, debug_flag)
  to_addr = MAIL_ADMIN_ADDR if to_addr.nil?
  message = Mailgun::MessageBuilder.new

  message.add_recipient  :to, "#{to_addr.join(',')}"
  message.add_recipient  :cc, "#{MAIL_CC_ADDR.join(',')}" if !debug_flag
  message.add_recipient  :from, MAIL_FROM_ADDR
  message.subject        subject
  message.body_html      body

  mg.send_message MAILGUN_DOMAIN_NAME, message
end

# main loop
rows.each do |row|
# ...
  mailgun = Mailgun::Client.new MAILGUN_API_KEY
  subject="[#{cur_end}] Cost management of #{row[:name]}"
  logger.debug("subject: #{subject}")
  logger.info("Send mail to #{addr}.")
  begin
    func_sendMail(mailgun, addr, subject, body, OPTIONS[:debug])
  rescue Mailgun::CommunicationError => ex
    logger.error("Failed send mail : #{ex.message}")
    rtn_ok = rtn_not_ok
  end
# ...
end

source "https://rubygems.org"

gem "sinatra"
gem 'google-api-client'
gem 'google-cloud-bigquery'
gem 'google-cloud-storage'
gem 'systemu'
gem 'mailgun-ruby'

cron:
- description: dena-cost-alert-for-gcp
  url: /hogehoge/check-cost-for-gcp
  schedule: 5,10,15,20,25,30 of month 11:00
  timezone: Asia/Tokyo
  target: default

require "sinatra"
require "date"
require "systemu"
get "/hogehoge/check-cost-for-gcp" do
  rtn_code = 500
  batch_command = "ruby scripts/check-cost-for-gcp.rb"

  batch_status, batch_stdout, batch_stderr = systemu batch_command
  if batch_status.inspect.include?('exit 0')
    rtn_code = 200
  end

  batch_stdout.each_line {|line| p line.chomp }
  p batch_stderr
  status rtn_code
end