CREATE TABLE ENCLOSURE (
    EnclosureId        VARCHAR2(255) NOT NULL,
    SiteID             VARCHAR2(255),
    Name               VARCHAR2(255),
    ObjectID           VARCHAR2(255),
    EnclosureType      VARCHAR2(50),
    Material           VARCHAR2(255),
    IpRating           VARCHAR2(255),
    MaxTrayCapacity    INTEGER,
    EntryPoint         INTEGER,
    MountingType       VARCHAR2(50),
	versionId		   VARCHAR2(255),

    INSERTDATE         DATE,
    INSERTEDBY         VARCHAR2(255),
    CHANGEDATE         DATE,
    CHANGEDBY          VARCHAR2(255),
    EXTERNAL_CODE      VARCHAR2(255),
    NET_STATUS         VARCHAR2(100),
    SUBNETSTATUS       VARCHAR2(100),
    PROVISIONEDDATE    DATE,

    CONSTRAINT PK_ENCLOSURE PRIMARY KEY (EnclosureId)
);


CREATE TABLE PATCHCORD (
    PatchCordId        VARCHAR2(255) NOT NULL,
    SiteID             VARCHAR2(255),
    Name               VARCHAR2(255),
    ObjectId           VARCHAR2(255),
    ConnectorTypeA     VARCHAR2(50),
    ConnectorTypeB     VARCHAR2(50),
    FiberTypes         INTEGER,
    Length             FLOAT,
    Color              VARCHAR2(255),
    Polarity           VARCHAR2(50),
	versionId		   VARCHAR2(255),

    INSERTDATE         DATE,
    INSERTEDBY         VARCHAR2(255),
    CHANGEDATE         DATE,
    CHANGEDBY          VARCHAR2(255),
    EXTERNAL_CODE      VARCHAR2(255),
    NET_STATUS         VARCHAR2(100),
    SUBNETSTATUS       VARCHAR2(100),
    PROVISIONEDDATE    DATE,

    CONSTRAINT PK_PATCHCORD PRIMARY KEY (PatchCordId)
);


CREATE TABLE OSPINNERDUCT (
    ID             	   VARCHAR2(255)   NOT NULL,
    NAME           	   VARCHAR2(255),
	VERSIONID          VARCHAR2(255),
    DUCTINDEX     	   NUMBER(20),
    DIAMETER		   FLOAT,	
    OSPPATHWAYID   	   VARCHAR2(255),
	
	INSERTDATE         DATE,
    INSERTEDBY         VARCHAR2(255),
    CHANGEDATE         DATE,
    CHANGEDBY          VARCHAR2(255),
    EXTERNAL_CODE      VARCHAR2(255),
    NET_STATUS         VARCHAR2(100),
    SUBNETSTATUS       VARCHAR2(100),
    PROVISIONEDDATE    DATE,

    CONSTRAINT PK_OSPINNERDUCT PRIMARY KEY (ID)
);

ALTER TABLE SPLICEMODULE ADD EnclosureId VARCHAR(255);
ALTER TABLE SPLICEMODULE ADD SequenceNumber INTEGER;
ALTER TABLE SPLICEMODULE ADD Capacity INTEGER;
ALTER TABLE SPLICEMODULE ADD SpliceType VARCHAR2(50);
ALTER TABLE SPLICEMODULE ADD FiberType VARCHAR2(50);
ALTER TABLE SPLICEMODULE ADD OccupiedSlot INTEGER;
ALTER TABLE SPLICEMODULE ADD Color VARCHAR(255);

ALTER TABLE SPLICEMODULE
ADD CONSTRAINT FK_SPLICEMODULE_ENCLOSURE
FOREIGN KEY (EnclosureId)
REFERENCES ENCLOSURE(EnclosureId)
ON DELETE CASCADE;


COMMIT;

CREATE OR REPLACE VIEW UCMDB_ENCLOSURE_VW ("ENCLOSUREID", "SITEID", "NAME", "OBJECTID", "ENCLOSURETYPE", "MATERIAL", "IPRATING", "MAXTRAYCAPACITY", "ENTRYPOINT", "MOUNTINGTYPE", "VERSIONID", "INSERTDATE", "INSERTEDBY", "CHANGEDATE", "CHANGEDBY", "EXTERNAL_CODE", "NET_STATUS", "SUBNETSTATUS", "PROVISIONEDDATE", "SITE_NAME", "LATITUDE", "LONGITUDE", "SITE_OBJECTID", "STATUS_NAME") AS 
  select 
    en."ENCLOSUREID",en."SITEID",en."NAME",en."OBJECTID",en."ENCLOSURETYPE",en."MATERIAL",en."IPRATING",en."MAXTRAYCAPACITY",en."ENTRYPOINT",en."MOUNTINGTYPE",en."VERSIONID",en."INSERTDATE",en."INSERTEDBY",en."CHANGEDATE",en."CHANGEDBY",en."EXTERNAL_CODE",en."NET_STATUS",en."SUBNETSTATUS",en."PROVISIONEDDATE",
    s.name as site_name,
    s.latitude,
    s.longitude,
    s.objectid AS site_objectid,
    nep.name AS status_name
    
    from Enclosure en
    LEFT JOIN site s ON s.siteid = en.siteid
    LEFT JOIN NepStatus nep ON nep.id = en.Net_Status;


 CREATE OR REPLACE VIEW UCMDB_PATCH_CORD_VW ("PATCHCORDID", "SITEID", "NAME", "OBJECTID", "CONNECTORTYPEA", "CONNECTORTYPEB", "FIBERTYPES", "LENGTH", "COLOR", "POLARITY", "VERSIONID", "INSERTDATE", "INSERTEDBY", "CHANGEDATE", "CHANGEDBY", "EXTERNAL_CODE", "NET_STATUS", "SUBNETSTATUS", "PROVISIONEDDATE", "SITE_NAME", "SITE_OBJECTID", "STATUS_NAME") AS 
  select
       	pCoard."PATCHCORDID",pCoard."SITEID",pCoard."NAME",pCoard."OBJECTID",pCoard."CONNECTORTYPEA",pCoard."CONNECTORTYPEB",pCoard."FIBERTYPES",pCoard."LENGTH",pCoard."COLOR",pCoard."POLARITY",pCoard."VERSIONID",pCoard."INSERTDATE",pCoard."INSERTEDBY",pCoard."CHANGEDATE",pCoard."CHANGEDBY",pCoard."EXTERNAL_CODE",pCoard."NET_STATUS",pCoard."SUBNETSTATUS",pCoard."PROVISIONEDDATE",
        SITE.name      as SITE_NAME,
        SITE.ObjectID     as SITE_ObjectID,
          nep.name AS status_name
    from
        PATCHCORD        pCoard
        left join SITE   SITE on SITE.siteid = pCoard.siteid
         LEFT JOIN NepStatus nep ON nep.id = pCoard.Net_Status;


  CREATE OR REPLACE VIEW SPLICE_MODULES_VW ("ID", "NAME", "SITEID", "SPLICEMODULETYPEID", "INSERTDATE", "CHANGEDATE", "INSERTEDBY", "CHANGEDBY", "EXTERNAL_CODE", "NET_STATUS", "PROVISIONEDDATE", "SHELF_INDEX", "NBOFSPLICEENCLOSURES", "VERSIONID", "SUBNETSTATUS", "ENCLOSUREID", "SEQUENCENUMBER", "CAPACITY", "SPLICETYPE", "FIBERTYPE", "OCCUPIEDSLOT", "COLOR", "SITE_OBJECTID", "SITE_NAME", "NET_STATUS_NAME", "SMODULE_TYPE", "MANUFACTURER_NAME", "ENCLOSURE_NAME", "ENCLOSURE_OBJECTID") AS 
  SELECT
  smodule."ID",smodule."NAME",smodule."SITEID",smodule."SPLICEMODULETYPEID",smodule."INSERTDATE",smodule."CHANGEDATE",smodule."INSERTEDBY",smodule."CHANGEDBY",smodule."EXTERNAL_CODE",smodule."NET_STATUS",smodule."PROVISIONEDDATE",smodule."SHELF_INDEX",smodule."NBOFSPLICEENCLOSURES",smodule."VERSIONID",smodule."SUBNETSTATUS",smodule."ENCLOSUREID",smodule."SEQUENCENUMBER",smodule."CAPACITY",smodule."SPLICETYPE",smodule."FIBERTYPE",smodule."OCCUPIEDSLOT",smodule."COLOR",
  s.objectid AS site_objectid,
  s.name AS site_name,
  nep.name AS net_status_name,
  smodulet.name AS smodule_type,
  manuf.name AS manufacturer_name,
  enc.name AS enclosure_name,
  enc.OBJECTID as enclosure_objectid

FROM SPLICEMODULE smodule
LEFT JOIN SITE s ON s.siteid = smodule.siteid
LEFT JOIN NEPSTATUS nep ON nep.id = smodule.net_status
LEFT JOIN SPLICEMODULETYPE smodulet ON smodulet.id = smodule.splicemoduletypeid
LEFT JOIN MANUFACTURER manuf ON smodulet.manufacturerid = manuf.manufacturerid
LEFT JOIN ENCLOSURE enc ON enc.enclosureid = smodule.enclosureid;

commit;
