namespace TMSDAL.Query.TrainingProgrammeRevision
{
    public static class TrainingProgrammeRevisionQB
    {
        // Covered by IX_TTRAININGPROGRAMMEREVISION_PROGRAMMEID_REVISEDON (PROGRAMMEID, REVISEDON DESC)
        public const string GET_REVISION_LIST_BY_PROGRAMME = @"
            SELECT
                R.PROGRAMMEREVISIONID  AS ProgrammeRevisionId,
                R.PROGRAMMEID          AS ProgrammeId,
                R.REVISIONNO           AS RevisionNo,
                R.EFFECTIVEFROM        AS EffectiveFrom,
                R.CHANGESUMMARY        AS ChangeSummary,
                R.REVISEDBYID          AS RevisedById,
                U.USERNAME             AS RevisedByName,
                R.REVISEDON            AS RevisedOn
            FROM   DBO.TTRAININGPROGRAMMEREVISION R
            LEFT   JOIN DBO.MUSER U ON U.USERID = R.REVISEDBYID
            WHERE  R.PROGRAMMEID = @programmeid
            AND    R.TENANTID    = @tenantid
            ORDER  BY R.REVISEDON DESC;";

        public const string GET_REVISION_BY_ID = @"
            SELECT
                R.PROGRAMMEREVISIONID  AS ProgrammeRevisionId,
                R.PROGRAMMEID          AS ProgrammeId,
                P.PROGRAMMECODE        AS ProgrammeCode,
                P.PROGRAMMETITLE       AS ProgrammeTitle,
                R.REVISIONNO           AS RevisionNo,
                R.EFFECTIVEFROM        AS EffectiveFrom,
                R.PROGRAMMESNAPSHOTJSON AS ProgrammeSnapshotJson,
                R.CHANGESUMMARY        AS ChangeSummary,
                R.REVISEDBYID          AS RevisedById,
                U.USERNAME             AS RevisedByName,
                R.REVISEDON            AS RevisedOn,
                R.TENANTID             AS TenantId
            FROM   DBO.TTRAININGPROGRAMMEREVISION R
            JOIN   DBO.MTRAININGPROGRAMME P ON P.PROGRAMMEID = R.PROGRAMMEID
            LEFT   JOIN DBO.MUSER U ON U.USERID = R.REVISEDBYID
            WHERE  R.PROGRAMMEREVISIONID = @programmerevisionid
            AND    R.TENANTID            = @tenantid;";

        public const string SAVE_REVISION = @"
            INSERT INTO DBO.TTRAININGPROGRAMMEREVISION
            (
                PROGRAMMEID, REVISIONNO, EFFECTIVEFROM, PROGRAMMESNAPSHOTJSON,
                CHANGESUMMARY, REVISEDBYID, REVISEDON, TENANTID
            )
            VALUES
            (
                @ProgrammeId, @RevisionNo, @EffectiveFrom, @ProgrammeSnapshotJson,
                @ChangeSummary, @RevisedById, @RevisedOn, @TenantId
            );";
    }
}