-- Singapore storage prices, USD per GiB-month.
DECLARE active_logical_price    FLOAT64 DEFAULT 0.023;
DECLARE long_term_logical_price FLOAT64 DEFAULT 0.016;
DECLARE active_physical_price   FLOAT64 DEFAULT 0.040;
DECLARE long_term_physical_price FLOAT64 DEFAULT 0.020;
WITH dataset_options AS (
  SELECT
    schema_name,
    MAX(
      IF(
        option_name = 'storage_billing_model',
        UPPER(option_value),
        NULL
      )
    ) AS configured_billing_model
  FROM
    `<project_name>`.`region-asia-southeast1`
      .INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  GROUP BY schema_name
),
storage AS (
  SELECT
    table_storage.*,
    CASE
      WHEN dataset_options.configured_billing_model = 'PHYSICAL'
        THEN 'PHYSICAL'
      ELSE 'LOGICAL'
    END AS billing_model
  FROM
    `<project_name>`.`region-asia-southeast1`
      .INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT AS table_storage
  LEFT JOIN dataset_options
    ON table_storage.table_schema = dataset_options.schema_name
  WHERE
    table_storage.table_type = 'BASE TABLE'
    AND NOT table_storage.deleted
)
SELECT
  table_catalog AS project_id,
  table_schema AS dataset_id,
  table_name,
  billing_model,
  ROUND(active_logical_bytes / POW(1024, 3), 2)
    AS active_logical_gib,
  ROUND(long_term_logical_bytes / POW(1024, 3), 2)
    AS long_term_logical_gib,
  ROUND(active_physical_bytes / POW(1024, 3), 2)
    AS active_physical_gib,
  ROUND(long_term_physical_bytes / POW(1024, 3), 2)
    AS long_term_physical_gib,
  ROUND(time_travel_physical_bytes / POW(1024, 3), 2)
    AS time_travel_physical_gib,
  ROUND(COALESCE(fail_safe_physical_bytes, 0) / POW(1024, 3), 2)
    AS fail_safe_physical_gib,
  -- Cost if the dataset uses logical billing.
  ROUND(
    active_logical_bytes / POW(1024, 3) * active_logical_price
    + long_term_logical_bytes / POW(1024, 3) * long_term_logical_price,
    2
  ) AS logical_cost_usd_month,
  -- Active physical bytes already include time-travel bytes.
  ROUND(
    (
      active_physical_bytes
      + COALESCE(fail_safe_physical_bytes, 0)
    ) / POW(1024, 3) * active_physical_price
    + long_term_physical_bytes / POW(1024, 3)
      * long_term_physical_price,
    2
  ) AS physical_cost_usd_month,
  -- Estimate based on the dataset's actual billing model.
  ROUND(
    CASE billing_model
      WHEN 'PHYSICAL' THEN
        (
          active_physical_bytes
          + COALESCE(fail_safe_physical_bytes, 0)
        ) / POW(1024, 3) * active_physical_price
        + long_term_physical_bytes / POW(1024, 3)
          * long_term_physical_price
      ELSE
        active_logical_bytes / POW(1024, 3)
          * active_logical_price
        + long_term_logical_bytes / POW(1024, 3)
          * long_term_logical_price
    END,
    2
  ) AS estimated_cost_usd_month
FROM storage
ORDER BY estimated_cost_usd_month DESC;

ALTER SCHEMA `<project_name>.<dataset>`
SET OPTIONS (
  storage_billing_model = 'PHYSICAL'
);

ALTER SCHEMA `project_a.analytics`
SET OPTIONS (
  storage_billing_model = 'PHYSICAL'
);