// ============================================================================
// All Available Cost Columns Query
//
// Description:
//   This query returns a fully enriched, analytics-ready view of all cost and usage records
//   from the FinOps Hub Costs() table. It includes all standard cost columns, toolkit
//   enrichment columns (prefixed with x_), hybrid benefit and commitment discount logic,
//   savings calculations, and resource metadata. This is the canonical base query for
//   Power BI, dashboards, and custom analytics.
//
// Parameters:
//   startDate: Start of the reporting window (default: startofmonth(ago(30d)))
//   endDate: End of the reporting window (default: startofmonth(now()))
//
// Key Features:
//   - Applies summarization window (lookback period)
//   - Adds reporting and charge month columns
//   - Enriches with VM, SKU, and hybrid benefit details
//   - Calculates license status, type, and quantity for VMs
//   - Computes commitment discount utilization and savings
//   - Adds toolkit metadata (tool, version, resource parent, etc.)
//   - Adds unique name helpers for reporting
//   - Explains zero cost records
//   - Projects away temp columns for a clean output
//
// Output:
//   Each row represents a single cost record with all enrichment and analytics columns needed
//   for FinOps reporting, allocation, and optimization.
//
// For full schema and column definitions, see:
//   https://raw.githubusercontent.com/microsoft/finops-toolkit/refs/heads/msbrett/features/ghc/src/queries/finops-hub-database-guide.md#column-definitions
// ============================================================================

let startDate = startofmonth(ago(30d));
let endDate = startofmonth(now());
Costs()
//
// Apply summarization settings
| where ChargePeriodStart >= startDate and ChargePeriodStart < endDate
| as filteredCosts
| extend x_ChargeMonth = startofmonth(ChargePeriodStart)
| extend x_ReportingDate = startofmonth(ChargePeriodStart)
//
| extend x_SkuVMProperties = tostring(x_SkuDetails.VMProperties)
| extend x_CapacityReservationId = tostring(x_SkuDetails.VMCapacityReservationId)
//
| extend tmp_IsVMUsage  = x_SkuMeterCategory in ('Virtual Machines', 'Virtual Machine Licenses') and ChargeCategory == 'Usage'
| extend x_SkuCoreCount = iff(tmp_IsVMUsage, toint(coalesce(x_SkuDetails.VCPUs, x_SkuDetails.vCores)), toint(''))
| extend x_SkuUsageType = tostring(x_SkuDetails.UsageType)
| extend x_SkuImageType = iff(tmp_IsVMUsage, tostring(x_SkuDetails.ImageType), '')
| extend x_SkuType      = iff(tmp_IsVMUsage, tostring(x_SkuDetails.ServiceType), '')
| extend tmp_IsSQLAHB = tolower(x_SkuDetails.AHB) == 'true'
| extend x_ConsumedCoreHours = iff(tmp_IsVMUsage and isnotempty(x_SkuCoreCount), toreal(x_SkuCoreCount * ConsumedQuantity), toreal(''))
| extend x_SkuLicenseStatus = case(
    (isnotempty(x_SkuDetails.ImageType) and x_SkuDetails.ImageType contains 'Windows Server BYOL') or tmp_IsSQLAHB or (isnotempty(x_SkuMeterSubcategory) and x_SkuMeterSubcategory contains 'Azure Hybrid Benefit'), 'Enabled',
    (isnotempty(x_SkuMeterSubcategory) and x_SkuMeterSubcategory contains 'Windows') or not(tmp_IsSQLAHB), 'Not enabled',
    ''
)
| extend x_SkuLicenseType = case(
    x_SkuDetails.ImageType contains 'Windows Server BYOL', 'Windows Server',
    x_SkuMeterSubcategory == 'SQL Server Azure Hybrid Benefit', 'SQL Server',
    ''
)
| extend x_SkuLicenseQuantity = case(
    isempty(x_SkuCoreCount), toint(''),
    x_SkuCoreCount <= 8, 8,
    x_SkuCoreCount > 8, x_SkuCoreCount,
    toint('')
)
| extend x_SkuLicenseUnit = iff(isnotempty(x_SkuLicenseQuantity), 'Cores', '')
//
| extend x_CommitmentDiscountKey = iff(tmp_IsVMUsage and isnotempty(x_SkuDetails.ServiceType), strcat(x_SkuDetails.ServiceType, x_SkuMeterId), '')
| extend x_CommitmentDiscountUtilizationPotential = case(
    ChargeCategory == 'Purchase', toreal(0),
    ProviderName == 'Microsoft' and isnotempty(CommitmentDiscountCategory), toreal(EffectiveCost),
    CommitmentDiscountCategory == 'Usage', toreal(ConsumedQuantity),
    CommitmentDiscountCategory == 'Spend', toreal(EffectiveCost),
    toreal(0)
)
| extend x_CommitmentDiscountUtilizationAmount = iff(CommitmentDiscountStatus == 'Used', x_CommitmentDiscountUtilizationPotential, toreal(0))
| extend x_SkuTermLabel = case(isempty(x_SkuTerm) or x_SkuTerm <= 0, '', x_SkuTerm < 12, strcat(x_SkuTerm, ' month', iff(x_SkuTerm != 1, 's', '')), strcat(x_SkuTerm / 12, ' year', iff(x_SkuTerm != 12, 's', '')))
//
| extend BilledCost = round(toreal(BilledCost), 6)
| extend EffectiveCost = round(toreal(EffectiveCost), 6)
| extend ContractedCost = round(toreal(ContractedCost), 6)
| extend ListCost = round(toreal(ListCost), 6)
| extend ConsumedQuantity = round(toreal(ConsumedQuantity), 6)
| extend ListUnitPrice = round(toreal(ListUnitPrice), 6)
| extend ContractedUnitPrice = round(toreal(ContractedUnitPrice), 6)
| extend x_EffectiveUnitPrice = round(toreal(x_EffectiveUnitPrice), 6)
| extend x_BilledUnitPrice = round(toreal(x_BilledUnitPrice), 6)
| extend PricingQuantity = round(toreal(PricingQuantity), 6)
| extend x_CommitmentDiscountSavings = iff(ContractedCost < EffectiveCost, toreal(0), round(toreal(ContractedCost - EffectiveCost), 6))
| extend x_NegotiatedDiscountSavings = iff(ListCost < ContractedCost, toreal(0), round(toreal(ListCost - ContractedCost), 6))
| extend x_TotalSavings              = iff(ListCost < EffectiveCost, toreal(0), round(toreal(ListCost - EffectiveCost), 6))
| extend x_CommitmentDiscountPercent = iff(ContractedUnitPrice == 0,        toreal(0), round(toreal((ContractedUnitPrice - x_EffectiveUnitPrice) / ContractedUnitPrice), 6))
| extend x_NegotiatedDiscountPercent = iff(ListUnitPrice == 0,              toreal(0), round(toreal((ListUnitPrice - ContractedUnitPrice) / ListUnitPrice), 6))
| extend x_TotalDiscountPercent      = iff(ListUnitPrice == 0,              toreal(0), round(toreal((ListUnitPrice - x_EffectiveUnitPrice) / ListUnitPrice), 6))
//
| extend x_ToolkitTool = tostring(Tags['ftk-tool'])
| extend x_ToolkitVersion = tostring(Tags['ftk-version'])
| extend tmp_ResourceParent = database('Ingestion').parse_resourceid(Tags['cm-resource-parent'])
| extend x_ResourceParentId = tostring(tmp_ResourceParent.ResourceId)
| extend x_ResourceParentName = tostring(tmp_ResourceParent.ResourceName)
| extend x_ResourceParentType = tostring(tmp_ResourceParent.ResourceType)
//
| extend CommitmentDiscountNameUnique = iff(isempty(CommitmentDiscountId), '', strcat(CommitmentDiscountName, ' (', CommitmentDiscountType, ')'))
| extend ResourceNameUnique           = iff(isempty(ResourceId),           '', strcat(ResourceName,           ' (', ResourceType, ')'))
| extend x_ResourceGroupNameUnique    = iff(isempty(x_ResourceGroupName),  '', strcat(x_ResourceGroupName,    ' (', SubAccountName, ')'))
| extend SubAccountNameUnique         = iff(isempty(SubAccountId),         '', strcat(SubAccountName,         ' (', split(SubAccountId, '/')[3], ')'))
//
| extend x_FreeReason = case(
    BilledCost != 0.0 or EffectiveCost != 0.0, '',
    PricingCategory == 'Committed', strcat('Unknown ', CommitmentDiscountStatus, ' Commitment'),
    x_BilledUnitPrice == 0.0 and x_EffectiveUnitPrice == 0.0 and ContractedUnitPrice == 0.0 and ListUnitPrice == 0.0 and isempty(CommitmentDiscountType), case(
        x_SkuDescription contains 'Trial', 'Trial',
        x_SkuDescription contains 'Preview', 'Preview',
        'Other'
    ),
    x_BilledUnitPrice > 0.0 or x_EffectiveUnitPrice > 0.0, case(
        PricingQuantity > 0.0, 'Low Usage',
        PricingQuantity == 0.0, 'No Usage',
        'Unknown Negative Quantity'
    ),
    'Unknown'
)
| extend Tags = tostring(Tags)
| extend x_SkuDetails = tostring(x_SkuDetails)
| project-away tmp_IsSQLAHB, tmp_IsVMUsage, tmp_ResourceParent