create table fr_asset_bck_ls_384 as select * from fr_asset ;

ALTER TABLE nep_history_reference
ADD virtual_col GENERATED ALWAYS AS (
  CASE 
    WHEN elementid LIKE 'Cabinet%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Shelf%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 2) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Board%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 4) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Transceiver%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 5) - INSTR(elementid, '_') - 1
        )
    ELSE NULL
  END
) VIRTUAL;

CREATE INDEX idx_nep_virtual_col_2 ON nep_history_reference(virtual_col);

ALTER TABLE nep_change_log
ADD virtual_col GENERATED ALWAYS AS (
  CASE 
    WHEN elementid LIKE 'Cabinet%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Shelf%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 2) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Board%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 4) - INSTR(elementid, '_') - 1
        )
    WHEN elementid LIKE 'Transceiver%' THEN SUBSTR(elementid,
         INSTR(elementid, '_') + 1,
         INSTR(elementid, '/', -1, 5) - INSTR(elementid, '_') - 1
        )
    ELSE NULL
  END
) VIRTUAL;

CREATE INDEX idx_nep_virtual_col ON nep_change_log(virtual_col);

CREATE TABLE TMP_TABLE_8_4 AS 
(
    SELECT OBJECTID, 'BSC' AS R FROM BSC
    UNION
    SELECT OBJECTID, 'MME' AS R FROM MME
    UNION
    SELECT OBJECTID, 'RNC' AS R FROM RNC
    UNION
    SELECT OBJECTID, 'SGW' AS R FROM SGW
    UNION
    SELECT OBJECTID, 'SRANCONTROLLER' AS R FROM SRANCONTROLLER
    UNION
    SELECT OBJECTID, 'SRANBS' AS R FROM RANBSNODE
    UNION
    SELECT OBJECTID, 'BTS' AS R FROM BTS
    UNION
    SELECT OBJECTID, 'ENODEB' AS R FROM ENODEB
    UNION
    SELECT OBJECTID, 'GNODEB' AS R FROM GNODEB
    UNION
    SELECT OBJECTID, 'NODEB' AS R FROM NODEB
    UNION
    SELECT OBJECTID, 'CGP' AS R FROM CGP
    UNION
    SELECT OBJECTID, 'COREELEMENT' AS R FROM COREELEMENT
    UNION
    SELECT OBJECTID, 'DXX' AS R FROM DXX
    UNION
    SELECT OBJECTID, 'ADM' AS R FROM ADM
    UNION
    SELECT OBJECTID, 'ATMSWITCH' AS R FROM ATMSWITCH
    UNION
    SELECT OBJECTID, 'ETHERNETSWITCH' AS R FROM ETHERNETSWITCH
    UNION
    SELECT OBJECTID, 'IDU' AS R FROM IDU
    UNION
    SELECT OBJECTID, 'ROUTER' AS R FROM ROUTER
    UNION
    SELECT OBJECTID, 'SMARTEXTENSION' AS R FROM SMARTEXTENSION
    UNION
    SELECT OBJECTID, 'WDM' AS R FROM WDM
    UNION
    SELECT OBJECTID, 'DDF' AS R FROM GSMDFD
    UNION
    SELECT OBJECTID, 'GGSN' AS R FROM GGSN
    UNION
    SELECT OBJECTID, 'HLR' AS R FROM HLR
    UNION
    SELECT OBJECTID, 'HSS' AS R FROM HSS
    UNION
    SELECT OBJECTID, 'IPCLK' AS R FROM IPCLK
    UNION
    SELECT OBJECTID, 'MASK' AS R FROM MASK
    UNION
    SELECT OBJECTID, 'MDU' AS R FROM MDU
    UNION
    SELECT OBJECTID, 'MFS' AS R FROM MFS
    UNION
    SELECT OBJECTID, 'MGW' AS R FROM MGW
    UNION
    SELECT OBJECTID, 'MISCTRAFFIC' AS R FROM MISCTRAFFIC
    UNION
    SELECT OBJECTID, 'MSC' AS R FROM MSC
    UNION
    SELECT OBJECTID, 'MSS' AS R FROM MSS
    UNION
    SELECT OBJECTID, 'MULTI TECHNOLOGY ACCESS' AS R FROM MULTITECHNOLOGYACCESS
    UNION
    SELECT OBJECTID, 'ONT' AS R FROM ONT
    UNION
    SELECT OBJECTID, 'OPTICAL SPLITTER' AS R FROM OPTICAL_SPLITTER
    UNION
    SELECT OBJECTID, 'PGW' AS R FROM PGW
    UNION
    SELECT OBJECTID, 'SGSN' AS R FROM SGSN
    UNION
    SELECT OBJECTID, 'TRAU' AS R FROM TRAU
    UNION
    SELECT OBJECTID, 'XDSL BOX' AS R FROM XDSL_BOX
    UNION
    SELECT OBJECTID, 'XDSL SPLITTER' AS R FROM XDSL_SPLITTER
);

create table objectid_tmp_table_001 as select * from (select objectid, max(r)as r from TMP_TABLE_8_4 group by objectid having count(*) = 1);

-- set non decomissioned and non existing serials as decomissioned
update nep_history_reference set is_decomissioned = 1 where serial_number is not null and is_decomissioned = 0 and serial_number not in
(select value from elementadditionalinfo where lower(attribute) = 'serialnumber' and value is not null);

-- set history referencing more than one node as decomissioned
update nep_history_reference set is_decomissioned = 1  
where virtual_col is not null and serial_number is not null and virtual_col in (select objectid from TMP_TABLE_8_4 group by objectid having count(*) > 1);

-- set history without existing nodes to 1
update nep_history_reference set is_decomissioned = 1 where is_decomissioned = 0 and serial_number is not null and virtual_col is not null
and virtual_col not in (select objectid from TMP_TABLE_8_4);


merge into nep_history_reference n 
using objectid_tmp_table_001 t
on (t.objectid = n.virtual_col and n.virtual_col is not null)
when matched then update set n.elementid = replace(elementid, n.virtual_col, t.r || '_' || n.virtual_col);

update nep_history_reference set is_decomissioned = 1 where elementid like 'Financial%' and serial_number is not null
and is_decomissioned = 0;

merge into nep_change_log n 
using objectid_tmp_table_001 t
on (t.objectid = n.virtual_col and n.virtual_col is not null)
when matched then update set n.elementid = replace(elementid, n.virtual_col, t.r || '_' || n.virtual_col);


DROP INDEX IDX_NEP_VIRTUAL_COL;
DROP INDEX IDX_NEP_VIRTUAL_COL_2;
drop table objectid_tmp_table_001;
ALTER TABLE nep_change_log DROP COLUMN virtual_col;
ALTER TABLE nep_history_reference DROP COLUMN virtual_col;
drop table TMP_TABLE_8_4;




DELETE FROM fr_asset
WHERE
    serial_number IS NULL
    AND ( initial_cost IS NULL
          OR initial_cost = 0 )
    AND serial IN (
        SELECT
            serial_number
        FROM
            fr_asset
        WHERE
            serial_number IS NOT NULL
    );