text: DROP FUNCTION IF EXISTS grest.account_info (text);
  
  CREATE FUNCTION grest.account_info (_address text)
    RETURNS TABLE (
      STATUS text,
      DELEGATED_POOL varchar,
      TOTAL_BALANCE numeric,
      UTXO numeric,
      REWARDS numeric,
      WITHDRAWALS numeric,
      REWARDS_AVAILABLE numeric,
      RESERVES numeric,
      TREASURY numeric)
    LANGUAGE PLPGSQL
    AS $$
  DECLARE
    SA_ID integer DEFAULT NULL;
  BEGIN
    IF _address LIKE 'stake%' THEN
      -- Shelley stake address
      SELECT
        STAKE_ADDRESS.ID
      FROM
        STAKE_ADDRESS
      WHERE
        STAKE_ADDRESS.VIEW = _address INTO SA_ID;
      RETURN QUERY
      SELECT
        CASE WHEN STATUS_T.REGISTERED = TRUE THEN
          'registered'
        ELSE
          'not registered'
        END AS STATUS,
        POOL_T.DELEGATED_POOL,
        CASE WHEN (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)) < 0 THEN
          COALESCE(UTXO_T.UTXO, 0) + COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) + COALESCE(RESERVES_T.RESERVES, 0) + COALESCE(TREASURY_T.TREASURY, 0) - (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0))
        ELSE
          COALESCE(UTXO_T.UTXO, 0) + COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) + COALESCE(RESERVES_T.RESERVES, 0) + COALESCE(TREASURY_T.TREASURY, 0)
        END AS TOTAL_BALANCE,
        COALESCE(UTXO_T.UTXO, 0) AS UTXO,
        COALESCE(REWARDS_T.REWARDS, 0) AS REWARDS,
        COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) AS WITHDRAWALS,
        CASE WHEN (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)) <= 0 THEN
          0
        ELSE
          COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)
        END AS REWARDS_AVAILABLE,
        COALESCE(RESERVES_T.RESERVES, 0) AS RESERVES,
        COALESCE(TREASURY_T.TREASURY, 0) AS TREASURY
      FROM (
        SELECT
          EXISTS (
            SELECT
              1
            FROM
              STAKE_REGISTRATION
            WHERE
              STAKE_REGISTRATION.ADDR_ID = SA_ID
              AND NOT EXISTS (
                SELECT
                  TRUE
                FROM
                  STAKE_DEREGISTRATION
                WHERE
                  STAKE_DEREGISTRATION.ADDR_ID = STAKE_REGISTRATION.ADDR_ID
                  AND STAKE_DEREGISTRATION.TX_ID > STAKE_REGISTRATION.TX_ID)) AS REGISTERED) STATUS_T
    LEFT JOIN (
      SELECT
        POOL_HASH.VIEW AS DELEGATED_POOL
      FROM
        DELEGATION
        INNER JOIN POOL_HASH ON POOL_HASH.ID = DELEGATION.POOL_HASH_ID
      WHERE
        DELEGATION.ADDR_ID = SA_ID
        AND NOT EXISTS (
          SELECT
            TRUE
          FROM
            DELEGATION D
          WHERE
            D.ADDR_ID = DELEGATION.ADDR_ID
            AND D.ID > DELEGATION.ID)
          AND NOT EXISTS (
            SELECT
              TRUE
            FROM
              STAKE_DEREGISTRATION
            WHERE
              STAKE_DEREGISTRATION.ADDR_ID = DELEGATION.ADDR_ID
              AND STAKE_DEREGISTRATION.TX_ID > DELEGATION.TX_ID)) POOL_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(VALUE), 0) AS UTXO
      FROM
        TX_OUT
        LEFT JOIN TX_IN ON TX_OUT.TX_ID = TX_IN.TX_OUT_ID
          AND TX_OUT.INDEX::smallint = TX_IN.TX_OUT_INDEX::smallint
      WHERE
        TX_OUT.STAKE_ADDRESS_ID = SA_ID
        AND TX_IN.TX_IN_ID IS NULL) UTXO_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(REWARD.AMOUNT), 0) AS REWARDS
      FROM
        REWARD
      WHERE
        REWARD.ADDR_ID = SA_ID
        AND REWARD.SPENDABLE_EPOCH <= (
          SELECT
            MAX(NO)
          FROM
            EPOCH)) REWARDS_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(WITHDRAWAL.AMOUNT), 0) AS WITHDRAWALS
      FROM
        WITHDRAWAL
      WHERE
        WITHDRAWAL.ADDR_ID = SA_ID) WITHDRAWALS_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(RESERVE.AMOUNT), 0) AS RESERVES
      FROM
        RESERVE
      WHERE
        RESERVE.ADDR_ID = SA_ID) RESERVES_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(TREASURY.AMOUNT), 0) AS TREASURY
      FROM
        TREASURY
      WHERE
        TREASURY.ADDR_ID = SA_ID) TREASURY_T ON TRUE;
    ELSE
      SELECT
        TX_OUT.STAKE_ADDRESS_ID INTO SA_ID
      FROM
        TX_OUT
      WHERE
        ADDRESS = _address;
      IF SA_ID IS NOT NULL THEN
        -- Shelley address with an associated stake key
        RETURN QUERY
        SELECT
          CASE WHEN STATUS_T.REGISTERED = TRUE THEN
            'registered'
          ELSE
            'not registered'
          END AS STATUS,
          POOL_T.DELEGATED_POOL,
          CASE WHEN (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)) < 0 THEN
            COALESCE(UTXO_T.UTXO, 0) + COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) + COALESCE(RESERVES_T.RESERVES, 0) + COALESCE(TREASURY_T.TREASURY, 0) - (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0))
          ELSE
            COALESCE(UTXO_T.UTXO, 0) + COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) + COALESCE(RESERVES_T.RESERVES, 0) + COALESCE(TREASURY_T.TREASURY, 0)
          END AS TOTAL_BALANCE,
          COALESCE(UTXO_T.UTXO, 0) AS UTXO,
          COALESCE(REWARDS_T.REWARDS, 0) AS REWARDS,
          COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0) AS WITHDRAWALS,
          CASE WHEN (COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)) <= 0 THEN
            0
          ELSE
            COALESCE(REWARDS_T.REWARDS, 0) - COALESCE(WITHDRAWALS_T.WITHDRAWALS, 0)
          END AS REWARDS_AVAILABLE,
          COALESCE(RESERVES_T.RESERVES, 0) AS RESERVES,
          COALESCE(TREASURY_T.TREASURY, 0) AS TREASURY
        FROM (
          SELECT
            EXISTS (
              SELECT
                1
              FROM
                STAKE_REGISTRATION
              WHERE
                STAKE_REGISTRATION.ADDR_ID = SA_ID
                AND NOT EXISTS (
                  SELECT
                    TRUE
                  FROM
                    STAKE_DEREGISTRATION
                  WHERE
                    STAKE_DEREGISTRATION.ADDR_ID = STAKE_REGISTRATION.ADDR_ID
                    AND STAKE_DEREGISTRATION.TX_ID > STAKE_REGISTRATION.TX_ID)) AS REGISTERED) STATUS_T
      LEFT JOIN (
        SELECT
          POOL_HASH.VIEW AS DELEGATED_POOL
        FROM
          DELEGATION
          INNER JOIN POOL_HASH ON POOL_HASH.ID = DELEGATION.POOL_HASH_ID
        WHERE
          DELEGATION.ADDR_ID = SA_ID
          AND NOT EXISTS (
            SELECT
              TRUE
            FROM
              DELEGATION D
            WHERE
              D.ADDR_ID = DELEGATION.ADDR_ID
              AND D.ID > DELEGATION.ID)
            AND NOT EXISTS (
              SELECT
                TRUE
              FROM
                STAKE_DEREGISTRATION
              WHERE
                STAKE_DEREGISTRATION.ADDR_ID = DELEGATION.ADDR_ID
                AND STAKE_DEREGISTRATION.TX_ID > DELEGATION.TX_ID)) POOL_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(VALUE), 0) AS UTXO
      FROM
        TX_OUT
        LEFT JOIN TX_IN ON TX_OUT.TX_ID = TX_IN.TX_OUT_ID
          AND TX_OUT.INDEX::smallint = TX_IN.TX_OUT_INDEX::smallint
      WHERE
        TX_OUT.STAKE_ADDRESS_ID = SA_ID
          AND TX_IN.TX_IN_ID IS NULL) UTXO_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(REWARD.AMOUNT), 0) AS REWARDS
      FROM
        REWARD
      WHERE
        REWARD.ADDR_ID = SA_ID
          AND REWARD.SPENDABLE_EPOCH <= (
            SELECT
              MAX(NO)
            FROM
              EPOCH)) REWARDS_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(WITHDRAWAL.AMOUNT), 0) AS WITHDRAWALS
      FROM
        WITHDRAWAL
      WHERE
        WITHDRAWAL.ADDR_ID = SA_ID) WITHDRAWALS_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(RESERVE.AMOUNT), 0) AS RESERVES
      FROM
        RESERVE
      WHERE
        RESERVE.ADDR_ID = SA_ID) RESERVES_T ON TRUE
    LEFT JOIN (
      SELECT
        COALESCE(SUM(TREASURY.AMOUNT), 0) AS TREASURY
      FROM
        TREASURY
      WHERE
        TREASURY.ADDR_ID = SA_ID) TREASURY_T ON TRUE;
      END IF;
    END IF;
  END;
  $$;
  
  COMMENT ON FUNCTION grest.account_info IS 'Get the account info of an address';
  
  