DROP TABLE IF EXISTS dbo.IssueHistory_STAGED

CREATE TABLE dbo.IssueHistory_STAGED (
  IssueId AS CAST(JSON_VALUE(RawJson,'$.id') AS INT)
, HistoryRecordId INT NOT NULL IDENTITY
, StagedDateTime DATETIME NOT NULL DEFAULT GETDATE()
, SourceModifiedDateTime AS CAST(LEFT(JSON_VALUE(RawJson,'$.fields.updated'),23) AS DATETIME) 
, RawJson NVARCHAR(MAX)
)

DROP VIEW IF EXISTS dbo.uvw_IssueHistory
DROP VIEW IF EXISTS dbo.uvw_Issue
DROP VIEW IF EXISTS dbo.uvw_IssueComponent
DROP VIEW IF EXISTS dbo.uvw_IssueComment
DROP VIEW IF EXISTS dbo.uvw_IssueLabel

GO

CREATE VIEW dbo.uvw_IssueHistory 
AS

WITH hx AS (
  SELECT
    IssueId
  , RawJson
  , StagedDateTime AS VersionValidFromDateTime
  , COALESCE(
      DATEADD(
        MILLISECOND
      , -3
      , LEAD(StagedDateTime) OVER (
          PARTITION BY IssueId
          ORDER BY 
            StagedDateTime
          , HistoryRecordId
        ) 
      )
    , '9000-12-31'
    ) AS VersionValidToDateTime
  FROM dbo.IssueHistory_STAGED
)
SELECT 
  *
FROM hx
WHERE VersionValidToDateTime >= VersionValidFromDateTime

GO

CREATE VIEW dbo.uvw_Issue
AS

SELECT 
  IssueId
, JSON_VALUE(RawJson,'$.key') AS IssueCode
, CAST(LEFT(JSON_VALUE(RawJson,'$.fields.created'),23) AS DATETIME) AS CreatedDateTime
, JSON_VALUE(RawJson,'$.fields.project.name') AS IssueProject
--, CAST(JSON_VALUE(RawJson,'$.fields.project.id') AS INT) AS IssueProjectId
, JSON_VALUE(RawJson,'$.fields.issuetype.name') AS IssueType
--, CAST(JSON_VALUE(RawJson,'$.fields.issuetype.id') AS INT) AS IssueTypeId
, JSON_VALUE(RawJson,'$.fields.summary') AS Summary
--, JSON_VALUE(RawJson,'$.fields.description') AS [Description]
, JSON_VALUE(RawJson,'$.fields.status.name') AS IssueStatus
--, CAST(JSON_VALUE(RawJson,'$.fields.status.id') AS INT) AS IssueStatusId
, RawJson
FROM dbo.uvw_IssueHistory
WHERE GETDATE() BETWEEN VersionValidFromDateTime AND VersionValidToDateTime
 
GO

CREATE VIEW dbo.uvw_IssueComponent
AS

SELECT 
  src.IssueId
, JSON_VALUE(shred.[value], '$.name') AS ComponentName
--, CAST(JSON_VALUE(shred.[value], '$.id') AS INT) AS ComponentId
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.components')) shred

GO

CREATE VIEW dbo.uvw_IssueComment
AS

SELECT 
  src.IssueId
, JSON_VALUE(shred.[value], '$.body') AS Comment
, CAST(LEFT(JSON_VALUE(shred.[value],'$.created'),23) AS DATETIME) AS CreatedDateTime
, JSON_VALUE(shred.[value], '$.author.name') AS Author
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.comment.comments')) shred

GO

CREATE VIEW dbo.uvw_IssueLabel
AS

SELECT 
  src.IssueId
, shred.[value] AS Label
FROM dbo.uvw_Issue src
  CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.labels')) shred

GO