namespace TMSDAL.Query.TxSkillUpgrade
{
    public static class TxSkillUpgradeQB
    {
        public const string GET_TXSKILLUPGRADE = @"
            SELECT
                U.TXSKILLUPGRADEID   AS TxSkillUpgradeId,
                U.ENROLLMENTID        AS EnrollmentId,
                TE.EMPLOYEEID         AS EmployeeId,
                EMP.EMPLOYEENAME      AS EmployeeName,
                U.SKILLID             AS SkillId,
                SK.SKILLCODE          AS SkillCode,
                SK.SKILLNAME          AS SkillName,
                U.PRODUCTFAMILYID     AS ProductFamilyId,
                U.MODELID             AS ModelId,
                U.PREVIOUSLEVELNO     AS PreviousLevelNo,
                U.PROPOSEDLEVELNO     AS ProposedLevelNo,
                U.PROPOSEDON          AS ProposedOn,
                U.UPGRADESTATUS       AS UpgradeStatus,
                U.REVIEWEDBYID        AS ReviewedById,
                RVW.EMPLOYEENAME      AS ReviewedByName,
                U.REVIEWEDON          AS ReviewedOn,
                U.UPDATEDPROFILEID    AS UpdatedProfileId,
                U.REJECTIONREASON     AS RejectionReason,
                U.REMARKS             AS Remarks,
                U.VERSION             AS Version,
                U.STATUS              AS Status,
                U.SORTORDER           AS SortOrder,
                U.SOURCETYPE          AS SourceType,
                U.CREATEDBYID         AS CreatedById,
                U.CREATEDON           AS CreatedOn,
                U.MODIFIEDBYID        AS ModifiedById,
                U.MODIFIEDON          AS ModifiedOn,
                U.TENANTID            AS TenantId
            FROM   DBO.TTXSKILLUPGRADE U
            JOIN   DBO.TTRAININGENROLLMENT TE ON TE.ENROLLMENTID = U.ENROLLMENTID
            JOIN   DBO.MEMPLOYEE EMP            ON EMP.EMPLOYEEID  = TE.EMPLOYEEID
            LEFT   JOIN DBO.MSKILL SK           ON SK.SKILLID      = U.SKILLID
            LEFT   JOIN DBO.MEMPLOYEE RVW       ON RVW.EMPLOYEEID  = U.REVIEWEDBYID
            WHERE  U.TXSKILLUPGRADEID = @txskillupgradeid
            AND    U.TENANTID         = @tenantid;";

        public const string GET_TXSKILLUPGRADE_LIST = @"
            SELECT
                U.TXSKILLUPGRADEID   AS TxSkillUpgradeId,
                U.ENROLLMENTID        AS EnrollmentId,
                TE.EMPLOYEEID         AS EmployeeId,
                EMP.EMPLOYEENAME      AS EmployeeName,
                U.SKILLID             AS SkillId,
                SK.SKILLCODE          AS SkillCode,
                SK.SKILLNAME          AS SkillName,
                U.PROPOSEDLEVELNO     AS ProposedLevelNo,
                U.PROPOSEDON          AS ProposedOn,
                U.UPGRADESTATUS       AS UpgradeStatus
            FROM   DBO.TTXSKILLUPGRADE U
            JOIN   DBO.TTRAININGENROLLMENT TE ON TE.ENROLLMENTID = U.ENROLLMENTID
            JOIN   DBO.MEMPLOYEE EMP            ON EMP.EMPLOYEEID  = TE.EMPLOYEEID
            LEFT   JOIN DBO.MSKILL SK           ON SK.SKILLID      = U.SKILLID
            WHERE  U.TENANTID = @tenantid
            AND    (@upgradestatus = -1 OR U.UPGRADESTATUS = @upgradestatus)
            ORDER  BY U.PROPOSEDON DESC;";

        public const string SAVE_TXSKILLUPGRADE = @"
            INSERT INTO DBO.TTXSKILLUPGRADE
            (
                TXSKILLUPGRADEID, ENROLLMENTID, SKILLID, PRODUCTFAMILYID, MODELID,
                PREVIOUSLEVELNO, PROPOSEDLEVELNO, PROPOSEDON, UPGRADESTATUS,
                REVIEWEDBYID, REVIEWEDON, UPDATEDPROFILEID, REJECTIONREASON, REMARKS,
                VERSION, STATUS, SORTORDER, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
            )
            VALUES
            (
                @TxSkillUpgradeId, @EnrollmentId, @SkillId, @ProductFamilyId, @ModelId,
                @PreviousLevelNo, @ProposedLevelNo, @ProposedOn, @UpgradeStatus,
                @ReviewedById, @ReviewedOn, @UpdatedProfileId, @RejectionReason, @Remarks,
                @Version, @Status, @SortOrder, @SourceType,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
            );";

        // Only Proposed/UnderReview can be reviewed.
        public const string REVIEW_TXSKILLUPGRADE = @"
            UPDATE DBO.TTXSKILLUPGRADE
            SET    UPGRADESTATUS   = @UpgradeStatus,
                   REVIEWEDBYID    = @ReviewedById,
                   REVIEWEDON      = @ReviewedOn,
                   REJECTIONREASON = @RejectionReason,
                   REMARKS         = @Remarks,
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = @ModifiedOn
            WHERE  TXSKILLUPGRADEID = @TxSkillUpgradeId
            AND    TENANTID         = @TenantId
            AND    UPGRADESTATUS IN (0,1);";

        public const string GET_TXSKILLUPGRADE_EMPLOYEEID = @"
            SELECT TE.EMPLOYEEID
            FROM   DBO.TTXSKILLUPGRADE U
            JOIN   DBO.TTRAININGENROLLMENT TE ON TE.ENROLLMENTID = U.ENROLLMENTID
            WHERE  U.TXSKILLUPGRADEID = @txskillupgradeid
            AND    U.TENANTID         = @tenantid;";

        public const string GET_EMPLOYEEID_BY_ENROLLMENT = @"
            SELECT EMPLOYEEID
            FROM   DBO.TTRAININGENROLLMENT
            WHERE  ENROLLMENTID = @enrollmentid
            AND    TENANTID     = @tenantid;";

        // Manual create/edit path (SaveTxSkillUpgrade) — a supervisor proposing/editing an
        // upgrade directly, as opposed to ApplySkillMappingAsync's auto-trigger path.
        public const string UPDATE_TXSKILLUPGRADE = @"
            UPDATE DBO.TTXSKILLUPGRADE
            SET    ENROLLMENTID     = @EnrollmentId,
                   SKILLID          = @SkillId,
                   PRODUCTFAMILYID  = @ProductFamilyId,
                   MODELID          = @ModelId,
                   PREVIOUSLEVELNO  = @PreviousLevelNo,
                   PROPOSEDLEVELNO  = @ProposedLevelNo,
                   PROPOSEDON       = @ProposedOn,
                   UPGRADESTATUS    = @UpgradeStatus,
                   REVIEWEDBYID     = @ReviewedById,
                   REVIEWEDON       = @ReviewedOn,
                   UPDATEDPROFILEID = @UpdatedProfileId,
                   REJECTIONREASON  = @RejectionReason,
                   REMARKS          = @Remarks,
                   VERSION          = @Version,
                   STATUS           = @Status,
                   SORTORDER        = @SortOrder,
                   MODIFIEDBYID     = @ModifiedById,
                   MODIFIEDON       = @ModifiedOn
            WHERE  TXSKILLUPGRADEID = @TxSkillUpgradeId
            AND    TENANTID         = @TenantId;";

        // Hard delete — administrative cleanup of an erroneous manual/auto proposal.
        public const string DELETE_TXSKILLUPGRADE = @"
            DELETE FROM DBO.TTXSKILLUPGRADE
            WHERE  TXSKILLUPGRADEID = @TxSkillUpgradeId
            AND    TENANTID         = @TenantId;";

        public const string GET_SELECTLIST_TXSKILLUPGRADE = @"
            SELECT
                U.TXSKILLUPGRADEID  AS TxSkillUpgradeId,
                SK.SKILLNAME        AS SkillName,
                TE.ENROLLMENTCODE   AS EnrollmentCode,
                U.PROPOSEDLEVELNO   AS ProposedLevelNo,
                U.UPGRADESTATUS     AS UpgradeStatus
            FROM   DBO.TTXSKILLUPGRADE U
            LEFT   JOIN DBO.TTRAININGENROLLMENT TE ON TE.ENROLLMENTID = U.ENROLLMENTID
            LEFT   JOIN DBO.MSKILL SK              ON SK.SKILLID      = U.SKILLID
            WHERE  U.TENANTID = @TenantId
            AND    U.STATUS   = 1
            {DYNAMIC_WHERE}
            ORDER  BY SK.SKILLNAME;";
    }
}