namespace ECPDAL.Meeting.QueryBuilders;

public static class RecordingTranscriptQB
{
    private const string SELECT_COLUMNS = @"
            RT.RECORDINGTRANSCRIPTID AS RecordingTranscriptId,
            RT.OBJECTTYPEID           AS ObjectTypeId,
            RT.OBJECTID               AS ObjectId,
            RT.RECORDINGATTACHMENTID  AS RecordingAttachmentId,
            RT.TRANSCRIPTTEXT         AS TranscriptText,
            RT.SUMMARYTEXT            AS SummaryText,
            RT.PROCESSINGSTATUS       AS ProcessingStatus,
            RT.FAILUREREASON          AS FailureReason,
            RT.TENANTID               AS TenantId,
            RT.DATABASENAME           AS DatabaseName,
            RT.DATABASETYPE           AS DatabaseType,
            RT.VERSION                AS Version,
            RT.STATUS                 AS Status,
            RT.CREATEDBYID            AS CreatedById,
            RT.CREATEDON              AS CreatedOn,
            RT.MODIFIEDBYID           AS ModifiedById,
            RT.MODIFIEDON             AS ModifiedOn";

    // Index: PK (RecordingTranscriptId)
    public const string GET_RECORDING_TRANSCRIPT = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TRECORDINGTRANSCRIPT RT
        WHERE  RT.RECORDINGTRANSCRIPTID = @RecordingTranscriptId
          AND  RT.DATABASENAME          = @DatabaseName";

    // Index: IX_TRECORDINGTRANSCRIPT_OBJECTTYPEID_OBJECTID
    public const string GET_RECORDING_TRANSCRIPTS_FOR_OBJECT = @"
        SELECT" + SELECT_COLUMNS + @"
        FROM   TRECORDINGTRANSCRIPT RT
        WHERE  RT.OBJECTTYPEID  = @ObjectTypeId
          AND  RT.OBJECTID      = @ObjectId
          AND  RT.STATUS        = 1
          AND  RT.DATABASENAME  = @DatabaseName
        ORDER BY RT.CREATEDON DESC";

    public const string SAVE_RECORDING_TRANSCRIPT = @"
        INSERT INTO TRECORDINGTRANSCRIPT (
            RECORDINGTRANSCRIPTID, OBJECTTYPEID, OBJECTID, RECORDINGATTACHMENTID,
            TRANSCRIPTTEXT, SUMMARYTEXT, PROCESSINGSTATUS, FAILUREREASON,
            TENANTID, DATABASENAME, DATABASETYPE, VERSION, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @RecordingTranscriptId, @ObjectTypeId, @ObjectId, @RecordingAttachmentId,
            @TranscriptText, @SummaryText, @ProcessingStatus, @FailureReason,
            @TenantId, @DatabaseName, @DatabaseType, @Version, @Status,
            @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
        )";

    public const string UPDATE_TRANSCRIBED = @"
        UPDATE TRECORDINGTRANSCRIPT
        SET    TRANSCRIPTTEXT   = @TranscriptText,
               PROCESSINGSTATUS = 2,
               FAILUREREASON    = NULL,
               MODIFIEDBYID     = @ModifiedById,
               MODIFIEDON       = @ModifiedOn,
               VERSION          = VERSION + 1
        WHERE  RECORDINGTRANSCRIPTID = @RecordingTranscriptId
          AND  DATABASENAME          = @DatabaseName";

    public const string UPDATE_SUMMARIZED = @"
        UPDATE TRECORDINGTRANSCRIPT
        SET    SUMMARYTEXT      = @SummaryText,
               PROCESSINGSTATUS = 3,
               FAILUREREASON    = NULL,
               MODIFIEDBYID     = @ModifiedById,
               MODIFIEDON       = @ModifiedOn,
               VERSION          = VERSION + 1
        WHERE  RECORDINGTRANSCRIPTID = @RecordingTranscriptId
          AND  DATABASENAME          = @DatabaseName";

    // Called when IAICapabilityService throws (e.g. NotConfiguredAICapabilityService's
    // InvalidOperationException) — the endpoint/BLL catches that exception and marks the row
    // failed with a diagnosable reason instead of letting it bubble as an unhandled 500.
    // Soft-delete (STATUS=2) — same row-level Status column already used by GET_RECORDING_TRANSCRIPTS_FOR_OBJECT's filter.
    public const string DELETE_RECORDING_TRANSCRIPT = @"
        UPDATE TRECORDINGTRANSCRIPT
        SET    STATUS       = 2,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = @ModifiedOn,
               VERSION      = VERSION + 1
        WHERE  RECORDINGTRANSCRIPTID = @RecordingTranscriptId
          AND  DATABASENAME          = @DatabaseName";

    public const string UPDATE_FAILED = @"
        UPDATE TRECORDINGTRANSCRIPT
        SET    PROCESSINGSTATUS = 4,
               FAILUREREASON    = @FailureReason,
               MODIFIEDBYID     = @ModifiedById,
               MODIFIEDON       = @ModifiedOn,
               VERSION          = VERSION + 1
        WHERE  RECORDINGTRANSCRIPTID = @RecordingTranscriptId
          AND  DATABASENAME          = @DatabaseName";
}