namespace AutomationDAL.Query.Script;

public static class ScriptQB
{
    public const string INSERT = @"
        INSERT INTO AUTO.TSCRIPT
               (SCRIPTNAME, DESCRIPTION, CATEGORY, RUNTIMETYPE, CRITICALITY, OWNERUSERID,
                ISACTIVE, TENANTID, VERSION, CREATEDBYID, CREATEDON)
        VALUES (@ScriptName, @Description, @Category, @RuntimeType, @Criticality, @OwnerUserId,
                1, @TenantId, 1, @CreatedById, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE = @"
        UPDATE AUTO.TSCRIPT
        SET    SCRIPTNAME   = @ScriptName,
               DESCRIPTION  = @Description,
               CATEGORY     = @Category,
               RUNTIMETYPE  = @RuntimeType,
               CRITICALITY  = @Criticality,
               OWNERUSERID  = @OwnerUserId,
               ISACTIVE     = @IsActive,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SCRIPTID = @ScriptId
        AND    TENANTID = @TenantId";

    // Covering index required: IX_TSCRIPT_TENANTID (TENANTID, ISACTIVE)
    public const string GET_BY_ID = @"
        SELECT SCRIPTID, SCRIPTNAME, DESCRIPTION, CATEGORY, RUNTIMETYPE, CRITICALITY,
               OWNERUSERID, ISACTIVE, TENANTID, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        FROM   AUTO.TSCRIPT
        WHERE  SCRIPTID = @ScriptId
        AND    TENANTID = @TenantId";

    public const string GET_CATALOG = @"
        SELECT SCRIPTID, SCRIPTNAME, CATEGORY, RUNTIMETYPE, CRITICALITY, ISACTIVE, OWNERUSERID
        FROM   AUTO.TSCRIPT
        WHERE  TENANTID = @TenantId
        AND    (@Category IS NULL OR CATEGORY = @Category)
        AND    (@SearchText IS NULL OR SCRIPTNAME LIKE '%' + @SearchText + '%')
        ORDER BY SCRIPTNAME";
}