CREATE TABLE ENCLOSURE (
    enclosureid        VARCHAR(255) NOT NULL,
    siteid             VARCHAR(255),
    name               VARCHAR(255),
    objectid           VARCHAR(255),
    enclosuretype      VARCHAR(50),
    material           VARCHAR(255),
    iprating           VARCHAR(255),
    maxtraycapacity    INTEGER,
    entrypoint         INTEGER,
    mountingtype       VARCHAR(50),
    versionid          VARCHAR(255),

    insertdate         TIMESTAMP,
    insertedby         VARCHAR(255),
    changedate         TIMESTAMP,
    changedby          VARCHAR(255),
    external_code      VARCHAR(255),
    net_status         VARCHAR(100),
    subnetstatus       VARCHAR(100),
    provisioneddate    TIMESTAMP,

    CONSTRAINT pk_enclosure PRIMARY KEY (enclosureid)
);


CREATE TABLE PATCHCORD (
    patchcordid        VARCHAR(255) NOT NULL,
    siteid             VARCHAR(255),
    name               VARCHAR(255),
    objectid           VARCHAR(255),
    connectortypea     VARCHAR(50),
    connectortypeb     VARCHAR(50),
    fibertypes         INTEGER,
    length             DOUBLE PRECISION,
    color              VARCHAR(255),
    polarity           VARCHAR(50),
    versionid          VARCHAR(255),

    insertdate         TIMESTAMP,
    insertedby         VARCHAR(255),
    changedate         TIMESTAMP,
    changedby          VARCHAR(255),
    external_code      VARCHAR(255),
    net_status         VARCHAR(100),
    subnetstatus       VARCHAR(100),
    provisioneddate    TIMESTAMP,

    CONSTRAINT pk_patchcord PRIMARY KEY (patchcordid)
);


CREATE TABLE OSPINNERDUCT (
    id                 VARCHAR(255) NOT NULL,
    name               VARCHAR(255),
    versionid          VARCHAR(255),
    ductindex          BIGINT,
    diameter           DOUBLE PRECISION,
    osppathwayid       VARCHAR(255),

    insertdate         TIMESTAMP,
    insertedby         VARCHAR(255),
    changedate         TIMESTAMP,
    changedby          VARCHAR(255),
    external_code      VARCHAR(255),
    net_status         VARCHAR(100),
    subnetstatus       VARCHAR(100),
    provisioneddate    TIMESTAMP,

    CONSTRAINT pk_ospinnerduct PRIMARY KEY (id)
);


ALTER TABLE splicemodule 
ADD COLUMN enclosureid VARCHAR(255);

ALTER TABLE splicemodule 
ADD COLUMN sequencenumber INTEGER;

ALTER TABLE splicemodule 
ADD COLUMN capacity INTEGER;

ALTER TABLE splicemodule 
ADD COLUMN splicetype VARCHAR(50);

ALTER TABLE splicemodule 
ADD COLUMN fibertype VARCHAR(50);

ALTER TABLE splicemodule 
ADD COLUMN occupiedslot INTEGER;

ALTER TABLE splicemodule 
ADD COLUMN color VARCHAR(255);


ALTER TABLE splicemodule
ADD CONSTRAINT fk_splicemodule_enclosure
FOREIGN KEY (enclosureid)
REFERENCES enclosure(enclosureid)
ON DELETE CASCADE;




----------------------------------------------------View------------------------------------------------------------------------

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,
    s.latitude,
    s.longitude,
    s.objectid,
    nep.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,
    SITE.objectid,
    nep.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
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
   FROM splicemodule smodule
     LEFT JOIN site s ON s.siteid::text = smodule.siteid::text
     LEFT JOIN nepstatus nep ON nep.id::text = smodule.net_status::text
     LEFT JOIN splicemoduletype smodulet ON smodulet.id::text = smodule.splicemoduletypeid::text
     LEFT JOIN manufacturer manuf ON smodulet.manufacturerid::text = manuf.manufacturerid::text
     LEFT JOIN enclosure enc ON enc.enclosureid::text = smodule.enclosureid::text;

COMMIT;