namespace DXPDAL.Kyc;

// Required indexes:
//   MDXPPARTYKYCPROFILE: (DXPPARTYBRANCHLINKID, STATUS), (GSTINHASH)
//
// DBMS-neutral: plain ANSI SQL — see Party/PartyQB.cs header for the rationale.
public static class PartyKycQB
{
    public const string GET_BY_ID = @"
SELECT K.DXPPARTYKYCPROFILEID  AS DxpPartyKycProfileId,
       K.DXPPARTYBRANCHLINKID   AS DxpPartyBranchLinkId,
       K.GSTINENCRYPTED          AS GstinEncrypted,
       K.GSTINHASH               AS GstinHash,
       K.PANENCRYPTED            AS PanEncrypted,
       K.PANHASH                 AS PanHash,
       K.VERIFICATIONSTATUS      AS VerificationStatus,
       K.VERIFIEDON              AS VerifiedOn,
       K.VERIFIEDBYID            AS VerifiedById,
       K.REJECTIONREASON         AS RejectionReason,
       K.ROWGUID                 AS RowGuid,
       K.VERSION                 AS Version,
       K.STATUS                  AS Status,
       K.CREATEDBYID             AS CreatedById,
       K.CREATEDON               AS CreatedOn,
       K.MODIFIEDBYID            AS ModifiedById,
       K.MODIFIEDON              AS ModifiedOn
FROM   MDXPPARTYKYCPROFILE K
WHERE  K.DXPPARTYKYCPROFILEID = @DxpPartyKycProfileId";

    public const string GET_ACTIVE_BY_BRANCH_LINK = @"
SELECT K.DXPPARTYKYCPROFILEID  AS DxpPartyKycProfileId,
       K.DXPPARTYBRANCHLINKID   AS DxpPartyBranchLinkId,
       K.GSTINENCRYPTED          AS GstinEncrypted,
       K.GSTINHASH               AS GstinHash,
       K.PANENCRYPTED            AS PanEncrypted,
       K.PANHASH                 AS PanHash,
       K.VERIFICATIONSTATUS      AS VerificationStatus,
       K.VERIFIEDON              AS VerifiedOn,
       K.VERIFIEDBYID            AS VerifiedById,
       K.REJECTIONREASON         AS RejectionReason,
       K.ROWGUID                 AS RowGuid,
       K.VERSION                 AS Version,
       K.STATUS                  AS Status,
       K.CREATEDBYID             AS CreatedById,
       K.CREATEDON               AS CreatedOn,
       K.MODIFIEDBYID            AS ModifiedById,
       K.MODIFIEDON              AS ModifiedOn
FROM   MDXPPARTYKYCPROFILE K
WHERE  K.DXPPARTYBRANCHLINKID = @DxpPartyBranchLinkId
  AND  K.STATUS = 1";

    public const string INSERT = @"
INSERT INTO MDXPPARTYKYCPROFILE
    (DXPPARTYKYCPROFILEID, DXPPARTYBRANCHLINKID, GSTINENCRYPTED, GSTINHASH, PANENCRYPTED, PANHASH,
     VERIFICATIONSTATUS, ROWGUID,
     VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@DxpPartyKycProfileId, @DxpPartyBranchLinkId, @GstinEncrypted, @GstinHash, @PanEncrypted, @PanHash,
     @VerificationStatus, @RowGuid,
     @Version, @Status, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

    // Superseding an existing profile on re-submission — never overwrite the audit trail.
    public const string SUPERSEDE = @"
UPDATE MDXPPARTYKYCPROFILE SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  DXPPARTYKYCPROFILEID = @DxpPartyKycProfileId";

    public const string VERIFY = @"
UPDATE MDXPPARTYKYCPROFILE SET
    VERIFICATIONSTATUS = @VerificationStatus,
    VERIFIEDON          = @VerifiedOn,
    VERIFIEDBYID         = @VerifiedById,
    REJECTIONREASON      = @RejectionReason,
    MODIFIEDBYID         = @ModifiedById,
    MODIFIEDON           = @ModifiedOn
WHERE  DXPPARTYKYCPROFILEID = @DxpPartyKycProfileId";
}