cubes:
  - name: department_level
    sql: >
      SELECT
        * 
      FROM
        (
        SELECT
          department.larkID AS lev1LarkID,
          department.NAME AS lev1,
          department.larkID AS lev2LarkID,
          department.NAME AS lev2,
          department.larkID AS lev3LarkID,
          department.NAME AS lev3,
          1 AS orderRank1,
          1 AS orderRank2,
          1 AS lev
        FROM
          department 
        WHERE
          deletedAt IS NULL 
          AND `Level` = 1 
          AND flatChildrenLarkID <> '' UNION
        SELECT
          t1.larkID AS lev1LarkID,
          t1.NAME AS lev1,
          t2.larkID AS lev2LarkID,
          t2.NAME AS lev2,
          t2.larkID AS lev3LarkID,
          t2.NAME AS lev3,
          2 AS orderRank1,
          2 AS orderRank2,
          2 AS lev
        FROM
          ( SELECT * FROM department WHERE deletedAt IS NULL ) AS t1
          LEFT JOIN ( SELECT * FROM department WHERE deletedAt IS NULL ) AS t2 ON t2.parentLarkID = t1.larkID 
        WHERE
          t1.parentLarkID = '0' 
          AND t1.larkID <> '0' 
          AND t2.larkID IS NOT NULL UNION
        SELECT
          t1.larkID AS lev1LarkID,
          t1.NAME AS lev1,
          t2.larkID AS lev2LarkID,
          t2.NAME AS lev2,
          t3.larkID AS lev3LarkID,
          t3.NAME AS lev3,
          2 AS orderRank1,
          3 AS orderRank2,
          3 AS lev
        FROM
          ( SELECT * FROM department WHERE deletedAt IS NULL ) AS t1
          LEFT JOIN ( SELECT * FROM department WHERE deletedAt IS NULL ) AS t2 ON t2.parentLarkID = t1.larkID
          LEFT JOIN ( SELECT * FROM department WHERE deletedAt IS NULL ) AS t3 ON t3.parentLarkID = t2.larkID 
        WHERE
          t1.parentLarkID = '0' 
          AND t1.larkID <> '0' 
          AND t2.larkID IS NOT NULL 
          AND t3.larkID IS NOT NULL 
        ) AS a 
      ORDER BY
        a.lev1 ASC,
        a.orderRank1 ASC,
        a.lev2 ASC,
        a.orderRank2 ASC,
        a.lev3 ASC
    data_source: default
    joins: []

    dimensions:
      - name: uniqueid
        sql: "CONCAT(lev1LarkID, '-', lev2LarkID, '-', lev3LarkID)"
        type: string
        primary_key: true

      - name: lev1
        sql: lev1
        type: string

      - name: lev1LarkID
        sql: lev1LarkID
        type: string

      - name: lev2
        sql: lev2
        type: string

      - name: lev2LarkID
        sql: lev2LarkID
        type: string
      
      - name: lev3
        sql: lev3
        type: string

      - name: lev3LarkID
        sql: lev3LarkID
        type: string

      - name: lev
        sql: lev
        type: number

    measures:
      - name: count
        type: count

    pre_aggregations:
      # Pre-aggregation definitions go here.
      # Learn more in the documentation: https://cube.dev/docs/caching/pre-aggregations/getting-started