USE SystemDoc
USE master
DROP DATABASE SystemDoc
CREATE DATABASE SystemDoc

CREATE TABLE NodeTypes
(
    NodeTypeId INT IDENTITY(1, 1),
    Name VARCHAR(200) NOT NULL,
    
    PRIMARY KEY(NodeTypeId)
)


CREATE TABLE Node
(
    NodeId INT IDENTITY(1, 1) NOT NULL,
    NodeTypeId INT NOT NULL,
    Name VARCHAR(200) NOT NULL,
    ParentNodeId INT NULL,

    PRIMARY KEY(NodeId),
    FOREIGN KEY(ParentNodeId) REFERENCES Node(NodeId),
    FOREIGN KEY(NodeTypeId) REFERENCES NodeTypes(NodeTypeId)
    
)

CREATE TABLE NodeInfoTypes
(
    NodeInfoTypeId INT IDENTITY(1, 1),
    NodeInfoType VARCHAR(100),
    
    PRIMARY KEY(NodeInfoTypeId)
)

CREATE TABLE NodeInfo
(
    NodeId INT NOT NULL,
    NodeInfoTypeId INT NOT NULL,
    InfoText VARCHAR(500) NOT NULL,

    PRIMARY KEY(NodeId, NodeInfoTypeId),
    FOREIGN KEY(NodeId) REFERENCES Node(NodeId),
    FOREIGN KEY(NodeInfoTypeId) REFERENCES NodeInfoTypes(NodeInfoTypeId)
)

CREATE TABLE RelationshipTypes
(
    RelationshipTypeId INT IDENTITY(1, 1) NOT NULL,
    RelationShip VARCHAR(100) NOT NULL,

    PRIMARY KEY(RelationshipTypeId)
)

CREATE TABLE NodeRelationship
(
    NodeId INT NOT NULL,
    RelatedToNodeId INT NOT NULL,
    RelationshipTypeId INT,
    Comment VARCHAR(500) NULL,
    
    PRIMARY KEY(NodeId, RelatedToNodeId),
    FOREIGN KEY(NodeId) REFERENCES Node(NodeId),
    FOREIGN KEY(RelatedToNodeId) REFERENCES Node(NodeId),
    FOREIGN KEY(RelationshipTypeId) REFERENCES RelationshipTypes(RelationshipTypeId)
)



INSERT NodeTypes(Name)
SELECT 'Rackskåp' UNION ALL
SELECT 'Physical Computer' UNION ALL
SELECT 'Virtual machine' UNION ALL
SELECT 'Windows 2003 Server' UNION ALL
SELECT 'Windows 2002 Server' UNION ALL
SELECT 'Web server' UNION ALL
SELECT 'Web site' UNION ALL
SELECT 'Web service' UNION ALL
SELECT 'SQL Server' UNION ALL
SELECT 'Database' UNION ALL
SELECT 'Subversion Server' UNION ALL
SELECT 'FTP Server' UNION ALL
SELECT 'Mail Server'


INSERT NodeInfoTypes(NodeInfoType) 
SELECT 'Info' UNION ALL
SELECT 'IP' UNION ALL
SELECT 'More 1' UNION ALL
SELECT 'More 2' UNION ALL
SELECT 'Racknummer' UNION ALL
SELECT 'MAC' union all
select 'File path to Documentation' union all
select 'URL to Web based documentation'


SELECT * FROM node
SELECT * FROM nodeinfo
SELECT * FROM nodeTypes

EXEC dbo.ListAllChildren 5
INNER JOIN NodeTypes


--select * from dbo.SelRecursiveChildren(5)

GO

CREATE FUNCTION dbo.SelRecursiveChildren(@NodeId INT)
RETURNS
@res TABLE (
        NodeId INT
        ,Name VARCHAR(200)
        ,NodeTypeId INT
        ,ParentNodeId INT
)
AS
--alter procedure dbo.ListAllChildren(@NodeId int)
--as
BEGIN 
WITH Nodd AS 
(
    SELECT
         NodeId
        ,Name
        ,NodeTypeId 
        ,ParentNodeId
        
    FROM Node
        WHERE NodeId = @NodeId
    UNION ALL
    SELECT
         n.NodeId
        ,n.Name
        ,n.NodeTypeId 
        ,n.ParentNodeId
        
    FROM Node n
    INNER JOIN Nodd b
        ON n.ParentNodeId = b.NodeId
)
INSERT @res
SELECT * FROM nodd
RETURN
END

-- läser även "relationer" rekursivt
WITH Nodd AS -- varning för cirkulärt rekursiva relationer
(
    SELECT
         n.NodeId
        ,n.Name
        ,n.NodeTypeId 
        ,n.ParentNodeId
        ,nr.RelatedToNodeId as RelatedTo
        ,nr.RelationshipTypeId
        ,nr.Comment
    FROM 
        Node n
    INNER JOIN 
        NodeRelationship nr
        ON nr.NodeId = n.NodeId
    WHERE 
        n.NodeId = 9

    UNION ALL
    SELECT
         nod.NodeId
        ,nod.Name
        ,nod.NodeTypeId 
        ,nod.ParentNodeId
        ,nr.RelatedToNodeId  as RelatedTo
        ,nr.RelationshipTypeId
        ,nr.Comment
    FROM 
        Nodd n
    INNER JOIN 
        Node nod
        ON n.RelatedTo = nod.NodeId
    INNER JOIN
        NodeRelationship nr
        ON nr.NodeId = nod.NodeId
    
)

select * from nodd