import dotenv from "dotenv";
dotenv.config();

// /accounts
export const accountsQuery = `SELECT DISTINCT
    line_item_usage_account_id AS accountId FROM ${process.env.ATHENA_CU_TABLE};`;

// /accounts/:accountId/regions
export const regionsQuery = `SELECT DISTINCT
    line_item_usage_account_id AS accountId,
    COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode
    FROM ${process.env.ATHENA_CU_TABLE}
    WHERE line_item_usage_account_id = '%accountId%';`;

// /accounts/:accountId/regions/:regionCode/products
export const productsByRegionQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
    COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE line_item_usage_account_id = '%accountId%' AND LOWER(product_region_code) = LOWER('%regionCode%');`;

// /accounts/:accountId/regions/:regionCode/products/:productCode
export const productByRegionQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
    COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE
    line_item_usage_account_id = '%accountId%' AND 
    product_region_code = '%regionCode%' AND
    LOWER(line_item_product_code) = LOWER('%productCode%');`;

// /accounts/:accountId/regions/:regionCode/products/:productCode/usage
export const productUsageByRegionQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE line_item_usage_account_id = '%accountId%'
	AND product_region_code = '%regionCode%'
	AND LOWER(line_item_product_code) = LOWER('%productCode%');`;

// /accounts/:accountId/regions/:regionCode/products/:productCode/usage/:year
export const productUsageByRegionYearQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE line_item_usage_account_id = '%accountId%'
	AND product_region_code = '%regionCode%'
	AND LOWER(line_item_product_code) = LOWER('%productCode%')
    AND year = '%year%';`;

// /accounts/:accountId/regions/:regionCode/products/:productCode/usage/:year/:month
export const productUsageByRegionYearMonthQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE line_item_usage_account_id = '%accountId%'
	AND product_region_code = '%regionCode%'
	AND LOWER(line_item_product_code) = LOWER('%productCode%')
    AND year = '%year%'
    AND month = '%month%';`;

// /products
export const productQuery = `SELECT DISTINCT
	line_item_product_code AS productCode,
    COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId
FROM ${process.env.ATHENA_CU_TABLE};`;

export const productByCodeQuery = `SELECT DISTINCT
	line_item_product_code AS productCode,
    COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId
FROM ${process.env.ATHENA_CU_TABLE}
    WHERE LOWER(line_item_product_code) = LOWER('%productCode%');`;

export const productByCodeUsageQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE LOWER(line_item_product_code) = LOWER('%productCode%');`;

export const productByCodeUsageYearQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE LOWER(line_item_product_code) = LOWER('%productCode%')
    AND year = '%year%';`;

export const productByCodeUsageYearMonthQuery = `SELECT DISTINCT
    line_item_product_code AS productCode,
	COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') AS regionCode,
	line_item_usage_account_id AS accountId,
    line_item_resource_id AS resourceId,
	line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE LOWER(line_item_product_code) = LOWER('%productCode%')
    AND year = '%year%'
    AND month = '%month%';`;

export const invoices = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month
FROM ${process.env.ATHENA_CU_TABLE}`;

export const invoiceById = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%';`;

export const invoiceByIdProducts = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%';`;

export const invoiceByProductCode = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%'
    AND LOWER(line_item_product_code) = LOWER('%productCode%');`;

export const invoiceByProductCodeUsage = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId,
    line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%'
    AND LOWER(line_item_product_code) = LOWER('%productCode%');`;

export const invoiceByIdAccounts = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_usage_account_id as accountId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%';`;

export const invoiceByIdAccount = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%'
    AND line_item_usage_account_id = '%accountId%';`;

export const invoiceByIdAccountProducts = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%'
    AND line_item_usage_account_id = '%accountId%'
    AND LOWER(line_item_product_code) = LOWER('%productCode%');`;

export const invoiceByIdAccountProductsUsage = `select DISTINCT 
    bill_invoice_id as invoiceId,
    year, month,
    line_item_product_code as productCode,
    line_item_usage_account_id as accountId,
    line_item_resource_id as resourceId,
    line_item_usage_type AS usageType,
	line_item_usage_amount AS usageAmount,
	line_item_unblended_rate AS unblendedRate,
	line_item_unblended_cost AS unblendedCost,
	line_item_blended_rate AS blendedRate,
	line_item_blended_cost AS blendedCost,
	pricing_term AS pricingTerm,
	pricing_unit AS pricingUnit,
	pricing_rate_code AS pricingRateCode,
	pricing_currency AS pricingCurrency,
    line_item_usage_start_date AS startDate,
	line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
WHERE bill_invoice_id = '%invoiceId%'
    AND line_item_usage_account_id = '%accountId%'
    AND LOWER(line_item_product_code) = LOWER('%productCode%');`;