//implementation details
//https://github.com/cube-js/cube.js/issues/563

cube(`Orders`, {
  sql: `
  SELECT 
  o.c_order_id,
  ol.c_orderline_id,
  o.dateordered,
  ol.ad_client_id,
  ol.QtyOrdered,
  COALESCE(sr.lastname,'Empty') order_salesrep_name,

  COALESCE(ords.name,'Empty') as order_source_name,
  o.issotrx
FROM c_orderline ol
JOIN c_order o  ON (o.c_order_id=ol.c_order_id)
LEFT JOIN ad_user sr ON o.salesrep_id = sr.ad_user_id
LEFT JOIN c_ordersource ords ON o.c_ordersource_id = ords.c_ordersource_id

WHERE ol.ad_client_id=1000026 
AND (o.docstatus = ANY (ARRAY['CO'::text, 'CL'::text])) 
AND o.issotrx='Y' 
AND o.dateordered  = '2020-04-01'

  `,


  title: `Order Facts`,
  description: `All Order related information`,

  measures: {
    orderLineCount: {
      title: `Order Line Count`,
      type: `count`,
      sql: `c_order_id`
    },

    orderDistinctCount: {
      title: `Order Count`,
      sql: `c_order_id`,
      type: `countDistinct`
    },

    orderDistinctApprox: {
      title: `Total Distinct Approx`,
      sql: `c_order_id`,
      type: `countDistinctApprox`
    }
  },

  dimensions: {
    ordersource: {
      sql: `order_source_name`,
      type: `string`
    },

    dateordered: {
      title: `Order Date`,
      sql: `dateordered`,
      type: `time`
    }
  },

  //https://cube.dev/docs/pre-aggregations#rollup
  preAggregations: {

    //this preaggregation
    // Use this REST query: {"measures":["Orders.orderLineCount"],"timeDimensions":[{"dimension":"Orders.dateordered","dateRange":"This month"}],"dimensions":["Orders.ordersource"],"filters":[]}
    // RESULT: PASS
    count: {
      type: `rollup`,
      measureReferences: [orderLineCount],
      dimensionReferences: [dateordered, ordersource],
      timeDimensionReference: dateordered,
      granularity: `day`
    },

    //// Use this REST query: {"measures":["Orders.orderDistinctCount"],"timeDimensions":[{"dimension":"Orders.dateordered","dateRange":"This month"}],"dimensions":["Orders.ordersource"],"filters":[]}
    // RESULT: FALSE => doesn't start preaggregation according to Selection RULES https://cube.dev/docs/pre-aggregations#rollup-rollup-selection-rules
    Distinct: {
      type: `rollup`,
      measureReferences: [orderDistinctCount],
      dimensionReferences: [dateordered, ordersource],
      timeDimensionReference: dateordered,
      granularity: `day`
    },

    //// Use this REST query: {"measures":["Orders.orderDistinctApprox"],"timeDimensions":[{"dimension":"Orders.dateordered","granularity":"day"}],"dimensions":["Orders.ordersource"],"filters":[]}
    // RESULT: FALSE => Error: Error: Approximate distinct count is not supported by this DB
    DistinctApprox: {
      type: `rollup`,
      measureReferences: [orderDistinctApprox],
      dimensionReferences: [dateordered, ordersource],
      timeDimensionReference: dateordered,
      granularity: `day`
    }
  }

});