-- ITBBU
select updated_date
     , moc
     
     , split_part(file_name, '/',1) as region

     , managedelement ->> 'managedElementType'   as managedElementType
     , managedelement ->> 'moId'                 as managedElement
     , cleansing(managedelement ->> 'userLabel') as ne_name
     , ldn                                       as ldn

     , parameters ->> 'moId'                     as moId
     , cleansing(parameters ->> 'userLabel')     as userLabel
     , cleansing(parameters ->> 'alias')         as alias
     , parameters ->> 'cellLocalId'              as cellLocalId
     , parameters ->> 'tac'                      as tac
     , parameters ->> 'simuLoadSwchDl'           as simuLoadSwchDl
     , parameters ->> 'altitude'                 as altitude

--      , managedelement
--      , parameters
from dump
where moc = 'CUEUtranCellFDDLTE'
  and left(cleansing(managedelement ->> 'userLabel'), 6) in ('AGA002','AGA003')
order by moc
limit 100
;

select * from network_site where tech = '4G';

-- SDR
select updated_date
     , moc
     
     , file_name

     , managedelement ->> 'subType'              as managedElementType
     , managedelement ->> 'MEID'                 as managedElement
     , cleansing(managedelement ->> 'USERLABEL') as ne_name
     , ldn                                       as ldn

     , parameters ->> 'EUtranCellFDDId'          as moId
     , cleansing(parameters ->> 'userLabel')     as userLabel
     , cleansing(parameters ->> 'alias')         as alias
     , parameters ->> 'cellLocalId'              as cellLocalId
     , parameters ->> 'tac'                      as tac
     , parameters ->> 'simuLoadSwchDl'           as simuLoadSwchDl
     , parameters ->> 'altitude'                 as altitude

--      , managedelement
--      , parameters
from dump
where moc = 'EUtranCellFDD'
  and left(cleansing(managedelement ->> 'USERLABEL'), 6) in ('AMB002','AMB003')
order by moc
limit 100
;


-- COMBINED ITBBU + SDR
select updated_date
     , moc

     , managedelement ->> 'managedElementType'   as managedElementType
     , managedelement ->> 'moId'                 as managedElement
     , cleansing(managedelement ->> 'userLabel') as ne_name
     , ldn                                       as ldn

     , parameters ->> 'moId'                     as moId
     , cleansing(parameters ->> 'userLabel')     as userLabel
     , cleansing(parameters ->> 'alias')         as alias
     , parameters ->> 'cellLocalId'              as cellLocalId
     , parameters ->> 'tac'                      as tac
     , parameters ->> 'simuLoadSwchDl'           as simuLoadSwchDl
     , parameters ->> 'altitude'                 as altitude

--      , managedelement
--      , parameters
from dump
where moc = 'CUEUtranCellFDDLTE'
  and left(cleansing(managedelement ->> 'userLabel'), 6) = 'AGA002'

union all

select updated_date
     , moc

     , managedelement ->> 'subType'              as managedElementType
     , managedelement ->> 'MEID'                 as managedElement
     , cleansing(managedelement ->> 'USERLABEL') as ne_name
     , ldn                                       as ldn

     , parameters ->> 'EUtranCellFDDId'          as moId
     , cleansing(parameters ->> 'userLabel')     as userLabel
     , cleansing(parameters ->> 'alias')         as alias
     , parameters ->> 'cellLocalId'              as cellLocalId
     , parameters ->> 'tac'                      as tac
     , parameters ->> 'simuLoadSwchDl'           as simuLoadSwchDl
     , parameters ->> 'altitude'                 as altitude

--      , managedelement
--      , parameters
from dump
where moc = 'EUtranCellFDD'
  and left(cleansing(managedelement ->> 'USERLABEL'), 6) = 'AMB002'
;


select distinct case 
        when split_part(file_name, '/',1) = 'ume10' then 'SULAWESI' 
        when split_part(file_name, '/',1) = 'ume9' then 'KALIMANTAN' 
        when split_part(file_name, '/',1) = 'ume7' then 'PUMA' end || '/' || split_part(file_name, '/',2) as filename 
from dump
limit 100
;

update dump
set file_name = case
                    when split_part(file_name, '/',1) = 'ume10' then 'SULAWESI'
                    when split_part(file_name, '/',1) = 'ume9' then 'KALIMANTAN'
                    when split_part(file_name, '/',1) = 'ume7' then 'PUMA' end || '/' || split_part(file_name, '/',2)
-- where 1 = 1
;