namespace TMSDAL.Query.CertRenewalAlert
{
    /// <summary>
    /// Required indexes:
    ///   IX_LCERTRENEWALALERT_TRAININGCERTIFICATEID on (TRAININGCERTIFICATEID)
    ///   IX_LCERTRENEWALALERT_ALERTSTATUS on (ALERTSTATUS)
    ///   IX_LCERTRENEWALALERT_SCHEDULEDAT on (SCHEDULEDAT)
    /// Note: LOG table — no UPDATE or DELETE queries.
    /// </summary>
    public static class CertRenewalAlertQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_CERTRENEWALALERT = @"
SELECT  ca.LCERTRENEWALALERTID      AS LCertRenewalAlertId,
        ca.TRAININGCERTIFICATEID    AS TrainingCertificateId,
        tc.CERTIFICATENUMBER        AS CertificateNumber,
        tc.CERTIFICATETITLE         AS CertificateTitle,
        ca.ALERTTYPE                AS AlertType,
        ca.DAYSBEFOREEXPIRY         AS DaysBeforeExpiry,
        ca.SCHEDULEDAT              AS ScheduledAt,
        ca.SENTAT                   AS SentAt,
        ca.RECIPIENTTYPE            AS RecipientType,
        ca.ALERTCHANNEL             AS AlertChannel,
        ca.ALERTSTATUS              AS AlertStatus,
        ca.ACKNOWLEDGEDAT           AS AcknowledgedAt,
        ca.CREATEDBYID              AS CreatedById,
        ca.CREATEDON                AS CreatedOn
FROM    DBO.LCERTRENEWALALERT ca
INNER JOIN DBO.TTRAININGCERTIFICATE tc
        ON tc.TRAININGCERTIFICATEID = ca.TRAININGCERTIFICATEID
WHERE   ca.LCERTRENEWALALERTID = @LCertRenewalAlertId";


        // =====================================================
        // GET LIST BY CERTIFICATE
        // =====================================================
        public const string GET_CERTRENEWALALERT_LIST = @"
SELECT  ca.LCERTRENEWALALERTID      AS LCertRenewalAlertId,
        ca.TRAININGCERTIFICATEID    AS TrainingCertificateId,
        tc.CERTIFICATENUMBER        AS CertificateNumber,
        tc.CERTIFICATETITLE         AS CertificateTitle,
        ca.ALERTTYPE                AS AlertType,
        ca.DAYSBEFOREEXPIRY         AS DaysBeforeExpiry,
        ca.SCHEDULEDAT              AS ScheduledAt,
        ca.SENTAT                   AS SentAt,
        ca.RECIPIENTTYPE            AS RecipientType,
        ca.ALERTCHANNEL             AS AlertChannel,
        ca.ALERTSTATUS              AS AlertStatus,
        ca.ACKNOWLEDGEDAT           AS AcknowledgedAt,
        ca.CREATEDBYID              AS CreatedById,
        ca.CREATEDON                AS CreatedOn
FROM    DBO.LCERTRENEWALALERT ca
INNER JOIN DBO.TTRAININGCERTIFICATE tc
        ON tc.TRAININGCERTIFICATEID = ca.TRAININGCERTIFICATEID
WHERE   ca.TRAININGCERTIFICATEID = @TrainingCertificateId
ORDER BY ca.SCHEDULEDAT DESC";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_CERTRENEWALALERT = @"
SELECT  ca.LCERTRENEWALALERTID      AS LCertRenewalAlertId,
        ca.TRAININGCERTIFICATEID    AS TrainingCertificateId,
        tc.CERTIFICATENUMBER        AS CertificateNumber,
        tc.CERTIFICATETITLE         AS CertificateTitle,
        ca.ALERTTYPE                AS AlertType,
        ca.ALERTSTATUS              AS AlertStatus,
        ca.SCHEDULEDAT              AS ScheduledAt,
        ca.SENTAT                   AS SentAt
FROM    DBO.LCERTRENEWALALERT ca
INNER JOIN DBO.TTRAININGCERTIFICATE tc
        ON tc.TRAININGCERTIFICATEID = ca.TRAININGCERTIFICATEID
WHERE   1 = 1
{DYNAMIC_WHERE}
ORDER BY ca.SCHEDULEDAT DESC";


        // =====================================================
        // SAVE — LOG table, IDENTITY PK, no UPDATE/DELETE
        // =====================================================
        public const string SAVE_CERTRENEWALALERT = @"
INSERT INTO DBO.LCERTRENEWALALERT
(
    TRAININGCERTIFICATEID,
    ALERTTYPE,
    DAYSBEFOREEXPIRY,
    SCHEDULEDAT,
    SENTAT,
    RECIPIENTTYPE,
    ALERTCHANNEL,
    ALERTSTATUS,
    ACKNOWLEDGEDAT,
    CREATEDBYID,
    CREATEDON
)
VALUES
(
    @TrainingCertificateId,
    @AlertType,
    @DaysBeforeExpiry,
    @ScheduledAt,
    @SentAt,
    @RecipientType,
    @AlertChannel,
    @AlertStatus,
    @AcknowledgedAt,
    @CreatedById,
    @CreatedOn
)";
    }
}