-- Insert version configuration
INSERT INTO DB_VIEW_CONF (VIEW_NAME, ATTRIBUTE_NAME, VALUE)
VALUES ('NE_ELEMENT_VIEW', 'VERSION_NAME', 'Actual Network');

-- Create/Recreate the view with all elements unified using single version lookup
CREATE OR REPLACE FORCE EDITIONABLE VIEW NE_ELEMENT_VIEW (
    NODE_NAME,
    NODE_TYPE,
    NODE_ID,
    NE_TYPE,
    VENDOR,
    SITE_ID
) AS
WITH SELECTED_VERSION AS (
    SELECT VERSIONID AS VERSION_ID
    FROM VERSION
    WHERE NAME = (
        SELECT VALUE
        FROM DB_VIEW_CONF
        WHERE VIEW_NAME = 'NE_ELEMENT_VIEW'
          AND ATTRIBUTE_NAME = 'VERSION_NAME'
    )
)
SELECT NODE_NAME, NODE_TYPE, NODE_ID, NE_TYPE, VENDOR, SITE_ID
FROM (
    SELECT v.VERSION_ID, t.*
    FROM SELECTED_VERSION v
    CROSS JOIN (
        /* BTS */
        SELECT bts.name AS NODE_NAME,
               CASE WHEN bts.dtype = 'Bts' THEN 'BTS' ELSE 'Role2G' END AS NODE_TYPE,
               bts.objectid AS NODE_ID,
               btstype.name AS NE_TYPE,
               manuf.name AS VENDOR,
               site.objectid AS SITE_ID,
               bts.versionid
        FROM bts
        LEFT JOIN btstype ON bts.btstypeid = btstype.btstypeid
        LEFT JOIN manufacturer manuf ON btstype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON bts.siteid = site.siteid

        UNION ALL

        /* MDF */
        SELECT mdf.name,
               'MDF',
               mdf.objectid,
               mdftype.name,
               manuf.name,
               site.objectid,
               mdf.versionid
        FROM mdf
        JOIN mdftype ON mdf.mdftypeid = mdftype.mdftypeid
        LEFT JOIN manufacturer manuf ON mdftype.manufacturerid = manuf.manufacturerid
        JOIN site ON mdf.siteid = site.siteid

        UNION ALL

        /* Router */
        SELECT router.name,
               'Router',
               router.objectid,
               routertype.name,
               manuf.name,
               site.objectid,
               router.versionid
        FROM router
        LEFT JOIN routertype ON router.routertypeid = routertype.routertypeid
        LEFT JOIN manufacturer manuf ON routertype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON router.siteid = site.siteid

        UNION ALL

        /* NodeB */
        SELECT nodeb.name,
               CASE WHEN nodeb.dtype = 'NodeB' THEN 'NodeB' ELSE 'Role3G' END,
               nodeb.objectid,
               nodebtype.name,
               manuf.name,
               site.objectid,
               nodeb.versionid
        FROM nodeb
        LEFT JOIN nodebtype ON nodeb.nodebtypeid = nodebtype.nodebtypeid
        LEFT JOIN manufacturer manuf ON nodebtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON nodeb.siteid = site.siteid

        UNION ALL

        /* ENodeB */
        SELECT enodeb.name,
               CASE WHEN enodeb.dtype = 'ENodeB' THEN 'eNodeB' ELSE 'Role4G' END,
               enodeb.objectid,
               enodebtype.name,
               manuf.name,
               site.objectid,
               enodeb.versionid
        FROM enodeb
        LEFT JOIN enodebtype ON enodeb.enodebtypeid = enodebtype.enodebtypeid
        LEFT JOIN manufacturer manuf ON enodebtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON enodeb.siteid = site.siteid

        UNION ALL

        /* RanBsNode */
        SELECT ranbsnode.name,
               'RanBs',
               ranbsnode.objectid,
               ranbsnodetype.name,
               manuf.name,
               site.objectid,
               ranbsnode.versionid
        FROM ranbsnode
        LEFT JOIN ranbsnodetype ON ranbsnode.ranbstypeid = ranbsnodetype.ranbstypeid
        LEFT JOIN manufacturer manuf ON ranbsnodetype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON ranbsnode.siteid = site.siteid

        UNION ALL

        /* BSC */
        SELECT bsc.name,
               CASE WHEN bsc.dtype = 'Bsc' THEN 'BSC' ELSE 'Role2GController' END,
               bsc.objectid,
               bsctype.name,
               manuf.name,
               site.objectid,
               bsc.versionid
        FROM bsc
        LEFT JOIN bsctype ON bsc.bsctypeid = bsctype.bsctypeid
        LEFT JOIN manufacturer manuf ON bsctype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON bsc.siteid = site.siteid

        UNION ALL

        /* RNC */
        SELECT rnc.name,
               CASE WHEN rnc.dtype = 'Rnc' THEN 'RNC' ELSE 'Role3GController' END,
               rnc.objectid,
               rnctype.name,
               manuf.name,
               site.objectid,
               rnc.versionid
        FROM rnc
        LEFT JOIN rnctype ON rnc.rnctypeid = rnctype.rnctypeid
        LEFT JOIN manufacturer manuf ON rnctype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON rnc.siteid = site.siteid

        UNION ALL

        /* SRanController */
        SELECT srancontroller.name,
               'SRanController',
               srancontroller.objectid,
               srancontrollertype.name,
               manuf.name,
               site.objectid,
               srancontroller.versionid
        FROM srancontroller
        LEFT JOIN srancontrollertype ON srancontroller.srantypeid = srancontrollertype.srantypeid
        LEFT JOIN manufacturer manuf ON srancontrollertype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON srancontroller.siteid = site.siteid

        UNION ALL

        /* TRAU */
        SELECT trau.name,
               'TRAU',
               trau.objectid,
               trautype.name,
               manuf.name,
               site.objectid,
               trau.versionid
        FROM trau
        LEFT JOIN trautype ON trau.trautypeid = trautype.trautypeid
        LEFT JOIN manufacturer manuf ON trautype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON trau.siteid = site.siteid

        UNION ALL

        /* CoreElement */
        SELECT coreelement.name,
               'COREELEMENT',
               coreelement.objectid,
               coreelementtype.name,
               manuf.name,
               site.objectid,
               coreelement.versionid
        FROM coreelement
        LEFT JOIN coreelementtype ON coreelement.coreelementtypeid = coreelementtype.coreelementtypeid
        LEFT JOIN manufacturer manuf ON coreelementtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON coreelement.siteid = site.siteid

        UNION ALL

        /* MSC */
        SELECT msc.name,
               'MSC',
               msc.objectid,
               msctype.name,
               manuf.name,
               site.objectid,
               msc.versionid
        FROM msc
        LEFT JOIN msctype ON msc.msctypeid = msctype.msctypeid
        LEFT JOIN manufacturer manuf ON msctype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON msc.siteid = site.siteid

        UNION ALL

        /* MSS */
        SELECT mss.name,
               'MSS',
               mss.objectid,
               msstype.name,
               manuf.name,
               site.objectid,
               mss.versionid
        FROM mss
        LEFT JOIN msstype ON mss.msstypeid = msstype.msstypeid
        LEFT JOIN manufacturer manuf ON msstype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON mss.siteid = site.siteid

        UNION ALL

        /* MGW */
        SELECT mgw.name,
               'MGW',
               mgw.objectid,
               mgwtype.name,
               manuf.name,
               site.objectid,
               mgw.versionid
        FROM mgw
        LEFT JOIN mgwtype ON mgw.mgwtypeid = mgwtype.mgwtypeid
        LEFT JOIN manufacturer manuf ON mgwtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON mgw.siteid = site.siteid

        UNION ALL

        /* MME */
        SELECT mme.name,
               'MME',
               mme.objectid,
               mmetype.name,
               manuf.name,
               site.objectid,
               mme.versionid
        FROM mme
        LEFT JOIN mmetype ON mme.mmetypeid = mmetype.mmetypeid
        LEFT JOIN manufacturer manuf ON mmetype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON mme.siteid = site.siteid

        UNION ALL

        /* SGW */
        SELECT sgw.name,
               'SGW',
               sgw.objectid,
               sgwtype.name,
               manuf.name,
               site.objectid,
               sgw.versionid
        FROM sgw
        LEFT JOIN sgwtype ON sgw.sgwtypeid = sgwtype.sgwtypeid
        LEFT JOIN manufacturer manuf ON sgwtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON sgw.siteid = site.siteid

        UNION ALL

        /* SGSN */
        SELECT sgsn.name,
               'SGSN',
               sgsn.objectid,
               sgsntype.name,
               manuf.name,
               site.objectid,
               sgsn.versionid
        FROM sgsn
        LEFT JOIN sgsntype ON sgsn.sgsntypeid = sgsntype.sgsntypeid
        LEFT JOIN manufacturer manuf ON sgsntype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON sgsn.siteid = site.siteid

        UNION ALL

        /* GGSN */
        SELECT ggsn.name,
               'GGSN',
               ggsn.objectid,
               ggsntype.name,
               manuf.name,
               site.objectid,
               ggsn.versionid
        FROM ggsn
        LEFT JOIN ggsntype ON ggsn.ggsntypeid = ggsntype.ggsntypeid
        LEFT JOIN manufacturer manuf ON ggsntype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON ggsn.siteid = site.siteid

        UNION ALL

        /* HSS */
        SELECT hss.name,
               'HSS',
               hss.objectid,
               hsstype.name,
               manuf.name,
               site.objectid,
               hss.versionid
        FROM hss
        LEFT JOIN hsstype ON hss.hsstypeid = hsstype.hsstypeid
        LEFT JOIN manufacturer manuf ON hsstype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON hss.siteid = site.siteid

        UNION ALL

        /* HLR */
        SELECT hlr.name,
               'HLR',
               hlr.objectid,
               hlrtype.name,
               manuf.name,
               site.objectid,
               hlr.versionid
        FROM hlr
        LEFT JOIN hlrtype ON hlr.hlrtypeid = hlrtype.hlrtypeid
        LEFT JOIN manufacturer manuf ON hlrtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON hlr.siteid = site.siteid

        UNION ALL

        /* PGW */
        SELECT pgw.name,
               'PGW',
               pgw.objectid,
               pgwtype.name,
               manuf.name,
               site.objectid,
               pgw.versionid
        FROM pgw
        LEFT JOIN pgwtype ON pgw.pgwtypeid = pgwtype.pgwtypeid
        LEFT JOIN manufacturer manuf ON pgwtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON pgw.siteid = site.siteid

        UNION ALL

        /* IPCLK */
        SELECT ipclk.name,
               'IPCLK',
               ipclk.objectid,
               ipclktype.name,
               manuf.name,
               site.objectid,
               ipclk.versionid
        FROM ipclk
        LEFT JOIN ipclktype ON ipclk.ipclktypeid = ipclktype.ipclktypeid
        LEFT JOIN manufacturer manuf ON ipclktype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON ipclk.siteid = site.siteid

        UNION ALL

        /* MDU */
        SELECT mdu.name,
               'MDU',
               mdu.objectid,
               mdutype.name,
               manuf.name,
               site.objectid,
               mdu.versionid
        FROM mdu
        LEFT JOIN mdutype ON mdu.mdutypeid = mdutype.mdutypeid
        LEFT JOIN manufacturer manuf ON mdutype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON mdu.siteid = site.siteid

        UNION ALL

        /* MultiTechnologyAccess */
        SELECT multitechnologyaccess.name,
               'MultiTechnologyAccess',
               multitechnologyaccess.objectid,
               multitechnologyaccesstype.name,
               manuf.name,
               site.objectid,
               multitechnologyaccess.versionid
        FROM multitechnologyaccess
        LEFT JOIN multitechnologyaccesstype ON multitechnologyaccess.multitechnologyaccesstypeid = multitechnologyaccesstype.multitechnologyaccesstypeid
        LEFT JOIN manufacturer manuf ON multitechnologyaccesstype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON multitechnologyaccess.siteid = site.siteid

        UNION ALL

        /* IDU */
        SELECT idu.name,
               'IDU',
               idu.objectid,
               idutype.name,
               manuf.name,
               site.objectid,
               idu.versionid
        FROM idu
        LEFT JOIN idutype ON idu.idutypeid = idutype.idutypeid
        LEFT JOIN manufacturer manuf ON idutype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON idu.siteid = site.siteid

        UNION ALL

        /* WDM */
        SELECT wdm.name,
               'WDM',
               wdm.objectid,
               wdmtype.name,
               manuf.name,
               site.objectid,
               wdm.versionid
        FROM wdm
        LEFT JOIN wdmtype ON wdm.wdmtypeid = wdmtype.wdmtypeid
        LEFT JOIN manufacturer manuf ON wdmtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON wdm.siteid = site.siteid

        UNION ALL

        /* ADM */
        SELECT adm.name,
               'ADM',
               adm.objectid,
               admtype.name,
               manuf.name,
               site.objectid,
               adm.versionid
        FROM adm
        LEFT JOIN admtype ON adm.admtypeid = admtype.admtypeid
        LEFT JOIN manufacturer manuf ON admtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON adm.siteid = site.siteid

        UNION ALL

        /* Optical Splitter */
        SELECT optical_splitter.name,
               'Optical_Splitter',
               optical_splitter.objectid,
               type.name,
               manuf.name,
               site.objectid,
               optical_splitter.versionid
        FROM optical_splitter
        LEFT JOIN optical_splitter_type type ON optical_splitter.osplitter_type_id = type.id
        LEFT JOIN manufacturer manuf ON type.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON optical_splitter.siteid = site.siteid

        UNION ALL

        /* XDSL Splitter */
        SELECT xdsl_splitter.name,
               'XDSL_SPLITTER',
               xdsl_splitter.objectid,
               type.name,
               manuf.name,
               site.objectid,
               xdsl_splitter.versionid
        FROM xdsl_splitter
        LEFT JOIN xdsl_splitter_type type ON xdsl_splitter.xdsl_type_id = type.id
        LEFT JOIN manufacturer manuf ON type.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON xdsl_splitter.siteid = site.siteid

        UNION ALL

        /* XDSL Box */
        SELECT xdsl_box.name,
               'XDSL_BOX',
               xdsl_box.objectid,
               type.name,
               manuf.name,
               site.objectid,
               xdsl_box.versionid
        FROM xdsl_box
        LEFT JOIN xdsl_box_type type ON xdsl_box.xdslbox_type_id = type.id
        LEFT JOIN manufacturer manuf ON type.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON xdsl_box.siteid = site.siteid

        UNION ALL

        /* Ethernet Switch */
        SELECT ethernet_switch.name,
               'EthernetSwitch',
               ethernet_switch.objectid,
               type.name,
               manuf.name,
               site.objectid,
               ethernet_switch.versionid
        FROM ethernetswitch ethernet_switch
        LEFT JOIN ethernetswitchtype type ON ethernet_switch.ethernetswitchtypeid = type.ethernetswitchtypeid
        LEFT JOIN manufacturer manuf ON type.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON ethernet_switch.siteid = site.siteid

        UNION ALL

        /* ONT */
        SELECT ont.name,
               'ONT',
               ont.objectid,
               onttype.name,
               manuf.name,
               site.objectid,
               ont.versionid
        FROM ont
        LEFT JOIN onttype ON ont.ont_type_id = onttype.onttypeid
        LEFT JOIN manufacturer manuf ON onttype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON ont.siteid = site.siteid

        UNION ALL

        /* gNodeB 
        SELECT gnodeb.name,
               'gNodeB',
               gnodeb.objectid,
               gnodebtype.name,
               manuf.name,
               site.objectid,
               gnodeb.versionid
        FROM gnodeb
        LEFT JOIN gnodebtype ON gnodeb.gnodebtypeid = gnodebtype.gnodebtypeid
        LEFT JOIN manufacturer manuf ON gnodebtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON gnodeb.siteid = site.siteid
        */

        /* ODF */
        SELECT odf.name,
               'ODF',
               odf.objectid,
               odftype.name,
               manuf.name,
               site.objectid,
               odf.versionid
        FROM odf
        LEFT JOIN odftype ON odf.odftypeid = odftype.odftypeid
        LEFT JOIN manufacturer manuf ON odftype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON odf.siteid = site.siteid

        UNION ALL

        /* DXX */
        SELECT dxx.name,
               'DXX',
               dxx.objectid,
               dxxtype.name,
               manuf.name,
               site.objectid,
               dxx.versionid
        FROM dxx
        LEFT JOIN dxxtype ON dxx.dxxtypeid = dxxtype.dxxtypeid
        LEFT JOIN manufacturer manuf ON dxxtype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON dxx.siteid = site.siteid

        UNION ALL

        /* MFS */
        SELECT MFS.name,
               'MFS',
               MFS.objectid,
               mfstype.name,
               manuf.name,
               site.objectid,
               MFS.versionid
        FROM MFS
        LEFT JOIN mfstype ON MFS.MFSTYPEID = mfstype.MFSTYPEID
        LEFT JOIN manufacturer manuf ON mfstype.manufacturerid = manuf.manufacturerid
        LEFT JOIN site ON MFS.siteid = site.siteid


    ) t
    WHERE t.versionid = v.VERSION_ID
);

select * from NE_ELEMENT_VIEW;