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

export const productResourceUsageQuery = `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 COALESCE(NULLIF(TRIM(product_region_code), ''), 'global') = '%regionCode%'
    AND LOWER(line_item_product_code) = LOWER('%productCode%')
    AND line_item_resource_id = '%resourceId%'
ORDER BY line_item_usage_start_date ASC;`;


export const allProductUsageQuery = `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_cost AS unblendedCost,
    line_item_blended_cost AS blendedCost,
    line_item_usage_start_date AS startDate,
    line_item_usage_end_date AS endDate
FROM ${process.env.ATHENA_CU_TABLE}
ORDER BY productCode, regionCode, accountId, startDate ASC;`;



// /accounts/cost-usage-summary
export const accountCostUsageSummaryQuery = `
    SELECT 
        line_item_usage_account_id AS accountId,
        SUM(line_item_usage_amount) AS totalUsageAmount,
        SUM(line_item_unblended_cost) AS totalUnblendedCost,
        SUM(line_item_blended_cost) AS totalBlendedCost
    FROM ${process.env.ATHENA_CU_TABLE}
    GROUP BY line_item_usage_account_id
    ORDER BY totalUnblendedCost DESC;
`;

export const currentMonthSummary = `
  SELECT 
    SUM(line_item_blended_cost) as totalBlendedCost,
    SUM(line_item_unblended_cost) as totalUnblendedCost,
    COUNT(DISTINCT line_item_usage_account_id) as accountCount,
    COUNT(DISTINCT line_item_product_code) as serviceCount,
    COUNT(DISTINCT bill_invoice_id) as invoiceCount,
    CONCAT(year, '-', LPAD(month, 2, '0')) as month
  FROM ${process.env.ATHENA_CU_TABLE}
   WHERE year = CAST(YEAR(CURRENT_DATE) AS VARCHAR) 
    AND month = CAST(MONTH(CURRENT_DATE) AS VARCHAR)
    AND line_item_blended_cost > 0
  GROUP BY year, month
`;

export const previousMonthSummary = `
  SELECT 
    SUM(line_item_blended_cost) as totalBlendedCost,
    CONCAT(year, '-', LPAD(month, 2, '0')) as month
  FROM ${process.env.ATHENA_CU_TABLE}
   WHERE year = CAST(YEAR(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND month = CAST(MONTH(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND line_item_blended_cost > 0
  GROUP BY year, month
`;

export const serviceBreakdown = `
  SELECT 
    line_item_product_code as productCode,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_resource_id) as resourceCount,
    COUNT(DISTINCT line_item_usage_account_id) as accountCount
  FROM ${process.env.ATHENA_CU_TABLE}
   WHERE year = CAST(YEAR(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND month = CAST(MONTH(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND line_item_blended_cost > 0
  GROUP BY line_item_product_code
  ORDER BY totalCost DESC
  LIMIT 10
`;

export const topAccounts = `
  SELECT 
    line_item_usage_account_id as accountId,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_product_code) as serviceCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE year = CAST(YEAR(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND month = CAST(MONTH(DATE_ADD('month', -1, CURRENT_DATE)) AS VARCHAR)
    AND line_item_blended_cost > 0
  GROUP BY line_item_usage_account_id
  ORDER BY totalCost DESC
  LIMIT 10
`;

export const dailyTrends = `
  SELECT 
    line_item_usage_start_date as date,
    SUM(line_item_blended_cost) as blendedCost,
    SUM(line_item_unblended_cost) as unblendedCost
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date >= date_add('day', -30, current_date)
    AND line_item_blended_cost > 0
  GROUP BY line_item_usage_start_date
  ORDER BY date DESC
  LIMIT 31
`;

export const todaySpending = `
  SELECT
    SUM(line_item_blended_cost) as todaysCost,
    COUNT(DISTINCT line_item_product_code) as servicesUsed
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date = CURRENT_DATE
    AND line_item_blended_cost > 0`;

// This week vs last week
export const weeklyComparison = `
  SELECT 
    CASE 
      WHEN line_item_usage_start_date >= date_add('day', -7, current_date) THEN 'this_week'
      WHEN line_item_usage_start_date >= date_add('day', -14, current_date) THEN 'last_week'
    END as week_period,
    SUM(line_item_blended_cost) as totalCost
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date >= date_add('day', -14, current_date)
  GROUP BY CASE 
    WHEN line_item_usage_start_date >= date_add('day', -7, current_date) THEN 'this_week'
    WHEN line_item_usage_start_date >= date_add('day', -14, current_date) THEN 'last_week'
  END
`;

// Service drill-down queries
export const serviceAccountBreakdown = `
  SELECT 
    line_item_usage_account_id as accountId,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_resource_id) as resourceCount,
    COUNT(DISTINCT line_item_usage_start_date) as daysActive
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_product_code = '%serviceCode%'
    AND line_item_usage_start_date >= date_add('day', -%days%, current_date)
    AND line_item_blended_cost > 0
  GROUP BY line_item_usage_account_id
  ORDER BY totalCost DESC
`;

export const serviceTrends = `
  SELECT 
    line_item_usage_start_date as date,
    SUM(line_item_blended_cost) as dailyCost,
    COUNT(DISTINCT line_item_usage_account_id) as accountCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_product_code = '%serviceCode%'
    AND line_item_usage_start_date >= date_add('day', -%days%, current_date)
    AND line_item_blended_cost > 0
  GROUP BY line_item_usage_start_date
  ORDER BY date DESC
`;

// Account drill-down queries
export const accountServiceBreakdown = `
  SELECT 
    line_item_product_code as productCode,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_resource_id) as resourceCount,
    COUNT(DISTINCT line_item_usage_start_date) as daysActive
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_account_id = '%accountId%'
    AND line_item_usage_start_date >= date_add('day', -%days%, current_date)
    AND line_item_blended_cost > 0
  GROUP BY line_item_product_code
  ORDER BY totalCost DESC
`;

export const accountTrends = `
  SELECT 
    line_item_usage_start_date as date,
    SUM(line_item_blended_cost) as dailyCost,
    COUNT(DISTINCT line_item_product_code) as serviceCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_account_id = '%accountId%'
    AND line_item_usage_start_date >= date_add('day', -%days%, current_date)
    AND line_item_blended_cost > 0
  GROUP BY line_item_usage_start_date
  ORDER BY date DESC
`;

// Flexible date range queries
export const trendsWithDateRange = (startDate, endDate) => `
  SELECT 
    line_item_usage_start_date as date,
    SUM(line_item_blended_cost) as blendedCost,
    SUM(line_item_unblended_cost) as unblendedCost,
    COUNT(DISTINCT line_item_product_code) as serviceCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date >= date('${startDate}')
    AND line_item_usage_start_date <= date('${endDate}')
  GROUP BY line_item_usage_start_date
  ORDER BY date DESC
`;

export const servicesWithDateRange = (startDate, endDate) => `
  SELECT 
    line_item_product_code as productCode,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_usage_account_id) as accountCount,
    COUNT(DISTINCT line_item_resource_id) as resourceCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date >= date('${startDate}')
    AND line_item_usage_start_date <= date('${endDate}')
  GROUP BY line_item_product_code
  ORDER BY totalCost DESC
`;

export const accountsWithDateRange = (startDate, endDate) => `
  SELECT 
    line_item_usage_account_id as accountId,
    SUM(line_item_blended_cost) as totalCost,
    COUNT(DISTINCT line_item_product_code) as serviceCount,
    COUNT(DISTINCT line_item_resource_id) as resourceCount
  FROM ${process.env.ATHENA_CU_TABLE}
  WHERE line_item_usage_start_date >= date('${startDate}')
    AND line_item_usage_start_date <= date('${endDate}')
  GROUP BY line_item_usage_account_id
  ORDER BY totalCost DESC
`;