---------------------------------------------------------------------------------------------------
-- Corrupted data Case 1
-- Missing lot for non-contracts

SELECT
    i2.id answerId, m.id requirementId, i2.TenderId
        INTO #OrphanAnswers
    FROM requirement m
        INNER JOIN document i1 ON m.documentId=i1.id
        INNER JOIN answer i2 ON m.id = i2.requirementid
    WHERE
        m.lotid IS null         -- orphan requirement
        AND i1.documentType<>2  -- not contract

/*
SELECT * FROM Requirement WHERE id IN (SELECT requirementid FROM #OrphanAnswers)

SELECT top 100 * FROM AnswerRow WHERE answerid in (SELECT answerId FROM #OrphanAnswers)

-- tenders without any other answers
SELECT
    m.tenderId, count(l1.id) cnt
    FROM #orphanAnswers m
        LEFT JOIN answer l1
            ON l1.tenderId=m.tenderId and l1.id<>m.answerId
    GROUP BY m.tenderId
    ORDER BY cnt

-- Check if all answers are from simulated tenders
SELECT * FROM Tender
    WHERE id IN (SELECT distinct tenderId FROM #OrphanAnswers)
        AND IsSimulated=0
*/

DELETE FROM AnswerRow WHERE answerid in (SELECT answerId FROM #OrphanAnswers)
DELETE FROM Answer WHERE id IN (SELECT answerId FROM #OrphanAnswers)

DROP TABLE #OrphanAnswers
---------------------------------------------------------------------------------------------------
-- Corrupted data Case 2
-- Missing tender lot for non-contracts

-- Expected 39
SELECT
    -- top 1000 *
    m.id as answerId INTO #CorruptedSimAnswers
FROM dbo.Answer m
    INNER JOIN Requirement i1 ON i1.Id = m.RequirementId
    INNER JOIN Document i2 ON i1.DocumentId = i2.Id
    INNER JOIN Tender i3 ON m.TenderId = i3.id
WHERE
    NOT EXISTS(select top 1 1 FROM TenderLot where TenderId=m.TenderId and lotid=i1.LotId)
    AND DocumentType<>2
    AND i3.IsSimulated=1

DELETE FROM ObjectFile WHERE answerId in (select AnswerId from #CorruptedSimAnswers)
DELETE FROM AnswerRow where AnswerId in (select AnswerId from #CorruptedSimAnswers)
DELETE FROM Answer where Id in (select AnswerId from #CorruptedSimAnswers)

DROP TABLE #CorruptedSimAnswers

---------------------------------------------------------------------------------------------------
-- Corrupted data Case 3
-- Missing tender lot for non-contracts, Project Id missmatch in Document and Tender

-- Expected 257
SELECT
    -- top 1000 *
    m.id as answerId INTO #ObsoleteAnswers
FROM dbo.Answer m
    INNER JOIN Requirement i1 ON i1.Id = m.RequirementId
    INNER JOIN Document i2 ON i1.DocumentId = i2.Id
    INNER JOIN Tender i3 ON m.TenderId = i3.id
WHERE
    NOT EXISTS(select top 1 1 FROM TenderLot where TenderId=m.TenderId and lotid=i1.LotId)
    AND DocumentType<>2
    AND i2.ProjectId <> i3.ProjectId AND i3.IsSimulated=0       -- 257 not simulated, project differs
    AND len(i3.externalid)=36   -- Contract tender


DELETE FROM AnswerRow where AnswerId in (select AnswerId from #ObsoleteAnswers)
DELETE FROM Answer where Id in (select AnswerId from #ObsoleteAnswers)

DROP TABLE #ObsoleteAnswers


---------------------------------------------------------------------------------------------------
-- Corrupted data Case 4
-- Contract requirements without Lots

-- Expected 64351
UPDATE i2
    SET
        LotId = (SELECT TOP 1 Id FROM lot WHERE ProjectId=i1.ProjectId)
    FROM answer m
        INNER JOIN tender i1 ON m.TenderId=i1.id
        INNER JOIN Requirement i2 ON m.RequirementId=i2.id
        INNER JOIN Document i3 ON i2.DocumentId=i3.id
    WHERE
        LEN(i1.ExternalId)=36
        AND i2.LotId is null
        AND i3.DocumentType=2
        AND EXISTS(SELECT TOP 1 1 FROM lot WHERE ProjectId=i1.ProjectId)

---------------------------------------------------------------------------------------------------
-- Create tender lots for contracts

-- Expected 19214
INSERT INTO TenderLot(
    TenderId, LotId,
    TenderSum, ComparisonSum,
    UnansweredCount, MissedMandatoryCount, RequirementsCount,
    CreatedOn, ModifiedOn,
    [Enabled])
SELECT
    m.id, i1.id,
    null, null,
    0,0,0,
    getutcdate(), getutcdate(),
    1
    FROM tender m
    INNER JOIN lot i1 ON m.ProjectId=i1.ProjectId
        WHERE LEN(m.ExternalId)=36                              -- is contract
            AND EXISTS (SELECT TOP 1 1 FROM Answer sm
                INNER JOIN Requirement si1 ON sm.RequirementId=si1.id
                    WHERE sm.TenderId=m.id AND si1.LotId=i1.Id) -- tender has answers for lot


/*
-- This must return 0 after above queries were executed
SELECT
    count(1)
    -- top 100 *
FROM dbo.Answer A
INNER JOIN Requirement R ON R.Id = A.RequirementId
WHERE
    NOT EXISTS(SELECT TOP 1 1 FROM TenderLot TL WHERE TL.TenderId = A.TenderId AND R.LotId = Tl.LotId)
*/