/* This view approximates CD1's account_dim table. - Sam T - 6/23/23 */
CREATE OR REPLACE VIEW canvas.view_accounts AS
SELECT a.id
, a.name 
/* null values for depth means you need more left joins and case blocks */
, CASE
  WHEN a.parent_account_id IS NULL THEN 0
  WHEN b.parent_account_id IS NULL THEN 1
  WHEN c.parent_account_id IS NULL THEN 2
  WHEN d.parent_account_id IS NULL THEN 3
  WHEN e.parent_account_id IS NULL THEN 4
  WHEN f.parent_account_id IS NULL THEN 5
  WHEN g.parent_account_id IS NULL THEN 6
  ELSE NULL
  END AS depth
, a.workflow_state
, b.name AS parent_account
, a.parent_account_id 
, c.name AS grandparent_account
, b.parent_account_id AS grandparent_account_id

/*Root_account column seems unnecessary. It should always be the same. The
  logic is interestinc. Account is root if it has no parent. */
, CASE 
  WHEN a.parent_account_id IS NULL THEN a.name
  WHEN b.parent_account_id IS NULL THEN b.name
  WHEN c.parent_account_id IS NULL THEN c.name
  WHEN d.parent_account_id IS NULL THEN d.name
  WHEN e.parent_account_id IS NULL THEN e.name
  WHEN f.parent_account_id IS NULL THEN f.name
  WHEN g.parent_account_id IS NULL THEN g.name
  ELSE NULL
  END AS root_account

, CASE 
  WHEN a.parent_account_id IS NULL THEN a.id
  WHEN b.parent_account_id IS NULL THEN b.id
  WHEN c.parent_account_id IS NULL THEN c.id
  WHEN d.parent_account_id IS NULL THEN d.id
  WHEN e.parent_account_id IS NULL THEN e.id
  WHEN f.parent_account_id IS NULL THEN f.id
  WHEN g.parent_account_id IS NULL THEN g.id
  ELSE NULL
  END AS root_account_id 

/*Account is at subaccount1 level if it has parent but no grandparent.
  In this block it is tempting to have 
  WHEN g.parent_account_id IS NOT NULL 
  THEN g.name 
  but we cannot be certain that h.parent_account_id IS NULL, because we
  don't have left join h. If any row has non-null g.parent_account_id,
  it will also have null depth. Both indicate that your account hierachy 
  is deep enough that you need more left joins and case blocks. */
, CASE
  WHEN a.parent_account_id IS NOT NULL 
  AND b.parent_account_id IS NULL 
  THEN a.name
  WHEN b.parent_account_id IS NOT NULL
  AND c.parent_account_id IS NULL 
  THEN b.name
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN c.name
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN d.name
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN e.name
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN f.name
  ELSE NULL
  END AS subaccount1

, CASE
  WHEN a.parent_account_id IS NOT NULL 
  AND b.parent_account_id IS NULL 
  THEN a.id
  WHEN b.parent_account_id IS NOT NULL
  AND c.parent_account_id IS NULL 
  THEN b.id
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN c.id
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN d.id
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN e.id
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN f.id
  ELSE NULL
  END AS subaccount1_id 

/*Account is subaccount2 if it has grandparent, no great-grandparent*/
, CASE
  WHEN b.parent_account_id IS NOT NULL
  AND c.parent_account_id IS NULL 
  THEN a.name
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN b.name
  WHEN d.parent_account_id IS NOT NULL 
  AND e.parent_account_id IS NULL 
  THEN c.name
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN d.name
  WHEN f.parent_account_id IS NOT NULL 
  AND g.parent_account_id IS NULL 
  THEN e.name
  ELSE NULL
  END AS subaccount2

, CASE
  WHEN b.parent_account_id IS NOT NULL
  AND c.parent_account_id IS NULL 
  THEN a.id
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN b.id
  WHEN d.parent_account_id IS NOT NULL 
  AND e.parent_account_id IS NULL 
  THEN c.id
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN d.id
  WHEN f.parent_account_id IS NOT NULL 
  AND g.parent_account_id IS NULL 
  THEN e.id
  ELSE NULL
  END AS subaccount2_id 
  
/*Account is subaccount3 if it has great-grandparent, no second great-grandparent*/
, CASE
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN a.name
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN b.name
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN c.name
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN d.name
  ELSE NULL
  END AS subaccount3 

, CASE
  WHEN c.parent_account_id IS NOT NULL
  AND d.parent_account_id IS NULL 
  THEN a.id
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN b.id
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN c.id
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN d.id
  ELSE NULL
  END AS subaccount3_id 
  
/*Account is subaccount4 if it has second great-grandparent, no third great-grandparent*/
, CASE
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN a.name
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN b.name
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN c.name
  ELSE NULL
  END AS subaccount4

, CASE
  WHEN d.parent_account_id IS NOT NULL
  AND e.parent_account_id IS NULL 
  THEN a.id
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN b.id
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN c.id
  ELSE NULL
  END AS subaccount4_id

/*Account is subaccount5 if it has third great-grandparent, no fourth great-grandparent*/
, CASE
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN a.name
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN b.name
  ELSE NULL
  END AS subaccount5

, CASE
  WHEN e.parent_account_id IS NOT NULL
  AND f.parent_account_id IS NULL 
  THEN a.id
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN b.id
  ELSE NULL
  END AS subaccount5_id

/*Account is subaccount6 if it has fourth great-grandparent, no fifth great-grandparent*/
, CASE
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN a.name
  ELSE NULL
  END AS subaccount6

, CASE
  WHEN f.parent_account_id IS NOT NULL
  AND g.parent_account_id IS NULL 
  THEN a.id
  ELSE NULL
  END AS subaccount6_id

, a.sis_source_id
FROM canvas.accounts a
LEFT JOIN canvas.accounts b ON b.id = a.parent_account_id
LEFT JOIN canvas.accounts c ON c.id = b.parent_account_id
LEFT JOIN canvas.accounts d ON d.id = c.parent_account_id
LEFT JOIN canvas.accounts e ON e.id = d.parent_account_id
LEFT JOIN canvas.accounts f ON f.id = e.parent_account_id
LEFT JOIN canvas.accounts g ON f.id = f.parent_account_id
;