namespace ECPDAL.Correspondence.QueryBuilders;

public static class CorrespondenceQB
{
    private const string SELECT_COLUMNS = @"
            C.CORRESPONDENCEID       AS CorrespondenceId,
            C.OBJECTTYPEID           AS ObjectTypeId,
            C.OBJECTID               AS ObjectId,
            C.CORRESPONDENCETYPEID   AS CorrespondenceTypeId,
            CT.CODE                  AS CorrespondenceTypeCode,
            CT.NAME                  AS CorrespondenceTypeName,
            C.BIZTRANSACTIONTYPEID   AS BizTransactionTypeId,
            C.DIRECTION              AS Direction,
            C.MODE                   AS Mode,
            C.SUBJECT                AS Subject,
            C.TEMPLATEID             AS TemplateId,
            C.GENERATEDATTACHMENTID  AS GeneratedAttachmentId,
            C.FROMID                 AS FromId,
            C.TOIDS                  AS ToIds,
            C.CCIDS                  AS CcIds,
            C.BCCIDS                 AS BccIds,
            C.SCHEDULEDON            AS ScheduledOn,
            C.SENTON                 AS SentOn,
            C.ACTIONSTATUS           AS ActionStatus,
            C.BIZTRANSACTIONID       AS BizTransactionId,
            C.OUID                   AS OuId,
            C.TENANTID               AS TenantId,
            C.DATABASENAME           AS DatabaseName,
            C.DATABASETYPE           AS DatabaseType,
            C.VERSION                AS Version,
            C.STATUS                 AS Status,
            C.CREATEDBYID            AS CreatedById,
            C.CREATEDON              AS CreatedOn,
            C.MODIFIEDBYID           AS ModifiedById,
            C.MODIFIEDON             AS ModifiedOn";

    // Index: PK (CorrespondenceId)
    public const string GET_CORRESPONDENCE = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TCORRESPONDENCE C
        LEFT JOIN MCORRESPONDENCETYPE CT ON C.CORRESPONDENCETYPEID = CT.CORRESPONDENCETYPEID
        WHERE  C.CORRESPONDENCEID = @CorrespondenceId
          AND  C.DATABASENAME     = @DatabaseName";

    // Index: IX_TCORRESPONDENCE_OBJECTTYPEID_OBJECTID — history listing for an object.
    public const string GET_CORRESPONDENCE_HISTORY = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TCORRESPONDENCE C
        LEFT JOIN MCORRESPONDENCETYPE CT ON C.CORRESPONDENCETYPEID = CT.CORRESPONDENCETYPEID
        WHERE  C.OBJECTTYPEID = @ObjectTypeId
          AND  C.OBJECTID     = @ObjectId
          AND  C.DATABASENAME = @DatabaseName
          AND  C.STATUS       = 1
        ORDER BY C.CREATEDON DESC";

    // Index: IX_TCORRESPONDENCE_DATABASENAME_STATUS_CREATEDON — tenant-wide, paged, filterable
    // list (not scoped to one object). {CRITERIA} filled in by CorrespondenceDAL.ApplyCorrespondenceListCriteria.
    public const string GET_CORRESPONDENCE_LIST = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TCORRESPONDENCE C
        LEFT JOIN MCORRESPONDENCETYPE CT ON C.CORRESPONDENCETYPEID = CT.CORRESPONDENCETYPEID
        WHERE  C.DATABASENAME = @DatabaseName
          AND  C.STATUS       = 1
          {CRITERIA}
        ORDER BY C.CREATEDON DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    // Separate, non-paginated count query for the same filter set (matching CRM TaskDAL's
    // GetSelectListTask_Count precedent) -- NOT QueryPagedAsync, which wraps whatever SQL string
    // it's given in "SELECT COUNT(*) FROM (<sql>)"; if that sql already has OFFSET/FETCH baked
    // in, the resulting count would just be the current page's row count, not the true total.
    public const string GET_CORRESPONDENCE_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   TCORRESPONDENCE C
        WHERE  C.DATABASENAME = @DatabaseName
          AND  C.STATUS       = 1
          {CRITERIA}";

    public const string SAVE_CORRESPONDENCE = @"
        INSERT INTO TCORRESPONDENCE (
            CORRESPONDENCEID, OBJECTTYPEID, OBJECTID, CORRESPONDENCETYPEID, BIZTRANSACTIONTYPEID,
            DIRECTION, MODE, SUBJECT, TEMPLATEID, GENERATEDATTACHMENTID,
            FROMID, TOIDS, CCIDS, BCCIDS, SCHEDULEDON, SENTON, ACTIONSTATUS,
            BIZTRANSACTIONID, OUID, TENANTID, DATABASENAME, DATABASETYPE,
            VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @CorrespondenceId, @ObjectTypeId, @ObjectId, @CorrespondenceTypeId, @BizTransactionTypeId,
            @Direction, @Mode, @Subject, @TemplateId, @GeneratedAttachmentId,
            @FromId, @ToIds, @CcIds, @BccIds, @ScheduledOn, @SentOn, @ActionStatus,
            @BizTransactionId, @OuId, @TenantId, @DatabaseName, @DatabaseType,
            @Version, @Status, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_ACTION_STATUS = @"
        UPDATE TCORRESPONDENCE
        SET    ACTIONSTATUS = @ActionStatus,
               SENTON       = @SentOn,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  CORRESPONDENCEID = @CorrespondenceId
          AND  DATABASENAME     = @DatabaseName";

    // Editable fields only — routing/scheduling details for a not-yet-sent correspondence.
    // Deliberately excludes ObjectTypeId/ObjectId/TemplateId/GeneratedAttachmentId/Direction
    // (identity/generated fields, not user-editable after creation). Optimistic concurrency via
    // VERSION, same convention as SaveSignatory/UpdateSignatory.
    public const string UPDATE_CORRESPONDENCE = @"
        UPDATE TCORRESPONDENCE
        SET    CORRESPONDENCETYPEID = @CorrespondenceTypeId,
               BIZTRANSACTIONTYPEID = @BizTransactionTypeId,
               MODE                 = @Mode,
               SUBJECT              = @Subject,
               FROMID               = @FromId,
               TOIDS                = @ToIds,
               CCIDS                = @CcIds,
               BCCIDS               = @BccIds,
               SCHEDULEDON          = @ScheduledOn,
               BIZTRANSACTIONID     = @BizTransactionId,
               OUID                 = @OuId,
               MODIFIEDBYID         = @ModifiedById,
               MODIFIEDON           = @ModifiedOn,
               VERSION              = VERSION + 1
        WHERE  CORRESPONDENCEID = @CorrespondenceId
          AND  DATABASENAME     = @DatabaseName
          AND  VERSION          = @Version";

    // Soft delete — same STATUS=2 convention as every other M/T table in this codebase.
    public const string DELETE_CORRESPONDENCE = @"
        UPDATE TCORRESPONDENCE
        SET    STATUS       = 2,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  CORRESPONDENCEID = @CorrespondenceId
          AND  DATABASENAME     = @DatabaseName
          AND  STATUS           = 1";
}