from google.cloud import bigquery


def query(sql):
    # Construct a BigQuery client object.
    client = bigquery.Client()
    query_job = client.query(sql)  # Make an API request.
    return query_job


def generate_query_time(start_time, end_time):
    query_time = "declare DS_START_DATE string default '%s'; declare DS_END_DATE string default '%s';" % \
                 (start_time, end_time)
    return query_time


def generate_table_name(account_id):
    query_time = "`gcp-digital-bi.aws_nonprod_billing.%s_aws_billing_data` " % account_id
    return query_time


def query_region_bills(account_id, start_time, end_time):
    query_time = generate_query_time(start_time, end_time)
    table_name = generate_table_name(account_id)
    print(query_time)
    query_sql = query_time + ("""
    SELECT DISTINCT
    product_region,sum(line_item_unblended_cost),
    FROM
    """) + table_name + ("""
    WHERE
    line_item_product_code != 'ComputeSavingsPlans' AND line_item_unblended_cost > 0.01
    AND CAST(line_item_usage_start_date AS DATE) >= PARSE_DATE('%Y%m%d', DS_START_DATE)
    AND CAST(line_item_usage_start_date AS DATE) <= PARSE_DATE('%Y%m%d', DS_END_DATE)
    group by product_region
    """)
    result = query(query_sql)
    print(query_sql)
    return result


def query_project_bills(account_id, start_time, end_time):
    query_time = generate_query_time(start_time, end_time)
    table_name = generate_table_name(account_id)
    query_sql = query_time + ("""
    SELECT DISTINCT
    resource_tags_user_project,sum(line_item_unblended_cost),
    FROM""") + table_name + ("""
    WHERE
    line_item_product_code != 'ComputeSavingsPlans' AND line_item_unblended_cost > 0.01
    AND CAST(line_item_usage_start_date AS DATE) >= PARSE_DATE('%Y%m%d', DS_START_DATE)
    AND CAST(line_item_usage_start_date AS DATE) <= PARSE_DATE('%Y%m%d', DS_END_DATE)
    group by resource_tags_user_project
    """)
    result = query(query_sql)
    return result


def query_project_cluster_bills(account_id, project_id, start_time, end_time):
    query_time = generate_query_time(start_time, end_time)
    table_name = generate_table_name(account_id)
    query_sql = query_time + ("""
    SELECT DISTINCT
    resource_tags_user_cluster,sum(line_item_unblended_cost),
    FROM""") + table_name + ("""
    WHERE
    line_item_product_code != 'ComputeSavingsPlans' AND line_item_unblended_cost > 0
    AND CAST(line_item_usage_start_date AS DATE) >= PARSE_DATE('%Y%m%d', DS_START_DATE)
    AND CAST(line_item_usage_start_date AS DATE) <= PARSE_DATE('%Y%m%d', DS_END_DATE)
     """) + "AND resource_tags_user_project = '%s'" % project_id + ("""
     group by resource_tags_user_cluster
     """)
    result = query(query_sql)
    return result