namespace TMSDAL.Query.Certificate
{
    public static class CertificateQB
    {
        // =========================
        // GET SINGLE CERTIFICATE
        // =========================
        public const string GET_CERTIFICATE = @"
SELECT
        tc.TRAININGCERTIFICATEID        AS TrainingCertificateId,
        tc.TRAININGCOMPLETIONID         AS TrainingCompletionId,
        tco.TRAININGCOMPLETIONCODE      AS TrainingCompletionCode,
        tco.TRAININGCOMPLETIONNAME      AS TrainingCompletionName,

        tc.ENROLLMENTID                 AS EnrollmentId,
        en.ENROLLMENTCODE               AS EnrollmentCode,
        en.ENROLLMENTNAME               AS EnrollmentName,

        tc.PROGRAMMEID                  AS ProgrammeId,
        tp.PROGRAMMECODE                AS ProgrammeCode,
        tp.PROGRAMMETITLE               AS ProgrammeTitle,

        tc.EMPLOYEEID                   AS EmployeeId,
        e.EMPLOYEECODE                  AS EmployeeCode,
        e.EMPLOYEENAME                  AS EmployeeName,

        tc.CERTIFICATENUMBER            AS CertificateNumber,
        tc.CERTIFICATETITLE             AS CertificateTitle,

        tc.ISSUEDON                     AS IssuedOn,
        tc.ISSUEDBYID                   AS IssuedById,
        isb.EMPLOYEECODE                AS IssuedByCode,
        isb.EMPLOYEENAME                AS IssuedByName,

        tc.EXPIRYDATE                   AS ExpiryDate,
        tc.RENEWALWINDOWDAYS            AS RenewalWindowDays,

        tc.CERTIFICATEURL               AS CertificateUrl,
        tc.EXTERNALCERTREF              AS ExternalCertRef,
        tc.VERIFICATIONTOKEN            AS VerificationToken,
        tc.SIGNATORYID                  AS SignatoryId,
        sig.SIGNATORYNAME               AS SignatoryName,

        tc.REVOKEDREASON                AS RevokedReason,
        tc.REVOKEDON                    AS RevokedOn,
        tc.REVOKEDBYID                  AS RevokedById,
        rvk.EMPLOYEENAME                AS RevokedByName,

        tc.CERTSTATUS                   AS CertStatus,
        tc.VERSION                      AS Version,
        tc.STATUS                       AS Status,
        tc.SORTORDER                    AS SortOrder,

        tc.TENANTID                     AS TenantId,
        tc.SOURCETYPE                   AS SourceType,

        tc.CREATEDBYID                  AS CreatedById,
        tc.CREATEDON                    AS CreatedOn,
        tc.MODIFIEDBYID                 AS ModifiedById,
        tc.MODIFIEDON                   AS ModifiedOn

    FROM DBO.TTRAININGCERTIFICATE tc
    LEFT JOIN DBO.MTRAININGPROGRAMME tp
        ON tp.PROGRAMMEID   = tc.PROGRAMMEID
       AND tp.TENANTID      = tc.TENANTID
    LEFT JOIN DBO.MEMPLOYEE e
        ON e.EMPLOYEEID     = tc.EMPLOYEEID
    LEFT JOIN DBO.TTRAININGCOMPLETION tco
        ON tco.TRAININGCOMPLETIONID = tc.TRAININGCOMPLETIONID
    LEFT JOIN DBO.TTRAININGENROLLMENT en
        ON en.ENROLLMENTID  = tc.ENROLLMENTID
    LEFT JOIN DBO.MEMPLOYEE isb
        ON isb.EMPLOYEEID   = tc.ISSUEDBYID
    LEFT JOIN DBO.MEMPLOYEE rvk
        ON rvk.EMPLOYEEID   = tc.REVOKEDBYID
    LEFT JOIN DBO.MSIGNATORY sig 
        ON sig.SIGNATORYID = tc.SIGNATORYID
            WHERE tc.TRAININGCERTIFICATEID = @TrainingCertificateId
              AND tc.TENANTID = @TenantId";

        // =========================
        // GET BY EMPLOYEE
        // =========================
        public const string GET_CERTIFICATE_LIST_BY_EMPLOYEE = @"
 SELECT
        tc.TRAININGCERTIFICATEID        AS TrainingCertificateId,
        tc.TRAININGCOMPLETIONID         AS TrainingCompletionId,
        tco.TRAININGCOMPLETIONCODE      AS TrainingCompletionCode,
        tco.TRAININGCOMPLETIONNAME      AS TrainingCompletionName,

        tc.ENROLLMENTID                 AS EnrollmentId,
        en.ENROLLMENTCODE               AS EnrollmentCode,
        en.ENROLLMENTNAME               AS EnrollmentName,

        tc.PROGRAMMEID                  AS ProgrammeId,
        tp.PROGRAMMECODE                AS ProgrammeCode,
        tp.PROGRAMMETITLE               AS ProgrammeTitle,

        tc.EMPLOYEEID                   AS EmployeeId,
        e.EMPLOYEECODE                  AS EmployeeCode,
        e.EMPLOYEENAME                  AS EmployeeName,

        tc.CERTIFICATENUMBER            AS CertificateNumber,
        tc.CERTIFICATETITLE             AS CertificateTitle,

        tc.ISSUEDON                     AS IssuedOn,
        tc.ISSUEDBYID                   AS IssuedById,
        isb.EMPLOYEECODE                AS IssuedByCode,
        isb.EMPLOYEENAME                AS IssuedByName,

        tc.EXPIRYDATE                   AS ExpiryDate,
        tc.RENEWALWINDOWDAYS            AS RenewalWindowDays,

        tc.CERTIFICATEURL               AS CertificateUrl,
        tc.EXTERNALCERTREF              AS ExternalCertRef,
        tc.VERIFICATIONTOKEN            AS VerificationToken,
        tc.SIGNATORYID                  AS SignatoryId,

        tc.REVOKEDREASON                AS RevokedReason,
        tc.REVOKEDON                    AS RevokedOn,
        tc.REVOKEDBYID                  AS RevokedById,
        rvk.EMPLOYEENAME                AS RevokedByName,

        tc.CERTSTATUS                   AS CertStatus,
        tc.VERSION                      AS Version,
        tc.STATUS                       AS Status,
        tc.SORTORDER                    AS SortOrder,

        tc.TENANTID                     AS TenantId,
        tc.SOURCETYPE                   AS SourceType,

        tc.CREATEDBYID                  AS CreatedById,
        tc.CREATEDON                    AS CreatedOn,
        tc.MODIFIEDBYID                 AS ModifiedById,
        tc.MODIFIEDON                   AS ModifiedOn

    FROM DBO.TTRAININGCERTIFICATE tc
    LEFT JOIN DBO.MTRAININGPROGRAMME tp
        ON tp.PROGRAMMEID   = tc.PROGRAMMEID
       AND tp.TENANTID      = tc.TENANTID
    LEFT JOIN DBO.MEMPLOYEE e
        ON e.EMPLOYEEID     = tc.EMPLOYEEID
    LEFT JOIN DBO.TTRAININGCOMPLETION tco
        ON tco.TRAININGCOMPLETIONID = tc.TRAININGCOMPLETIONID
    LEFT JOIN DBO.TTRAININGENROLLMENT en
        ON en.ENROLLMENTID  = tc.ENROLLMENTID
    LEFT JOIN DBO.MEMPLOYEE isb
        ON isb.EMPLOYEEID   = tc.ISSUEDBYID
    LEFT JOIN DBO.MEMPLOYEE rvk
        ON rvk.EMPLOYEEID   = tc.REVOKEDBYID
            WHERE tc.TENANTID   = @TenantId
            ORDER BY tc.ISSUEDON DESC";

        // =========================
        // INSERT
        // =========================
        public const string SAVE_CERTIFICATE = @"
            INSERT INTO DBO.TTRAININGCERTIFICATE
            (
                TRAININGCERTIFICATEID,
                TRAININGCOMPLETIONID,
                ENROLLMENTID,
                PROGRAMMEID,
                EMPLOYEEID,

                CERTIFICATENUMBER,
                CERTIFICATETITLE,

                ISSUEDON,
                ISSUEDBYID,
                EXPIRYDATE,
                RENEWALWINDOWDAYS,
                CERTIFICATEURL,
                EXTERNALCERTREF,
                VERIFICATIONTOKEN,
                SIGNATORYID,

                CERTSTATUS,
                VERSION,
                STATUS,
                SORTORDER,

                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES
            (
                @TrainingCertificateId,
                @TrainingCompletionId,
                @EnrollmentId,
                @ProgrammeId,
                @EmployeeId,

                @CertificateNumber,
                @CertificateTitle,

                @IssuedOn,
                @IssuedById,
                @ExpiryDate,
                @RenewalWindowDays,
                @CertificateUrl,
                @ExternalCertRef,
                @VerificationToken,
                @SignatoryId,

                @CertStatus,
                @Version,
                @Status,
                @SortOrder,

                @TenantId,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn,
                @SourceType
            )";

        // =========================
        // UPDATE
        // =========================
        public const string UPDATE_CERTIFICATE = @"
            UPDATE DBO.TTRAININGCERTIFICATE
            SET
                TRAININGCOMPLETIONID  = @TrainingCompletionId,
                ENROLLMENTID          = @EnrollmentId,
                PROGRAMMEID           = @ProgrammeId,
                EMPLOYEEID            = @EmployeeId,

                CERTIFICATENUMBER     = @CertificateNumber,
                CERTIFICATETITLE      = @CertificateTitle,

                ISSUEDON              = @IssuedOn,
                ISSUEDBYID            = @IssuedById,
                EXPIRYDATE            = @ExpiryDate,
                RENEWALWINDOWDAYS     = @RenewalWindowDays,
                CERTIFICATEURL        = @CertificateUrl,
                EXTERNALCERTREF       = @ExternalCertRef,
                VERIFICATIONTOKEN     = @VerificationToken,
                SIGNATORYID           = @SignatoryId,

                CERTSTATUS            = @CertStatus,
                VERSION               = @Version,
                STATUS                = @Status,
                SORTORDER             = @SortOrder,

                MODIFIEDBYID          = @ModifiedById,
                MODIFIEDON            = @ModifiedOn,
                SOURCETYPE            = @SourceType
            WHERE TRAININGCERTIFICATEID = @TrainingCertificateId
              AND TENANTID = @TenantId";

        public const string BULK_INSERT_CERTIFICATE = SAVE_CERTIFICATE;

        public const string BULK_UPDATE_CERTIFICATE = UPDATE_CERTIFICATE;

        // =========================
        // MARK OLD CERT AS EXPIRED (on renewal)
        // =========================
        public const string MARK_CERT_RENEWED = @"
            UPDATE DBO.TTRAININGCERTIFICATE
            SET
                CERTSTATUS   = @CertStatus,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE TRAININGCERTIFICATEID = @TrainingCertificateId
              AND TENANTID = @TenantId";

        // =========================
        // REVOKE
        // =========================
        public const string REVOKE_CERTIFICATE = @"
            UPDATE DBO.TTRAININGCERTIFICATE
SET
    CERTSTATUS    = @CertStatus,
    REVOKEDREASON = @RevokedReason,
    REVOKEDON     = @RevokedOn,
    REVOKEDBYID   = @RevokedById,
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn
WHERE TRAININGCERTIFICATEID = @TrainingCertificateId
  AND TENANTID = @TenantId;";

        // =========================
        // LOG ALERT
        // =========================
        public const string LOG_RENEWAL_ALERT = @"
            INSERT INTO DBO.LCERTRENEWALALERT
            (
                ALERTID,
                TRAININGCERTIFICATEID,
                EMPLOYEEID,
                ALERTTYPE,
                ALERTCHANNEL,
                ALERTSTATUS,
                ALERTSENTON,
                DAYSBEFOREEXPIRY,
                TENANTID
            )
            VALUES
            (
                @AlertId,
                @TrainingCertificateId,
                @EmployeeId,
                @AlertType,
                @AlertChannel,
                @AlertStatus,
                @AlertSentOn,
                @DaysBeforeExpiry,
                @TenantId
            )";

        // =========================
        // SELECT LIST
        // =========================
        public const string GET_SELECTLIST_CERTIFICATE = @"
            SELECT
                tc.TRAININGCERTIFICATEID   AS TrainingCertificateId,
                tc.TRAININGCOMPLETIONID    AS TrainingCompletionId,
                tc.ENROLLMENTID            AS EnrollmentId,
                tc.PROGRAMMEID             AS ProgrammeId,
                tp.PROGRAMMECODE           AS ProgrammeCode,
                tp.PROGRAMMETITLE          AS ProgrammeTitle,
                tc.CERTIFICATETITLE        AS CertificateTitle,
                tc.CERTIFICATENUMBER       AS CertificateNumber,
                tc.EMPLOYEEID              AS EmployeeId,
                e.EMPLOYEECODE             AS EmployeeCode,
                e.EMPLOYEENAME             AS EmployeeName,
                tc.ISSUEDON                AS IssuedOn,
                tc.EXPIRYDATE              AS ExpiryDate,
                tc.CERTSTATUS              AS CertStatus
            FROM DBO.TTRAININGCERTIFICATE tc
            LEFT JOIN DBO.MTRAININGPROGRAMME tp
                ON tp.PROGRAMMEID = tc.PROGRAMMEID
               AND tp.TENANTID    = tc.TENANTID
            LEFT JOIN DBO.MEMPLOYEE e
                ON e.EMPLOYEEID = tc.EMPLOYEEID
            WHERE tc.TENANTID = @TenantId
            ORDER BY e.EMPLOYEENAME, tc.ISSUEDON DESC";

        // =========================
        // VERIFY BY TOKEN — public, no tenant filter (mirrors FlsRespondentDAL.GetByTokenAsync's
        // ClientId=0/UserId=-1 pre-authentication precedent). Token is the sole access-control
        // mechanism. Only the minimal fields the public VerifyCertificate response needs.
        // =========================
        public const string GET_CERTIFICATE_BY_VERIFICATION_TOKEN = @"
            SELECT
                tc.CERTIFICATENUMBER  AS CertificateNumber,
                tc.CERTIFICATETITLE   AS CertificateTitle,
                e.EMPLOYEENAME        AS RecipientName,
                tc.ISSUEDON           AS IssuedOn,
                tc.EXPIRYDATE         AS ExpiryDate,
                tc.CERTSTATUS         AS CertStatus
            FROM   DBO.TTRAININGCERTIFICATE tc
            LEFT JOIN DBO.MEMPLOYEE e ON e.EMPLOYEEID = tc.EMPLOYEEID
            WHERE  tc.VERIFICATIONTOKEN = @VerificationToken";

        public const string SET_CERTIFICATE_VERIFICATION_TOKEN = @"
            UPDATE DBO.TTRAININGCERTIFICATE
            SET    VERIFICATIONTOKEN = @VerificationToken,
                   SIGNATORYID       = @SignatoryId
            WHERE  TRAININGCERTIFICATEID = @TrainingCertificateId
            AND    TENANTID               = @TenantId";

        // =========================
        // SIGNATURE METADATA (TTRAININGCERTIFICATESIGNATURE)
        // =========================
        // Upsert: RegenerateCertificate can re-run the pipeline for a certificate that already
        // has a signature row, and must overwrite it rather than fail on a duplicate key.
        public const string SAVE_CERTIFICATE_SIGNATURE = @"
            DELETE FROM DBO.TTRAININGCERTIFICATESIGNATURE
            WHERE  TRAININGCERTIFICATEID = @TrainingCertificateId
            AND    TENANTID               = @TenantId;

            INSERT INTO DBO.TTRAININGCERTIFICATESIGNATURE
            (
                TRAININGCERTIFICATEID, ATTACHMENTID, PDFSHA256HASH, SIGNATUREBASE64,
                SIGNATUREALGORITHM, SIGNINGTHUMBPRINT, SIGNEDON, TENANTID
            )
            VALUES
            (
                @TrainingCertificateId, @AttachmentId, @PdfSha256Hash, @SignatureBase64,
                @SignatureAlgorithm, @SigningThumbprint, @SignedOn, @TenantId
            );";

        public const string GET_ATTACHMENTID_BY_CERTIFICATE = @"
            SELECT ATTACHMENTID
            FROM   DBO.TTRAININGCERTIFICATESIGNATURE
            WHERE  TRAININGCERTIFICATEID = @TrainingCertificateId
            AND    TENANTID               = @TenantId";

        // Fallback used when TTRAININGCERTIFICATESIGNATURE has no row — the PDF still uploaded
        // successfully even when crypto-signing was skipped (e.g. signing cert not yet
        // provisioned; see CertificatePdfPipeline's non-fatal handling), so the download endpoint
        // must not require a signature row to exist. @ObjectTypeId is
        // EntityConstant.OBJECTTRAININGCERTIFICATE, passed from C# rather than hardcoded here.
        public const string GET_LATEST_ATTACHMENTID_BY_OBJECT = @"
            SELECT TOP 1 ATTACHMENTID
            FROM   DBO.TATTACHMENT
            WHERE  OBJECTTYPEID = @ObjectTypeId
            AND    OBJECTID     = @TrainingCertificateId
            AND    STATUS       = 1
            ORDER  BY CREATEDON DESC";

        // Storage location for a TATTACHMENT row — CMSID/ATTACHMENTOPTION are what
        // IAttachmentStorageResolver needs to fetch the file from wherever it actually lives
        // (S3 or Network), independent of the legacy IFileUploadBLL local-disk-only path.
        public const string GET_ATTACHMENT_STORAGE_INFO = @"
            SELECT
                CMSID            AS CmsId,
                ATTACHMENTOPTION AS AttachmentOption,
                DISPLAYFILENAME  AS DisplayFileName,
                MIMETYPE         AS MimeType
            FROM   DBO.TATTACHMENT
            WHERE  ATTACHMENTID = @AttachmentId
            AND    STATUS       = 1";
    }
}