DROP VIEW IF EXISTS HL7.TRGD;
CREATE VIEW HL7.TRGD AS
    SELECT
        T.SRC,
        T.SEQ,
        0 AS ROW,
        T.GRP,
        T.GRPCRC,
        T.CHLD,
        T.CHLDCRC
    FROM (
        SELECT H.SRC,
            H.SEQ,
            TRG.GRP,
            TRG.GRPCRC,
            COUNT(DISTINCT H.CRC) AS SCORE,
            TRG.MessageStructureId AS CHLD,
            TRG.MessageStructureIdCRC AS CHLDCRC
        FROM HL7 AS H
        INNER JOIN TRG
            ON H.CRC IN (TRG.MessageTypeCRC, TRG.MessageTriggerCRC)
        WHERE H.ROW = 0
        AND  H.COL = 8
        GROUP BY H.SRC, H.SEQ, TRG.GRP
    ) AS T
    WHERE T.SCORE = 2;



DROP VIEW IF EXISTS HL7.GRPD;
CREATE VIEW HL7.GRPD AS
    SELECT
        H.SRC,
        H.SEQ,
        H.ROW,
        GRP.GRP,
        GRP.GRPCRC,
        GRP.HNT AS CHLD,
        GRP.HNTCRC AS CHLDCRC
    FROM HL7 AS H
    INNER JOIN GRP
        ON H.CRC = GRP.SEGCRC
    WHERE H.COL = 0
    AND H.ROW != 0
    GROUP BY
        H.SRC,
        H.SEQ,
        H.ROW,
        GRP.GRPCRC;

DROP VIEW IF EXISTS HL7.SSRT;
CREATE VIEW HL7.SSRT AS
SELECT 
    H.SRC, 
    H.SEQ, 
    H.ROW, 
    H.REP, 
    S.VAL||H.COL||'.'||H.COM||'.'||H.SUB AS FLD, 
    H.VAL
FROM HL7 AS H
INNER JOIN HL7 AS S
    ON S.COL = 0 
    AND S.SRC = H.SRC 
    AND S.SEQ = H.SEQ 
    AND S.ROW = H.ROW 
    AND S.REP = H.REP
WHERE H.COL > 0;

DROP VIEW IF EXISTS HL7.TGDD;
CREATE VIEW HL7.TGDD AS
SELECT
    D.SRC,
    D.SEQ,
    D.ROW,
    GROUP_CONCAT(DISTINCT D.GRP) AS GRPS,
    GROUP_CONCAT(DISTINCT D.CHLD) AS CHLDS
FROM (
    SELECT * FROM TRGD
    UNION
    SELECT * FROM GRPD
) AS D
GROUP BY 
    D.SRC,
    D.SEQ,
    D.ROW;



DROP VIEW IF EXISTS HL7.PID;
CREATE VIEW HL7.PID AS
SELECT 
        S.SRC, 
        S.SEQ, 
        S.ROW,
        H.COL,
        H.REP,
        H.VAL AS ExternalCode,
        A.VAL AS Authority,
        P.VAL AS Prout,
        F.VAL AS Facility
    FROM HL7 AS S
    INNER JOIN HL7 AS H
        ON H.SRC = S.SRC
        AND H.SEQ = S.SEQ
        AND H.ROW = S.ROW
        AND H.COL IN (3,4)
        AND H.COM = 0
        AND H.SUB = 0
    LEFT JOIN HL7 AS F
        ON  F.SRC = H.SRC
        AND F.SEQ = H.SEQ
        AND F.ROW = H.ROW
        AND F.COL = H.COL
        AND F.REP = H.REP
        AND F.COM = 3
        AND F.SUB = 0
    LEFT JOIN HL7 AS A
        ON A.SRC = H.SRC
        AND A.SEQ = H.SEQ
        AND A.ROW = H.ROW
        AND A.COL = H.COL
        AND A.REP = H.REP
        AND A.COM = 4
        AND A.SUB = 0
    LEFT JOIN HL7 AS P
        ON  P.SRC = H.SRC
        AND P.SEQ = H.SEQ
        AND P.ROW = H.ROW
        AND P.COL = H.COL
        AND P.REP = H.REP
        AND P.COM = 5
        AND P.SUB = 0
    WHERE S.COL = 0
    AND S.VAL = 'PID';
    
DROP VIEW IF EXISTS HL7.MSH;
CREATE VIEW HL7.MSH AS
SELECT
    H.SRC,
    H.SEQ,
    substr(D.VAL||'000000',0,14) AS DateOfMessage,
    T.VAL AS MessageType,
    TR.VAL AS MessageTrigger,
    I.VAL AS MessageControlId,
    F.VAL AS SendingFacility
FROM HL7 AS H
    INNER JOIN HL7 AS D
        ON D.SRC = H.SRC
        AND D.SEQ = H.SEQ
        AND D.ROW = 0
        AND D.COL = 6
        AND D.REP = 0
        AND D.COM = 0
        AND D.SUB = 0
    LEFT JOIN HL7 AS I
        ON I.SRC = H.SRC
        AND I.SEQ = H.SEQ
        AND I.ROW = 0
        AND I.COL = 9
        AND I.REP = 0
        AND I.COM = 0
        AND I.SUB = 0
    LEFT JOIN HL7 AS T
        ON T.SRC = H.SRC
        AND T.SEQ = H.SEQ
        AND T.ROW = 0
        AND T.COL = 8
        AND T.REP = 0
        AND T.COM = 0
        AND T.SUB = 0
    LEFT JOIN HL7 AS TR
        ON TR.SRC = H.SRC
        AND TR.SEQ = H.SEQ
        AND TR.ROW = 0
        AND TR.COL = 8
        AND TR.REP = 0
        AND TR.COM = 1
        AND TR.SUB = 0
    LEFT JOIN HL7 AS F
        ON F.SRC = H.SRC
        AND F.SEQ = H.SEQ
        AND F.ROW = 0
        AND F.COL = 3
        AND F.REP = 0
        AND F.COM = 0
        AND F.SUB = 0
WHERE H.ROW = 0
AND H.COL = 0
;


CREATE VIEW HL7.RNG AS
SELECT BITERRR.I + BITERR.I + BITER.I + BITE.I AS I 
FROM 
    (SELECT 0 AS I UNION SELECT 8) AS BITERRR,
    (SELECT 0 AS I UNION SELECT 4) AS BITERR,
    (SELECT 0 AS I UNION SELECT 2) AS BITER,
    (SELECT 0 AS I UNION SELECT 1) AS BITE

;   
DROP VIEW IF EXISTS HL7.HL7D;
CREATE VIEW HL7.HL7D AS
SELECT RTRIM(GROUP_CONCAT(VAL,'|'),'|') AS VAL,SRC,SEQ,ROW
FROM (
    SELECT RTRIM(GROUP_CONCAT(VAL,'~'),'~') AS VAL,SRC,SEQ,ROW,COL
    FROM (
        SELECT RTRIM(GROUP_CONCAT(VAL,'^'),'^') AS VAL,SRC,SEQ,ROW,COL,REP
        FROM (
            SELECT RTRIM(GROUP_CONCAT(VAL,'&'),'&') AS VAL,SRC,SEQ,ROW,COL,REP,COM
            FROM (
                SELECT
                    CASE VAL
                    WHEN '&' THEN '^~\&'
                    ELSE VAL
                    END AS VAL,
                    SRC,
                    SEQ,
                    ROW,
                    COL,
                    REP,
                    COM,
                    SUB
                FROM (
                    SELECT
                        H.SRC,
                        H.SEQ,
                        H.ROW,
                        COLS.I AS COL,
                        COALESCE(F.REP,0) AS REP,
                        COMS.I AS COM,
                        SUBS.I AS SUB,
                        COALESCE(F.VAL,'') AS VAL
                    FROM HL7 AS H,
                        RNG AS COLS,
                        RNG AS COMS,
                        RNG AS SUBS
                    LEFT JOIN HL7 AS F 
                        ON F.SRC = H.SRC 
                        AND F.SEQ = H.SEQ 
                        AND F.ROW = H.ROW 
                        AND F.COL = COLS.I 
                        AND F.COM = COMS.I 
                        AND F.SUB = SUBS.I
                    WHERE H.COL = 0 
                )
                ORDER BY COL,REP,COM,SUB
            )
            GROUP BY SRC,SEQ,ROW,COL,REP,COM
        )
        GROUP BY SRC,SEQ,ROW,COL,REP
    )
    GROUP BY SRC,SEQ,ROW,COL
)
GROUP BY SRC,SEQ,ROW
;


DROP VIEW IF EXISTS HL7.LUMED;
CREATE VIEW HL7.LUMED AS
SELECT 
    TSTD.SRC,TSTD.SEQ,
    MSH.DateOfMessage,
    MSH.MessageType||'^'||MSH.MessageTrigger AS MessageType,
    GROUP_CONCAT(PID.ExternalCode, ' ') AS ExternalCodes,
    'public void Test_'||MSH.MessageType||'_'||MSH.MessageTrigger||'_'||PID.ExternalCode||'()\n{\nvar patient = GetPatient("'||PID.EXternalCode||'");\n\n'||TSTD.MESSAGE||'\n}\n' AS TEST
FROM (
    SELECT SRC,SEQ, GROUP_CONCAT(SEGMENT, '\n\n') AS MESSAGE
    FROM (
        SELECT SRC,SEQ,ROW, GROUP_CONCAT(LINE,'\n') AS SEGMENT
        FROM (
            SELECT SRC,SEQ,ROW,LINE
            FROM (
                SELECT SRC, SEQ, ROW, '' AS FLD, '//'||VAL AS LINE FROM HL7D
                UNION
                SELECT SRC, SEQ, ROW, FLD, 'Assert.AreEqual( "'||VAL||'" , "'||FLD||'" );' AS LINE FROM SSRT
            )
            ORDER BY SRC,SEQ,ROW, FLD
        )
        GROUP BY SRC, SEQ, ROW
    )
    GROUP BY SRC,SEQ
) AS TSTD
INNER JOIN MSH ON MSH.SRC = TSTD.SRC AND MSH.SEQ = TSTD.SEQ
INNER JOIN PID ON PID.SRC = TSTD.SRC AND PID.SEQ = TSTD.SEQ
GROUP BY TSTD.SRC,TSTD.SEQ
;
